【ClickHouse】常用查询语句demo

ClickHouse

ClickHouse中常用的查询语句语法，仅供参考，详见官方参考文档。

时间操作相关

• 时间戳 to 日期时间

select toDateTime(1604457372);      -- 2020-11-04 02:36:12
select toDateTime64(1604188800, 3, 'Asia/Shanghai');    -- 2020-11-01 08:00:00.000
select toDateTime64(1604457372, 3, 'Asia/Shanghai');    -- 2020-11-04 10:36:12.000
select toDateTime64(1604188800 - 3600*24*7, 3, 'Asia/Shanghai');    -- 2020-10-25 08:00:00.000

• 日期时间 to 时间戳

select toUnixTimestamp(toDateTime('2020-11-01 00:00:00'));  -- 1604188800
select toUnixTimestamp(now());      -- 1604457372

• 字符串 to 时间格式

select toDate('2020-11-01') as date;    -- 2020-11-01
-- datetime64时，需要提供时分秒信息，否则转换的结果不正确
select toDateTime64('2020-11-01', 3, 'Asia/Shanghai') as date_time64;   -- 0000-00-00 00:00:00.000
select toDateTime64('2020-11-01 00:01:02', 3, 'Asia/Shanghai') as date_time64;  -- 2020-11-01 00:01:02.000

• 数值 to 时间格式

select toDate(parseDateTimeBestEffort(toString(20201201)));     -- 2020-12-01

• 字符串 to 日期时间

select toDateTime('2020-11-01 00:01:02') as date_time;    -- 2020-11-01 00:01:02
select toDateTime(toDate('2020-11-01')) as date_time;       -- 2020-11-01 00:00:00
select toStartOfDay(toDate('2020-11-01')) as start_day_time;    -- 2020-11-01 00:00:00
select toDateTime64(toDate('2020-11-01'), 3, 'Asia/Shanghai') as date_time64;   -- 2020-11-01 08:00:00.000

• 日期时间数值运算

select toDateTime(subtractDays(toStartOfDay(toDate('2020-11-01')), 7));   -- 2020-10-25
select addMinutes(toStartOfInterval(now(), interval 1 minute), 1);        -- 在当前时间基础上增加一分钟
select addMinutes(toStartOfInterval(now(), interval 1 minute), -1);     -- 在当前时间基础上减少一分钟

统计分析

• 测试聚合函数

-- 测试聚合函数
select median(*) from values('data Int32', 1, 2, 3, 400, 5);    -- 3
select avg(*) from values('data Int32', 1, 2, 3, 400, 5);       -- 82.2

• 绘制统计条形图demo

select
       toStartOfInterval(start_time, interval 120 minute) as slot,
       count() as cnt,
       bar(cnt, 0, 1000, 20) as bar
from dw_haozan_lives.haozan_live_attributes
where start_time > subtractDays(now(), 1)
group by slot
order by slot;

其他

• 生成数字序列

select * from numbers(6);
select number from numbers(6);