window_sum

Definition

A window aggregation function that returns the sum of rows in a range relative to the current row.

Syntax

window_sum(agg_expr)
window_sum(agg_expr, order: order_expr, ...)
window_sum(agg_expr, range, order: order_expr, ...)
window_sum(agg_expr, range, order: order_expr, ..., reset: partition_expr, ...)
window_sum(agg_expr, range, order: order_expr, ..., partition: partition_expr, ...)

Examples

window_sum(count(users.id))
window_sum(count(users.id), order: count(users.id) | desc())
window_sum(count(users.id), -2..2, order: users.created_at | month())
window_sum(count(users.id), order: users.created_at | month(), reset: users.gender)

// Axis-aware examples
window_sum(revenue, order: 'rows')                        // Running sum across rows
window_sum(revenue, order: 'columns', partition: 'rows')  // Running sum within each row
window_sum(revenue, order: 'x_axis' | desc())             // Running sum in reverse row order
window_sum(revenue, order: 'legend', partition: 'x_axis') // Running sum within each column

Input

• agg_expr (required): An aggregation expression to be summed.
• range (optional): A range of rows to include in the sum. Negative values indicate rows before the current row, and positive values indicate rows after the current row, while 0 indicates the current row. If the beginning or end of the range is not specified, the range will include all rows from the beginning or end of the table. By default, if the range is not specified:
  • If order is specified, the range is ..0 (from the first row to the current row).
  • If order is not specified, the range is .. (from the first row to the last row).
• order (required, repeatable): A field that is used for ordering. The order defaults to ascending. The order can be set explicitly with asc() or desc(). You can also use axis references:
  • 'rows' or 'x_axis': Order by dimensions mapped to rows/x-axis
  • 'columns' or 'legend': Order by dimensions mapped to columns/legend
  • Axis references can be modified with asc() or desc(): order: 'rows' | desc()
  warning

  If the specified order does not uniquely identify rows, the result of the function can be non-deterministic. For example, if you use order: users.age, and there are multiple users with the same age in the same partition, the result can be unexpected.

• partition or reset (repeatable, optional): A field that is used for partitioning the table. You can also use axis references like 'rows', 'columns', 'x_axis', or 'legend'. If partitions are not specified:
  • If order is specified, the table will be partitioned by all other grouping columns.
  • If order is not specified, the table will be considered as a single partition.

Output

The sum of the current row and the rows within the specified range.

Sample Usages

Running Sum of Count over Order Status

explore {
  dimensions {
    users.gender,
    orders.status
  }
  measures {
    count(orders.id),
    _running_sum: window_sum(count(orders.id), order: orders.status)
  }
}

Result

SQL

WITH "aql__t1" AS (
  SELECT
    "users"."gender" AS "users->gender",
    "orders"."status" AS "orders->status",
    COUNT("orders"."id") AS "count_orders->id"
  FROM
    "demo"."orders" "orders"
    LEFT JOIN "demo"."users" "users" ON "orders"."user_id" = "users"."id"
  GROUP BY
    1,
    2
)
SELECT
  "aql__t1"."users->gender" AS "users->gender",
  "aql__t1"."orders->status" AS "orders->status",
  "aql__t1"."count_orders->id" AS "count_orders->id",
  SUM("aql__t1"."count_orders->id") OVER (PARTITION BY "aql__t1"."users->gender" ORDER BY "aql__t1"."orders->status" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "_running_sum"
FROM
  "aql__t1"
WHERE
  "aql__t1"."count_orders->id" IS NOT NULL

Notice that it automatically resets the sum when the gender changes. This is because we only specify order and thus reset defaults to gender. If you want it to not reset, you can order by both gender and status.

Running Sum of Count over Gender and Order Status

explore {
  dimensions {
    users.gender,
    orders.status
  }
  measures {
    count(orders.id),
    _running_sum: window_sum(count(orders.id), order: users.gender, order: orders.status)
  }
}

Result

SQL

WITH "aql__t1" AS (
  SELECT
    "users"."gender" AS "users->gender",
    "orders"."status" AS "orders->status",
    COUNT("orders"."id") AS "count_orders->id"
  FROM
    "demo"."orders" "orders"
    LEFT JOIN "demo"."users" "users" ON "orders"."user_id" = "users"."id"
  GROUP BY
    1,
    2
)
SELECT
  "aql__t1"."users->gender" AS "users->gender",
  "aql__t1"."orders->status" AS "orders->status",
  "aql__t1"."count_orders->id" AS "count_orders->id",
  SUM("aql__t1"."count_orders->id") OVER ( ORDER BY "aql__t1"."users->gender" ASC, "aql__t1"."orders->status" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "_running_sum"
FROM
  "aql__t1"
WHERE
  "aql__t1"."count_orders->id" IS NOT NULL

By default, the range is ..0 (from the first row to the current row). Thus if we want to sum all rows, we can use .. for the range. And since we are not using relative range, we can omit the order: parameter.

Total Count of Users with percentage

explore {
  dimensions {
    users.gender,
    orders.status
  }
  measures {
    count(orders.id),
    pct: count(orders.id) / window_sum(count(orders.id), ..)
  }
}

Result

SQL

WITH "aql__t1" AS (
  SELECT
    "users"."gender" AS "users->gender",
    "orders"."status" AS "orders->status",
    COUNT("orders"."id") AS "count_orders->id"
  FROM
    "demo"."orders" "orders"
    LEFT JOIN "demo"."users" "users" ON "orders"."user_id" = "users"."id"
  GROUP BY
    1,
    2
)
SELECT
  "aql__t1"."users->gender" AS "users->gender",
  "aql__t1"."orders->status" AS "orders->status",
  "aql__t1"."count_orders->id" AS "count_orders->id",
  ("aql__t1"."count_orders->id" / (SUM("aql__t1"."count_orders->id") OVER (  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))) AS "pct"
FROM
  "aql__t1"
WHERE
  "aql__t1"."count_orders->id" IS NOT NULL

Axis-Aware Usage

You can use axis references to create running totals that adapt to your visualization structure:

explore {
  dimensions {
    rows {
      _quarter: orders.created_at | quarter()
    }
    columns {
      _status: orders.status
    }
  }

  measures {
    order_count: count(orders.id),

    // Running total across quarters for each status
    running_by_quarter: window_sum(order_count, order: 'rows'),

    // Running total across statuses for each quarter
    running_by_status: window_sum(order_count, order: 'columns'),

    // Override visualization sort order
    running_reverse: window_sum(order_count, order: 'x_axis' | desc())
  }
}

This approach is particularly useful when:

• Your visualization structure might change dynamically
• You want calculations to automatically adapt to different groupings
• You need to respect the visualization's sort order