源表數(shù)據(jù)如下：
| time | province | order_cnt |
|---|---|---|
| 20200601 | shandong | 100 |
| 20200601 | jiangsu | 200 |
| 20200601 | zhejiang | 300 |
| 20200602 | shandong | 200 |
| 20200602 | jiangsu | 300 |
| 20200602 | zhejiang | 400 |
| 20200603 | shandong | 400 |
| 20200603 | jiangsu | 500 |
| 20200603 | zhejiang | 600 |
需求是按照省份和時(shí)間分類，每天累計(jì)求和，需求的結(jié)果如下：
（每天的數(shù)據(jù)都要按照省份將之前的訂單數(shù)目進(jìn)行累加）
| time | province | order_cnt_sum |
|---|---|---|
| 20200601 | shandong | 100 |
| 20200601 | jiangsu | 200 |
| 20200601 | zhejiang | 300 |
| 20200602 | shandong | 300 |
| 20200602 | jiangsu | 500 |
| 20200602 | zhejiang | 700 |
| 20200603 | shandong | 700 |
| 20200603 | jiangsu | 1000 |
| 20200603 | zhejiang | 1300 |
需要借助clickhouse的array系列函數(shù)實(shí)現(xiàn): ArrayFunction
SELECT
time,
province,
arraySum(arraySlice(order_cnts, 1, i)) AS order_cnt_sum
FROM
(
SELECT groupArray(order_cnt) AS order_cnts,
groupArray(time) AS times,
groupArray(province) AS provinces
FROM
(
SELECT *
FROM test_table order by time
) group by province
)
ARRAY JOIN
times as time,
provinces AS province,
arrayEnumerate(order_cnts) AS i;
