一道SQL题

最新推荐文章于 2024-08-20 20:45:00 发布

青青的无痕

最新推荐文章于 2024-08-20 20:45:00 发布

阅读量1.3k

点赞数

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

本文链接：https://blog.csdn.net/gaobingjin/article/details/131815895

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

文章探讨了一个关于数据仓库的题目，要求保证用户在5天内每天都做新题，尤其是最后一天必须是新题。给出的SQL查询用于找出在特定日期范围内，最后一天做了新题的用户。查询涉及到对日志表的分析，通过比较不同日期用户的题目ID来判断是否为新题。

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

有个搞数仓的朋友不知道从哪儿弄了个题。。。
做了做体验了一下。。。
记录记录。
在这里插入图片描述

分析

要保证每天都要做新题
5天必须都做题，不然GG
最后一天必须做新题，如果最后一天做新题了，前面那几天没做新题，做的是老题

最后一天，如果没做题会被踢出
最后一天做的题，不是新题记录0，当求和之后为0，说明没有新题，那就踢出
如果是最后一天做了新题，第四天没做新题

下面的问题需要处理，

用户a，前四天（1，2，3）
第一天：1，2，3
第二天：1，2，3
第三天：1，2，3
第四天：1，2，3

第五天：1，2，3，4

保证最后一天做新题的案例：

SELECT
	info.user_id 
FROM
(
	SELECT
		two.user_id,
		sum(
		IF
			(
				two.question_id IN ( SELECT DISTINCT one.question_id FROM log_table AS one WHERE one.date between "2022-09-19" and "2022-09-22" AND one.user_id = two.user_id ),
				0,
				1 
			)) AS num 
	FROM
		log_table AS two 
	WHERE
		two.date = "2022-09-23"
	GROUP BY
		two.user_id 
	) info 
WHERE
	info.num >0

补充完善

select
	* 
from (SELECT
	info.user_id 
FROM
(
	SELECT
		two.user_id,
		sum(
		IF
			(
				two.question_id IN ( SELECT DISTINCT one.question_id FROM log_table AS one WHERE one.date between "2022-09-19" and "2022-09-22" AND one.user_id = two.user_id ),
				0,
				1 
			)) AS num 
	FROM
		log_table AS two 
	WHERE
		two.date = "2022-09-23"
	GROUP BY
		two.user_id 
	) info 
WHERE
	info.num >0) as d5 
left join (SELECT
	info.user_id 
FROM
(
	SELECT
		two.user_id,
		sum(
		IF
			(
				two.question_id IN ( SELECT DISTINCT one.question_id FROM log_table AS one WHERE one.date between "2022-09-19" and "2022-09-21" AND one.user_id = two.user_id ),
				0,
				1 
			)) AS num 
	FROM
		log_table AS two 
	WHERE
		two.date = "2022-09-22"
	GROUP BY
		two.user_id 
	) info 
WHERE
	info.num >0) as d4 
on d5.user_id = d4.user_id
left join (SELECT
	info.user_id 
FROM
(
	SELECT
		two.user_id,
		sum(
		IF
			(
				two.question_id IN ( SELECT DISTINCT one.question_id FROM log_table AS one WHERE one.date between "2022-09-19" and "2022-09-20" AND one.user_id = two.user_id ),
				0,
				1 
			)) AS num 
	FROM
		log_table AS two 
	WHERE
		two.date = "2022-09-21"
	GROUP BY
		two.user_id 
	) info 
WHERE
	info.num >0) as d3 
on d5.user_id = d3.user_id
left join (SELECT
	info.user_id 
FROM
(
	SELECT
		two.user_id,
		sum(
		IF
			(
				two.question_id IN ( SELECT DISTINCT one.question_id FROM log_table AS one WHERE one.date between "2022-09-19" and "2022-09-19" AND one.user_id = two.user_id ),
				0,
				1 
			)) AS num 
	FROM
		log_table AS two 
	WHERE
		two.date = "2022-09-20"
	GROUP BY
		two.user_id 
	) info 
WHERE
	info.num >0) as d2 
on d5.user_id = d2.user_id
left join (SELECT
	info.user_id 
FROM
(
	SELECT
		two.user_id,
		sum(
		IF
			(
				two.question_id IN ( SELECT DISTINCT one.question_id FROM log_table AS one WHERE one.date between "2022-09-18" and "2022-09-18" AND one.user_id = two.user_id ),
				0,
				1 
			)) AS num 
	FROM
		log_table AS two 
	WHERE
		two.date = "2022-09-19"
	GROUP BY
		two.user_id 
	) info 
WHERE
	info.num >0) as d1 
on d5.user_id = d1.user_id