RANK() 在分区上重置等级

如何

获得在分区更改时重新启动的 RANK？我有这张表：

ID    Date        Value  
1     2015-01-01  1  
2     2015-01-02  1 <redundant  
3     2015-01-03  2  
4     2015-01-05  2 <redundant  
5     2015-01-06  1  
6     2015-01-08  1 <redundant  
7     2015-01-09  1 <redundant  
8     2015-01-10  2  
9     2015-01-11  3  
10    2015-01-12  3 <redundant

并且我正在尝试删除所有值未与上一个条目更改的行（标记为<冗余>）。我尝试使用游标，但花费的时间太长，因为该表有 ~5000 万行。

我也尝试过使用RANK：

SELECT ID, Date, Value,
RANK() over(partition by Value order by Date ASC) Rank,
FROM DataLogging 
ORDER BY Date ASC

但我得到：

ID    Date        Value  Rank   (Rank)
1     2015-01-01  1      1      (1)
2     2015-01-02  1      2      (2)
3     2015-01-03  2      1      (1)
4     2015-01-05  2      2      (2)
5     2015-01-06  1      3      (1)
6     2015-01-08  1      4      (2)
7     2015-01-09  1      5      (3)
8     2015-01-10  2      3      (1)
9     2015-01-11  3      1      (1)
10    2015-01-12  3      2      (2)

paranthes 是我想要的 Rank，这样我就可以过滤掉 Rank = 1 的行并删除其余的行。

编辑：我已经接受了似乎最容易写的答案，但不幸的是，没有一个答案运行得足够快，无法删除行。最后，我决定毕竟使用CURSOR。我已经将数据拆分为大约 250k 行的 chucks，光标在每批 250k 行的 ~11 分钟内运行并删除行，下面的答案使用 DELETE，每批 250k 行需要 ~35 分钟。

这里有一个有点复杂的方法：

WITH CTE AS
(
    SELECT  *, 
            ROW_NUMBER() OVER(ORDER BY [Date]) RN1,
            ROW_NUMBER() OVER(PARTITION BY Value ORDER BY [Date]) RN2
    FROM dbo.YourTable
), CTE2 AS
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Value, RN1 - RN2 ORDER BY [Date]) N
    FROM CTE
)
SELECT *
FROM CTE2
ORDER BY ID;

结果是：

╔════╦════════════╦═══════╦═════╦═════╦═══╗
║ ID ║    Date    ║ Value ║ RN1 ║ RN2 ║ N ║
╠════╬════════════╬═══════╬═════╬═════╬═══╣
║  1 ║ 2015-01-01 ║     1 ║   1 ║   1 ║ 1 ║
║  2 ║ 2015-01-02 ║     1 ║   2 ║   2 ║ 2 ║
║  3 ║ 2015-01-03 ║     2 ║   3 ║   1 ║ 1 ║
║  4 ║ 2015-01-05 ║     2 ║   4 ║   2 ║ 2 ║
║  5 ║ 2015-01-06 ║     1 ║   5 ║   3 ║ 1 ║
║  6 ║ 2015-01-08 ║     1 ║   6 ║   4 ║ 2 ║
║  7 ║ 2015-01-09 ║     1 ║   7 ║   5 ║ 3 ║
║  8 ║ 2015-01-10 ║     2 ║   8 ║   3 ║ 1 ║
║  9 ║ 2015-01-11 ║     3 ║   9 ║   1 ║ 1 ║
║ 10 ║ 2015-01-12 ║     3 ║  10 ║   2 ║ 2 ║
╚════╩════════════╩═══════╩═════╩═════╩═══╝

要删除不需要的行，您只需要执行以下操作：

DELETE FROM CTE2
WHERE N > 1;

select * 
from  ( select ID, Date, Value, lag(Value, 1, 0) over (order by ID) as ValueLag 
        from table ) tt
where ValueLag is null or ValueLag <> Value

如果订单是日期，则结束（按日期排序）

这应该显示你的好坏 - 它基于ID - 你需要日期然后修改
它可能看起来很长，但它应该非常有效

declare @tt table  (id tinyint, val tinyint);
insert into @tt values 
( 1, 1),
( 2, 1),
( 3, 2),
( 4, 2),
( 5, 1),
( 6, 1),
( 7, 1),
( 8, 2),
( 9, 3),
(10, 3);
select id, val, LAG(val) over (order by id) as lagVal
from @tt;
-- find the good
select id, val 
from ( select id, val, LAG(val) over (order by id) as lagVal
       from @tt 
     ) tt
where  lagVal is null or lagVal <> val 
-- select the bad 
select tt.id, tt.val 
  from @tt tt
  left join ( select id, val 
                from ( select id, val, LAG(val) over (order by id) as lagVal
                         from @tt 
                     ) ttt
               where   ttt.lagVal is null or ttt.lagVal <> ttt.val 
            ) tttt 
    on tttt.id = tt.id 
 where tttt.id is null

如果要

删除行，我建议您使用lag()：

with todelete as (
      select t.*, lag(value) over (order by date) as prev_value
      from t
     )
delete from todelete
    where value = prev_value;

我不太确定rank()与这个问题有什么关系。

编辑：

查看未使用相同逻辑删除的行：

with todelete as (
      select t.*, lag(value) over (order by date) as prev_value
      from t
     )
select *
from todelete
where value <> prev_value or prev_value is null;

where 子句只是第一个查询中 where 子句的反转，考虑了NULL值。

这很有趣，所以我想我会跳进去。不幸的是，在没有首先转换数据的情况下创建具有RANK()（或者更确切地说，ROW_NUMBER()）的解决方案似乎是无法获得的。为了转换数据，我想出了这个使用 1 ROW_NUMBER()的解决方案：

;WITH Ordered AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY [Date]) AS [Row], *
    FROM DataLogging
),
Final AS
(
    SELECT
        o1.*, NULLIF(o1.Value - ISNULL(o2.Value, o1.Value - 1), 0) [Change]
    FROM
        Ordered o1
        LEFT JOIN Ordered o2 ON
            o1.[Row] = o2.[Row] + 1
)
SELECT * FROM Final

在最后一Change列中，如果值没有变化，则将NULL该值（但如果有更改，则将有差异）。

因此，要进行删除，请将选择更改为

DELETE FROM DataLogging where Change IS NULL

编辑：滞后也可以在这里工作，但我在进行过程中可视化解决方案，完全忘记了这一点。

为我的情况工作！谢谢我必须为员工获取有关先前reports_to值和 effdt 的report_to更改。换句话说，每个reports_to更改员工的最小有效日期行。

与检查为（选择 T.emplid，T.reports_to，T.effdt， lag（reports_to） over （排序）作为prev_value 从 PS_JOB T )选择*从到检查其中reports_to <> prev_value或prev_value为空;

进一步添加了更改，如P

相关内容

最新更新

热门标签：