时间戳

首页 > 解决方案 > 根据前一行的增量创建新的 RANK

sql - 根据前一行的增量创建新的 RANK

问题描述

几天来我一直在研究一个问题，但我似乎无法找到正确的解决方法。有人有想法吗？

案子

我们希望在员工辞职超过 1 天时创建一个新的序列号。我们有当前就业记录和上一个就业记录的增量，因此我们可以检查顺序。我们要计算相隔不超过 1 天的每个就业记录的最小（开始）和最大（结束）。

数据

员工 合同 单元 开始 结尾 三角洲
约翰·多伊 1 A单元 2014-01-01 2017-12-31 空值
约翰·多伊 2 A单元 2018-02-01 2018-12-31 31
约翰·多伊 3 B单元 2019-01-01 2020-05-31 1
约翰·多伊 4 A单元 2020-06-01 空值 1

通过查询，它应该返回：

员工 合同 单元 开始 结尾 三角洲 顺序
约翰·多伊 1 A单元 2014-01-01 2017-12-31 空值 1
约翰·多伊 2 A单元 2018-02-01 2018-12-31 31 2
约翰·多伊 3 B单元 2019-01-01 2020-05-31 1 2
约翰·多伊 4 A单元 2020-06-01 空值 1 2

这是因为序列 1 在 31-12-2017 结束，而新的序列在 2018 年 2 月开始，因此记录之间的间隔已超过 1 天。以下都具有 2 的序列，因为它正在继续。

询问

我已经用 lag() 和 lead() 尝试了一些东西，但我一直在用我拥有的数据样本让自己陷入困境。当我在全套上运行它时，它不会工作。

SELECT
    Employee,
    Start,
    End,
    DeltaPrevious,
    Delta,
    DeltaNext,
    case
        when DeltaPrevious IS NULL AND Delta = 1 then 1
        when DeltaPrevious = 1 AND Delta > 1 then min(Contract) OVER (PARTITION BY Employee ORDER BY Contract ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
        when DeltaPrevious > 1 AND Delta = 1 then min(Contract) OVER (PARTITION BY Employee ORDER BY Contract ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
    end as Sequence
FROM
    Contracts
ORDER BY
    Employee, Start ASC

希望有人有一个好主意。

谢谢，

标签： sqlsql-serverdatesequence

解决方案

如果我从第二个表中的定义中理解正确Sequence，那么您对​​.DeltaNext比对Delta(Previous). 这是一个尝试，包括使用另外两名员工创建示例输入日期的代码：

CREATE TABLE #input_table (Employee VARCHAR(255), [Contract] INT, Unit VARCHAR(6), [Start] DATE, [End] DATE)

INSERT INTO #input_table
VALUES
('John Doe',    1,  'Unit A',   '2014-01-01',   '2017-12-31'),
('John Doe',    2,  'Unit A',   '2018-02-01',   '2018-12-31'),
('John Doe',    3,  'Unit B',   '2019-01-01',   '2020-05-31'),
('John Doe',    4,  'Unit A',   '2020-06-01',   NULL),
('Alice',       1,  'Unit A',   '2020-01-01',   NULL),
('Bob',         1,  'Unit C',   '2020-01-01',   '2020-02-20')

首先我们创建增量：

SELECT *
    , DeltaPrev = DATEDIFF(DAY, LAG([End], 1, NULL) OVER(PARTITION BY Employee 
ORDER BY [Start]), [Start])  -- Not relevant (?)
    , DeltaNext = DATEDIFF(DAY, [End], LEAD([Start], 1, NULL) OVER(PARTITION BY Employee ORDER BY [Start]))
INTO #cte_delta -- I'll create a CTE at the end
FROM #input_table

然后我们定义Sequence：

SELECT *
    , [Sequence] = CASE WHEN DeltaNext > 1 THEN 1 ELSE 2 END
INTO #cte_sequence
FROM #cte_delta

然后，我们通过为具有连续/相同 s 的每个员工Sequence分配一个唯一的来对相同的 s 进行分组：ROW_NUMBERSequence

SELECT *
    , GRP = ROW_NUMBER() OVER(PARTITION BY Employee ORDER BY [Start]) - ROW_NUMBER() OVER(PARTITION BY Employee, [Sequence] ORDER BY [Start]) 
INTO #cte_grp
FROM #cte_sequence

最后我们计算合同期限的min和：max

SELECT *
    , MIN([Start]) OVER(PARTITION BY Employee, GRP) AS ContractStart
    , CASE WHEN COUNT(*) OVER(PARTITION BY Employee, GRP) = COUNT([End]) 
OVER(PARTITION BY Employee, GRP) THEN MAX([End]) OVER(PARTITION BY Employee, GRP) ELSE NULL END AS ContractEnd  
FROM cte_grp

COUNT(*)and比较是必要的COUNT([End])，否则ContractEnd将是最大非 NULL 值，即2018-02-01.

带 s 的整个代码在CTE这里：

WITH cte_delta AS (
    SELECT *
        , DeltaPrev = DATEDIFF(DAY, LAG([End], 1, NULL) OVER(PARTITION BY Employee ORDER BY [Start]), [Start])  -- Not relevant (?)
        , DeltaNext = DATEDIFF(DAY, [End], LEAD([Start], 1, NULL) OVER(PARTITION BY Employee ORDER BY [Start]))
    FROM #input_table
)
, cte_sequence AS (
    SELECT *
        , [Sequence] = CASE WHEN DeltaNext > 1 THEN 1 ELSE 2 END
    FROM cte_delta
)
, cte_grp AS (
SELECT *
    , GRP = ROW_NUMBER() OVER(PARTITION BY Employee ORDER BY [Start]) - ROW_NUMBER() OVER(PARTITION BY Employee, [Sequence] ORDER BY [Start])
FROM cte_sequence
) 
SELECT *
    , MIN([Start]) OVER(PARTITION BY Employee, GRP) AS ContractStart
    , CASE WHEN COUNT(*) OVER(PARTITION BY Employee, GRP) = COUNT([End]) OVER(PARTITION BY Employee, GRP) THEN MAX([End]) OVER(PARTITION BY Employee, GRP) ELSE NULL END AS ContractEnd  
FROM cte_grp

这里的输出：

员工 合同 单元 开始 结尾 DeltaPrev DeltaNext 顺序 玻璃钢 合同开始 合同结束
爱丽丝 1 A单元 2020-01-01 空值 空值 空值 2 0 2020-01-01 空值
鲍勃 1 C单元 2020-01-01 2020-02-20 空值 空值 2 0 2020-01-01 2020-02-20
约翰·多伊 1 A单元 2014-01-01 2017-12-31 空值 32 1 0 2014-01-01 2017-12-31
约翰·多伊 2 A单元 2018-02-01 2018-12-31 32 1 2 1 2018-02-01 空值
约翰·多伊 3 B单元 2019-01-01 2020-05-31 1 1 2 1 2018-02-01 空值
约翰·多伊 4 A单元 2020-06-01 空值 1 空值 2 1 2018-02-01 空值

随意DISTINCT根据您的需要选择记录。

推荐阅读