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);