SQL 清理历史记录重复了中间值

Question

我注意到我的表有多个冗余值应该清理，这是一个记录价格变化的表，所以我想通过以下方式清理：

product | price | date
------------------------
1       | 1     | 1
1       | 1     | 2
1       | 1     | 3
1       | 1     | 4
2       | 77    | 5
1       | 1     | 6
1       | 2     | 7
1       | 2     | 8
1       | 1     | 9
1       | 1     | 10
1       | 1     | 11
1       | 1     | 12
1       | 3     | 13

对此：

product | price | date
------------------------
1       | 1     | 1
2       | 77    | 5
1       | 2     | 7
1       | 1     | 9
1       | 3     | 13

还假设在这种情况下，列id与date相同。

SELECT DISTINCT ON (product, price)不起作用，因为它会忽略第9天或1product1更改， 问题是我想按product分组，price但只能根据date的相关变化以一定的间隔分组。

即使可以订购product也很难忽略date并price更改顺序。

目标是删除不在预期结果表中的所有 ID。

有人有什么建议吗？

Answer 1

这是一个缺口和孤岛问题，您希望将具有相同价格的同一产品的相邻行组合在一起。

这是一种使用行号之间的差异来定义组的方法

select product, price, min(date) date
from (
select 
t.*,
row_number() over(partition by product order by date) rn1,
row_number() over(partition by product, price order by date) rn2
from mytable t
) t
group by product, price, rn1 - rn2
order by min(date)

DB小提琴上的演示：

产品展示 | 价格 | 日期 ------: |----: |---: , ρ    , ρ   1 2 |   77 |   5 , ρ    2 |   7 , ρ    , ρ   9 , ρ    1>  13

Answer 2

删除重复的行并保持打开(使用 min() 或 max() 保留最旧/最新的行)

您可以通过分组来筛选哪些列应确定重复项

DELETE FROM MyTable WHERE RowId NOT IN (SELECT MIN(RowId) FROM MyTable GROUP BY Col1, Col2, Col3);

Answer 3

当价格发生变化时，您似乎想要第一行。 如果是这样，我建议lag()：

select t.product, t.product, t.price
from (select t.*,
lag(price) over (partition by product order by date) as prev_price
from t
) t
where prev_price is null or prev_price <> price;

无需聚合。 此解决方案应该比使用聚合和窗口函数的解决方案更合适。