最近基于ClickHouse做了一些统计,用到了一些挺好用的函数,特记录下
使用语音间隔时长最长的是几天
用户每用一次语音就记录一条数据,数据中有时间、userId等,现在需要统计在某个时间范围内使用语音间隔时长最长是几天,也就是有几天没有语音数据。
思路如下:
1、先做一个子查询过滤下数据,按照userId、eventDate分组再按照时间排序,同时将时间字段(eventData)转为1到366的数字
select userId, toDayOfYear(eventDate) as d
from db_test.ods_asr_log_distributed
where eventDate between '2020-12-01' and '2020-12-31'
group by userId, eventDate
order by eventDate
2、再基于上述的子查询,按照用户分组,得到userId下所有的日期数组,同时计算下日期数组中前后两个的差值,这里用到了groupArray()函数;还有arrayDifference()函数,传入一个数组,得到一个新的数组,里面是该数组内所有元素前后的差值,但是如果传入的是日期是不支持的,所以在第一步中将日期转为1到366的数字。
select userId, groupArray(d) arr_day, arrayDifference(arr_day) arr_dif
from (
select userId, toDayOfYear(eventDate) as d
from db_test.ods_asr_log_distributed
where eventDate between '2020-12-01' and '2020-12-31'
group by userId, eventDate
order by eventDate)
group by userId
查询结果部分截图如下:
3、基于上述的子查询,用array join。array join 对于包含数组列的表来说是一种常见的操作,用于生成一个新表,该表具有包含该初始列中的每个单独数组元素的列,而其他列的值将被重复显示。简单点将就是:将数组中的元素一个个取出来和其他列一起展示,将数组元素扁平化。这样的话我们就能得到每个userId下每天的和前一天的日期间隔
select userId, dif
from (
select userId, groupArray(d) arr_day, arrayDifference(arr_day) arr_dif
from (
select userId, toDayOfYear(eventDate) as d
from db_test.ods_asr_log_distributed
where eventDate between '2020-12-01' and '2020-12-31'
group by userId, eventDate
order by eventDate)
group by userId
)
array join arr_dif as dif)
查询结果部分截图如下:
4、最后一步就简单了,基于上述结果,按照userId分组,得到最大的间隔天数。这里按照间隔天数排序再用 limit 1 by userId。
select userId, dif
from (
select userId, dif
from (
select userId