文章目录
mysql是5.6版本,没有窗口函数
一、前言
应用场景
在使用mysql统计数据的时候,经常会遇到这类需求
180天内每日的用户的某种冷门行为次数
但用户不是每天都有着这个冷门行为,通过行为记录表可以统计出得到以下的数据
PS:(注意统计日期是不连续的)
stime(统计日期) | behavior_num(行为次数) |
---|---|
… | … |
2021-12-01 | 30 |
2021-12-03 | 40 |
2021-12-05 | 5 |
… | … |
可是需求方需要的是这样的报表数据
PS:(此时统计日期连续的)
stime(统计日期) | behavior_num(行为次数) |
---|---|
… | … |
2021-12-01 | 30 |
2021-12-02 | 0 |
2021-12-03 | 40 |
2021-12-04 | 0 |
2021-12-05 | 5 |
… | … |
如果不想每次手动复制粘贴填充,还有什么办法能解决呢?
解决方法
1、vlookup函数
使用EXCEL 的vlookup函数能解决,但是如果字段多起来了,函数就会变得特别复杂
2、常量表
左链接一张常量表,里面有连续的日期字段 。但是这个常量表不一定能满足需求所要的范围
3、查询小技巧(模拟常量表)
通过mysql 的变量,选中一个记录数合适的表作为种子表,来生成想要的数值(id|日期),模拟成常量表,之后在左链接统计结果
4、其他分析工具,如python
将数据读取到内存中,通过生成指定变量(id|日期),然后拼接
5、未发现
知道的朋友也可以告诉我,让我学习一下
二、实现
SELECT to_time
from
(
SELECT @total_time:='2021-06-12'
) as init, -- 初始化 起始值
(
SELECT @total_time:=DATE_ADD(@total_time,INTERVAL 1 day) as to_time
from obc.user_sys -- 纯粹做种子用的 max(17条记录)
) as aa
WHERE to_time <= '2021-12-12' -- 限制生成记录条数 还受到 max的限制
这个是user_sys 表 , 可看到最多是17条记录 ,以此生成的日期序列也就最多十七天
三、原理
实现原理
主要是使用到了mysql 变量和普通链接
首先初始化变量,然后依附种子表(最大范围)的记录,生成想要的变量(日期|id)
利用这个可以生成规律的数值
连续的日期
连续的id
每个月的第一天和最后一天
…
注意事项
1、初始化变量<这个必须出现在种子表前面> ,不然第一次执行全是nul,要执行第二次才有结果
2、(因为没有指明链接条件,所以默认是全链接(常量与种子表的全链接)),如果指明链接条件,可能会发生🤔的事情,可以尝试一下
四、拓展
1、统计每日行为记录
利用有序的日期,采用( = )等值链接 计算结果可得到连续的每日的行为统计
2、统计累计值
使用( < ) 进行链接 ,最后对统计日期进行group by ,就能计算累计值
未完待续…
如果有问题也可以在下方留言讨论
陈棋烂柯,一甲子矣
转载须获得本人许可