sql-server - 如何使用 TSQL 识别记录中的记录模式序列？

Question

这对我来说是一个相当新的练习，但我需要找到一种方法来识别表中的模式序列。例如，假设我有一个类似于以下内容的简单表格:

现在我想做的是识别并分组所有具有值 5、9 和 6 的有序模式的记录，这些记录在查询中呈现。您将如何使用 T-SQL 完成此任务？

结果应如下所示:

我寻找了一些如何实现这一目标的潜在示例，但找不到任何真正有帮助的内容。

Answer 1

您可以使用封装在 CTE 中的以下查询，以便将序列号分配给序列中包含的值:

;WITH Seq AS (
    SELECT v, ROW_NUMBER() OVER(ORDER BY k) AS rn
    FROM (VALUES(1, 5), (2, 9), (3, 6)) x(k,v)
)

输出:

v   rn
-------
5   1
9   2
6   3

使用上面的CTE，您可以识别岛，即包含整个序列的连续行的切片:

;WITH Seq AS (
    SELECT v, ROW_NUMBER() OVER(ORDER BY k) AS rn
    FROM (VALUES(1, 5), (2, 9), (3, 6)) x(k,v)
), Grp AS (
SELECT [Key], [Value], 
       ROW_NUMBER() OVER (ORDER BY [Key]) - rn AS grp            
FROM mytable AS m
LEFT JOIN Seq AS s ON m.Value = s.v
)
SELECT *
FROM Grp

输出:

    Key Value   grp
   -----------------
    1   5       0
    2   9       0
    3   6       0
    6   5       3
    7   9       3
    8   6       3

grp 字段可帮助您准确识别这些岛屿。

您现在需要做的就是过滤掉部分组:

;WITH Seq AS (
    SELECT v, ROW_NUMBER() OVER(ORDER BY k) AS rn
    FROM (VALUES(1, 5), (2, 9), (3, 6)) x(k,v)
), Grp AS (
SELECT [Key], [Value], 
       ROW_NUMBER() OVER (ORDER BY [Key]) - rn AS grp            
FROM mytable AS m
LEFT JOIN Seq AS s ON m.Value = s.v
)
SELECT g1.[Key], g1.[Value]
FROM Grp AS g1
INNER JOIN (
   SELECT grp
   FROM Grp
   GROUP BY grp
   HAVING COUNT(*) = 3 ) AS g2
ON g1.grp = g2.grp

Demo here

注意:此答案的初始版本使用 INNER JOIN 到 Seq。如果表包含诸如 5, 42, 9, 6 之类的值，则此操作将不起作用，因为 42 将被 INNER JOIN 过滤掉，并且该序列被错误地识别为有效序列。此编辑归功于 @HABO。