ClickHouse SQL记录

最近基于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