LEAD/LAG window function in elasticsearch

[Sanjana_Saswade]

I need to use LEAD /LAG function in elasticsearch. i tried collapse in ELK but it's not working. How do I fix this?
sql query for convert in Elasticsearch query:

select name, LAG(salary) OVER(PARTITION BY department
                              ORDER BY date) AS prev_sal 
from employee;

[PRASHANT_MEHTA]

Hi,

I'm not sure if this helps you.
Try to do something with derivative aggregation in elastic query.

In Elasticsearch, you can use the derivative aggregation to calculate the difference between consecutive values of a field, similar to the LAG and LEAD window functions in SQL. The derivative aggregation calculates the rate of change between consecutive documents within a bucket.

For example, let's say you have an index of sales data with the following fields: date, productand sales . To calculate the difference between the sales values for each product on consecutive days:

POST /sales/_search
{
  "aggs": {
    "product_sales": {
      "terms": {
        "field": "product"
      },
      "aggs": {
        "daily_sales": {
          "date_histogram": {
            "field": "date",
            "interval": "day"
          },
          "aggs": {
            "sales_diff": {
              "derivative": {
                "buckets_path": "sales"
              }
            }
          }
        }
      }
    }
  }
}

Not sure if this helps you!! Derivative agg might help you.

[Sanjana_Saswade]

@PRASHANT_MEHTA

sales

hi @PRASHANT_MEHTA ,

Thanks for your solution but its not work properly In derivative aggregation we need to pass bucket path but we didn't mention any path can you please explain me how we can do that or else is there any way to use painless script for LEAD/LAG function?

[PRASHANT_MEHTA]

Hi,

Try this if this helps,lead/Lag is not native to elasticsearch I suppose.
This is the nearest I can think of.
The buckets_path parameter takes a map where each key represents a name that can be used to reference the output of an aggregation. The value of each key is a string that defines the path to the output of the referenced aggregation

GET /employee/_search
{
  "size": 0,
  "aggs": {
    "departments": {
      "terms": {
        "field": "department"
      },
      "aggs": {
        "employees": {
          "top_hits": {
            "sort": [
              {
                "date": {
                  "order": "asc"
                }
              }
            ],
            "_source": {
              "includes": [
                "name",
                "salary",
                "date"
              ]
            },
            "size": 10
          },
          "aggs": {
            "prev_sal": {
              "bucket_selector": {
                "buckets_path": {
                  "prev_sal": "_source.salary",
                  "prev_date": "_source.date"
                },
                "script": "doc['date'].value > prev_date"
              }
            }
          }
        }
      }
    }
  }
}

[Sanjana_Saswade]

thanks @PRASHANT_MEHTA but getting below error:

  "error" : {
    "root_cause" : [
      {
        "type" : "aggregation_initialization_exception",
        "reason" : "Aggregator [employees] of type [top_hits] cannot accept sub-aggregations"
      }
    ],
    "type" : "aggregation_initialization_exception",
    "reason" : "Aggregator [employees] of type [top_hits] cannot accept sub-aggregations"
  },
  "status" : 500
}```

also i tried to change the employees with collapse but does not work