有个搞数仓的朋友不知道从哪儿弄了个题。。。
做了做体验了一下。。。
记录记录。

<>分析

要保证每天都要做新题
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

技术
今日推荐
阅读数 170189
阅读数 23
阅读数 5
阅读数 0
下载桌面版
GitHub
百度网盘(提取码:draw)
Gitee
云服务器优惠
阿里云优惠券
腾讯云优惠券
华为云优惠券
站点信息
问题反馈
邮箱:ixiaoyang8@qq.com
QQ群:766591547
关注微信