[SQL]計算與上一筆的差值

網友的詢問,小喵把自己的回覆整理到Blog,提供有類似網友的參考。上下筆的差異值,先決條件要有上下筆的排序條件,有了之後,透過給順號、SubQuery、自己對自己JOIN的方式,就可以達成,實際的詳細內容,敬請看下去~

緣起

這個問題是在討論區中,一個網友詢問提出的,相關的連結如下:
[SQL]計算與上一筆的差值
在有【指定排序方式】的前提下,這樣的需求是可以做到的,詳細如何處理,請往下繼續看下去~

準備範例資料

我就以自己訂定的資料表來當作範例

資料表的名稱為:TCATDataTime
相關欄位如下:
DTime : 資料的時間 (DateTime)
SData:文字資料(NVarchar(50))
SValue:數字資料(Int)

相關的範例資料表與範例資料的產生語法如下:

CREATE TABLE [dbo].[TCATDataTime](
	[DTime] [datetime] NOT NULL,
	[SData] [nvarchar](50) NOT NULL,
	[SValue] [decimal](18, 0) NOT NULL,
 CONSTRAINT [PK_TCATDataTime] PRIMARY KEY CLUSTERED 
(
	[DTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:47:09.497' AS DateTime), N'資料', CAST(123 AS Decimal(18, 0)))
GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:47:25.507' AS DateTime), N'資料', CAST(133 AS Decimal(18, 0)))
GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:49:22.130' AS DateTime), N'資料', CAST(987 AS Decimal(18, 0)))
GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:49:33.930' AS DateTime), N'資料', CAST(900 AS Decimal(18, 0)))
GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:49:53.150' AS DateTime), N'資料', CAST(700 AS Decimal(18, 0)))
GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:51:04.820' AS DateTime), N'資料', CAST(735 AS Decimal(18, 0)))
GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:51:09.960' AS DateTime), N'資料', CAST(820 AS Decimal(18, 0)))
GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:51:11.743' AS DateTime), N'資料', CAST(779 AS Decimal(18, 0)))
GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:51:15.137' AS DateTime), N'資料', CAST(850 AS Decimal(18, 0)))
GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:51:18.600' AS DateTime), N'資料', CAST(860 AS Decimal(18, 0)))
GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:51:22.053' AS DateTime), N'資料', CAST(920 AS Decimal(18, 0)))
GO
ALTER TABLE [dbo].[TCATDataTime] ADD  CONSTRAINT [DF_TCATDataTime_DTime]  DEFAULT (getdate()) FOR [DTime]
GO
ALTER TABLE [dbo].[TCATDataTime] ADD  CONSTRAINT [DF_TCATDataTime_SData]  DEFAULT (N'資料') FOR [SData]
GO
ALTER TABLE [dbo].[TCATDataTime] ADD  CONSTRAINT [DF_TCATDataTime_SValue]  DEFAULT ((0)) FOR [SValue]
GO

資料就會長類似這樣

2020-04-06 09:47:09.497	資料	123
2020-04-06 09:47:25.507	資料	133
2020-04-06 09:49:22.130	資料	987
2020-04-06 09:49:33.930	資料	900
2020-04-06 09:49:53.150	資料	700
2020-04-06 09:51:04.820	資料	735
2020-04-06 09:51:09.960	資料	820
2020-04-06 09:51:11.743	資料	779
2020-04-06 09:51:15.137	資料	850
2020-04-06 09:51:18.600	資料	860
2020-04-06 09:51:22.053	資料	920

開始處理過程

接著就來說明處理的方式
我們假設以【時間(DTime)排序】來區別【前一筆、後一筆】

首先,依據Row_Number的方式,依據時間排序之後給予編號,並且給予【編號減1】的來做為比對JOIN的Key
請參考以下這篇來設計Row_Number

https://dotblogs.com.tw/topcat/2009/08/06/9906

因此就是這樣的語法來處理

SELECT ROW_NUMBER() OVER(ORDER BY DTime) AS ROWID
	, (ROW_NUMBER() OVER(ORDER BY DTime)) - 1 AS PreROWID
	, *
FROM [dbo].[TCATDataTime] (NOLOCK)

這樣可以得到以下這樣的結果

1	0	2020-04-06 09:47:09.497	資料	123
2	1	2020-04-06 09:47:25.507	資料	133
3	2	2020-04-06 09:49:22.130	資料	987
4	3	2020-04-06 09:49:33.930	資料	900
5	4	2020-04-06 09:49:53.150	資料	700
6	5	2020-04-06 09:51:04.820	資料	735
7	6	2020-04-06 09:51:09.960	資料	820
8	7	2020-04-06 09:51:11.743	資料	779
9	8	2020-04-06 09:51:15.137	資料	850
10	9	2020-04-06 09:51:18.600	資料	860
11	10	2020-04-06 09:51:22.053	資料	920

利用SubQuery的方式,讓這樣的資料【自己與自己】JOIN,把其中一個當作是【當下狀態】,另一個當作是【後一個狀態】

於是就可以寫成這樣的語法

SELECT C.ROWID, C.PreROWID, C.SData, C.SValue, ISNULL(P.SValue,0) AS PSValue
FROM 
(	
	SELECT ROW_NUMBER() OVER(ORDER BY DTime) AS ROWID
		, (ROW_NUMBER() OVER(ORDER BY DTime)) - 1 AS PreROWID
		, *
	FROM [dbo].[TCATDataTime] (NOLOCK)
) P
RIGHT OUTER JOIN 
(
	SELECT ROW_NUMBER() OVER(ORDER BY DTime) AS ROWID
		, (ROW_NUMBER() OVER(ORDER BY DTime)) - 1 AS PreROWID
		, *
	FROM [dbo].[TCATDataTime] (NOLOCK)
) C
	ON C.PreROWID = P.ROWID

這樣後就可以得到以下這樣的結果

1	0	資料	123	0
2	1	資料	133	123
3	2	資料	987	133
4	3	資料	900	987
5	4	資料	700	900
6	5	資料	735	700
7	6	資料	820	735
8	7	資料	779	820
9	8	資料	850	779
10	9	資料	860	850
11	10	資料	920	860

我們把下一筆的數值,成功地放在上一筆相同Row,不同的Column中
到這邊,我們要的結果已經呼之欲出了
接著,就只要把當下的數值-前一筆的數值,就可以得到結果了

SELECT *, SValue-PSValue AS SDiff
FROM
(
	SELECT C.ROWID, C.PreROWID, C.SData, C.SValue, ISNULL(P.SValue,0) AS PSValue
	FROM 
	(	
		SELECT ROW_NUMBER() OVER(ORDER BY DTime) AS ROWID
			, (ROW_NUMBER() OVER(ORDER BY DTime)) - 1 AS PreROWID
			, *
		FROM [dbo].[TCATDataTime] (NOLOCK)
	) P
	RIGHT OUTER JOIN 
	(
		SELECT ROW_NUMBER() OVER(ORDER BY DTime) AS ROWID
			, (ROW_NUMBER() OVER(ORDER BY DTime)) - 1 AS PreROWID
			, *
		FROM [dbo].[TCATDataTime] (NOLOCK)
	) C
		ON C.PreROWID = P.ROWID
) V

最後得到的結果如下:

1	0	資料	123		0		123
2	1	資料	133		123		10
3	2	資料	987		133		854
4	3	資料	900		987		-87
5	4	資料	700		900		-200
6	5	資料	735		700		35
7	6	資料	820		735		85
8	7	資料	779		820		-41
9	8	資料	850		779		71
10	9	資料	860		850		10
11	10	資料	920		860		60

到此,要的前後筆差異值就此得出。


以上小喵自己筆記,也同時提供網友們參考

^_^

 

 


以下是簽名:


Microsoft MVP
Visual Studio and Development Technologies
(2005~2019/6)