MySQL查询每月记录数
CHJH_MingYI 人气:0以下演示将在下表数据中进行:
其中:id为主键用于表的连接;value1为需要统计的主体,如用户等;date为记录日期。
先说结论
SELECT tmp.value1 AS `value1`, MONTH(SUBSTRING_INDEX(tmp.ct, ',', 1)) AS `month`, LENGTH(tmp.ct) - LENGTH( REPLACE (tmp.ct, ',', '') ) + 1 AS `cnt` FROM ( SELECT id, value1, GROUP_CONCAT(date_value) AS ct FROM test_year_record GROUP BY value1, INTERVAL( date_value, DATE(CONCAT('2022', '-01-01')), DATE(CONCAT('2022', '-02-01')), DATE(CONCAT('2022', '-03-01')), DATE(CONCAT('2022', '-04-01')), DATE(CONCAT('2022', '-05-01')), DATE(CONCAT('2022', '-06-01')), DATE(CONCAT('2022', '-07-01')), DATE(CONCAT('2022', '-08-01')), DATE(CONCAT('2022', '-09-01')), DATE(CONCAT('2022', '-10-01')), DATE(CONCAT('2022', '-11-01')), DATE(CONCAT('2022', '-12-01')), DATE(CONCAT('2023', '-01-01')) ) ) AS tmp JOIN test_year_record AS ot ON ot.id = tmp.id WHERE ot.value1 = 1 AND YEAR(SUBSTRING_INDEX(tmp.ct, ',', 1)) = '2022'
注:以’2022’为例,上面结论中使用了CONCAT方法进行字符串拼接,方便了年份替换,可以直接替换置对应的ORM的参数等。
查询结果
思路及SQL解释
这个问题可以划分为如下几个子问题,我们可以挨个分析解决:
1. 如何以月份划分
对于一个月份的数据可以如下判断:
date_value >= DATE_ADD(date_value, INTERVAL - DAY(date_value) + 1 DAY) AND data_value <= LAST_DAY(data_value)
解释一下:
DATE_ADD(date_value, INTERVAL - DAY(date_value) + 1 DAY):data_value所在月的第一天,原理为在data_value的基础上加上-DAY(data_value)天数再+1,当然也可以使用DATE_SUB或者去YEAR和MONTH信息再进行拼接;
LAST_DAY(date_value):data_value所在月的最后一天。
一个月的解决了,那么多个月的无非就手写几个范围就可以了(x
当然不能手写这些范围,一方面是很麻烦而且不好看,另一方面是会给mysql带来过多的计算量。
那么如何给12月进行划分呢:
INTERVAL() 函数可以解决我们的问题:
INTERVAL( N , n 1 , n 2 , ⋯ , n 3 N,n_1,n_2,\cdots,n_3 N,n1,n2,⋯,n3),其中 N N N为带判断是数据,后面的 n 1 ∼ n n n_1 \sim n_n n1∼nn分别为各个间断点,这个函数的返回值如下,当 N < n 1 N < n1 N<n1返回0,当 n 1 ≤ N < n 2 n_1 \leq N < n_2 n1≤N<n2时返回1,当 n 2 ≤ N < n 3 n_2 \leq N < n_3 n2≤N<n3时返回2,…,以此类推。
据此,我们可以给一年做一个分段:
INTERVAL( date_value, DATE(CONCAT('2022', '-01-01')), # 一月 DATE(CONCAT('2022', '-02-01')), # 二月 DATE(CONCAT('2022', '-03-01')), # 三月 DATE(CONCAT('2022', '-04-01')), # 四月 DATE(CONCAT('2022', '-05-01')), # 五月 DATE(CONCAT('2022', '-06-01')), # 六月 DATE(CONCAT('2022', '-07-01')), # 七月 DATE(CONCAT('2022', '-08-01')), # 八月 DATE(CONCAT('2022', '-09-01')), # 九月 DATE(CONCAT('2022', '-10-01')), # 十月 DATE(CONCAT('2022', '-11-01')), # 十一月 DATE(CONCAT('2022', '-12-01')), # 十二月 DATE(CONCAT('2023', '-01-01')) # 次年一月,防止次年的数据记录进当年12月中 )
注: 这里其实还有个问题,就是结果会返回去年的数据(0),可以像我一样在外查询里面进行一个年份判断,也可以交给java等检测。
2.获取每月数据
可以使用GROUP BY子句,以INTERVAL的值进行分组(为了保证属于同一个value1的数据,还需要以value1进行分组)。
注:GROUP BY 子句中含有多个参数时,将会是多条这些数据都一样的记录分为一组。
仅仅是做了分组是不够的,我们还需要GROUP_CONCAT()函数来获取一个分组中的数据集。
执行完当前这步,可以获取的结果如下:
3.统计每月数据
在ct这一列中,我们获取的数据是有规律的,比如一个日期中会有两个"-"、两个日期之间以",“分隔。
这里我们选择以”,"为标志,统计出有多少个分隔符,再+1就得到了数据的数量。
至于实现方式,可以使用如下方式:
即
LENGTH(tmp.ct) - LENGTH(REPLACE(tmp.ct, ',', '')) + 1
4.统计值与月份相对应
取得GROUP_CONCAT获取的第一个日期即可代表这一整个数据所在的月份。
可以使用SUBSTRING_INDEX()函数,它有三个参数,第一个参数为待片取的字符串、第二个参数为分隔符、第三个参数为第几个截取到第几个分隔符。
如此一来:
SUBSTRING_INDEX(tmp.ct, ',', 1)
便可以取到该日期,再使用MONTH函数即可获取对应的月份。
5.总体整合
我这里是使用了一次子查询,子查询获取对应的分组及GROUP_CONCAT数据,再交由外查询进行处理。
结语
这里给出的方案仅仅是一种方案,也许存在着其他更快更好的解决方案但我没有想到,在复杂问题面前一步一步获取小数据是我习惯,这也就使得很可能出现多个嵌套着的子查询。
加载全部内容