主页 / dba / 问题 / 251271
Accepted
Sean Brookins
Asked: 2019-10-17 09:19:21 +0800 CST

了解窗口函数以在保留真实更改的同时删除重复记录

  • 772

我接近解决这个问题，但我只是被困在墙上。我正在尝试理解 Aaron Betrand 的一篇文章，并将其应用于我遇到的一种情况，在这种情况下，由于我继承了先前的设计错误，我有一个大量重复的更改表。示例数据集在概念上与我的真实数据集相同，除了 SortOrder 通常是日期时间值而不是整数。我试过的代码在这里：

; with main as (
select *, ROW_NUMBER() over (partition by ID, Val, sortorder order by ID, SortOrder) as "Rank"
, row_number() over (partition by ID, val order by ID, sortorder) as "s_rank" 
from 
(values (1, 'A', 1), (1, 'A', 1), (1, 'B', 2), (1, 'C', 3), (1, 'B', 4), (1, 'A', 5), (1, 'A', 5), (2, 'A', 1), (2, 'B', 2), (2, 'A', 3), (3, 'A', 1), (3, 'A', 1), (3, 'A', 2) ) 
        as x("ID", "VAL", "SortOrder") 
group by id, val, SortOrder
--order by ID, "SortOrder"
)
, cte_rest as (
select *
from main
where "s_rank" > 1
)
select *
from main left join cte_rest rest on main.id = rest.id and main.s_rank > 1 and main.SortOrder = rest.SortOrder
--where not exists (select 1 from cte_rest r where r.id = main.id and r.val <> main.VAL and main.s_rank < s_rank)

order by main.ID, main.SortOrder

结果几乎是有效的；但是，最后一行突出显示了我无法解释的情况：日期更改，值没有更改。我希望排除此记录，因为它不是真正的值更改。

╔════╦═════╦═══════════╦══════╦════════╦══════╦══════╦═══════════╦══════╦════════╗
║ ID ║ VAL ║ SortOrder ║ Rank ║ s_rank ║  ID  ║ VAL  ║ SortOrder ║ Rank ║ s_rank ║
╠════╬═════╬═══════════╬══════╬════════╬══════╬══════╬═══════════╬══════╬════════╣
║  1 ║ A   ║         1 ║    1 ║      1 ║ NULL ║ NULL ║ NULL      ║ NULL ║ NULL   ║
║  1 ║ B   ║         2 ║    1 ║      1 ║ NULL ║ NULL ║ NULL      ║ NULL ║ NULL   ║
║  1 ║ C   ║         3 ║    1 ║      1 ║ NULL ║ NULL ║ NULL      ║ NULL ║ NULL   ║
║  1 ║ B   ║         4 ║    1 ║      2 ║ 1    ║ B    ║ 4         ║ 1    ║ 2      ║
║  1 ║ A   ║         5 ║    1 ║      2 ║ 1    ║ A    ║ 5         ║ 1    ║ 2      ║
║  2 ║ A   ║         1 ║    1 ║      1 ║ NULL ║ NULL ║ NULL      ║ NULL ║ NULL   ║
║  2 ║ B   ║         2 ║    1 ║      1 ║ NULL ║ NULL ║ NULL      ║ NULL ║ NULL   ║
║  2 ║ A   ║         3 ║    1 ║      2 ║ 2    ║ A    ║ 3         ║ 1    ║ 2      ║
║  3 ║ A   ║         1 ║    1 ║      1 ║ NULL ║ NULL ║ NULL      ║ NULL ║ NULL   ║
║  3 ║ A   ║         2 ║    1 ║      2 ║ 3    ║ A    ║ 2         ║ 1    ║ 2      ║
╚════╩═════╩═══════════╩══════╩════════╩══════╩══════╩═══════════╩══════╩════════╝

我的一位同事建议了这段代码，虽然我可以了解它是如何到达的，但我不明白为什么第一个代码示例不起作用。在我看来，这需要大量额外的解析，并且对于大型数据集，我会担心性能影响。


WITH cte1
     AS (SELECT [id]
              , [val]
              , [sortorder]
              , ROW_NUMBER() OVER(PARTITION BY [id]
                                             , [val]
                                             , [sortorder]
                ORDER BY [id]
                       , [sortorder]) AS "rankall"
         FROM   (VALUES
                        ( 1, 'A', 1 ),
                        ( 1, 'A', 1 ),
                        ( 1, 'B', 2 ),
                        ( 1, 'C', 3 ),
                        ( 1, 'B', 4 ),
                        ( 1, 'A', 5 ),
                        ( 1, 'A', 5 ),
                        ( 2, 'A', 1 ),
                        ( 2, 'B', 2 ),
                        ( 2, 'A', 3 ),
                        ( 3, 'A', 1 ),
                        ( 3, 'A', 1 ),
                        ( 3, 'A', 2 )) AS x("id", "val", "sortorder")),
     ctedropped
     AS (SELECT [id]
              , [val]
              , [sortorder]
              , ROW_NUMBER() OVER(PARTITION BY [id]
                                             , [val]
                                             , [sortorder]
                ORDER BY [id]
                       , [sortorder]) AS "rankall"
         FROM   cte1
         WHERE  [cte1].[rankall] > 1)
     SELECT [cte1].[id]
          , [cte1].[val]
          , [cte1].[sortorder]
     FROM   cte1
     WHERE  NOT EXISTS
     (
         SELECT *
         FROM   [ctedropped]
         WHERE  [cte1].[id] = [ctedropped].[id] AND 
                [cte1].[val] = [ctedropped].[val] AND 
                [cte1].[rankall] = [ctedropped].[rankall]
     )
     ORDER BY [cte1].[id]
            , [cte1].[sortorder];
sql-server

1 个回答

  1. Best Answer

    目前尚不清楚您的数据集和预期结果是否与引用的问题相同。我认为您正在寻找识别 id 更新为与以前不同的值的最新时间。在这种情况下，您可以尝试以下

    create table #test (
id int,
val varchar(1),
v_date datetime
)

insert into #test values (1,'A',getdate())
insert into #test values (1,'B',dateadd(mi,5,getdate()))
insert into #test values (1,'C',dateadd(mi,10,getdate()))
insert into #test values (2,'A',getdate())
insert into #test values (2,'B',dateadd(mi,15,getdate()))
insert into #test values (2,'B',dateadd(mi,20,getdate()))
insert into #test values (3,'A',getdate())
insert into #test values (3,'A',dateadd(mi,21,getdate()))
insert into #test values (3,'B',dateadd(mi,25,getdate()))
insert into #test values (3,'C',dateadd(mi,30,getdate()))
insert into #test values (4,'B',dateadd(mi,35,getdate()))
insert into #test values (4,'B',dateadd(mi,36,getdate()))
insert into #test values (4,'B',dateadd(mi,37,getdate()))
insert into #test values (5,'Z',dateadd(mi,-10,getdate()))

;with t1 as (
   select id,
          val,
          v_date,
          row_number() over(partition by id order by v_date asc) as rn
     from #test
), t2 as (

select t.id,
       t.val,
       t.v_date,
       row_number() over(partition by t.id order by t.v_date desc) as rn
  from t1 t
  left join t1 tt
    on t.id = tt.id
   and t.rn - 1 = tt.rn
  where t.val <> tt.val or tt.val is null
)

select *
  from t2

    分贝小提琴

    • 0

相关问题