MYSQL8怎么查询订单信息表中最少连续3天下单的用户id？

MellowYou

已于 2023-04-03 11:19:48 修改

阅读量893

点赞数 1

分类专栏： MYSQL 文章标签： sql 数据库 mysql

于 2023-04-03 10:21:02 首次发布

本文链接：https://blog.csdn.net/Jeck_wu/article/details/129922721

版权

MYSQL 专栏收录该内容

9 篇文章 1 订阅

订阅专栏

文章介绍了如何在MySQL8环境中，通过使用ROW_NUMBER()窗口函数结合GROUPBY对订单信息表进行分组和排序，来找出至少连续3天有下单记录的用户ID。首先，按用户ID和日期分组并计算序号，然后通过子查询和EXISTS操作判断连续性。最后，提供了一个优化后的SQL查询，减少了表扫描次数。

摘要由CSDN通过智能技术生成

查询订单信息表中最少连续3天下单的用户id

MYSQL8实操练习

表结构

CREATE TABLE `2_order_info` (
  `user_id` int DEFAULT NULL,
  `total_amount` int DEFAULT NULL,
  `create_date` varchar(255) DEFAULT NULL,
  `order_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

思路：
第一步：根据用户id和日期分组，得到下单日期和用户id，然后通过 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_date)，然后再根据user_id进行子分组，按照下单日期排序,得到连续日期，且标识序号。
第二步：通过第一步的结果数据去EXISTS下，筛选需求数据，通过两个一样的数据集【上边SELECT数据集1，EXISTS里数据集2】，关联一下就出来了。需求是要查询连续三天下单的userId，所以利用刚刚通过ROW_NUMBER分组出来的数据关联就好了。
通过条件userId，第一个数据集日期+2，第一个数据集序号+2【这里序号最好用+2的方式，还可以用第二个数据集的序号-2，日期-2，但是序号列需要强转！不然SQL会报错，因为ROW_NUMBER只支持无符号整数，序号-2会产生负数】就能知道某个用户是否连续下单三天了。
在这里插入图片描述

具体SQL

SELECT DISTINCT user_id
FROM (
    SELECT 
-- 				order_id,
        user_id, 
        create_date, 
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_date) AS rnk,
				sum(total_amount) sum_amount
    FROM 2_order_info
		GROUP BY user_id,create_date
) t1
WHERE EXISTS (
    SELECT 1 FROM (
        SELECT 
            user_id, 
            create_date, 
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_date) AS rnk
        FROM 2_order_info
				GROUP BY user_id,create_date
    ) t2
    WHERE t1.user_id = t2.user_id 
    AND t1.create_date = DATE_ADD(t2.create_date, INTERVAL 2 DAY) 
    AND t1.rnk = t2.rnk+2
);

优化后
上面SQL扫了两次表，下面优化了，在同一个数据集中重复筛选操作！

SELECT DISTINCT
	user_id 
FROM
	(
	SELECT
		user_id,
		create_date,
		rn,
		date_sub( create_date, INTERVAL rn DAY ) AS group_col 
	FROM
		(
		SELECT
			user_id,
			create_date,
			ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY create_date ) rn 
		FROM
			( SELECT user_id, create_date FROM 2_order_info GROUP BY user_id, create_date ) t1 
		GROUP BY
			user_id,
			create_date 
		) t2 
	) t3
	GROUP BY user_id,group_col
	HAVING COUNT(*)>=3
	```