一条SQL 统计近 7天、30天、全部的订单量
简化的表结构:
CREATE TABLE order ( `id` bigint NOT NULL AUTO_INCREMENT, `order_no` varchar(32) NOT NULL COMMENT '订单号', `user_id` bigint NOT NULL COMMENT '用户id', `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`id`) USING BTREE, KEY `idx_userid_createtime` (`user_id`,`create_time`) USING BTREE )
今天是 2023-09-12 ,如果我们要统计近 1 天的订单量,那么 SQL 很简单:
SELECT count(*) FROM order where user_id = 'xx' and time_create >'2023-09-12 00:00:00'
同理 7天、30天
SELECT count(*) FROM order where user_id = 'xx' and time_create >'2023-09-06 00:00:00' SELECT count(*) FROM order where user_id = 'xx' and time_create >'2023-08-14 00:00:00'
还有全部
SELECT count(*) FROM order where user_id = 'xx'
但是这样一来需要查四次数据库!能不能整个花活,把它压缩成一条 SQL 一次性查询呢?
还真行!看下面这条 SQL:
SELECT statistics, count(*) from ( SELECT CASE WHEN time_create > '2023-09-12 00:00:00' THEN '1' WHEN time_create > '2023-09-06 00:00:00' THEN '7' WHEN time_create > '2023-08-14 00:00:00' THEN '30' ELSE 'all' END as statistics from `order` where user_id = 'xxx' ) temp GROUP BY statistics;
思路就是利用 case when 先给对应时间数据打个标记,存放在临时表,然后通过 group by 统计。
我用了一个 4w 多订单数据的用户测试了一下,执行时间是 0.5s ,问题主要出在临时表,一旦数据量起来就不太行,但是暂时没接数仓就先这样顶着了,就前期用用。