Clickhouse数据库之SQL篇

标签: Clickhouse 分类: 数据库 创建时间:2020-02-25 08:38:45 更新时间:2025-01-17 10:39:22

1.时间分组

我这里有个需求,就是按小时和介质类型两种分组统计每种介质每小时中的最大记录,有一个特殊的要求就是00点要作为前一个小时的结束时间,比如13:00:00是12点的结束时间,00:00:00是前一天的结束时间。
这个需求我想了很多天, 也试了很多种方法。

1
2
select RecordTime,toYYYYMMDD(RecordTime)*100+floor(((toHour(RecordTime)*60+toMinute(RecordTime)))/60-0.001) as groupid 
from lsmfhx.v_realtime as v where RecordTime >= '2016-09-19 00:00:00' and RecordTime <='2016-09-20 23:59:00' and SumFlux IS NOT NULL and SumFlux != 0

将时间转换为分钟,然后除以60取整,可以将数据按小时分组了,为了实现将整点分入前一个分组,所以减去了一个0.001,在一天24小时内,还是好的,但是要跨日期了,比如2019-01-22 00:00:00,本意作为21日的23点,但是会出现一个错误,就是会出现一个99的错误。

后来我想到了使用的是用clickhouse自带的subtractMinutes,将时间先减去一分钟,然后在进行分组就好了。

1
2
select RecordTime,subtractMinutes(RecordTime,1) as newRecordTime,(toYYYYMMDD(newRecordTime)*100+floor(((toHour(newRecordTime)*60+toMinute(newRecordTime)))/60)) as groupid 
from lsmfhx.v_realtime as v where RecordTime >= '2016-09-19 00:00:00' and RecordTime <='2016-09-20 23:59:00' and SumFlux IS NOT NULL and SumFlux != 0

我这里是把分钟数除以60,按一个小时进行分组,如果除以30,那就是按30分钟进行分组了,这样可以做到更加的灵活。

参考文章:
1.时间日期函数

2.查看表结构

1
desc 表名;
参考文章:
1.查看表结构 (这个入门还是挺不错的,除了表结构,还有其他的知识)

3.日期时间函数

日期类型

1
2
3
4
Datetime('Asia/Shanghai')

Datetime64

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 (这是一个去重的函数)
小额赞助
本人提供免费与付费咨询服务,感谢您的支持!赞助请发邮件通知,方便公布您的善意!
**光 3.01 元
Sun 3.00 元
bibichuan 3.00 元
微信公众号
广告位
诚心邀请广大金主爸爸洽谈合作
每日一省
isNaN 和 Number.isNaN 函数的区别?

1.函数 isNaN 接收参数后,会尝试将这个参数转换为数值,任何不能被转换为数值的的值都会返回 true,因此非数字值传入也会返回 true ,会影响 NaN 的判断。

2.函数 Number.isNaN 会首先判断传入参数是否为数字,如果是数字再继续判断是否为 NaN ,不会进行数据类型的转换,这种方法对于 NaN 的判断更为准确。

每日二省
为什么0.1+0.2 ! == 0.3,如何让其相等?

一个直接的解决方法就是设置一个误差范围,通常称为“机器精度”。对JavaScript来说,这个值通常为2-52,在ES6中,提供了Number.EPSILON属性,而它的值就是2-52,只要判断0.1+0.2-0.3是否小于Number.EPSILON,如果小于,就可以判断为0.1+0.2 ===0.3。

每日三省
== 操作符的强制类型转换规则?

1.首先会判断两者类型是否**相同,**相同的话就比较两者的大小。

2.类型不相同的话,就会进行类型转换。

3.会先判断是否在对比 null 和 undefined,是的话就会返回 true。

4.判断两者类型是否为 string 和 number,是的话就会将字符串转换为 number。

5.判断其中一方是否为 boolean,是的话就会把 boolean 转为 number 再进行判断。

6.判断其中一方是否为 object 且另一方为 string、number 或者 symbol,是的话就会把 object 转为原始类型再进行判断。

每日英语
Happiness is time precipitation, smile is the lonely sad.
幸福是年华的沉淀,微笑是寂寞的悲伤。