mysql 常见统计方案整理汇总
普通分组统计
场景一:根据订单状态统计订单数量。一个很常见,也很简单的统计需求。其中状态字段是订单实体的一个属性
select count(*) count from orders group by status;
场景二:每日统计
-- 统计每日
SELECT DATE_FORMAT(create_date,'%Y-%m-%d') as days, COUNT(id) count FROM order GROUP BY days;
-- 统计每周
SELECT DATE_FORMAT(create_date,'%Y-%u') as weeks, COUNT(id) count FROM order GROUP BY weeks;
-- 统计每月
SELECT DATE_FORMAT(create_date,'%Y-%m') as months, COUNT(id) count FROM order GROUP BY months;
思考:如果其中又一天为空没有数据那么查询时间就有间隔了。如何解决如下
select tmp.days,
case when base.count >0 then base.count else tmp.count end as count
from
(select DATE_ADD('2020-01-01', INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) DAY) as days,(select 0 ) as count from mysql.help_topic where help_topic_id < DATEDIFF('2022-12-12', '2020-01-01'))
tmp
left join
(select base.* from (SELECT FROM_UNIXTIME(ctime,'%Y-%m-%d') as days, COUNT(id) count FROM orders GROUP BY days) base)
base
on tmp.days = base.days;
进一步思考:获取的数据多少是根据表[ mysql.help_topic]里面数据量的多少来展示,如果在实际开发过程中要设置这个表数据量的大小
区间范围统计
这是一个较为常见的需求,比如按照年龄段统计人员分布情况,甚至要求分别统计男女人数分布情况。
只根据年龄范围统计,没有其他限制条件,使用SUM只需要加一。
SELECT INTERVAL(age,10,20,30,40,50,60,70,80,90) AS ageRatio,
SUM(1) AS count FROM user GROUP BY ageRatio
在场景五的基础上多了一个区分性别,用流程控制函数来设置SUM加一的情况
SELECT INTERVAL(age,10,20,30,40,50,60,70,80,90) AS ageRatio,
SUM(CASE WHEN sex=1 THEN 1 ELSE 0 END) AS male,
SUM(CASE WHEN sex=0 THEN 1 ELSE 0 END) AS female FROM user GROUP BY ageRatio
思考
在MySQL输入interval()函数就会显示相应的语法格式:
INTERVAL(N,N1,N2,N3,...)
INTERVAL()函数为分区函数,其中,N是要判断的数值,N1,N2,N3,...是分段的间隔。
这个函数的返回值是段的位置:
如果N<N1,则返回0,
如果N1<=N<N2,则返回1,
如果N2<=N<N3,则返回2。
区间是左闭右开的。
案例
SELECT INTERVAL(price,94,96) AS ageRatio, SUM(1) AS count FROM mygoods GROUP BY ageRatio