# LEAD/LAG window function in elasticsearch

**URL:** <https://discuss.elastic.co/t/lead-lag-window-function-in-elasticsearch/332866>\
**Category:** Kibana\
**Tags:** painless, kql-kibana-query-language\
**Created:** [May 9, 2023, 5:56am UTC](https://discuss.elastic.co/t/lead-lag-window-function-in-elasticsearch/332866 "2023-05-09T05:56:38Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sanjana\_Saswade](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sanjana_saswade/32/120771_2.png) [@Sanjana\_Saswade](https://discuss.elastic.co/u/Sanjana_Saswade)\
**Post date:** [May 9, 2023, 5:56am UTC](https://discuss.elastic.co/t/lead-lag-window-function-in-elasticsearch/332866/1 "2023-05-09T05:56:38Z")

</div>

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:

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

```

---

<div class="post-metadata">

**Author:** ![PRASHANT\_MEHTA](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/prashant_mehta/32/101764_2.png) [@PRASHANT\_MEHTA](https://discuss.elastic.co/u/PRASHANT_MEHTA)\
**Post date:** [May 9, 2023, 6:47am UTC](https://discuss.elastic.co/t/lead-lag-window-function-in-elasticsearch/332866/2 "2023-05-09T06:47:59Z")

</div>

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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![Sanjana\_Saswade](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sanjana_saswade/32/120771_2.png) [@Sanjana\_Saswade](https://discuss.elastic.co/u/Sanjana_Saswade)\
**Post date:** [May 9, 2023, 9:30am UTC](https://discuss.elastic.co/t/lead-lag-window-function-in-elasticsearch/332866/3 "2023-05-09T09:30:44Z")

</div>

> [@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?

---

<div class="post-metadata">

**Author:** ![PRASHANT\_MEHTA](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/prashant_mehta/32/101764_2.png) [@PRASHANT\_MEHTA](https://discuss.elastic.co/u/PRASHANT_MEHTA)\
**Post date:** [May 9, 2023, 10:02am UTC](https://discuss.elastic.co/t/lead-lag-window-function-in-elasticsearch/332866/4 "2023-05-09T10:02:15Z")

</div>

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

```auto
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"
              }
            }
          }
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![Sanjana\_Saswade](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sanjana_saswade/32/120771_2.png) [@Sanjana\_Saswade](https://discuss.elastic.co/u/Sanjana_Saswade)\
**Post date:** [May 9, 2023, 2:47pm UTC](https://discuss.elastic.co/t/lead-lag-window-function-in-elasticsearch/332866/5 "2023-05-09T14:47:33Z")

</div>

thanks @PRASHANT_MEHTA but getting below error:

````auto
  "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
````

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [June 7, 2023, 8:36am UTC](https://discuss.elastic.co/t/lead-lag-window-function-in-elasticsearch/332866/7 "2023-06-07T08:36:33Z")

</div>

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