时间戳

首页 > 解决方案 > SQL - 查找快照日期之间的新开始者和离开者的数量

sql - SQL - 查找快照日期之间的新开始者和离开者的数量

问题描述

我有一个 SQL 表staff，它在特定日期拍摄快照并添加每一行staffID和相应的DateID.

我需要找出自下一次以来有多少员工加入DateID，以及离开了多少。

因此，在staff下面 DateID B 的示例表中，StaffID's002和003fromDateID A不存在，所以有 'left'，并且DateID B有staffID's 004, 005，006它们不存在，DateID所以是 'new'。

StaffID  DateID
007      C
005      C
006      B
005      B
004      B
001      B
003      A
002      A
001      A

我推测这些结果将如何出现在下面。

DateID  New  Leavers
A       0    2
B       3    2
C       1    3

我目前也是唯一解决这个问题的方法是通过之前DateID的每一个，DateID然后加入较旧的日期，计算旧日期为空的行以获取新员工的数量，并为离开者交换表格。

SELECT t1.DateID, count(*) AS Total

(SELECT *
FROM staff
WHERE DateID = 'B') t1

LEFT JOIN

(SELECT *
FROM staff
WHERE DateID = 'A') t2

ON t1.StaffID = t2.StaffID
WHERE t2.StaffID is null
GROUP BY t1.DateID

这种方法对于一个更大的表来说效率非常低，希望任何人都可以在一个脚本中找到一种方法。或者，只为新员工准备的脚本和只为离职人员准备的脚本也一样好。

按照@Larnu 的要求，我添加了一个snapshot包含所有DateID 的表。该staff表被过滤为仅显示每周的 DateID。

DateID Weekly Monthly Yearly
A      Y      Y       N
B      Y      N       N
C      Y      N       N
D      N      N       N
E      Y      Y       N
F      N      N       Y

标签： sqlsql-serverjoindifference

解决方案

LEAD和LAG窗口功能在这里会有所帮助。

由于DateIDs 不是连续的，因此您还需要计算LEAD/LAG它，然后加入它

SELECT
  s.DateID,
  [New]   = COUNT(CASE WHEN s.PrevID IS NULL OR s.PrevID <> d.PrevDateID THEN 1 END),
  Leavers = COUNT(CASE WHEN s.NextID IS NULL OR s.NextID <> d.NextDateID THEN 1 END)
FROM (
    SELECT *,
      PrevDateID =  LAG(DateID) OVER (ORDER BY DateID),
      NextDateID = LEAD(DateID) OVER (ORDER BY DateID)
    FROM Dates d
) d
JOIN (
    SELECT *,
      PrevID =  LAG(s.DateID) OVER (PARTITION BY StaffID ORDER BY DateID),
      NextID = LEAD(s.DateID) OVER (PARTITION BY StaffID ORDER BY DateID)
    FROM staff s
) s ON s.DateID = d.DateID
GROUP BY
  s.DateID;

推荐阅读