使用Teradata或SQL删除在特定时间内出现的重复项
我有一个包含ID变量，日期和几个代理的数据集（请参见下面的示例）。每位患者已经对代理进行了几次测试，我想对第一个ID进行过滤，以显示第一个ID，并删除第一个ID的所有其他测试。之后，我再次想过滤第一个，并在整个数据集中删除所有其他内容。我还产生了显示一周，月和年的变量。
ID Date Week Month Year Agent
1 1 2010-12-09 49 12 2010 Agent1
2 1 2010-12-09 49 12 2010 Agent2
3 1 2010-12-09 49 12 2010 Agent3
4 1 2010-12-09 49 12 2010 Agent4
5 1 2010-12-09 49 12 2010 Agent1
6 1 2010-12-09 49 12 2010 Agent2
7 1 2010-12-09 49 12 2010 Agent3
8 1 2010-12-09 49 12 2010 Agent4
9 1 2010-12-27 52 12 2010 Agent1
10 1 2010-12-27 52 12 2010 Agent2
11 1 2010-12-27 52 12 2010 Agent3
12 1 2010-12-27 52 12 2010 Agent4
13 1 2011-01-14 2 1 2011 Agent1
14 1 2011-01-14 2 1 2011 Agent2
15 1 2011-01-14 2 1 2011 Agent3
16 1 2011-01-14 2 1 2011 Agent4
17 1 2011-01-14 2 1 2011 Agent1
18 1 2011-01-14 2 1 2011 Agent2
19 1 2011-01-14 2 1 2011 Agent3
20 1 2011-01-14 2 1 2011 Agent4
预期输出
ID Date Week Month Year Agent
1 1 2010-12-09 49 12 2010 Agent1
2 1 2010-12-09 49 12 2010 Agent2
3 1 2010-12-09 49 12 2010 Agent3
4 1 2010-12-09 49 12 2010 Agent4
13 1 2011-01-14 2 1 2011 Agent1
14 1 2011-01-14 2 1 2011 Agent2
15 1 2011-01-14 2 1 2011 Agent3
16 1 2011-01-14 2 1 2011 Agent4
I have a dataset containing an ID variable, a date, and several agents (see example below). The agents have been tested several times per patient and I want to filter for every ID the first one to appear and remove all the other tests appearing within 4 weeks after the first. After this, I again want to filter the first one and remove all the others appearing within 4 weeks - throughout the whole dataset. I also generated variables showing the week, month and year.
ID Date Week Month Year Agent
1 1 2010-12-09 49 12 2010 Agent1
2 1 2010-12-09 49 12 2010 Agent2
3 1 2010-12-09 49 12 2010 Agent3
4 1 2010-12-09 49 12 2010 Agent4
5 1 2010-12-09 49 12 2010 Agent1
6 1 2010-12-09 49 12 2010 Agent2
7 1 2010-12-09 49 12 2010 Agent3
8 1 2010-12-09 49 12 2010 Agent4
9 1 2010-12-27 52 12 2010 Agent1
10 1 2010-12-27 52 12 2010 Agent2
11 1 2010-12-27 52 12 2010 Agent3
12 1 2010-12-27 52 12 2010 Agent4
13 1 2011-01-14 2 1 2011 Agent1
14 1 2011-01-14 2 1 2011 Agent2
15 1 2011-01-14 2 1 2011 Agent3
16 1 2011-01-14 2 1 2011 Agent4
17 1 2011-01-14 2 1 2011 Agent1
18 1 2011-01-14 2 1 2011 Agent2
19 1 2011-01-14 2 1 2011 Agent3
20 1 2011-01-14 2 1 2011 Agent4
expected output
ID Date Week Month Year Agent
1 1 2010-12-09 49 12 2010 Agent1
2 1 2010-12-09 49 12 2010 Agent2
3 1 2010-12-09 49 12 2010 Agent3
4 1 2010-12-09 49 12 2010 Agent4
13 1 2011-01-14 2 1 2011 Agent1
14 1 2011-01-14 2 1 2011 Agent2
15 1 2011-01-14 2 1 2011 Agent3
16 1 2011-01-14 2 1 2011 Agent4
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论