Window functions

Frame boundaries

The frame determines which rows are included in each calculation relative to the current row.

      PARTITION
┌─────────────────┐  <-- UNBOUNDED PRECEDING (beginning of partition)
│                 │
│=================│  <-- N PRECEDING  <─┐
│      N ROWS     │                     │  F
│  Before CURRENT │                     │  R
│~~~~~~~~~~~~~~~~~│  <-- CURRENT ROW    │  A
│     M ROWS      │                     │  M
│   After CURRENT │                     │  E
│=================│  <-- M FOLLOWING  <─┘
│                 │
└─────────────────┘  <-- UNBOUNDED FOLLOWING (end of partition)

Default frame behavior:

• With no ORDER BY: the frame spans the entire partition (UNBOUNDED PRECEDING to UNBOUNDED FOLLOWING).

• With ORDER BY: the frame spans from UNBOUNDED PRECEDING to the current row.

Declare frame boundaries explicitly when the default behavior could produce unexpected results.

ClickHouse-specific function

nonNegativeDerivative(metric_column, timestamp_column[, INTERVAL X UNITS]) returns the non-negative derivative of metric_column over timestamp_column. The INTERVAL defaults to 1 second. Returns 0 for the first row. For subsequent rows, it computes (metric_i - metric_i-1) / (timestamp_i - timestamp_i-1) * interval.

Numbering rows

Use row_number(), rank(), and dense_rank() to assign sequence numbers to rows. The difference appears when rows have tied values.

CREATE TABLE salaries
(
    `team`     String,
    `player`   String,
    `salary`   UInt32,
    `position` String
)
ENGINE = Memory;

INSERT INTO salaries FORMAT Values
    ('Port Elizabeth Barbarians', 'Gary Chen',       195000, 'F'),
    ('New Coreystad Archdukes',   'Charles Juarez',  190000, 'F'),
    ('Port Elizabeth Barbarians', 'Michael Stanley', 150000, 'D'),
    ('New Coreystad Archdukes',   'Scott Harrison',  150000, 'D'),
    ('Port Elizabeth Barbarians', 'Robert George',   195000, 'M');

SELECT
    player,
    salary,
    row_number() OVER (ORDER BY salary ASC) AS row,
    rank()       OVER (ORDER BY salary ASC) AS rank,
    dense_rank() OVER (ORDER BY salary ASC) AS dense_rank
FROM salaries;

┌─player──────────┬─salary─┬─row─┬─rank─┬─dense_rank─┐
│ Michael Stanley │ 150000 │   1 │    1 │          1 │
│ Scott Harrison  │ 150000 │   2 │    1 │          1 │
│ Charles Juarez  │ 190000 │   3 │    3 │          2 │
│ Gary Chen       │ 195000 │   4 │    4 │          3 │
│ Robert George   │ 195000 │   5 │    4 │          3 │
└─────────────────┴────────┴─────┴──────┴────────────┘

row_number() increments for every row. rank() leaves gaps (jumps from 1 to 3 after the tie). dense_rank() has no gaps.

Maximum and total salary per department

Calculate each employee's salary as a percentage of department totals without losing per-employee rows.

CREATE TABLE employees
(
    `department`    String,
    `employee_name` String,
    `salary`        Float
)
ENGINE = Memory;

INSERT INTO employees FORMAT Values
    ('Finance', 'Jonh', 200),
    ('Finance', 'Joan', 210),
    ('Finance', 'Jean', 505),
    ('IT',      'Tim',  200),
    ('IT',      'Anna', 300),
    ('IT',      'Elen', 500);

SELECT
    department,
    employee_name AS emp,
    salary,
    max_salary_per_dep,
    total_salary_per_dep,
    round((salary / total_salary_per_dep) * 100, 2) AS `share_per_dep(%)`
FROM
(
    SELECT
        department,
        employee_name,
        salary,
        max(salary) OVER wndw AS max_salary_per_dep,
        sum(salary) OVER wndw AS total_salary_per_dep
    FROM employees
    WINDOW wndw AS (
        PARTITION BY department
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    )
    ORDER BY department ASC, employee_name ASC
);

┌─department─┬─emp──┬─salary─┬─max_salary_per_dep─┬─total_salary_per_dep─┬─share_per_dep(%)─┐
│ Finance    │ Jean │    505 │                505 │                  915 │            55.19 │
│ Finance    │ Joan │    210 │                505 │                  915 │            22.95 │
│ Finance    │ Jonh │    200 │                505 │                  915 │            21.86 │
│ IT         │ Anna │    300 │                500 │                 1000 │               30 │
│ IT         │ Elen │    500 │                500 │                 1000 │               50 │
│ IT         │ Tim  │    200 │                500 │                 1000 │               20 │
└────────────┴──────┴────────┴────────────────────┴──────────────────────┴──────────────────┘

