MySQL连续日期分组统计(by连续登录天数)

MySQL连续日期分组统计(by连续登录天数)

三月 07, 2022 阅读量

今天又被朋友问到（要）去年11月底一个夜晚帮他写的sql-一个广告投放的数据，想根据连续的投放日期来分组。开始完全不知道如何去分组，不过当晚灵机一动-用连续登录天数的思路貌似可以解决。然后马上实现就丢了回去（分组条件有点多，还搞了下去重，结果都是独立的，没有重复……白干了好多）。今天问到我的时候已经忘的差不多了，整理下思路备用

数据准备

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#MySQL80018

CREATE TABLE `ad_serving`  (
  `order_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `serving_date` date NULL DEFAULT NULL COMMENT '投放日期'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `ad_serving` VALUES ('1', '2022-03-01');
INSERT INTO `ad_serving` VALUES ('1', '2022-03-02');
INSERT INTO `ad_serving` VALUES ('1', '2022-03-03');
INSERT INTO `ad_serving` VALUES ('1', '2022-03-05');
INSERT INTO `ad_serving` VALUES ('1', '2022-03-06');
INSERT INTO `ad_serving` VALUES ('2', '2022-03-01');
INSERT INTO `ad_serving` VALUES ('2', '2022-03-02');
INSERT INTO `ad_serving` VALUES ('2', '2022-03-04');
INSERT INTO `ad_serving` VALUES ('2', '2022-03-05');
INSERT INTO `ad_serving` VALUES ('2', '2022-03-06');
INSERT INTO `ad_serving` VALUES ('2', '2022-03-07');
INSERT INTO `ad_serving` VALUES ('3', '2022-03-01');
INSERT INTO `ad_serving` VALUES ('3', '2022-03-03');
INSERT INTO `ad_serving` VALUES ('3', '2022-03-05');
INSERT INTO `ad_serving` VALUES ('3', '2022-03-06');

实现思路

可能不是最优思路，以下是我的实现方法。

先将数据按照条件，日期进行排序，这里是根据订单ID分区，然后投放日期升序排列。根据顺序新增一个行号列，用于后面起始日期计算。
这里之前我用的row_number()，前提是没有重复的日期。有重复日期的话请用dense_rank()，这样下一步计算起始日期才对

1
2
#分组添加行号
create table temp_ad_serving as select ad_serving.*, dense_rank() over(partition by order_id order by serving_date) AS r from ad_serving order by order_id, serving_date
order_id serving_date r
1 2022-03-01 1
1 2022-03-02 2
1 2022-03-03 3
1 2022-03-05 4
1 2022-03-06 5
2 2022-03-01 1
2 2022-03-02 2
2 2022-03-04 3
2 2022-03-05 4
2 2022-03-06 5
2 2022-03-07 6
3 2022-03-01 1
3 2022-03-03 2
3 2022-03-05 3
3 2022-03-06 4

然后计算分组辅助日期，用日期减去上面的行号=天数(这里天数去掉一天了, 不减去也可以，只是用于分组的条件)。新增一列分组辅助日期。

1
2
#添加分组辅助日期
create table temp_ad_serving_group as select temp_ad_serving.*, date_sub(serving_date, interval r - 1 day) 分组辅助日期 from temp_ad_serving
order_id serving_date r 分组辅助日期
1 2022-03-01 1 2022-03-01
1 2022-03-02 2 2022-03-01
1 2022-03-03 3 2022-03-01
1 2022-03-05 4 2022-03-02
1 2022-03-06 5 2022-03-02
2 2022-03-01 1 2022-03-01
2 2022-03-02 2 2022-03-01
2 2022-03-04 3 2022-03-02
2 2022-03-05 4 2022-03-02
2 2022-03-06 5 2022-03-02
2 2022-03-07 6 2022-03-02
3 2022-03-01 1 2022-03-01
3 2022-03-03 2 2022-03-02
3 2022-03-05 3 2022-03-03
3 2022-03-06 4 2022-03-03

根据分组条件，分组辅助日期进行分组（MySQL不需要把所有的分组条件写入SELECT，好评）。日期区间需要判断日期数量（这里之前用的count(*)，有重复日期的时候区间显示的不对，需要去除重复日期），就一天的时候直接显示，多天的话获取最小和最大时间进行展示。实际上就是连续天数统计，这里主要显示日期区间。

1
select order_id, case when count(distinct serving_date) = 1 then min(serving_date) else concat(min(serving_date), '-', max(serving_date)) end 投放期间, count(distinct serving_date) 连续天数 from temp_ad_serving_group group by order_id, 分组辅助日期
order_id 投放期间 连续天数
1 2022-03-01-2022-03-03 3
1 2022-03-05-2022-03-06 2
2 2022-03-01-2022-03-02 2
2 2022-03-04-2022-03-07 4
3 2022-03-01 1
3 2022-03-03 1
3 2022-03-05-2022-03-06 2