查找id在前几周可用，但在本周不可用

w8ntj3qf 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(233)

如何在滚动的基础上查找前几周存在但本周不可用的id。例如

Week1 has id 1,2,3,4,5
Week2 has id 3,4,5,7,8
Week3 has id 1,3,5,10,11

所以我找到了那个身份证 1 and 2 都不见了 week 2 和id 2,4,7,8 都不见了 week 3 从前两周开始，但是如何在一个滚动窗口上对分布在20多年期间的大量数据执行此操作
请查找示例数据集和预期输出。我希望输出根据 week_end 日期

Dataset

ID|WEEK_START|WEEK_END|APPEARING_DATE
7152|2015-12-27|2016-01-02|2015-12-27
8350|2015-12-27|2016-01-02|2015-12-27
7152|2015-12-27|2016-01-02|2015-12-29
4697|2015-12-27|2016-01-02|2015-12-30
7187|2015-12-27|2016-01-02|2015-01-01
8005|2015-12-27|2016-01-02|2015-12-27
8005|2015-12-27|2016-01-02|2015-12-29
6254|2016-01-03|2016-01-09|2016-01-03
7962|2016-01-03|2016-01-09|2016-01-04
3339|2016-01-03|2016-01-09|2016-01-06
7834|2016-01-03|2016-01-09|2016-01-03
7962|2016-01-03|2016-01-09|2016-01-05
7152|2016-01-03|2016-01-09|2016-01-07
8350|2016-01-03|2016-01-09|2016-01-09
2403|2016-01-10|2016-01-16|2016-01-10
0157|2016-01-10|2016-01-16|2016-01-11
2228|2016-01-10|2016-01-16|2016-01-14
4697|2016-01-10|2016-01-16|2016-01-14

Excepted Output

Partition1: WEEK_END=2016-01-02
ID|MAX(LAST_APPEARING_DATE) 
7152|2015-12-29
8350|2015-12-27
4697|2015-12-30
7187|2015-01-01
8005|2015-12-29

Partition1: WEEK_END=2016-01-09

ID|MAX(LAST_APPEARING_DATE) 
7152|2016-01-07
8350|2016-01-09
4697|2015-12-30
7187|2015-01-01
8005|2015-12-29
6254|2016-01-03
7962|2016-01-05
3339|2016-01-06
7834|2016-01-03

Partition3: WEEK_END=2016-01-10

ID|MAX(LAST_APPEARING_DATE) 
7152|2016-01-07
8350|2016-01-09
4697|2016-01-14
7187|2015-01-01
8005|2015-12-29
6254|2016-01-03
7962|2016-01-05
3339|2016-01-06
7834|2016-01-03
2403|2016-01-10
0157|2016-01-11
2228|2016-01-14

sql pyspark apache-spark-sql

来源：https://stackoverflow.com/questions/62989460/finding-ids-available-in-previous-weeks-but-not-in-current-week

2条答案

按热度按时间

ws51t4hk1#

请使用下面的查询，

select ID, MAX(APPEARING_DATE) from table_name
group by ID, WEEK_END;

或者，包括周末，

select ID, WEEK_END, MAX(APPEARING_DATE) from table_name
group by ID, WEEK_END;

赞(0）回复(0）举报 2021-07-24

mpgws1up2#

可以使用聚合：

select t.*, max(week_end)
from t
group by id
having max(week_end) < '2016-01-02';

调整中的日期 having 你想要的周末条款。
实际上，你的问题有点不清楚。我不确定晚些时候的周末会不会继续吵架。如果您想要“截止”数据，那么包括 where 条款：

select t.id, max(week_end)
from t
where week_end < '2016-01-02'
group by id
having max(week_end) < '2016-01-02';

如果要将其用于一系列日期，则可以使用派生表：

select we.the_week_end, t.id, max(week_end)
from (select '2016-01-02' as the_week_end union all
      select '2016-01-09' as the_week_end
     ) we cross join
     t
where t.week_end < we.the_week_end
group by id, we.the_week_end
having max(t.week_end) < we.the_week_end;

赞(0）回复(0）举报 2021-07-24

我来回答

查找id在前几周可用，但在本周不可用

2条答案

相关问题

热门标签

最新问答