时间戳

首页 > 解决方案 > SQL Count 连续重复字符串状态

sql - SQL Count 连续重复字符串状态

问题描述

我有一个包含 3 列（、、、Time）ItemName的表Status。Time 列是格式为每小时的间隔YYYY-MM-DD hh:mm:ss，ItemName 列由各种项目字符串组成，Status 列将定义项目字符串的状态。有 3 种状态结果，分别是“活动”、“不活动”和“关闭”。结果主要是 Active 和 Inactive，而 Closed 在状态 Inactive 重复超过 30 天时手动覆盖。为此，我们通常会手动计算 Inactive 超过 30 天的累计时间。

为了简化现有流程，我想问一下是否有可能进行查询，它可以计算 Inactive 的状态是否已重复超过 30 天？我目前正在使用如下计数语句，但无法通过。如果你能帮助我，真的很感激。

WITH STATUSCOUNT(
S_TIME, S_ITEMNAME, SSTATUS, S_COUNT#)
AS (
SELECT S_TIME, S_ITEMNAME, S_STATUS, COUNT(*) over (PARTITION BY S_STATUS) AS S_COUNT#
FROM TABLE_OUTPUT)
SELECT *
FROM STATUSCOUNT
WHERE S_ITEMNAME LIKE 'GRUNT'
ORDER BY S_TIME DESC

在此处输入图像描述

标签： sqlsql-server

解决方案

我想问一下是否有可能进行查询，它可以计算 Inactive 的状态是否已重复超过 30 天？

您可以将其视为数据孤岛问题，将相同状态的连续行合并到周期中，并使用周期范围来确定状态（非活动）是否已设置超过一个月。

CREATE TABLE ItemData 
(
S_TIME DATETIME,              
S_ITEMNAME VARCHAR(20),
S_STATUS VARCHAR(10)
);

INSERT INTO ItemData(S_TIME, S_ITEMNAME, S_STATUS)
SELECT DATEADD(hour, (nums.n-1)*1, '20190501'), 'ItemA', 'Inactive'
FROM 
(
    SELECT TOP (10000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n
    FROM master.dbo.spt_values AS a
    CROSS JOIN master.dbo.spt_values AS b
) AS nums
ORDER BY nums.n;

UPDATE d
SET S_STATUS = 'Active'
FROM
(
    SELECT TOP (20) S_STATUS
    FROM ItemData
    ORDER BY NEWID()
) AS d;


SELECT *
FROM ItemData
WHERE S_STATUS = 'Active';


--1: per row, slow
SELECT i.*, ISNULL(ctrl.IsClosed, 0) AS IsClosed
FROM ItemData AS i
OUTER APPLY
(
    SELECT 
    TOP (CASE WHEN EXISTS(SELECT * FROM ItemData AS b WHERE b.S_ITEMNAME = i.S_ITEMNAME AND b.S_TIME < DATEADD(month, -1, i.S_TIME)) THEN 1 ELSE 0 END) 1 AS IsClosed
    FROM ItemData AS a
    WHERE a.S_ITEMNAME = i.S_ITEMNAME
      AND a.S_TIME >= DATEADD(month, -1, i.S_TIME)
      AND a.S_TIME <= i.S_TIME
    HAVING MIN(a.S_STATUS) = MAX(a.S_STATUS)
      AND MIN(a.S_STATUS) = 'Inactive'
) AS ctrl
ORDER BY i.S_ITEMNAME, i.S_TIME;


--2: per row, faster
SELECT i.*, 
CASE WHEN 
    EXISTS(SELECT * FROM ItemData AS a WHERE a.S_ITEMNAME = i.S_ITEMNAME AND a.S_TIME < DATEADD(month, -1, i.S_TIME))
    AND NOT EXISTS
    (
    SELECT * FROM ItemData AS b 
    WHERE b.S_ITEMNAME = i.S_ITEMNAME 
      AND b.S_TIME >= DATEADD(month, -1, i.S_TIME)
      AND b.S_TIME <= i.S_TIME
      AND b.S_STATUS <> 'Inactive'
    ) THEN 1 ELSE 0 END AS IsClosed
FROM ItemData AS i;


--1. islands of status
SELECT sd.S_ITEMNAME, sd.S_STATUS, sd.S_TIME AS StartDateTime, MIN(ed.S_TIME) AS EndDateTime,
(SELECT COUNT(*) FROM ItemData AS c WHERE c.S_ITEMNAME = sd.S_ITEMNAME AND c.S_STATUS = sd.S_STATUS AND c.S_TIME >= sd.S_TIME AND c.S_TIME <= MIN(ed.S_TIME)) AS TheCounter
FROM
(
--start dates
SELECT *
FROM ItemData AS i
WHERE NOT EXISTS(SELECT * FROM ItemData AS b WHERE b.S_ITEMNAME = i.S_ITEMNAME AND b.S_STATUS = i.S_STATUS AND b.S_TIME = DATEADD(hour, -1, i.S_TIME))
) AS sd
JOIN
(
--end dates
SELECT *
FROM ItemData AS j
WHERE NOT EXISTS(SELECT * FROM ItemData AS b WHERE b.S_ITEMNAME = j.S_ITEMNAME AND b.S_STATUS = j.S_STATUS AND b.S_TIME = DATEADD(hour, 1, j.S_TIME))
) AS ed ON sd.S_ITEMNAME = ed.S_ITEMNAME AND sd.S_STATUS = ed.S_STATUS AND sd.S_TIME <= ed.S_TIME
GROUP BY sd.S_ITEMNAME, sd.S_STATUS, sd.S_TIME 
ORDER BY sd.S_ITEMNAME, sd.S_TIME;


--2:islands of status
WITH c AS
(
SELECT S_ITEMNAME, S_STATUS, S_TIME, ROW_NUMBER() OVER(PARTITION BY S_ITEMNAME ORDER BY S_TIME) - DENSE_RANK() OVER(PARTITION BY S_ITEMNAME, S_STATUS ORDER BY S_TIME) AS GroupId
FROM ItemData
)
SELECT S_ITEMNAME, S_STATUS, MIN(S_TIME) AS StartDateTime, MAX(S_TIME) AS EndDateTime, COUNT(*) AS TheCounter
FROM c
GROUP BY S_ITEMNAME, S_STATUS, GroupId

推荐阅读