Clickhouse数据库之SQL篇
1.时间分组
我这里有个需求,就是按小时和介质类型两种分组统计每种介质每小时中的最大记录,有一个特殊的要求就是00点要作为前一个小时的结束时间,比如13:00:00是12点的结束时间,00:00:00是前一天的结束时间。
这个需求我想了很多天, 也试了很多种方法。
1 | select RecordTime,toYYYYMMDD(RecordTime)*100+floor(((toHour(RecordTime)*60+toMinute(RecordTime)))/60-0.001) as groupid |
将时间转换为分钟,然后除以60取整,可以将数据按小时分组了,为了实现将整点分入前一个分组,所以减去了一个0.001,在一天24小时内,还是好的,但是要跨日期了,比如2019-01-22 00:00:00,本意作为21日的23点,但是会出现一个错误,就是会出现一个99的错误。
后来我想到了使用的是用clickhouse自带的subtractMinutes,将时间先减去一分钟,然后在进行分组就好了。
1 | select RecordTime,subtractMinutes(RecordTime,1) as newRecordTime,(toYYYYMMDD(newRecordTime)*100+floor(((toHour(newRecordTime)*60+toMinute(newRecordTime)))/60)) as groupid |
我这里是把分钟数除以60,按一个小时进行分组,如果除以30,那就是按30分钟进行分组了,这样可以做到更加的灵活。
1.时间日期函数
2.查看表结构
1 | desc 表名; |
1.查看表结构 (这个入门还是挺不错的,除了表结构,还有其他的知识)
3.日期时间函数
日期类型
1 | Datetime('Asia/Shanghai') |
3.分组取最大的一条
我在使用分组的时候,需要取时间最大的那条记录,但是数据有可能重复,在重复时,只取一条就可以了,同时我没有主键的概念。所以就浪费了很多的时间,使用了多种方式,比如uniq函数,groupArray函数,DISTINCT 等,都不是很理想。
最后使用的是LIMIT 1 BY RecordTime这个方法,就是取第一条的含义。
1.clickhouse(一、 高阶函数应用实现hive分区设置行号row_number() ) (这篇文章没有什么用)
2.Clickhouse 常见SQL需求案例 (这篇文章中提到了一个groupArray,可以分组取前N条,但是我没有实验成功)
3.LIMIT BY子句 (这是官方的LIMIT BY子句的说明)
4.【ClickHouse】row_number() over (partition by)的几种实现方法 (这篇文章刚开始也是提出了如何实现row_number()函数的功能,最后提到了使用LIMIT BY,我觉得很有用)
5.SQL分组获取记录的第一条数据——PARTITION BY (这是SQL语句中的内容,但是没有多大的作用)
6.uniqExact (这是一个去重的函数)