Pipeline Aggregations, Moving Functions, Derivatives, Bucket Scripts, and Time-Series Computation

Learning outcomes

AtlasMart wants “daily revenue change,” a two-day moving revenue signal and average revenue per order. Pipeline aggregations compute from aggregation output rather than directly from raw documents. That distinction matters: a pipeline can reduce or transform the final response, but upstream buckets and metrics still had to be collected. A beautiful bucket_sort size:10 at the end does not make an unbounded upstream tree cheap.

1. Pipeline aggregations consume bucket outputs

A metric such as daily revenue reads document values. A pipeline aggregation such as a derivative reads the daily revenue values already produced by the parent histogram. Parent pipelines write a result into each bucket; sibling pipelines summarize across a sibling multi-bucket aggregation. The buckets_path is therefore a path through aggregation results, not a field path in _source.

DELETE atlasmart-orders-agg-v1
PUT atlasmart-orders-agg-v1
{
  "settings": {"number_of_shards": 2, "number_of_replicas": 0},
  "mappings": {
    "properties": {
      "order_id":    {"type":"keyword"},
      "customer_id": {"type":"keyword"},
      "order_date":  {"type":"date"},
      "region":      {"type":"keyword"},
      "channel":     {"type":"keyword"},
      "status":      {"type":"keyword"},
      "total":       {"type":"double"},
      "latency_ms":  {"type":"long"},
      "store":       {"properties":{"location":{"type":"geo_point"}}},
      "items": {
        "type":"nested",
        "properties": {
          "sku":{"type":"keyword"},
          "category":{"type":"keyword"},
          "qty":{"type":"integer"},
          "line_total":{"type":"double"}
        }
      }
    }
  }
}

POST atlasmart-orders-agg-v1/_bulk?refresh=true
{ "index": { "_id": "o1" } }
{ "order_id":"o1","customer_id":"c1","order_date":"2026-09-01T10:00:00Z","region":"north","channel":"web","status":"paid","total":120.0,"latency_ms":80,"store":{"location":{"lat":35.72,"lon":51.41}},"items":[{"sku":"p1","category":"audio","qty":1,"line_total":100.0},{"sku":"p5","category":"office","qty":1,"line_total":20.0}] }
{ "index": { "_id": "o2" } }
{ "order_id":"o2","customer_id":"c2","order_date":"2026-09-01T12:00:00Z","region":"south","channel":"mobile","status":"paid","total":80.0,"latency_ms":160,"store":{"location":{"lat":29.61,"lon":52.53}},"items":[{"sku":"p3","category":"audio","qty":1,"line_total":80.0}] }
{ "index": { "_id": "o3" } }
{ "order_id":"o3","customer_id":"c1","order_date":"2026-09-02T08:30:00Z","region":"north","channel":"web","status":"refunded","total":60.0,"latency_ms":250,"store":{"location":{"lat":35.69,"lon":51.39}},"items":[{"sku":"p6","category":"sports","qty":1,"line_total":60.0}] }
{ "index": { "_id": "o4" } }
{ "order_id":"o4","customer_id":"c3","order_date":"2026-09-02T13:20:00Z","region":"west","channel":"store","status":"paid","total":200.0,"latency_ms":110,"store":{"location":{"lat":34.80,"lon":48.51}},"items":[{"sku":"p8","category":"audio","qty":1,"line_total":160.0},{"sku":"p7","category":"accessories","qty":2,"line_total":40.0}] }
{ "index": { "_id": "o5" } }
{ "order_id":"o5","customer_id":"c4","order_date":"2026-09-03T09:15:00Z","region":"north","channel":"mobile","status":"paid","total":150.0,"latency_ms":95,"store":{"location":{"lat":36.26,"lon":59.62}},"items":[{"sku":"p4","category":"audio","qty":1,"line_total":130.0},{"sku":"p5","category":"office","qty":1,"line_total":20.0}] }
{ "index": { "_id": "o6" } }
{ "order_id":"o6","customer_id":"c2","order_date":"2026-09-03T18:40:00Z","region":"south","channel":"web","status":"cancelled","total":40.0,"latency_ms":310,"store":{"location":{"lat":31.90,"lon":54.36}},"items":[{"sku":"p7","category":"accessories","qty":2,"line_total":40.0}] }

2. Build the ordered time-series parent first

GET atlasmart-orders-agg-v1/_search
{
  "size":0,
  "aggs": {
    "daily": {
      "date_histogram": {
        "field":"order_date",
        "calendar_interval":"day",
        "min_doc_count":0
      },
      "aggs": {
        "revenue":{"sum":{"field":"total"}},
        "orders":{"value_count":{"field":"order_id"}},
        "revenue_per_order": {
          "bucket_script": {
            "buckets_path":{"r":"revenue","n":"orders"},
            "script":"params.n == 0 ? 0 : params.r / params.n"
          }
        },
        "revenue_change": {
          "derivative":{"buckets_path":"revenue"}
        },
        "moving_revenue": {
          "moving_fn": {
            "buckets_path":"revenue",
            "window":2,
            "script":"MovingFunctions.unweightedAvg(values)"
          }
        }
      }
    }
  }
}

