您如何使用TSQL识别记录中的记录模式序列？ [英] How do you identify record pattern sequences in records using TSQL?

问题描述

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

This is a fairly new exercise for me but I need to find a way to identify pattern sequences within a table. So for example, lets say I have a simple table that resembles the following:

现在我想做的是识别并分组所有具有按顺序排列的值5、9和6的模式的记录，以在查询中显示它们。您将如何使用T-SQL完成此任务？

Now what I would like to do is identify and group all the records which have the sequenced pattern of values 5, 9 and 6 presenting them in a query. How would you accomplish this task using T-SQL?

结果应如下所示：

我一直在寻找一些可能的示例，说明如何实现此目标，但找不到真正有用的东西。

I've looked for some potential examples of how this might be accomplished but couldn't find anything that really helps.

推荐答案

您可以使用包装在 CTE 中的以下查询来进行分配序列号到序列中包含的值：

You can use the following query wrapped in a CTE in order to assign sequence numbers to the values contained in your sequence:

;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 ，您可以识别孤岛，即后继片包含整个序列的tial行：

Using the above CTE you can identify islands, i.e. slices of sequential rows containing the whole of the sequence:

;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 字段可帮助您准确识别这些岛屿。

grp field helps you identify exactly these islands.

现在您需要做的就是只过滤部分组：

All you need to do now is to just filter out partial groups:

;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

此处演示

注意：答案使用 INNER JOIN 到 Seq 。如果表格包含 5、42、9、6 之类的值，则此方法将不起作用，因为 42 将被过滤掉通过 INNER JOIN 将该序列错误地标识为有效序列。功劳归功于@HABO。

Note: The initial version of this answer used an INNER JOIN to Seq. This won't work if table contains values like 5, 42, 9, 6, as 42 will be filtered out by the INNER JOIN and this sequence falsely identified as a valid one. Credit goes to @HABO for this edit.

这篇关于您如何使用TSQL识别记录中的记录模式序列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！