一表 T 有以下內容。
用戶編號 采集行度 采集時間 狀態
001 42 2020-11-11 正常
001 12 2020-11-10 正常
001 10 2020-11-9 正常
003 132 2020-11-11 正常
003 101 2020-11-9 正常
003 92 2020-11-8 正常
004 46 2020-11-11 正常
004 22 2020-11-7 正常
004 18 2020-11-6 正常
004 33 2020-11-8 正常
取出 最新日均用水大于10的用戶，，注意,不是每天每個用戶都 有采集數 ，日均是取兩條最新的資料 來取日均數
如：
001 的用戶 最新日均用水 是 42-12/("2020-11-11" - "2020-11-10")=30/1=30
003 的用戶 最新日均用水 是 132-101/("2020-11-11" - "2020-11-9")=31/2=15
004 的用戶 最新日均用水 是 46-33/("2020-11-11" - "2020-11-8")=13/3=3
uj5u.com熱心網友回復：
--測驗資料
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([用戶編號] nvarchar(23),[采集行度] int,[采集時間] Date,[狀態] nvarchar(22))
Insert #T
select N'001',42,'2020-11-11',N'正常' union all
select N'001',12,'2020-11-10',N'正常' union all
select N'001',10,'2020-11-9',N'正常' union all
select N'003',132,'2020-11-11',N'正常' union all
select N'003',101,'2020-11-9',N'正常' union all
select N'003',92,'2020-11-8',N'正常' union all
select N'004',46,'2020-11-11',N'正常' union all
select N'004',22,'2020-11-7',N'正常' union all
select N'004',18,'2020-11-6',N'正常' union all
select N'004',33,'2020-11-8',N'正常'
Go
--測驗資料結束
;WITH cte AS (
SELECT * FROM (
Select *,ROW_NUMBER()OVER(PARTITION BY 用戶編號 ORDER BY 采集時間 DESC) rn from #T
)t WHERE rn<3
)
SELECT a.用戶編號,
(b.采集行度 - a.采集行度) / DATEDIFF(DAY, a.采集時間, b.采集時間) AS 最新日均用水
FROM cte a
JOIN cte b
ON b.用戶編號 = a.用戶編號
AND b.rn = a.rn + 1;
uj5u.com熱心網友回復：
SELECT t.用戶編號, t.采集行度, t.采集時間, t.狀態, t.上次度數, t.上次時間, (t.采集行度 - t.上次度數) / DATEDIFF(DAY, t.上次時間, t.采集時間) 日均度數
FROM ( SELECT *,
LEAD(采集行度, 1, NULL) OVER (PARTITION BY 用戶編號 ORDER BY 采集時間 DESC) 上次度數,--和本次時間最接近的上一次度數
LAG(采集行度, 1, NULL) OVER (PARTITION BY 用戶編號 ORDER BY 采集時間 DESC) 下次度數,--如果下次度數為NULL則代表是最新一次的
LEAD(采集時間, 1, NULL) OVER (PARTITION BY 用戶編號 ORDER BY 采集時間 DESC) 上次時間--取得上一次的時間，用來計算間隔天數
FROM #T) t
WHERE t.下次度數 IS NULL AND t.上次度數 IS NOT NULL;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/211560.html
標籤：疑難問題
上一篇：瞎編小辦法