时间戳

首页 > 解决方案 > SCD 类型 2 的 SQL 查询

sql - SCD 类型 2 的 SQL 查询

问题描述

我收到下表，但存在以下问题：

从数据集中创建一个渐变维度类型 2。EMPLOYEE 表有每个员工的每日记录。类型 2 - 将具有有效数据和到期日期。

员工ID 日期 姓名 经理编号
123 1-3月 约翰·史密斯 1
123 3月2日 约翰·史密斯 1
123 3-3月 约翰·史密斯 2
123 3月4日 约翰·史密斯 3
123 3月5日 约翰·史密斯 3

我相信我的目标表应该是这样的：

员工ID 姓名 经理编号 生效日期 截止日期
123 约翰·史密斯 1 1-3月 3-3月
123 约翰·史密斯 2 3-3月 3月4日
123 约翰·史密斯 3 3月4日 无效的

我尝试了以下查询：

SELECT employee_id, name, manager_id,
CASE
    WHEN LAG(manager_id) OVER() != manager_id THEN e.date 
    WHEN e.date = FIRST_VALUE(e.date) OVER() THEN e.date
    ELSE NULL
END as "Effective Date",
CASE 
    WHEN LEAD(manager_id) OVER() != manager_id THEN LEAD(e.date) OVER()
    ELSE NULL
END as "Expiration Date"
FROM employee e

我的结果表如下：

员工ID 姓名 经理编号 生效日期 截止日期
123 约翰·史密斯 1 1-3月 无效的
123 约翰·史密斯 1 无效的 3-3月
123 约翰·史密斯 2 3-3月 3月4日
123 约翰·史密斯 3 3月4日 无效的
123 约翰·史密斯 3 无效的 无效的

有谁知道我可以改变我的查询以实现我的目标表，基于我迄今为止所取得的成就？我不知何故只需要产生 3 个经理 ID，但 distinct 不起作用。此外，我需要找到一种方法来组合每个经理 ID 的生效日期和到期日期。任何帮助都将不胜感激。

标签： sqlsql-servertsqlscdscd2

解决方案

以下内容满足您的需求，并展示了如何添加 DDL+DML。它可能有点令人费解，但我看不到简化它的明显方法。

该解决方案考虑了经理可能重复的可能性。它并不假设每一天都会存在，所以如果一天不见了，它仍然可以工作。

declare @Test table (EmployeeID int, [Date] date, [Name] varchar(32), ManagerID int);

insert into @Test (EmployeeID, [Date], [Name], ManagerID)
values
(123, '1 Mar 2021', 'John Smith', 1),
(123, '2 Mar 2021', 'John Smith', 1),
(123, '3 Mar 2021', 'John Smith', 2),
(123, '4 Mar 2021', 'John Smith', 3),
(123, '5 Mar 2021', 'John Smith', 3);
--(123, '6 Mar 2021', 'John Smith', 2);

select EmployeeId, [Name], ManagerId, MinDate
  -- Use lead to get the last date of the next grouping - since it could in theory be more than one day on
  , lead(MinDate) over (partition by EmployeeId, [Name] order by Grouped) MaxDate
from (
  -- Get the min and max dates for a given grouping
  select EmployeeId, [Name], ManagerId, min([Date]) MinDate, max([Date]) MaxDate, Grouped
  from (
    select *
       -- Sum the change in manager to ensure that if a manager is repeated they form a different group
       , sum(Lagged) over (order by Date asc) Grouped
    from (
      select *
        -- Lag the manager to detect when it changes
        , case when lag(ManagerId,1,-1) over (order by [Date] asc) <> ManagerId then 1 else 0 end Lagged
      from @Test
    ) X
  ) Y
  group by EmployeeId, [Name], ManagerId, Grouped
) Z
order by EmployeeId, [Name], Grouped;

回报：

员工ID 姓名 经理编号 最小日期 最大日期
123 约翰·史密斯 1 2021-03-01 2021-03-03
123 约翰·史密斯 2 2021-03-03 2021-03-04
123 约翰·史密斯 3 2021-03-04 无效的

推荐阅读