sql - 分区和滑动窗口上的行号

Question

本质上，我想按人对我的表进行分区，并为该窗口中最近日期的 n 个月窗口内的每条记录添加一个行号 (EVENT)。对于下面的示例，n = 3。

示例数据:

PERSON  DATE (yyyy-mm-dd)
A       2014-05-02  
A       2014-01-09
A       2014-01-08
A       2014-01-07
A       2014-01-02
B       2014-07-11
B       2014-06-12
B       2014-01-10
C       2014-11-11

结果:

PERSON  DATE (yyyy-mm-dd)   EVENT   
A       2014-05-02          1       
A       2014-01-09          2       
A       2014-01-08          2       
A       2014-01-07          2       
A       2014-01-02          2       
B       2014-07-11          1       
B       2014-06-12          1       
B       2014-01-10          2       
C       2014-11-11          1       

我将如何获得这些结果？

我一直在尝试使用递归 CTE 来解决这个问题，但递归步骤让我失望了:

WITH testCTE (PERSON, DATE, EVENT)
AS
(
    SELECT A.PERSON, A.DATE, 1 AS EVENT
    FROM [dbo].[Records] A JOIN (SELECT MAX(PERSON) AS PERSON, MAX(DATE) AS DATE FROM [dbo].[Records] GROUP BY PERSON) B
        ON A.PERSON = B.PERSON AND A.DATE >= DATEADD(MONTH, -3, B.DATE)

    UNION ALL

    -- Not sure what to put here. This gives an error:
    -- Recursive references are not allowed on the right hand side of an EXCEPT operator in the recursive part of recursive CTEs.
    (
        SELECT PERSON, DATE, EVENT+1 AS EVENT
        FROM [dbo].[Records]

        EXCEPT

        SELECT A.PERSON, A.DATE, EVENT
        FROM [dbo].[Records] A JOIN testCTE B
            ON A.PERSON = B.PERSON AND A.DATE = B.DATE
     )
)
    SELECT * FROM testCTE

我目前使用的是 SQL sever 2008，但这最终将在 Oracle 10g 中实现。

Answer 1

这可能对您有用 - Oracle 解决方案非常相似:

WITH x1 AS (
  SELECT person, MAX(dt) AS max_dt
    FROM person_event
   GROUP BY person
)
SELECT p1.person, p1.dt, FLOOR(DATEDIFF(month, p1.dt, x1.max_dt)/3) + 1
  FROM person_event p1 INNER JOIN x1
    ON p1.person = x1.person

Please see SQL Fiddle Demo here .

在 Oracle 中，您可以执行以下操作:

WITH x1 AS (
  SELECT person, MAX(dt) AS max_dt
    FROM person_event
   GROUP BY person
)
SELECT p1.person, p1.dt, TRUNC(MONTHS_BETWEEN(p1.dt, x1.max_dt)/3) + 1
  FROM person_event p1 INNER JOIN x1
    ON p1.person = x1.person