组SQL上的滞后函数

Question

我的桌子是这样的。

    PID JNo EV Phase    Name
    100 1   1   NULL    Test1
    101 2   2   NULL    Test2
    102 3   3   NULL    Test3
    103 4   4   1       Test4.1
    103 4   4   1       Test4.2
    103 4   4   1       Test4.3
    104 5   5   3       Test5.1
    104 5   5   3       Test5.2
    104 5   5   3       Test5.3
    105 6   6   3       Test6.1
    105 6   6   3       Test6.2
    105 6   6   3       Test6.3
    107 7   7   NULL    Test7.1
    107 7   7   NULL    Test7.2
    107 7   7   NULL    Test7.3

我必须创建一个列，该列说明我的阶段是否已被更改或基于基准。

例如：

• 对于PID=100，第一阶段为null，因此应该将其作为基线。
• 对于PID=101阶段是空的。以前的值(100)为null，因此应该将其作为101的基线。同样，对于102，它应该是空的。
• 对于PID=103阶段= 1，以前的值为(102)为空。所以应该是PhaseChange。
• 对于PID=104相位= 3。以前的值是(103)，它是1，所以应该是PhaseChange。
• 对于PID=105相= 3，以前的值是(104)，它是3，所以它应该被基线化。

样本数据：

PID JNo Ev  Phase   Name    PCH
100 1   1   NULL    Test1   Baselined
101 2   2   NULL    Test2   Baselined
102 3   3   NULL    Test3   Baselined
103 4   4   1       Test4.1 PhaseChange
103 4   4   1       Test4.2 PhaseChange
103 4   4   1       Test4.3 PhaseChange
104 5   5   3       Test5.1 PhaseChange
104 5   5   3       Test5.2 PhaseChange
104 5   5   3       Test5.3 PhaseChange
105 6   6   3       Test6.1 Baselined
105 6   6   3       Test6.2 Baselined
105 6   6   3       Test6.3 Baselined
107 7   7   NULL    Test7.1 PhaseChange
107 7   7   NULL    Test7.2 PhaseChange
107 7   7   NULL    Test7.3 PhaseChange

代码示例：

create Table #SampleTable
(ProjectID int,
JustNo int,
ERev int,
Phase int,
Name varchar(100) )

insert into #SampleTable values(100,1,1,Null,'Test1') 
insert into #SampleTable values(101,2,2,Null,'Test2')   
insert into #SampleTable values(102,3,3,Null,'Test3')   
insert into #SampleTable values(103,4,4,1,'Test4.1')   
insert into #SampleTable values(103,4,4,1,'Test4.2')   
insert into #SampleTable values(103,4,4,1,'Test4.3')   

insert into #SampleTable values(104,5,5,3,'Test5.1')   
insert into #SampleTable values(104,5,5,3,'Test5.2')   
insert into #SampleTable values(104,5,5,3,'Test5.3')   
insert into #SampleTable values(105,6,6,3,'Test6.1')   
insert into #SampleTable values(105,6,6,3,'Test6.2')   
insert into #SampleTable values(105,6,6,3,'Test6.3')   
insert into #SampleTable values(107,7,7,null,'Test7.1')   
insert into #SampleTable values(107,7,7,null,'Test7.2')   
insert into #SampleTable values(107,7,7,null,'Test7.3')   

select * from #SampleTable