The named WINDOW clause (wndw) lets both max() and sum() share the same partition definition without repeating it.

Cumulative sum

Track the running stock balance per item over time.

CREATE TABLE warehouse
(
    `item`  String,
    `ts`    DateTime,
    `value` Float
)
ENGINE = Memory;

INSERT INTO warehouse VALUES
    ('sku38', '2020-01-01', 9),
    ('sku38', '2020-02-01', 1),
    ('sku38', '2020-03-01', -4),
    ('sku1',  '2020-01-01', 1),
    ('sku1',  '2020-02-01', 1),
    ('sku1',  '2020-03-01', 1);

SELECT
    item,
    ts,
    value,
    sum(value) OVER (PARTITION BY item ORDER BY ts ASC) AS stock_balance
FROM warehouse
ORDER BY item ASC, ts ASC;

┌─item──┬──────────────────ts─┬─value─┬─stock_balance─┐
│ sku1  │ 2020-01-01 00:00:00 │     1 │             1 │
│ sku1  │ 2020-02-01 00:00:00 │     1 │             2 │
│ sku1  │ 2020-03-01 00:00:00 │     1 │             3 │
│ sku38 │ 2020-01-01 00:00:00 │     9 │             9 │
│ sku38 │ 2020-02-01 00:00:00 │     1 │            10 │
│ sku38 │ 2020-03-01 00:00:00 │    -4 │             6 │
└───────┴─────────────────────┴───────┴───────────────┘

With ORDER BY and no explicit frame, the default frame is UNBOUNDED PRECEDING to CURRENT ROW — exactly what a running total needs.

Moving average (per 3 rows)

Smooth sensor readings using a 3-row sliding window.

CREATE TABLE sensors
(
    `metric` String,
    `ts`     DateTime,
    `value`  Float
)
ENGINE = Memory;

INSERT INTO sensors VALUES
    ('cpu_temp', '2020-01-01 00:00:00', 87),
    ('cpu_temp', '2020-01-01 00:00:01', 77),
    ('cpu_temp', '2020-01-01 00:00:02', 93),
    ('cpu_temp', '2020-01-01 00:00:03', 87),
    ('cpu_temp', '2020-01-01 00:00:04', 87),
    ('cpu_temp', '2020-01-01 00:00:05', 87),
    ('cpu_temp', '2020-01-01 00:00:06', 87),
    ('cpu_temp', '2020-01-01 00:00:07', 87);

SELECT
    metric,
    ts,
    value,
    avg(value) OVER (
        PARTITION BY metric
        ORDER BY ts ASC
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_temp
FROM sensors
ORDER BY metric ASC, ts ASC;

┌─metric───┬──────────────────ts─┬─value─┬───moving_avg_temp─┐
│ cpu_temp │ 2020-01-01 00:00:00 │    87 │                87 │
│ cpu_temp │ 2020-01-01 00:00:01 │    77 │                82 │
│ cpu_temp │ 2020-01-01 00:00:02 │    93 │ 85.66666666666667 │
│ cpu_temp │ 2020-01-01 00:00:03 │    87 │ 85.66666666666667 │
│ cpu_temp │ 2020-01-01 00:00:04 │    87 │                89 │
│ cpu_temp │ 2020-01-01 00:00:05 │    87 │                87 │
│ cpu_temp │ 2020-01-01 00:00:06 │    87 │                87 │
│ cpu_temp │ 2020-01-01 00:00:07 │    87 │                87 │
└──────────┴─────────────────────┴───────┴───────────────────┘

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW includes the current row and the two rows immediately before it — always exactly 3 rows except at the start of the partition.

Moving average (per 10 seconds)

Use RANGE instead of ROWS to define the window by value interval rather than row count.

SELECT
    metric,
    ts,
    value,
    avg(value) OVER (
        PARTITION BY metric
        ORDER BY ts
        RANGE BETWEEN 10 PRECEDING AND CURRENT ROW
    ) AS moving_avg_10_seconds_temp
FROM sensors
ORDER BY metric ASC, ts ASC;

RANGE BETWEEN 10 PRECEDING AND CURRENT ROW includes all rows whose ts value is within 10 seconds of the current row's ts. Because RANGE works with any numeric type, specify time offsets as seconds rather than using INTERVAL syntax.

Moving average (per 10 days)

To compute a day-level moving average from second-precision timestamps, apply toDate() in the ORDER BY clause. This converts the frame unit from seconds to days.

SELECT
    metric,
    ts,
    value,
    round(avg(value) OVER (
        PARTITION BY metric
        ORDER BY toDate(ts)
        RANGE BETWEEN 10 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_10_days_temp
FROM sensors
ORDER BY metric ASC, ts ASC;