Clikhouse-踩坑记录之求环比

Clikhouse-踩坑记录之求环比

需求:求订单环比

问题1:后端ibatis框架不支持子查询动态筛选,所以需要在sql中就实现排序.

问题2:使用date格式的天进行聚合计算式,group by day分组会默认按照day升序,此时使用neighbor函数就不会出现乱序求环比,如果group by week/month等字符串分组,默认排序就不是我们想要的,此时使用neighbor函数就会出现乱序,环比就不对了

SELECT month                                         date,
       count(1)                                                                                           serviceNum,
       neighbor(serviceNum, -1)                                                                           serviceNumYesterday,
       round((serviceNum - serviceNumYesterday) / if(serviceNumYesterday = 0, 1, serviceNumYesterday), 3) numRatio,
       count(distinct recipientMobile)                                                                    servicePerson,
       neighbor(servicePerson, -1) as                                                                     servicePersonYesterday,
       round((servicePerson - servicePersonYesterday) / if(servicePersonYesterday = 0, 1, servicePersonYesterday),3) personRatio
FROM datacenter.tdm_order_logistics_info final
WHERE (orderState = 'COLLECTED' AND month BETWEEN '2020-09' AND '2021-09' )
GROUP BY date

如图:此时出现了很严重的乱序,有人觉得增加order by就可以了,实际order by,是在计算结果出来之后再排序,结果并不是我们想要的

SELECT toInt16(concat(substring(month,4,1),substring(month,6,2)))                                         date,
       count(1)                                                                                           serviceNum,
       neighbor(serviceNum, -1)                                                                           serviceNumYesterday,
       round((serviceNum - serviceNumYesterday) / if(serviceNumYesterday = 0, 1, serviceNumYesterday), 3) numRatio,
       count(distinct recipientMobile)                                                                    servicePerson,
       neighbor(servicePerson, -1) as                                                                     servicePersonYesterday,
       round((servicePerson - servicePersonYesterday) / if(servicePersonYesterday = 0, 1, servicePersonYesterday),3) personRatio
FROM datacenter.tdm_order_logistics_info final
WHERE (orderState = 'COLLECTED' AND month BETWEEN '2020-09' AND '2021-09' )
GROUP BY date

如图:此时乱序现象不存在了

解决:通过切割拼接函数,把月与周,转换成int值,再进行group by,就会默认按照新的分组字段默认升序,此时neighbor就不会乱序了

注意:toInt8,toInt16才会默认升序排列,toInt32,toInt64(toFloat64,toFloat32)也是无法达到想要的结果,具体为啥可能需要看源码,如有大佬知道可以留言告知,谢谢啦