代码之家 › 专栏 › 技术社区 › Russell Steen

SQL Server,查找任意值序列

sql-server-2008 sql

Russell Steen · 技术社区 · 14 年前

假设我们有一个表维护

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

我们需要一个列表,列出在给定的一年中的任何时间点上有一个或多个连续的序列(14天长)以操作类型2登录的所有客户。

当然,我可以用代码很容易地做到这一点,甚至让它在小的集合上也相当快。在SQL中是否有一种非光标的方法来完成这项工作?

5 回复 | 直到 9 年前

Quassnoi 14 年前

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

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 )定义不间断组。

我们可以分组 微分作用 发现 MAX 和 MIN 在组内并对其进行筛选。

如果需要选择 14 行而不是 14 连续几天,只需过滤 COUNT(*) 而不是 DATEDIFF .

Raj More 14 年前

编辑:这对于最初的关于两个一行的问题是有效的。连续14次是一个不同的答案

首先,您需要一个序列,这样您就可以使用行号

可以使用rownumber=rownumber+1对自身进行自联接维护。

任何两个具有相同客户ID的自愿行,以及两个具有“2”actionType的行都将为您提供列表客户作为答案。

试试这个

WITH Maintenance AS
(
SELECT 1 as Customer, CONVERT (DateTime, '1/1/2008') DateTimeStamp, 1 ActionType
UNION
SELECT 1, '3/1/2009', 1
UNION
SELECT 1, '3/1/2006', 2
UNION
SELECT 2, '3/1/2009', 1
UNION
SELECT 2, '3/1/2006', 2
)
,RowNumberMaintenance AS
(SELECT  ROW_NUMBER () OVER (ORDER BY Customer, DateTimeStamp)  AS RowNumber, *
FROM Maintenance)
SELECT m1.Customer
From RowNumberMaintenance M1
    INNER JOIN RowNumberMaintenance M2
        ON M1.Customer = M2.Customer
        AND M1.RowNumber = M2.RowNumber + 1
WHERE 1=1
        AND M1.ActionType <> 2
        AND M2.ActionType <> 2

OMG Ponies 14 年前

用途:

WITH dates AS (
  SELECT CAST('2007-01-01' AS DATETIME) 'date'
  UNION ALL
   SELECT DATEADD(dd, 1, t.date) 
     FROM dates t
    WHERE DATEADD(dd, 1, t.date) <= GETDATE())
   SELECT m.customer, 
          m.actiontype
     FROM dates d
LEFT JOIN MAINTENANCE m ON m.last_login = d.date
    WHERE m.last_login IS NULL

Jason 14 年前

select customerID, count(customerID)
from maintenance
where actiontype = 2
group by customerID
having count(customerID) >= 1

Tom H 14 年前

我假设,按顺序,对于具有不同操作类型的同一个用户,您指的是两个或多个具有连续日期时间值的行,其中没有其他行。在这种情况下,这应该给你想要的:

SELECT DISTINCT
     T1.customer
FROM
     Maintenance T1
INNER JOIN Maintenance T2 ON
     T2.customer = T1.customer AND
     T2.action_type = 2 AND
     T2.last_login > T1.last_login
LEFT OUTER JOIN Maintenance T3 ON
     T3.customer = T1.customer AND
     T3.last_login > T1.last_login AND
     T3.last_login < T2.last_login AND
     T3.action_type <> 2
WHERE
     T1.actiontype = 2 AND
     T3.customer IS NULL

SQL按照我上面所说的进行操作-查找一行(T1)及其后面的另一行(T2),这两行的action_type=2,其中(T3)之间没有具有不同操作类型的行。t3.customer i s null检查是否为空,因为如果列为空(我假设它是非空列),则意味着左外部联接一定没有找到符合条件的行。