logo标签列表

SQL如何按连续递增序列拆分数据，并通过模式对每个子集进行筛选？

sqlsql-serversubsetsequences
4

我有一些关于IT技术的数据，我想从中识别出模式。然而每个表格中的数据都不完整（存在缺失行）。我想将表格分成完整数据的块，然后从每个块中识别出模式。我有一列可以用来确定数据是否完整，它叫做sequence。

数据看起来像：

Sequence      Position 
1              open
2              closed 
3              open
4              open
5              closed
8              closed
9              open
11             open
13             closed
14             open 
15             open
18             closed
19             open
20             closed

首先，我想将数据拆分为完整的部分：
   Sequence      Position 
    1              open
    2              closed 
    3              open
    4              open
    5              closed
---------------------------
    8              closed
    9              open
---------------------------
    11             open
---------------------------
    13             closed
    14             open 
    15             open
---------------------------
    18             closed
    19             open
    20             closed

接下来我想要确定模式closed open, ..., open, closed，这种模式是指我们需要在 n 行中（n 至少为 1）从关闭状态切换到开启状态，然后再切换回关闭状态。

根据样本数据，这将会得到：

     Sequence        Position 
        2              closed 
        3              open
        4              open
        5              closed
    ---------------------------
        18             closed
        19             open
        20             closed

这留下了我的最终表格，可以在上面进行分析，因为我知道没有损坏的序列。我还有另一列，其中position 是二进制的，如果使用这种方法更容易处理，则可以使用它。
由于表格很大，所以尽管我认为我可以编写循环来找出结果，但我认为那种方法不够高效。或者，我打算将整个表格拉入R，然后找到结果表格，但这需要首先将所有内容都拉入R，因此我想知道是否可以在SQL中实现。
编辑：更具代表性的不同样本数据：
Sequence      Position 
    1              open
    2              closed 
    3              open
    4              open
    5              closed
    8              closed
    9              open
    11             open
    13             closed
    14             open 
    15             open
    18             closed
    19             open
    20             closed
    21             closed
    22             closed
    23             closed
    24             open
    25             open
    26             closed
    27             open

请注意，这应该具有相同的结果，但也需要使用相同的HTML标签。
    23             closed
    24             open
    25             open
    26             closed
21、22和27不符合closed、open、open、closed的模式，因此它们不需要被包括在内。
但是，如果我们有一个28 closed，那么我们希望27和28被包括在内，因为没有时间间隔并且模式匹配。如果28被替换为29 closed，我们将不需要27或29（因为尽管模式正确但序列中断）。
为了增加一些背景信息，想象一台从停止到运行再到停止的机器。我们记录数据，但记录中存在断点，这里用序列中断来表示。除了停止运行停止周期中的缺失数据外，数据有时也会在机器已经运行时开始记录或在机器停止之前停止记录。我们不需要这些数据，因为它们不是完整的停止、运行、停止周期。我们只需要那些完整周期，并且序列连续的周期。这意味着我可以将原始数据集转换为只有一个接一个的完整周期。
我建议您设置一个SQL Fiddle或Rextester。 - Gordon Linoff
你实际上想要什么意思？是为此分离表格吗？ - Mr. Bhosale
不只是一个 select，它还过滤了数据。 - Olivia
2个回答
2

我认为实际上有一个相对简单的方法来看待这个问题。你可以通过以下方式确定关闭序列号：

  • 查看先前关闭的序列号
  • 查看先前关闭和当前关闭的累积开启次数
  • 进行算术运算以确保所有中间值都在数据中

这可以转化为一个查询：

select t.*,
       lag(sequence) over (partition by position order by sequence) as prev_sequence,
       lag(cume_opens) over (partition by position order by cume_opens) as prev_cume_opens
from (select t.*,
             sum(case when position = 'open' then 1 else 0 end) over (order by sequence) as cume_opens
      from t
     ) t
where position = 'close' and
      (cume_opens - prev_cume_opens) = sequence - prev_sequence - 1 and
      sequence > prev_sequence - 1;

现在你已经确定了这些序列，你可以连接它们以获取原始行：

