Elasticsearch window functions with SQL

The missing OVER clause#

A window function numbers, ranks or compares rows relative to their neighbours: the three newest organisations per country, the gap between one founding year and the previous one, a running total. In Snowflake or Postgres that is one OVER (PARTITION BY … ORDER BY …) clause. Elasticsearch works in documents and aggregation buckets, so the nearest tools are a top_hits aggregation for "top N per group" and a bucket_sort pipeline for "top N groups", and nothing at all for LAG. The cluster's own SQL does not add an OVER clause either.

FlatQL splits the statement. What the cluster can evaluate exactly is sent as Query DSL; the window itself runs in the embedded DuckDB engine on the rows that came back.

ROW_NUMBER, RANK and LAG in one statement#

The three most recently founded active organisations in every country, with their rank and the founding year of the organisation ranked just above them:

SELECT name, country, founded_year,
       ROW_NUMBER() OVER (PARTITION BY country ORDER BY founded_year DESC) AS rn,
       RANK()       OVER (PARTITION BY country ORDER BY founded_year DESC) AS rnk,
       LAG(founded_year) OVER (PARTITION BY country ORDER BY founded_year DESC) AS previous_year
FROM organizations
WHERE status = 'active'
  AND founded_year >= 2015
QUALIFY rn <= 3
ORDER BY country, rn;

QUALIFY filters on the window result the way HAVING filters on an aggregate, so there is no wrapping subquery. RANK leaves gaps after ties and ROW_NUMBER does not, which is why the filter is on rn: exactly three rows per country come back even when two organisations share a founding year. See the glossary entry for the clause and the CTEs, UNION and HAVING page for the long form if you prefer it.

What the cluster receives#

The two WHERE conjuncts compare a field with a literal, so both are pushed. The window, QUALIFY and the final ORDER BY cannot be, and the engine's rule explains why the sort stays behind: "LIMIT / ORDER BY: pushed only for single-table statements with no aggregation, DISTINCT, window or QUALIFY, and only when the whole WHERE was pushed." The request is a POST /organizations/_search?scroll=2m, paged by 5,000 and limited to the four fields the statement touches:

{
  "query": {
    "bool": {
      "filter": [
        { "term": { "status": "active" } },
        { "range": { "founded_year": { "gte": 2015 } } }
      ]
    }
  },
  "_source": ["country", "founded_year", "name", "status"],
  "size": 5000,
  "sort": ["_doc"],
  "track_total_hits": true
}

The result panel lists WHERE under "pushed to cluster" and ROW_NUMBER, RANK, LAG, QUALIFY and ORDER BY under "ran in engine", and the DSL tab shows the body above. If status were a text field with a keyword sub-field, the term would target status.keyword; in SQL you still write status.

Where GROUP BY fits#

Windows are often applied to aggregated rows: rank countries by how many organisations they have. On its own, a statement such as SELECT country, COUNT(*) AS orgs FROM organizations WHERE founded_year >= 2015 GROUP BY country is pushed to the cluster as a composite aggregation: size: 0, the range filter, and a composite source on country paged with after_key until every bucket is in. The rule, from the engine: "GROUP BY: pushed as a composite aggregation when every grouping key is a plain groupable field, every aggregate is COUNT / SUM / AVG / MIN / MAX (plus approximate COUNT DISTINCT) on a plain field, and the WHERE was pushed in full."

Adding a window to that same SELECT changes the plan. The aggregation planner only produces a composite plan for a single SELECT with no window, QUALIFY or DISTINCT, so the index is scanned with the pushed filters and both the GROUP BY and the ranking run in the engine. The result says so in its notes. For large indices, run the grouped statement first, check the bucket count, then add the window; or keep the ranking on the small result with a filter that the cluster applies.

The GROUP BY page covers the aggregation side on its own.

Reading the plan before you trust it#

A window function is only as good as the rows it sees, which is why FlatQL prints the plan with every result rather than hiding it. The engine works on at most about 50,000 rows per index per statement. A window over a whole index of two million documents is a window over the first 50,000 the scroll returned, and FlatQL will say "only the first 50,000 were scanned" in the response. The fix is always the same: a predicate on a keyword, numeric, date or boolean field that the cluster can apply, so the window sees the complete population you meant. PARTITION BY does not reduce the fetch; WHERE does. The pushdown rules list every predicate shape that travels.

Without FlatQL#

top_hits inside a terms aggregation. Group by country.keyword, sort the hits inside each bucket by founded_year descending and set size: 3. This is the Query DSL form of ROW_NUMBER() … <= 3. It works well for small N, returns nested hit arrays rather than rows, and offers no rank ties and no LAG.

bucket_sort pipeline aggregation. Sort the buckets of a parent aggregation and keep the first N. It answers "top five countries by count" and is the closest thing to RANK over groups, with the ranking implied by position. It cannot look at the previous bucket's value.

scripted_metric aggregation. Painless scripts that run per shard and combine on the coordinating node can compute almost anything, including differences between consecutive values once you sort inside the script. They are code, they run on the cluster's heap, and they are hard to review.

Client code. Pull the filtered documents with search_after or a point-in-time, load them into pandas or a DataFrame library and call rank, shift or cumsum. This is the honest equivalent of what FlatQL does, built per analysis.

Each has a place. top_hits belongs in a dashboard panel that refreshes every minute; a script belongs in a pipeline you control. For an analyst writing the question for the first time, SQL with an OVER clause is the shortest path, and FlatQL vs Kibana and FlatQL vs Elasticsearch SQL set out where each tool stops.

Run a window on your data#

Window functions and QUALIFY are in the Free plan with one cluster and one worksheet; see pricing for the Pro licence that adds exports and unlimited clusters. Register, connect your cluster, paste the statement and look at the DSL tab.