SQL Server，查找任意值序列

Question

假设我们有一张表Maintenance

Customer LastLogin ActionType
1        12/1/2007 2
1        12/2/2007 2
etc.

我们想要一个在给定年份中的任何时间点上具有一个或多个不间断序列（长14天）且操作类型为2的登录的所有客户的列表。

我当然可以轻松地用代码来做到这一点，甚至可以在小型代码集上相当快地做到这一点。在SQL中有非游标方法吗？

Answer 1

这将选择具有至少两个连续的相同类型操作的所有客户。

WITH    rows AS 
        (
        SELECT  customer, action,
                ROW_NUMBER() OVER (PARTITION BY customer ORDER BY lastlogin) AS rn
        FROM    mytable
        )
SELECT  DISTINCT customer
FROM    rows rp
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    rows rl
        WHERE   rl.customer = rp.customer
                AND rl.rn = rp.rn + 1
                AND rl.action = rp.action
        )

这是正义行动的更有效查询2：

WITH    rows AS 
        (
        SELECT  customer, ROW_NUMBER() OVER (PARTITION BY customer ORDER BY lastlogin) AS rn
        FROM    mytable
        WHERE   action = 2
        )
SELECT  DISTINCT customer
FROM    rows rp
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    rows rl
        WHERE   rl.customer = rp.customer
                AND rl.rn = rp.rn + 1
        )

更新2：

要选择不间断范围：

WITH    rows AS 
        (
        SELECT  customer, action, lastlogin
                ROW_NUMBER() OVER (PARTITION BY customer ORDER BY lastlogin) AS rn
                ROW_NUMBER() OVER (PARTITION BY customer, action ORDER BY lastlogin) AS series
        FROM    mytable
        )
SELECT  DISTINCT customer
FROM    (
        SELECT  customer
        FROM    rows rp
        WHERE   action
        GROUP BY
                customer, actioncode, series - rn
        HAVING
                DETEDIFF(day, MIN(lastlogin), MAX(lastlogin)) >= 14
        ) q

此查询计算两个序列：一个返回连续的序列ORDER BY lastlogin，第二个通过action另外划分：

action  logindate rn  series diff = rn - series
1       Jan 01    1   1      0
1       Jan 02    2   2      0
2       Jan 03    3   1      2
2       Jan 04    4   2      2
1       Jan 05    5   3      2
1       Jan 06    6   4      2

只要两种方案之间的差异相同，该系列就不会中断。每次打断都会打断系列。

这意味着（action, diff）的组合定义了不间断的组。

我们可以按分组action, diff，在分组中找到MAX和MIN，然后对其进行过滤。

如果您需要选择14行而不是14连续几天，只是过滤的COUNT(*)，而不是DATEDIFF。