这将选择具有至少两个连续的相同类型操作的所有客户。
WITH rows AS ( SELECt customer, action, ROW_NUMBER() OVER (PARTITION BY customer ORDER BY lastlogin) AS rn FROM mytable )SELECt DISTINCT customerFROM rows rpWHERe 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 customerFROM rows rpWHERe 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 customerFROM ( SELECt customer FROM rows rp WHERe action GROUP BY customer, actionpre, series - rn HAVINg DETEDIFF(day, MIN(lastlogin), MAX(lastlogin)) >= 14 ) q
此查询计算两个序列：一个返回连续的序列
ORDER BY lastlogin，第二个通过
action另外划分：
action logindate rn series diff = rn - series1 Jan 01 1 1 01 Jan 02 2 2 02 Jan 03 3 1 22 Jan 04 4 2 21 Jan 05 5 3 21 Jan 06 6 4 2
只要两种方案之间的差异相同，该系列就不会中断。每次打断都会打断系列。
这意味着（
action, diff）的组合定义了不间断的组。
我们可以按分组
action, diff，在分组中找到
MAX和
MIN，然后对其进行过滤。
如果您需要选择
14行而不是
14连续几天，只是过滤的
COUNT(*)，而不是
DATEDIFF。
