Subtract numeric fields between two documents with different timestamp

Elastic Stack Elasticsearch
1

Hello,

Lets say I have these data samples:

{
    "date": "2019-06-16",
    "rank": 150
    "name": "doc 1"
}

{
    "date": "2019-07-16",
    "rank": 100
    "name": "doc 1"
}

{
    "date": "2019-06-16",
    "rank": 50
    "name": "doc 2"
}

{
    "date": "2019-07-16",
    "rank": 80
    "name": "doc 2"
}

The expected result is by subtracting the rank field from two same name of docs with different date (old date - new date):

{
    "name": "doc 1",
    "diff_rank": 50
}

{
    "name": "doc 2",
    "diff_rank": -30
}

And sort by diff_rank if possible, otherwise I will just sort manually after getting the result.

What I have tried is by using date_histogram and serial_diff but some results are missing the diff_rank value in somehow which I am sure the data exist:

{
   "aggs" : {
        "group_by_name": {
            "terms": {
                "field": "name"
            },
            "aggs": {
                "days": {
                    "date_histogram": {
                        "field": "date",
                        "interval": "day"
                     },
                    "aggs": {
                        "the_rank": {
                            "sum": {
                                "field": "rank"
                            }
                        },
                        "diff_rank": {
                           "serial_diff": {
                              "buckets_path": "the_rank",
                              "lag" : 30 // 1 month or 30 days in this case
                           }
                        }
                    }
                }
            }
        }
    }
}

The help will be much appreciated to solve my issue above!

2

I found my own answer and posted it to https://stackoverflow.com/questions/57053482/subtract-numeric-fields-between-two-documents-with-different-timestamp/57120784#57120784, just in case someone else has similar problem.

1 Like
3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.