sql-server - 如何获取时间序列中状态更改的日期?
问题描述
我有以下问题:我有机器生命周期事件的时间表:
DROP TABLE IF EXISTS #machineStatus
CREATE TABLE #machineStatus
(
machineID VARCHAR(255),
machineStatus VARCHAR(255),
statusDate DATETIME
)
INSERT INTO #machineStatus (machineId, statusDate, machineStatus)
VALUES
('01255999', '2019-11-01', '1 - InStorage'),
('01255999', '2019-12-01', '1 - InStorage'),
('01255999', '2020-01-01', '1 - InStorage'),
('01255999', '2020-02-01', '1 - InStorage'),
('01255999', '2020-03-01', '1 - InStorage'),
('01255999', '2020-04-01', '1 - InStorage'),
('01255999', '2020-05-01', '1 - InStorage'),
('01255999', '2020-06-01', '1 - InStorage'),
('01255999', '2020-07-01', '1 - InStorage'),
('01255999', '2020-08-01', '1 - InStorage'),
('01255999', '2020-09-01', '1 - InStorage'),
('01255999', '2020-11-01', '1 - InStorage'),
('01255999', '2020-12-01', '1 - InStorage'),
('01255999', '2020-12-15', '1 - InStorage'),
('01255999', '2021-01-01', '2 - RentedOut'),
('01255999', '2021-03-01', '1 - InStorage'),
('01255999', '2021-04-01', '1 - InStorage'),
('01255999', '2021-04-02', '2 - RentedOut'),
('01255999', '2021-04-05', '3 - Service'),
('01255999', '2021-04-15', '4 - Repairs'),
('01255999', '2021-04-20', '2 - RentedOut'),
('01255999', '2021-05-27', '5 - Sold')
我需要创建一个新列,我必须在其中显示状态更改的最后日期:
SELECT
s.*,
(SELECT MAX(ss.statusDate)
FROM #machineStatus ss
WHERE ss.machineId = s.machineId
AND ss.machineStatus <> s.machineStatus
AND ss.statusDate < s.statusDate) AS statusChangeDate
FROM #machineStatus s
ORDER BY s.statusDate
我似乎没有在某种程度上得到它,但我的问题是我无法弄清楚如何获取机器的第一个/最早状态的日期。statusChangeDate 列中的所有 NULL 值都应为 2019-11-01,如下所示:
|machineID | machineStatus | statusDate | statusChangeDate |
|-----------|-------------- |----------------------|----------------------|
| 01255999 | 1 - InStorage | 2019-11-01T00:00:00Z | 2019-11-01T00:00:00Z |
| 01255999 | 1 - InStorage | 2019-12-01T00:00:00Z | 2019-11-01T00:00:00Z |
| 01255999 | 1 - InStorage | 2020-01-01T00:00:00Z | 2019-11-01T00:00:00Z |
| 01255999 | 1 - InStorage | 2020-02-01T00:00:00Z | 2019-11-01T00:00:00Z |
| 01255999 | 1 - InStorage | 2020-03-01T00:00:00Z | 2019-11-01T00:00:00Z |
| 01255999 | 1 - InStorage | 2020-04-01T00:00:00Z | 2019-11-01T00:00:00Z |
| 01255999 | 1 - InStorage | 2020-05-01T00:00:00Z | 2019-11-01T00:00:00Z |
| 01255999 | 1 - InStorage | 2020-06-01T00:00:00Z | 2019-11-01T00:00:00Z |
| 01255999 | 1 - InStorage | 2020-07-01T00:00:00Z | 2019-11-01T00:00:00Z |
| 01255999 | 1 - InStorage | 2020-08-01T00:00:00Z | 2019-11-01T00:00:00Z |
| 01255999 | 1 - InStorage | 2020-09-01T00:00:00Z | 2019-11-01T00:00:00Z |
| 01255999 | 1 - InStorage | 2020-11-01T00:00:00Z | 2019-11-01T00:00:00Z |
| 01255999 | 1 - InStorage | 2020-12-01T00:00:00Z | 2019-11-01T00:00:00Z |
| 01255999 | 1 - InStorage | 2020-12-15T00:00:00Z | 2019-11-01T00:00:00Z |
| 01255999 | 2 - RentedOut | 2021-01-01T00:00:00Z | 2020-12-15T00:00:00Z |
| 01255999 | 1 - InStorage | 2021-03-01T00:00:00Z | 2021-01-01T00:00:00Z |
| 01255999 | 1 - InStorage | 2021-04-01T00:00:00Z | 2021-01-01T00:00:00Z |
| 01255999 | 2 - RentedOut | 2021-04-02T00:00:00Z | 2021-04-01T00:00:00Z |
| 01255999 | 3 - Service | 2021-04-05T00:00:00Z | 2021-04-02T00:00:00Z |
| 01255999 | 4 - Repairs | 2021-04-15T00:00:00Z | 2021-04-05T00:00:00Z |
| 01255999 | 2 - RentedOut | 2021-04-20T00:00:00Z | 2021-04-15T00:00:00Z |
| 01255999 | 5 - Sold | 2021-05-27T00:00:00Z | 2021-04-20T00:00:00Z |
任何帮助表示赞赏。谢谢!!:)
解决方案
分两步进行。
首先,用于LAG() OVER ()
检查状态是否发生变化,并记录状态发生变化的日期。
然后,使用MAX() OVER ()
向前传播这些日期以填充 NULL (在状态未更改的行上)。
WITH
check_for_changes AS
(
SELECT
*,
CASE WHEN LAG(machineStatus) OVER (PARTITION BY machineID ORDER BY statusDate) = machineStatus THEN NULL ELSE statusDate END statusChangeDate
FROM
machineStatus
)
SELECT
*,
MAX(statusChangeDate) OVER (PARTITION BY machineID ORDER BY statusDate) AS lastStatusChangeDate
FROM
check_for_changes
ORDER BY
statusDate
推荐阅读
- zabbix - 问题接口链接断开,手动恢复
- apache-flink - 基于事件时间的 Flink 窗口操作在 watermark 小于 end of window ends 时触发
- django - 在 Django 中,如何以编程方式检查 IntegrityError 是否与模型的特定唯一性约束相关?
- linux - 关于卸载netbeans
- c++ - 带有矢量的“在错误中包含的文件中”
- php - 在 PHP 脚本中使用 HTML div 的总次数?
- python - Python如何捕获命令行调用的所有输出
- css - Css-grid:容器外的背景出血
- alloy - 合金分析仪是“伪造者”吗?
- nexus - 为什么 Nexus 的匿名使用恢复活跃?