select t.*
from t join
     (select t.*,
             lag(sequence) over (partition by position order by sequence) as prev_sequence,
             lag(cume_opens) over (partition by position order by cume_opens) as prev_cume_opens
      from (select t.*,
                   sum(case when position = 'open' then 1 else 0 end) over (order by sequence) as cume_opens
            from t
           ) t
      where position = 'close' and
            (cume_opens - prev_cume_opens) = sequence - prev_sequence - 1 and
            sequence > prev_sequence - 1
     ) seqs
     on t.sequence between seqs.prev_sequence and seqs.sequence;

我承认我没有测试过这个。不过我认为这个想法是可行的。唯一的问题是它会在每个序列组中选择多个“接近”的时间段。

2
你可以使用它。
DECLARE @MyTable TABLE (Sequence INT, Position VARCHAR(10))

INSERT INTO @MyTable
VALUES
(1,'open'),
(2,'closed') ,
(3,'open'),
(4,'open'),
(5,'closed'),
(8,'closed'),
(9,'open'),
(11,'open'),
(13,'closed'),
(14,'open') ,
(15,'open'),
(18,'closed'),
(19,'open'),
(20,'closed'),
(21,'closed'),
(22,'closed'),
(23,'closed'),
(24,'open'),
(25,'open'),
(26,'closed'),
(27,'open')


;WITH CTE AS(
    SELECT * ,
        CASE WHEN Position ='closed' AND LAG(Position) OVER(ORDER BY [Sequence]) ='closed' THEN 1 ELSE 0 END CloseMark
    FROM @MyTable
)
,CTE_2 AS 
(
    SELECT 
        [New_Sequence] = [Sequence] + (SUM(CloseMark) OVER(ORDER BY [Sequence] ROWS UNBOUNDED PRECEDING )) 
        , [Sequence]
        , Position
     FROM CTE
)
,CTE_3 AS (
    SELECT *, 
    RN = ROW_NUMBER() OVER(ORDER BY [New_Sequence]) 
    FROM CTE_2
)
,CTE_4 AS
(
    SELECT ([New_Sequence] - RN) G
    , MIN(CASE WHEN Position = 'closed' THEN [Sequence] END) MinCloseSq
    , MAX(CASE WHEN Position = 'closed' THEN [Sequence] END) MaxCloseSq
    FROM CTE_3 
    GROUP BY ([New_Sequence] - RN)
)
SELECT
    CTE.Sequence, CTE.Position
FROM CTE_4 
    INNER JOIN CTE  ON (CTE.Sequence BETWEEN CTE_4.MinCloseSq AND CTE_4.MaxCloseSq)
WHERE
    CTE_4.MaxCloseSq > CTE_4.MinCloseSq
    AND (CTE_4.MaxCloseSq IS NOT NULL AND CTE_4.MinCloseSq IS NOT NULL)

结果：

Sequence    Position
----------- ----------
2           closed
3           open
4           open
5           closed
---         ---
18          closed
19          open
20          closed
---         ---
23          closed
24          open
25          open
26          closed
这在我的真实数据上似乎不起作用。我的数据有更长的封闭和/或开放周期，而且会重复。但格式是相同的。发生了什么事？- 我说的是像1000个关闭，然后再1000个打开等等。 - Olivia
你能添加更多的数据进行测试吗？ - Serkan Arslan
很抱歉，我发现问题出在我的数据上。我正在使用round(((round(convert(float , datetime),5)- 42961.58227)* 99999.97 + 1),1)来创建序列，但是发现一些日期是重复的或者奇怪的，所以我将删除它们并再次尝试 - 虽然非常感谢您的快速响应。 - Olivia
这两个数字是表中的第一个日期，然后是两个不同日期时间之间差的倒数（相差一秒）。这只是将 “2017-08-16 13:58:27.837” 强制转换为1，“2017-08-16 13:58:28.837” 转换为2，以此类推。 - Olivia
1
这是一个有趣的解决方案，可以计算第二个差异。为什么不直接使用datediff(second, start_date, date)+1这样的函数呢？ - Serkan Arslan
显示剩余3条评论
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接