鑒于資料 下面，我想：
- [x] 按設備磁區資料
- [x] 匯報最新閱讀詳情
- [ ] 添加一個新欄位，用于捕獲最新設備狀態更改的日期
| ID | 設備 | 觀察日期 | 設備狀態 | 讀 |
|---|---|---|---|---|
| 2021100 | 一種 | 2021-10-14 00:00:00.000 | 1 | -0.3623 |
| 2021101 | 乙 | 2021-10-14 00:00:00.000 | 1 | -0.0004 |
| 2021102 | C | 2021-10-14 00:00:00.000 | 1 | 0.1333 |
| 2021103 | 一種 | 2021-10-21 00:00:00.000 | 1 | 0.1907 |
| 2021104 | 乙 | 2021-10-21 00:00:00.000 | 2 | -0.3548 |
| 2021105 | C | 2021-10-21 00:00:00.000 | 1 | -0.2275 |
| 2021106 | 一種 | 2021-10-28 00:00:00.000 | 2 | -0.2404 |
| 2021107 | 乙 | 2021-10-28 00:00:00.000 | 2 | -0.0244 |
| 2021108 | C | 2021-10-28 00:00:00.000 | 1 | -0.3441 |
| 2021109 | 一種 | 2021-11-04 00:00:00.000 | 2 | 0.2843 |
| 2021110 | 乙 | 2021-11-04 00:00:00.000 | 3 | 0.2376 |
| 2021111 | C | 2021-11-04 00:00:00.000 | 1 | -0.0247 |
| 2021112 | 一種 | 2021-11-11 00:00:00.000 | 2 | 0.1814 |
| 2021113 | 乙 | 2021-11-11 00:00:00.000 | 3 | -0.2816 |
| 2021114 | C | 2021-11-11 00:00:00.000 | 1 | -0.1493 |
| 2021115 | 一種 | 2021-11-18 00:00:00.000 | 2 | 0.1694 |
| 2021116 | 乙 | 2021-11-18 00:00:00.000 | 4 | 0.0864 |
| 2021117 | C | 2021-11-18 00:00:00.000 | 1 | 0.0395 |
| 2021118 | 一種 | 2021-11-25 00:00:00.000 | 2 | 0.0658 |
| 2021119 | 乙 | 2021-11-25 00:00:00.000 | 4 | 0.1291 |
| 2021120 | C | 2021-11-25 00:00:00.000 | 2 | -0.1907 |
使用下面的代碼，我可以按設備對資料進行磁區并獲取每個設備的最新記錄。
WITH o AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION by device
ORDER BY date_observation DESC) AS queue
FROM observations
)
SELECT *
FROM o
WHERE queue = 1
結果
| ID | 設備 | 觀察日期 | 設備狀態 | 讀 | 佇列 |
|---|---|---|---|---|---|
| 2021118 | 一種 | 2021-11-25 00:00:00.000 | 2 | 0.0658 | 1 |
| 2021119 | 乙 | 2021-11-25 00:00:00.000 | 4 | 0.1291 | 1 |
| 2021120 | C | 2021-11-25 00:00:00.000 | 2 | -0.1907 | 1 |
獲取最新狀態更改日期的最佳方法是什么？
例如，設備 A 的最新設備狀態更改發生在 2021 年 10 月 28 日。
預期的輸出。
| ID | 設備 | 觀察日期 | 設備狀態 | 讀 | 佇列 | 日期_狀態_更改 |
|---|---|---|---|---|---|---|
| 2021118 | 一種 | 2021-11-25 00:00:00.000 | 2 | 0.0658 | 1 | 2021-10-28 |
| 2021119 | 乙 | 2021-11-25 00:00:00.000 | 4 | 0.1291 | 1 | 2021-11-18 |
| 2021120 | C | 2021-11-25 00:00:00.000 | 2 | -0.1907 | 1 | 2021-11-25 |
想法？謝謝。
uj5u.com熱心網友回復：
您可以使用更多的視窗函式
- 使用
LEAD來識別切換行 - 使用
MAX以獲得最新的切換日期
WITH PrevValues AS (
SELECT *,
queue = ROW_NUMBER() OVER (PARTITION BY device ORDER BY date_observation DESC),
date_state_change = CASE WHEN device_state <>
LEAD(device_state, 1, -1) OVER (PARTITION BY device ORDER BY date_observation DESC)
THEN date_observation END
FROM observations o
),
MaxValues AS (
SELECT *,
max_date_state_change = MAX(date_state_change) OVER (PARTITION BY device)
FROM PrevValues o
)
SELECT
o.id,
o.device,
o.date_observation,
o.device_state,
o.reading,
o.queue,
o.max_date_state_change
FROM MaxValues o
WHERE o.queue = 1
資料庫<>小提琴
uj5u.com熱心網友回復：
我向您推薦一個基于您的第一部分的解決方案（我修改了它，因為我會構建查詢）和一個計算最新狀態變化的流程，包括以下步驟：
- 在每一行上添加前一個狀態
- 添加狀態變化的指示器
- 計算標識為狀態更改的行的最大日期
- 將結果加入你的
這是我提出的結果：
WITH prev_data AS (
SELECT id, device, date_observation, device_state, reading,
LAG(device_state) OVER (partition by device order by date_observation) as prev_state
FROM observations
),
data_changes AS (
SELECT id, device, date_observation, device_state, reading,
CASE WHEN device_state = prev_state THEN 0 else 1 END as change_state
FROM prev_data
),
last_change AS (
SELECT device, max(date_observation) as date_state_change
FROM data_changes
WHERE change_state = 1
GROUP BY device
),
max_date as (
SELECT id, device, date_observation, device_state, reading,
max(date_observation) over (partition by device) as max_date
FROM observations
)
SELECT id, m.device, date_observation, device_state, reading, date_state_change
FROM max_date m
LEFT JOIN last_change l ON m.device = l.device
WHERE date_observation = max_date
結果 ：
id device date_observation device_state reading date_state_change
2021118 A 2021-11-25 00:00:00.000 2 0.0658 2021-10-28 00:00:00.000
2021119 B 2021-11-25 00:00:00.000 4 0.1291 2021-11-18 00:00:00.000
2021120 C 2021-11-25 00:00:00.000 2 -0.1907 2021-11-25 00:00:00.000
您可以使用dbFiddle檢查結果
uj5u.com熱心網友回復：
您還可以使用 LAG() 比較每個設備的當前行 device_state 和前一行設備狀態，并使用 MAX() 獲取 date_state_change。
WITH
f1 AS (
SELECT *, MAX(date_observation) OVER (PARTITION BY device) AS max_date,
CASE WHEN device_state <> LAG(device_state) OVER (PARTITION BY device ORDER BY date_observation)
THEN date_observation END AS date_flag
FROM observations
),
f2 AS (
SELECT *, MAX(date_flag) OVER (PARTITION BY device) AS date_state_change
FROM f1
)
SELECT id, device, date_observation, device_state, reading, date_state_change
FROM f2
WHERE max_date = date_observation;
看演示
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/374184.html
標籤：sql sql-server 查询语句