Inspect the raw daily.revenue and daily.orders values before trusting any pipeline output. For this fixture the daily totals are hand-computable. A derivative needs an ordered series; the first bucket has no previous value. A moving window near the beginning contains fewer historical values unless the implementation/window semantics say otherwise.

3. buckets_path is part of the program

Portable concept: parent histogram/date_histogram -> numeric metric -> pipeline over bucket output.

Verify exact syntax on BOTH targets before freezing a lesson/application:
- derivative: buckets_path, gap_policy, optional unit/normalization behavior
- bucket_script: variable map + script language/context
- moving_fn: script functions/window/shift support
- bucket_sort/bucket_selector: post-collection behavior and limits

Do not assume every pipeline option or script helper is identical merely because the aggregation name exists in both products.

Rename an aggregation in a large request and downstream bucket paths can break. Treat aggregation names as stable internal API identifiers, not decorative labels. Unit-test the request body and validate returned paths in integration tests.

4. Gap policy is business semantics

An empty day can mean “zero orders” or “missing telemetry,” and those are not the same. min_doc_count:0 can materialize empty histogram buckets when bounds/range support it; pipeline gap_policy determines how missing metric values are handled. insert_zeros is only correct when zero is the intended meaning. Otherwise it can manufacture a false drop and distort moving averages/derivatives.

Day 1 revenue = 100
Day 2 missing because ingestion failed
Day 3 revenue = 120

If Day 2 is interpreted as zero:
  derivative Day2 = -100
  derivative Day3 = +120
That tells a false business story.

If Day 2 truly had zero orders, those derivatives may be correct.
The pipeline API cannot infer which story is true; ingestion/data-quality semantics must decide.

5. Scripts need bounded inputs and stable ownership

bucket_script is appropriate for simple derived KPIs such as revenue/order when numerator and denominator already exist. It should not become an unrestricted user programming surface. Keep scripts static/versioned, pass data as parameters, handle division by zero, constrain who may submit scripts, and measure CPU/latency. If a KPI is used constantly at large scale, precomputation may be cheaper and easier to govern.

"revenue_per_order": {
  "bucket_script": {
    "buckets_path": {"r":"revenue", "n":"orders"},
    "script": "params.n == 0 ? null : params.r / params.n"
  }
}

6. Late truncation does not erase upstream cost

ANTI-PATTERN:
1. Create 1-second buckets for a 90-day range.
2. Inside each bucket create 1,000 category buckets.
3. Add several scripted pipeline calculations.
4. Return the entire tree to a browser.

This can create millions of buckets before pipeline post-processing.
A later bucket_sort does NOT refund the work already spent creating upstream buckets.

Pipeline aggregations run after their inputs exist. bucket_sort can reduce what is returned, but parent buckets still had to be built. Control time range, histogram interval, terms cardinality and subaggregation depth before pipeline processing. Monitor the request circuit breaker and search rejection/latency signals; do not raise limits simply to make an unbounded dashboard succeed.

7. Independent oracle for the fixture

UTC day        orders    revenue    revenue/order
2026-09-01       2        200           100
2026-09-02       2        260           130
2026-09-03       2        190            95

First derivative of revenue:
2026-09-01: undefined/no previous bucket
2026-09-02: +60
2026-09-03: -70

Two-day trailing unweighted average (check exact window semantics on target):
expected values must be derived from the returned daily revenue series,
not copied from this note without verifying product/version behavior.

Production judgment

Use pipeline aggregations for bounded interactive derivations, not as a substitute for a time-series/warehouse architecture when the request needs millions of buckets or complex analytics. Version scripts and request schemas, measure upstream bucket count and heap pressure, separate missing data from zero, and preserve raw metrics so derived KPIs remain auditable.

Summary and next step

Pipeline aggregations are programs over bucket output. The final lesson combines the chapter into a production-style faceted analytics request with explicit limits, approximation contracts, response-size budgets and an exhaustive composite export path.

Authoritative references

• Elastic aggregations overview — Official aggregation concepts and API entry point.
• Elastic terms aggregation — Shard candidate collection, shard_size and document-count error behavior.
• Elastic cardinality aggregation — HyperLogLog++ approximation and precision_threshold tradeoffs.
• Elastic percentiles aggregation — Approximate percentile calculation and algorithm controls.
• Elastic composite aggregation — Deterministic bucket pagination with after_key and early-termination guidance.
• Elastic pipeline aggregations — Pipeline categories, bucket paths, derivatives and scripts.
• OpenSearch aggregations overview — Metric, bucket and pipeline aggregation structure and resource considerations.
• OpenSearch terms aggregation — Terms size/shard_size behavior and warnings about inaccurate ascending-count ordering.
• OpenSearch cardinality aggregation — Approximate distinct counts, precision_threshold and collector behavior.
• OpenSearch percentile aggregation — Approximate percentiles and TDigest controls.
• OpenSearch composite aggregation — Composite sources and after-key pagination.
• OpenSearch pipeline aggregations — Supported pipeline aggregations including moving_fn, derivative and bucket_script.
• OpenSearch derivative — Derivative semantics, bucket paths and gap policy.
• OpenSearch moving_fn — Sliding-window functions, scripts, windows and shift.
• OpenSearch bucket_script — Multi-metric derived KPI scripts and gap handling.