sql - 聚集超前/滞后

Question

我有一个名为activity的表，其中有一个memberId和一个时间戳。我想找出在给定的月份中有多少成员执行了一项 Activity (即-在 Activity 表中有记录)，但在过去12个月中，谁做了而没有。我认为超前/滞后在这里会有所帮助，但是我很难缠住它。

(我在这里同时标记了Apache Hadoop和MS SQL Server，因为我可以在任何一个中都这样做，而且我认为我可以很容易地将一个解决方案转换为另一个解决方案)。

任何帮助表示赞赏!

谢谢!

Answer 1

使用LAG函数时，我们需要先为每个成员和月份创建一条记录，然后使用LAG函数获取重要的 Activity 月份，最后使用where子句仅获取我们想要的内容:

DECLARE 
  @year int = 2018, 
  @month int = 7;

WITH
  monthwise (MemberID, FirstOfMonth) AS (
    SELECT DISTINCT  MemberID, DATEADD(month, DATEDIFF(month, 0, ActivityDate), 0)
    FROM Activities
  ),
  prevActivity (MemberID, FirstOfMonth, prevFirstOfMonth) AS (
    SELECT MemberID, FirstOfMonth
    , LAG(FirstOfMonth) OVER (PARTITION BY MemberID ORDER BY FirstOfMonth)
    FROM monthwise
  )
SELECT MemberID
FROM prevActivity
WHERE MONTH(FirstOfMonth) = @month
  AND YEAR(FirstOfMonth) = @year
  AND (prevFirstOfMonth IS NULL OR DATEDIFF(month, prevFirstOfMonth, FirstOfMonth) > 12)

您也可以不使用LAG功能来执行此操作:使用两个查询，一个查询用于本月 Activity 的成员，一个查询用于在过去十二个月中 Activity 的成员。然后使用内部联接和左联接查找本月 Activity 的成员，而前几个月没有 Activity :

DECLARE 
  @year int = 2018, 
  @month int = 7;

WITH
  this (MemberID) AS (
    SELECT DISTINCT MemberID
    FROM Activities 
    WHERE YEAR(ActivityDate) = @year
      AND MONTH(ActivityDate) = @month
  ),
  prev (MemberID) AS (
    SELECT DISTINCT MemberID
    FROM Activities
    WHERE ActivityDate < DATEADD(month, @month-1 +12*(@year-1900), 0)
      AND ActivityDate >= DATEADD(month, @month-1 +12*(@year-1901), 0)
  )
SELECT m.MemberID
FROM Members m
  INNER JOIN this ON m.MemberID = this.MemberID
  LEFT JOIN prev ON m.MemberID = prev.MemberID
WHERE prev.MemberID IS NULL