以A股日K数据为例,股票日交易数据表结构为:
CREATE TABLE stock_daily
(
symbol String, -- 股票代码
name String, --股票名称
date Date, --交易日期
open Float32, --开盘价
low Float32, --最低价
high Float32, --最高价
close Float32, --收盘价
volume Int64, --成交量
amount Float64 --成交额
)
ENGINE = MergeTree(date, (symbol, date), 8192)
需求为获取每个股票最后五个交易日的交易数据。ClickHouse不提供窗口函数,那么解决思路变为先找到每个股票的最后五个交易日期,然后再通过股票代码和交易日期关联源表获得相应详细交易数据。
SELECT symbol,name,date,open,low,high,close,volume,amount
FROM
(
SELECT symbol,name,date,open,low,high,close,volume,amount
FROM stock_daily
)ALL INNER JOIN(
SELECT symbol,arrayJoin(dates)AS date
FROM
(
SELECT symbol,groupArray(5)(date)AS dates
FROM
(
SELECT symbol,date
FROM stock_daily
ORDER BY symbol,date DESC
)
GROUP BY symbol
)
WHERE dates[1]< toDate('2018-03-16') -- 过滤2018年3月16日停牌中的股票,ClickHouse中数组元素序号从1开始。
)USING symbol,date
groupArray函数有两种用法:groupArray(x)和groupArray(max_size)(x),数组中元素的顺序就是分组数据中指定的顺序,groupArray(max_size)(x)可以指定数组的大小,即为需求中TopN的数值。
arrayJoin函数可以将array中的元素展开为行,当有多个array通过arrayJoin展开时结果为array的笛卡尔积。
也可以通过arrayMap函数实现上述功能:
SELECT symbol,name,date,open,low,high,close,volume,amount
FROM
(
SELECT symbol,name,date,open,low,high,close,volume,amount
FROM stock_daily
)
ALL INNER JOIN
(
SELECT symbol,arrayJoin(dates)AS date
FROM
(
SELECT
symbol,
arrayMap(lambda(tuple(i),dates[(i + 1)]),range(5))AS dates
FROM
(
SELECT
symbol,groupArray(date)AS dates
FROM
(
SELECT symbol,date
FROM stock_daily
ORDER BY symbol,date DESC
)
GROUP BY symbol
)
)
WHERE dates[1]< toDate('2018-03-16') -- 过滤2018年3月16日停牌中的股票,ClickHouse中数组元素序号从1开始。
)USING symbol,date
结果如下:
ClickHouse引擎适合各类OLAP场景,效率极高,官方提供了很多内置函数和特性,你值得拥有。