ウィンドウ関数を使用すると、現在の行と関連する行の集合を対象に計算を実行できます。
実行できる計算の一部は集約関数で行えるものと似ていますが、ウィンドウ関数では行が 1 つの結果行にグループ化されないため、各行は個別の行として返されます。
標準ウィンドウ関数
ClickHouse は、ウィンドウおよびウィンドウ関数を定義するための標準的な文法をサポートしています。以下の表は、各機能が現在サポートされているかどうかを示します。
| 機能 | サポート状況 |
|---|
アドホックなウィンドウ指定（count(*) over (partition by id order by time desc)） | ✅ |
ウィンドウ関数を含む式（例: (count(*) over ()) / 2） | ✅ |
WINDOW 句（select ... from table window w as (partition by id)） | ✅ |
ROWS フレーム | ✅ |
RANGE フレーム | ✅（デフォルト） |
DateTime 用 RANGE OFFSET フレームに対する INTERVAL 構文 | ❌（代わりに秒数を指定してください。RANGE は任意の数値型で動作します） |
GROUPS フレーム | ❌ |
フレームに対する集約関数の計算（sum(value) over (order by time)） | ✅（すべての集約関数がサポートされています） |
rank(), dense_rank(), row_number() | ✅
別名: denseRank() |
percent_rank() | ✅ パーティション内での値の相対的な順位を効率的に計算します。この関数は、ifNull((rank() OVER(PARTITION BY x ORDER BY y) - 1) / nullif(count(1) OVER(PARTITION BY x) - 1, 0), 0) のような、より冗長で計算コストの高い手動の SQL 計算を事実上置き換えます。
別名: percentRank() |
cume_dist() | ✅ 値の累積分布を計算します。現在の行の値以下の値を持つ行の割合（パーセンテージ）を返します。 |
lag/lead(value, offset) | ✅
次のいずれかの回避策も使用できます:
1) any(value) over (.... rows between <offset> preceding and <offset> preceding)、または lead の場合は following を使用します。
2) ウィンドウフレームを考慮する、類似の lagInFrame/leadInFrame を使用します。lag/lead と同じ動作を得るには、rows between unbounded preceding and unbounded following を使用します。 |
| ntile(buckets) | ✅
次のようにウィンドウを指定します: (partition by x order by y rows between unbounded preceding and unbounded following)。 |
ClickHouse固有のウィンドウ関数
以下のClickHouse固有のウィンドウ関数も提供されています：
nonNegativeDerivative(metric_column, timestamp_column[, INTERVAL X UNITS])
指定されたmetric_columnの非負の微分値をtimestamp_columnに基づいて算出します。
INTERVALは省略可能で、デフォルトはINTERVAL 1 SECONDです。
各行に対して算出される値は以下の通りです：
- 1行目：
0
- i行目：timestampi−timestampi−1metrici−metrici−1∗interval
aggregate_function (column_name)
OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column]
[ROWS or RANGE expression_to_bound_rows_withing_the_group]] | [window_name])
FROM table_name
WINDOW window_name as ([[PARTITION BY grouping_column] [ORDER BY sorting_column]])
PARTITION BY - 結果セットをどのようなグループに分割するかを定義します。ORDER BY - 集約関数 aggregate_function を計算するときに、グループ内の行をどのような順序で並べるかを定義します。ROWS or RANGE - フレームの境界を定義し、集約関数 aggregate_function はそのフレーム内で計算されます。WINDOW - 複数の式で同じウィンドウ定義を共通して利用できるようにします。
PARTITION
┌─────────────────┐ <-- UNBOUNDED PRECEDING (PARTITIONの先頭)
│ │
│ │
│=================│ <-- N PRECEDING <─┐
│ N ROWS │ │ F
│ Before CURRENT │ │ R
│~~~~~~~~~~~~~~~~~│ <-- CURRENT ROW │ A
│ M ROWS │ │ M
│ After CURRENT │ │ E
│=================│ <-- M FOLLOWING <─┘
│ │
│ │
└─────────────────┘ <--- UNBOUNDED FOLLOWING (PARTITIONの末尾)
これらの関数は、ウィンドウ関数としてのみ使用可能です。
ウィンドウ関数をどのように利用できるか、いくつかの例を見ていきます。
行に番号を振る
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
FROM salaries;
┌─player──────────┬─salary─┬─row─┐
│ Michael Stanley │ 150000 │ 1 │
│ Scott Harrison │ 150000 │ 2 │
│ Charles Juarez │ 190000 │ 3 │
│ Gary Chen │ 195000 │ 4 │
│ Robert George │ 195000 │ 5 │
└─────────────────┴────────┴─────┘
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 denseRank
FROM salaries;
┌─player──────────┬─salary─┬─row─┬─rank─┬─denseRank─┐
│ 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 │
└─────────────────┴────────┴─────┴──────┴───────────┘
集約関数
各選手の年俸を、その所属チームの平均年俸と比較します。
SELECT
player,
salary,
team,
avg(salary) OVER (PARTITION BY team) AS teamAvg,
salary - teamAvg AS diff
FROM salaries;
┌─player──────────┬─salary─┬─team──────────────────────┬─teamAvg─┬───diff─┐
│ Charles Juarez │ 190000 │ New Coreystad Archdukes │ 170000 │ 20000 │
│ Scott Harrison │ 150000 │ New Coreystad Archdukes │ 170000 │ -20000 │
│ Gary Chen │ 195000 │ Port Elizabeth Barbarians │ 180000 │ 15000 │
│ Michael Stanley │ 150000 │ Port Elizabeth Barbarians │ 180000 │ -30000 │
│ Robert George │ 195000 │ Port Elizabeth Barbarians │ 180000 │ 15000 │
└─────────────────┴────────┴───────────────────────────┴─────────┴────────┘
各選手の給与を、その選手のチーム内での最高給与と比較します。
SELECT
player,
salary,
team,
max(salary) OVER (PARTITION BY team) AS teamMax,
salary - teamMax AS diff
FROM salaries;
┌─player──────────┬─salary─┬─team──────────────────────┬─teamMax─┬───diff─┐
│ Charles Juarez │ 190000 │ New Coreystad Archdukes │ 190000 │ 0 │
│ Scott Harrison │ 150000 │ New Coreystad Archdukes │ 190000 │ -40000 │
│ Gary Chen │ 195000 │ Port Elizabeth Barbarians │ 195000 │ 0 │
│ Michael Stanley │ 150000 │ Port Elizabeth Barbarians │ 195000 │ -45000 │
│ Robert George │ 195000 │ Port Elizabeth Barbarians │ 195000 │ 0 │
└─────────────────┴────────┴───────────────────────────┴─────────┴────────┘
列によるパーティション分割
CREATE TABLE wf_partition
(
`part_key` UInt64,
`value` UInt64,
`order` UInt64
)
ENGINE = Memory;
INSERT INTO wf_partition FORMAT Values
(1,1,1), (1,2,2), (1,3,3), (2,0,0), (3,0,0);
SELECT
part_key,
value,
order,
groupArray(value) OVER (PARTITION BY part_key) AS frame_values
FROM wf_partition
ORDER BY
part_key ASC,
value ASC;
┌─part_key─┬─value─┬─order─┬─frame_values─┐
│ 1 │ 1 │ 1 │ [1,2,3] │ <┐
│ 1 │ 2 │ 2 │ [1,2,3] │ │ 第1グループ
│ 1 │ 3 │ 3 │ [1,2,3] │ <┘
│ 2 │ 0 │ 0 │ [0] │ <- 第2グループ
│ 3 │ 0 │ 0 │ [0] │ <- 第3グループ
└──────────┴───────┴───────┴──────────────┘
フレーム境界
CREATE TABLE wf_frame
(
`part_key` UInt64,
`value` UInt64,
`order` UInt64
)
ENGINE = Memory;
INSERT INTO wf_frame FORMAT Values
(1,1,1), (1,2,2), (1,3,3), (1,4,4), (1,5,5);
-- フレームはパーティションの境界で区切られます (BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
SELECT
part_key,
value,
order,
groupArray(value) OVER (
PARTITION BY part_key
ORDER BY order ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS frame_values
FROM wf_frame
ORDER BY
part_key ASC,
value ASC;
┌─part_key─┬─value─┬─order─┬─frame_values─┐
│ 1 │ 1 │ 1 │ [1,2,3,4,5] │
│ 1 │ 2 │ 2 │ [1,2,3,4,5] │
│ 1 │ 3 │ 3 │ [1,2,3,4,5] │
│ 1 │ 4 │ 4 │ [1,2,3,4,5] │
│ 1 │ 5 │ 5 │ [1,2,3,4,5] │
└──────────┴───────┴───────┴──────────────┘
-- 短縮形 - 境界式なし、ORDER BY なし
-- `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` と同等
SELECT
part_key,
value,
order,
groupArray(value) OVER (PARTITION BY part_key) AS frame_values_short,
groupArray(value) OVER (PARTITION BY part_key
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS frame_values
FROM wf_frame
ORDER BY
part_key ASC,
value ASC;
┌─part_key─┬─value─┬─order─┬─frame_values_short─┬─frame_values─┐
│ 1 │ 1 │ 1 │ [1,2,3,4,5] │ [1,2,3,4,5] │
│ 1 │ 2 │ 2 │ [1,2,3,4,5] │ [1,2,3,4,5] │
│ 1 │ 3 │ 3 │ [1,2,3,4,5] │ [1,2,3,4,5] │
│ 1 │ 4 │ 4 │ [1,2,3,4,5] │ [1,2,3,4,5] │
│ 1 │ 5 │ 5 │ [1,2,3,4,5] │ [1,2,3,4,5] │
└──────────┴───────┴───────┴────────────────────┴──────────────┘
-- フレームはパーティションの先頭から現在行までの範囲となる
SELECT
part_key,
value,
order,
groupArray(value) OVER (
PARTITION BY part_key
ORDER BY order ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS frame_values
FROM wf_frame
ORDER BY
part_key ASC,
value ASC;
┌─part_key─┬─value─┬─order─┬─frame_values─┐
│ 1 │ 1 │ 1 │ [1] │
│ 1 │ 2 │ 2 │ [1,2] │
│ 1 │ 3 │ 3 │ [1,2,3] │
│ 1 │ 4 │ 4 │ [1,2,3,4] │
│ 1 │ 5 │ 5 │ [1,2,3,4,5] │
└──────────┴───────┴───────┴──────────────┘
-- 短縮形式（フレームはパーティションの開始位置から現在行までの範囲）
-- `ORDER BY order ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` と同等
SELECT
part_key,
value,
order,
groupArray(value) OVER (PARTITION BY part_key ORDER BY order ASC) AS frame_values_short,
groupArray(value) OVER (PARTITION BY part_key ORDER BY order ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS frame_values
FROM wf_frame
ORDER BY
part_key ASC,
value ASC;
┌─part_key─┬─value─┬─order─┬─frame_values_short─┬─frame_values─┐
│ 1 │ 1 │ 1 │ [1] │ [1] │
│ 1 │ 2 │ 2 │ [1,2] │ [1,2] │
│ 1 │ 3 │ 3 │ [1,2,3] │ [1,2,3] │
│ 1 │ 4 │ 4 │ [1,2,3,4] │ [1,2,3,4] │
│ 1 │ 5 │ 5 │ [1,2,3,4,5] │ [1,2,3,4,5] │
└──────────┴───────┴───────┴────────────────────┴──────────────┘
-- フレームはパーティションの先頭から現在行までに制限されるが、順序は降順
SELECT
part_key,
value,
order,
groupArray(value) OVER (PARTITION BY part_key ORDER BY order DESC) AS frame_values
FROM wf_frame
ORDER BY
part_key ASC,
value ASC;
┌─part_key─┬─value─┬─order─┬─frame_values─┐
│ 1 │ 1 │ 1 │ [5,4,3,2,1] │
│ 1 │ 2 │ 2 │ [5,4,3,2] │
│ 1 │ 3 │ 3 │ [5,4,3] │
│ 1 │ 4 │ 4 │ [5,4] │
│ 1 │ 5 │ 5 │ [5] │
└──────────┴───────┴───────┴──────────────┘
-- スライディングフレーム - 1行前から現在行まで
SELECT
part_key,
value,
order,
groupArray(value) OVER (
PARTITION BY part_key
ORDER BY order ASC
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS frame_values
FROM wf_frame
ORDER BY
part_key ASC,
value ASC;
┌─part_key─┬─value─┬─order─┬─frame_values─┐
│ 1 │ 1 │ 1 │ [1] │
│ 1 │ 2 │ 2 │ [1,2] │
│ 1 │ 3 │ 3 │ [2,3] │
│ 1 │ 4 │ 4 │ [3,4] │
│ 1 │ 5 │ 5 │ [4,5] │
└──────────┴───────┴───────┴──────────────┘
-- スライディングフレーム - ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING
SELECT
part_key,
value,
order,
groupArray(value) OVER (
PARTITION BY part_key
ORDER BY order ASC
ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING
) AS frame_values
FROM wf_frame
ORDER BY
part_key ASC,
value ASC;
┌─part_key─┬─value─┬─order─┬─frame_values─┐
│ 1 │ 1 │ 1 │ [1,2,3,4,5] │
│ 1 │ 2 │ 2 │ [1,2,3,4,5] │
│ 1 │ 3 │ 3 │ [2,3,4,5] │
│ 1 │ 4 │ 4 │ [3,4,5] │
│ 1 │ 5 │ 5 │ [4,5] │
└──────────┴───────┴───────┴──────────────┘
-- row_numberはフレームを尊重しないため、rn_1 = rn_2 = rn_3 != rn_4となる
SELECT
part_key,
value,
order,
groupArray(value) OVER w1 AS frame_values,
row_number() OVER w1 AS rn_1,
sum(1) OVER w1 AS rn_2,
row_number() OVER w2 AS rn_3,
sum(1) OVER w2 AS rn_4
FROM wf_frame
WINDOW
w1 AS (PARTITION BY part_key ORDER BY order DESC),
w2 AS (
PARTITION BY part_key
ORDER BY order DESC
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
)
ORDER BY
part_key ASC,
value ASC;
┌─part_key─┬─value─┬─order─┬─frame_values─┬─rn_1─┬─rn_2─┬─rn_3─┬─rn_4─┐
│ 1 │ 1 │ 1 │ [5,4,3,2,1] │ 5 │ 5 │ 5 │ 2 │
│ 1 │ 2 │ 2 │ [5,4,3,2] │ 4 │ 4 │ 4 │ 2 │
│ 1 │ 3 │ 3 │ [5,4,3] │ 3 │ 3 │ 3 │ 2 │
│ 1 │ 4 │ 4 │ [5,4] │ 2 │ 2 │ 2 │ 2 │
│ 1 │ 5 │ 5 │ [5] │ 1 │ 1 │ 1 │ 1 │
└──────────┴───────┴───────┴──────────────┴──────┴──────┴──────┴──────┘
```sql
-- first_value と last_value はフレームに従う
SELECT
groupArray(value) OVER w1 AS frame_values_1,
first_value(value) OVER w1 AS first_value_1,
last_value(value) OVER w1 AS last_value_1,
groupArray(value) OVER w2 AS frame_values_2,
first_value(value) OVER w2 AS first_value_2,
last_value(value) OVER w2 AS last_value_2
FROM wf_frame
WINDOW
w1 AS (PARTITION BY part_key ORDER BY order ASC),
w2 AS (PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
ORDER BY
part_key ASC,
value ASC;
┌─frame_values_1─┬─first_value_1─┬─last_value_1─┬─frame_values_2─┬─first_value_2─┬─last_value_2─┐
│ [1] │ 1 │ 1 │ [1] │ 1 │ 1 │
│ [1,2] │ 1 │ 2 │ [1,2] │ 1 │ 2 │
│ [1,2,3] │ 1 │ 3 │ [2,3] │ 2 │ 3 │
│ [1,2,3,4] │ 1 │ 4 │ [3,4] │ 3 │ 4 │
│ [1,2,3,4,5] │ 1 │ 5 │ [4,5] │ 4 │ 5 │
└────────────────┴───────────────┴──────────────┴────────────────┴───────────────┴──────────────┘
-- フレーム内の2番目の値
SELECT
groupArray(value) OVER w1 AS frame_values_1,
nth_value(value, 2) OVER w1 AS second_value
FROM wf_frame
WINDOW w1 AS (PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
ORDER BY
part_key ASC,
value ASC;
┌─frame_values_1─┬─second_value─┐
│ [1] │ 0 │
│ [1,2] │ 2 │
│ [1,2,3] │ 2 │
│ [1,2,3,4] │ 2 │
│ [2,3,4,5] │ 3 │
└────────────────┴──────────────┘
-- フレーム内の2番目の値 + 欠損値に対するNull
SELECT
groupArray(value) OVER w1 AS frame_values_1,
nth_value(toNullable(value), 2) OVER w1 AS second_value
FROM wf_frame
WINDOW w1 AS (PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
ORDER BY
part_key ASC,
value ASC;
┌─frame_values_1─┬─second_value─┐
│ [1] │ ᴺᵁᴸᴸ │
│ [1,2] │ 2 │
│ [1,2,3] │ 2 │
│ [1,2,3,4] │ 2 │
│ [2,3,4,5] │ 3 │
└────────────────┴──────────────┘
以下は、現場でよくある課題を解決する例です。
部門別の最大／合計給与
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 │
└────────────┴──────┴────────┴────────────────────┴──────────────────────┴──────────────────┘
累積和
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 │
└───────┴─────────────────────┴───────┴───────────────┘
移動平均 / スライディング平均（3行ごと）
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);
```sql
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 │
└──────────┴─────────────────────┴───────┴───────────────────┘
移動平均／スライディング平均（10秒ごと）
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;
┌─metric───┬──────────────────ts─┬─value─┬─moving_avg_10_seconds_temp─┐
│ cpu_temp │ 2020-01-01 00:00:00 │ 87 │ 87 │
│ cpu_temp │ 2020-01-01 00:01:10 │ 77 │ 77 │
│ cpu_temp │ 2020-01-01 00:02:20 │ 93 │ 93 │
│ cpu_temp │ 2020-01-01 00:03:30 │ 87 │ 87 │
│ cpu_temp │ 2020-01-01 00:04:40 │ 87 │ 87 │
│ cpu_temp │ 2020-01-01 00:05:50 │ 87 │ 87 │
│ cpu_temp │ 2020-01-01 00:06:00 │ 87 │ 87 │
│ cpu_temp │ 2020-01-01 00:07:10 │ 87 │ 87 │
└──────────┴─────────────────────┴───────┴────────────────────────────┘
移動平均 / スライディング平均（10日ごと）
温度データは秒精度で保存されていますが、Range と ORDER BY toDate(ts) を使用することでサイズ 10 のフレームを作成し、toDate(ts) によってその単位は日になります。
CREATE TABLE sensors
(
`metric` String,
`ts` DateTime,
`value` Float
)
ENGINE = Memory;
insert into sensors values('ambient_temp', '2020-01-01 00:00:00', 16),
('ambient_temp', '2020-01-01 12:00:00', 16),
('ambient_temp', '2020-01-02 11:00:00', 9),
('ambient_temp', '2020-01-02 12:00:00', 9),
('ambient_temp', '2020-02-01 10:00:00', 10),
('ambient_temp', '2020-02-01 12:00:00', 10),
('ambient_temp', '2020-02-10 12:00:00', 12),
('ambient_temp', '2020-02-10 13:00:00', 12),
('ambient_temp', '2020-02-20 12:00:01', 16),
('ambient_temp', '2020-03-01 12:00:00', 16),
('ambient_temp', '2020-03-01 12:00:00', 16),
('ambient_temp', '2020-03-01 12:00:00', 16);
```sql
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;
┌─metric───────┬──────────────────ts─┬─value─┬─moving_avg_10_days_temp─┐
│ ambient_temp │ 2020-01-01 00:00:00 │ 16 │ 16 │
│ ambient_temp │ 2020-01-01 12:00:00 │ 16 │ 16 │
│ ambient_temp │ 2020-01-02 11:00:00 │ 9 │ 12.5 │
│ ambient_temp │ 2020-01-02 12:00:00 │ 9 │ 12.5 │
│ ambient_temp │ 2020-02-01 10:00:00 │ 10 │ 10 │
│ ambient_temp │ 2020-02-01 12:00:00 │ 10 │ 10 │
│ ambient_temp │ 2020-02-10 12:00:00 │ 12 │ 11 │
│ ambient_temp │ 2020-02-10 13:00:00 │ 12 │ 11 │
│ ambient_temp │ 2020-02-20 12:00:01 │ 16 │ 13.33 │
│ ambient_temp │ 2020-03-01 12:00:00 │ 16 │ 16 │
│ ambient_temp │ 2020-03-01 12:00:00 │ 16 │ 16 │
│ ambient_temp │ 2020-03-01 12:00:00 │ 16 │ 16 │
└──────────────┴─────────────────────┴───────┴─────────────────────────┘
GitHub Issues
ウィンドウ関数の初期サポートに関するロードマップは この Issue にあります。
ウィンドウ関数に関連するすべての GitHub Issue には comp-window-functions タグが付いています。
テスト
次のテストには、現在サポートされている構文の例が含まれています。
https://github.com/ClickHouse/ClickHouse/blob/master/tests/performance/window_functions.xml
https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/01591_window_functions.sql
Postgres ドキュメント
https://www.postgresql.org/docs/current/sql-select.html#SQL-WINDOW
https://www.postgresql.org/docs/devel/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
https://www.postgresql.org/docs/devel/functions-window.html
https://www.postgresql.org/docs/devel/tutorial-window.html
MySQL ドキュメント
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html
https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html
https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html
関連コンテンツ