我试过使用Lag函数，但没有运气:(

Answer 1

您需要一个子查询来计算以前的状态并分配唯一的row_number。然后使用DENSE_RANK()为每个组签名相同的id。

SQL Fiddle演示

WITH phases as (
  SELECT DISTINCT 
         ProjectID,
         Phase,
         DENSE_RANK() over (ORDER BY ProjectID) as rn      
  FROM SampleTable
), table_rank as (
  SELECT *,
         DENSE_RANK() over (ORDER BY ProjectID) as rn           
  FROM SampleTable
)
SELECT t.*, p.*,
       CASE WHEN p.Phase IS NULL AND t.Phase IS NULL THEN 'Baselined'
            WHEN p.Phase = t.Phase THEN 'Baselined'
            ELSE 'PhaseChange'
       END PCH
FROM table_rank t
LEFT JOIN  phases p
       ON t.rn = p.rn + 1

输出

我添加了额外的列，这样您就可以调试结果。

/ --            table_rank                       -- / --          phase_rn      --/
| ProjectID | JustNo | ERev |  Phase |    Name | rn | ProjectID |  Phase |     rn |         PCH |
|-----------|--------|------|--------|---------|----|-----------|--------|--------|-------------|
|       100 |      1 |    1 | (null) |   Test1 |  1 |    (null) | (null) | (null) |   Baselined |
|       101 |      2 |    2 | (null) |   Test2 |  2 |       100 | (null) |      1 |   Baselined |
|       102 |      3 |    3 | (null) |   Test3 |  3 |       101 | (null) |      2 |   Baselined |
|       103 |      4 |    4 |      1 | Test4.1 |  4 |       102 | (null) |      3 | PhaseChange |
|       103 |      4 |    4 |      1 | Test4.2 |  4 |       102 | (null) |      3 | PhaseChange |
|       103 |      4 |    4 |      1 | Test4.3 |  4 |       102 | (null) |      3 | PhaseChange |
|       104 |      5 |    5 |      3 | Test5.1 |  5 |       103 |      1 |      4 | PhaseChange |
|       104 |      5 |    5 |      3 | Test5.2 |  5 |       103 |      1 |      4 | PhaseChange |
|       104 |      5 |    5 |      3 | Test5.3 |  5 |       103 |      1 |      4 | PhaseChange |
|       105 |      6 |    6 |      3 | Test6.1 |  6 |       104 |      3 |      5 |   Baselined |
|       105 |      6 |    6 |      3 | Test6.2 |  6 |       104 |      3 |      5 |   Baselined |
|       105 |      6 |    6 |      3 | Test6.3 |  6 |       104 |      3 |      5 |   Baselined |
|       107 |      7 |    7 | (null) | Test7.1 |  7 |       105 |      3 |      6 | PhaseChange |
|       107 |      7 |    7 | (null) | Test7.2 |  7 |       105 |      3 |      6 | PhaseChange |
|       107 |      7 |    7 | (null) | Test7.3 |  7 |       105 |      3 |      6 | PhaseChange |

Answer 2

这里。有两件事要记住。初始负载将显示为相位差，因为没有以前的比较。此外，当阶段为null时，它必须被一个值替换，以便与前面的值进行比较(反之亦然)，所以我使用了isnull，并用-1替换了空。理想情况下，它们不会为null，或者确保用一个不正常使用的值替换null。

DECLARE @SampleTable TABLE
(ProjectID int,
JustNo int,
ERev int,
Phase int,
Name varchar(100) )

insert into @SampleTable values(100,1,1,Null,'Test1'),(101,2,2,Null,'Test2'),(102,3,3,Null,'Test3'),(103,4,4,1,'Test4.1'),(103,4,4,1,'Test4.2'),(103,4,4,1,'Test4.3'),(104,5,5,3,'Test5.1'),(104,5,5,3,'Test5.2'),(104,5,5,3,'Test5.3'),(105,6,6,3,'Test6.1'),(105,6,6,3,'Test6.2'),(105,6,6,3,'Test6.3'),(107,7,7,null,'Test7.1'),(107,7,7,null,'Test7.2'),(107,7,7,null,'Test7.3')


select
    PROJECTID
    ,JUSTNO
    ,EREV
    ,PHASE
    ,NAME
    ,CASE LAG(ISNULL(PHASE,'-1')) OVER (ORDER BY PROJECTID, PHASE)
        WHEN ISNULL(PHASE,'-1') THEN 'BASELINED'
        ELSE 'PHASECHANGED'
        END AS PHC
from @SampleTable