英文:
Delete all rows except first N rows within each time interval group
问题
我有一张表：
| ID | DateTime |
|---|---|
| 1 | 2022-01-30 01:02:03 |
| 1 | 2022-01-30 01:34:03 |
| 1 | 2022-01-30 02:59:03 |
| 2 | 2022-01-30 01:02:03 |
| 2 | 2022-01-30 01:34:03 |
| 2 | 2022-01-30 02:59:03 |
我想要删除除了每个唯一ID的每个小时之外的所有行。因此，结果表将如下所示：
| ID | DateTime |
|---|---|
| 1 | 2022-01-30 01:02:03 |
| 1 | 2022-01-30 02:59:03 |
| 2 | 2022-01-30 01:02:03 |
| 2 | 2022-01-30 02:59:03 |
英文:
I have a table:
| ID | DateTime |
|---|---|
| 1 | 2022-01-30 01:02:03 |
| 1 | 2022-01-30 01:34:03 |
| 1 | 2022-01-30 02:59:03 |
| 2 | 2022-01-30 01:02:03 |
| 2 | 2022-01-30 01:34:03 |
| 2 | 2022-01-30 02:59:03 |
And I would like to delete all the rows except for 1 every hour for each unique ID. So the resulting table would look like:
| ID | DateTime |
|---|---|
| 1 | 2022-01-30 01:02:03 |
| 1 | 2022-01-30 02:59:03 |
| 2 | 2022-01-30 01:02:03 |
| 2 | 2022-01-30 02:59:03 |
答案1
得分: 3
你可以使用通用表表达式 (CTE) 和窗口函数：
with cte as (
select *, row_number() over (
partition by id, cast(datetime as date), datepart(hour, datetime)
order by datetime
) as rn
from t
)
select * -- delete
from cte
where rn > 1
一旦确认查询包含正确的行，请将select *更改为delete。
英文:
You can use a cte (they could be used for delete) and window functions:
with cte as (
select *, row_number() over (
partition by id, cast(datetime as date), datepart(hour, datetime)
order by datetime
) as rn
from t
)
select * -- delete
from cte
where rn > 1
Change select * to delete once you're sure that the query contains the correct rows.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论