Posted on 2007-05-04 14:08
大大毛 閱讀(823)
評論(0) 編輯 收藏 所屬分類:
SQL
問題：
???按時間順序來存放的資料量很大，此時通常需要根據一個時間間隔來抽樣數據，例如說有一張表存放的是設備的運行狀態，現在需要根據輸入的時間間隔（比如5分鍾）來進行數據抽樣。
表結構及測試數據
Create
?
Table
?tbRunStatus?(
????rID?
int
?
identity
?
primary
?
key
,
????snapshotTime?
datetime
,
????thermometry?
int
,????
--
溫度
????hygrometry?
int
????
--
濕度
);
insert
?
into
?tbrunstatus?
values
?(
'
2007-05-04?12:05:12.077
'
,
30
,
85
);
insert
?
into
?tbrunstatus?
values
?(
'
2007-05-04?12:06:38.340
'
,
30
,
85
);
insert
?
into
?tbrunstatus?
values
?(
'
2007-05-04?12:10:39.750
'
,
30
,
85
);
insert
?
into
?tbrunstatus?
values
?(
'
2007-05-05?01:05:38.340
'
,
31
,
87
);
???這個表只做了幾個數據，實際情況下數據條數會很多，而且時間上是連續的(比如說每秒都至少會有一條資料)。
???當然了，這類問題為了實現抽樣，就一定會有幾個關鍵數據需要提供：
???1. 開始抽樣的時間，因為所謂間隔必須具有一個開始點；
???2. 抽樣的時間段；
???3. 抽樣的間隔；
解決：
???先來說一下思路，解決這個問題的關鍵在於：
???1. 先得到一個"時間上的間隔"；
???這個間隔是相對於上面提到的第1個關鍵點即開始時間(比如說開始時間為'2007-05-04 12:00:00' )，因為是時間上的計算，可以利用MS-SQL的 Datediff 時間函數來實現。
???2. 在第1點所得到的"間隔"上進行篩選。
???現在就利用嵌套SQL來分步實現(按分鍾間隔)：
???1. 得到"間隔"計算列，並應用抽樣的時間範圍(提高效率)
Select
????????
*
,?
datediff
(mi,
'
2007-05-04?12:00:00
'
,snapshotTime)?
As
?interval
????
From
????????tbRunStatus
????
Where
????????snapshotTime?
Between
?
'
2007-05-04?00:00:00
'
?
And
?
'
2007-05-05?23:59:59
'
???2.0 在第一步的結果集上做篩選
Select
????????
*
?
????
From
????????(
Select
????????????????
*
,?
datediff
(mi,
'
2007-05-04?12:00:00
'
,snapshotTime)?
As
?interval
????????????
From
????????????????tbRunStatus
????????????
Where
????????????????snapshotTime?
Between
?
'
2007-05-04?00:00:00
'
?
And
?
'
2007-05-05?23:59:59
'
????????)?t
????
Where
????????interval?
%
?
5
?
=
?
0
??????需要注意的一點就是條件中所應用的 interval % 5 = 0 ，意圖是每隔5分鍾抽一條出來，但是如果是在一分鍾內會有多條而我們只需要最近的那一條數據該怎麼辦呢(如果上面的數據按天來做間隔的話，那麼前3條的間隔都會是0)？
??????2.1 這樣一來就需要用 Group By 分組來解決(按天做為間隔)
Select
?
*
?
From
?tbRunStatus?
Join
?(
Select
????????
Min
(rID)?
As
?rID????
--
由於應用了分組,這裏只要取用關鍵列就好
????
From
????????(
Select
????????????????
*
,?
datediff
(
day
,
'
2007-05-04?12:00:00
'
,snapshotTime)?
As
?interval
????????????
From
????????????????tbRunStatus
????????????
Where
????????????????snapshotTime?
Between
?
'
2007-05-04?00:00:00
'
?
And
?
'
2007-05-05?23:59:59
'
????????)?t
????
Where
????????interval?
%
?
1
?
=
?
0
????
Group
?
By
????????interval
)?tbRunStatus1?
On
?tbRunStatus.rID?
=
?tbRunStatus1.rID
??????可以看到，實際上的處理也並不複雜，只是將結果集與表做了一次連接就成了，最後的抽樣結果會是兩條。
??????更深入的思考一下，如果間隔比較特殊造成並不是卡的每個時刻都會有數據，例如間隔卡在4分鍾，那麼就會存在著近似的存在，看看下面SQL以及結果就明白了：
Select
????????
*
,?interval?
/
?
4.0
?
As
?d
????
From
????????(
Select
????????????????
*
,?
datediff
(mi,
'
2007-05-04?12:00:00
'
,snapshotTime)?
As
?interval
????????????
From
????????????????tbRunStatus
????????????
Where
????????????????snapshotTime?
Between
?
'
2007-05-04?00:00:00
'
?
And
?
'
2007-05-05?23:59:59
'
????????)?t
??????結果集會是這樣：
|
rID
|
snapshotTime
|
thermometry
|
hygrometry
|
interval
|
d
|
|
10
|
2007-05-04 12:05:12.077
|
30
|
85
|
5
|
1.250000
|
|
11
|
2007-05-04 12:06:38.340
|
30
|
85
|
6
|
1.500000
|
|
12
|
2007-05-04 12:10:39.750
|
30
|
85
|
10
|
2.500000
|
|
13
|
2007-05-05 01:05:38.340
|
31
|
87
|
785
|
196.250000
|
??????可以通過這裏的示範列"d"的數值看出，如果是按4分鍾做為間隔的話，那麼 [4-8) 分鍾內有2條資料,而[8-12)分鍾內有1條，而按上面的方法則是一條資料也拿不到的?，F在需要在每4分鍾的間隔內取一條(如果有的話)，即應該取rID = (10,12,13)這3條才對。
??????2.2 對於這種近似匹配區間的情況，我們只需要改改就好
Select
?
*
?
From
?tbRunStatus?
Join
?(
Select
????????
Min
(rID)?
As
?rID
????
From
????????(
Select
????????????????
*
,?
datediff
(mi,
'
2007-05-04?12:00:00
'
,snapshotTime)?
/
4
??
As
?interval
????????????
From
????????????????tbRunStatus
????????????
Where
????????????????snapshotTime?
Between
?
'
2007-05-04?00:00:00
'
?
And
?
'
2007-05-05?23:59:59
'
????????)?t
????
Group
?
By
????????interval
)?tbRunStatus1?
On
?tbRunStatus.rID?
=
?tbRunStatus1.rID
??????呵呵，改改那個計算列的計算邏輯就能達到目的。