时间戳

首页 > 解决方案 > SQL - 返回不正确的结果

sql - SQL - 返回不正确的结果

问题描述

在下表中，我尝试输入相同状态在另一个状态之后重复的次数。如果状态保持不变并且事件状态顺序是连续的，则计数不应增加。如果状态改变并再次进入相同的状态，计数应该增加

   sid status   order   num_times_status_entered_again  Expected result
    1   A       1                     1                               1
    1   A       2                     2                               1 
    1   B       3                     1                               1
    1   C       4                     1                               1
    1   A       5                     3                               2
    1   B       7                     3                               2
    1   B       8                     4                               2
    1   B       9                     5                               2
    1   C       10                    2                               2
    1   A       11                    4                               3
    1   B       13                    7                               3
    1   B       14                    8                               3
    1   B       15                    9                               3
    1   D       16                    1                               1
    1   E       17                    1                               1

    2   A       1                     1                               1
    2   A       2                     2                               1
    2   B       3                     1                               1
    2   B       4                     2                               1
    2   A       5                     3                               2

这是我正在使用的查询

update t
set num_times_status_entered_again  = t1.Ncount
from #tableZ t,(select status
count(1) OVER (Partition by status order by status) as Ncount
from #sub_tableZ
where status is not null
group by status, date
) t1 GROUP BY sid

标签： sqlsql-server

解决方案

这是一个生成所需结果的查询，我认为：

SQL Server 的小提琴

same_stat并使用表达式在CTE 术语seq_ord中派生。xrowsCASE ... END

LAG(status) OVER (ORDER BY sid, ord)status根据sid, ord排序提供上一行中的值。

LAG(ord ) OVER (ORDER BY sid, ord)ord根据排序提供上一行中的值sid, ord，以确定最后一行order value和当前行order value之间是否存在差距。

对于 SQL Server：

WITH xrows AS (
        SELECT t.*
             , CASE WHEN status = LAG(status, 1, 'NA') OVER (ORDER BY sid, ord) THEN 1 ELSE 0 END AS same_stat
             , CASE WHEN ord-1  = LAG(ord   , 1, ord ) OVER (ORDER BY sid, ord) THEN 1 ELSE 0 END AS seq_ord
          FROM test AS t
     )
SELECT t.*
     , SUM(CASE WHEN same_stat = 1 AND seq_ord = 1 THEN 0 ELSE 1 END) OVER (PARTITION BY sid, status ORDER BY ord) AS xseq
  FROM xrows AS t
 ORDER BY sid, ord
;

对于 MySQL：

WITH xrows AS (
        SELECT t.*
             , status = COALESCE(LAG(status) OVER (ORDER BY sid, ord) , 'NA')       AS same_stat
             , ord-1  = COALESCE(LAG(ord   ) OVER (ORDER BY sid, ord) ,  ord)       AS seq_ord
          FROM test AS t
     )
SELECT t.*
     , SUM(CASE WHEN same_stat AND seq_ord THEN 0 ELSE 1 END) OVER (PARTITION BY sid, status ORDER BY ord) AS xseq
  FROM xrows AS t
 ORDER BY sid, ord
;

结果：

+------+--------+------+-----------+---------+------+
| sid  | status | ord  | same_stat | seq_ord | xseq |
+------+--------+------+-----------+---------+------+
|    1 | A      |    1 |         0 |       0 |    1 |
|    1 | A      |    2 |         1 |       1 |    1 |
|    1 | B      |    3 |         0 |       1 |    1 |
|    1 | C      |    4 |         0 |       1 |    1 |
|    1 | A      |    5 |         0 |       1 |    2 |
|    1 | B      |    7 |         0 |       0 |    2 |
|    1 | B      |    8 |         1 |       1 |    2 |
|    1 | B      |    9 |         1 |       1 |    2 |
|    1 | C      |   10 |         0 |       1 |    2 |
|    1 | A      |   11 |         0 |       1 |    3 |
|    1 | B      |   13 |         0 |       0 |    3 |
|    1 | B      |   14 |         1 |       1 |    3 |
|    1 | B      |   15 |         1 |       1 |    3 |
|    1 | D      |   16 |         0 |       1 |    1 |
|    1 | E      |   17 |         0 |       1 |    1 |
|    2 | A      |    1 |         0 |       0 |    1 |
|    2 | A      |    2 |         1 |       1 |    1 |
|    2 | B      |    3 |         0 |       1 |    1 |
|    2 | B      |    4 |         1 |       1 |    1 |
|    2 | A      |    5 |         0 |       1 |    2 |
+------+--------+------+-----------+---------+------+

设置：

CREATE TABLE test (
    sid         int
  , status      varchar(5)
  , ord         int
);


INSERT INTO test VALUES
  (   1 ,'A'     ,1   )
, (   1 ,'A'     ,2   )
, (   1 ,'B'     ,3   )
, (   1 ,'C'     ,4   )
, (   1 ,'A'     ,5   )
, (   1 ,'B'     ,7   )
, (   1 ,'B'     ,8   )
, (   1 ,'B'     ,9   )
, (   1 ,'C'     ,10  )
, (   1 ,'A'     ,11  )
, (   1 ,'B'     ,13  )
, (   1 ,'B'     ,14  )
, (   1 ,'B'     ,15  )
, (   1 ,'D'     ,16  )
, (   1 ,'E'     ,17  )
 
, (   2 ,'A'     ,1   )
, (   2 ,'A'     ,2   )
, (   2 ,'B'     ,3   )
, (   2 ,'B'     ,4   )
, (   2 ,'A'     ,5   )
;

推荐阅读