SQL 连续度统计

Question

请问下各位大小虾们，我想获取下图数据时间段中：连续3天平均温度小于-350的次数，用sql能够获取吗？中间一列是日期，第三列是平均温度。想获取的结果如下：
站点             次数
50136            2      连续度统计连续日期

Answer 1

又仔细看了看, 原来你要的"连续三天的温度"都是小于-350的次数, 而不是"平均三天"的. 那就相对简单多了.
你写个CTE, 比如
SELECT StationID, ObservedDate, row_number() AS ID
FROM 表
WHERE AverTemperature <= -350
Order By StationID, OberserveDate然后, 从上面的CTE, 再写个CTE, 每个ID的ObserveDate都减之前的第二个ObserveDate, 这样一来就得到下面的一个新的CTE, 比如:
StationID   天数差
50136         2
50136         2
50136         6
....最后, 统计一下出现"2"的次数就行了.给你个思路, 希望能帮到你.

Answer 2

USE test
GO
-->生成表tbif object_id('tb') is not null 
drop table tb
Go
Create table tb([Station] int,[ObserveDate] datetime,[AverTemperature] smallint)
Insert into tb
Select 50136,'1958-1-1',-315
Union all Select 50136,'1958-1-2',-305
Union all Select 50136,'1958-1-3',-294
Union all Select 50136,'1958-1-4',-270
Union all Select 50136,'1958-1-5',-181
Union all Select 50136,'1958-1-6',-194
Union all Select 50136,'1958-1-7',-271
Union all Select 50136,'1958-1-8',-334
Union all Select 50136,'1958-1-9',-324
Union all Select 50136,'1958-1-10',-349
Union all Select 50136,'1958-1-11',-385
Union all Select 50136,'1958-1-12',-383
Union all Select 50136,'1958-1-13',-355
Union all Select 50136,'1958-1-14',-354
Union all Select 50136,'1958-1-15',-287
Union all Select 50136,'1958-1-16',-282
Union all Select 50136,'1958-1-17',-339
Union all Select 50136,'1958-1-18',-359
Union all Select 50136,'1958-1-19',-356
Union all Select 50136,'1958-1-20',-349
Union all Select 50136,'1958-1-21',-297
Union all Select 50136,'1958-1-22',-279
Union all Select 50136,'1958-1-23',-314
Union all Select 50136,'1958-1-24',-279
Union all Select 50136,'1958-1-25',-294
Union all Select 50136,'1958-1-26',-290
Union all Select 50136,'1958-1-27',-332go
-------- 1.SELECT 
a.Station AS [站点]
,COUNT(1) AS [次数] 
FROM 
tb AS a
,tb AS b
,tb AS c
WHERE a.ObserveDate=b.ObserveDate-1
AND b.ObserveDate=c.ObserveDate-1
AND a.AverTemperature<-350
AND b.AverTemperature<-350
AND c.AverTemperature<-350
GROUP BY 
a.Station/*
站点        次数
----------- -----------
50136       2
*/-------- 2.
SELECT 
a.Station AS [站点]
,COUNT(1) AS [次数] 
FROM 
tb AS a
WHERE EXISTS(SELECT 1 FROM tb AS x
WHERE x.Station=a.Station
AND x.ObserveDate-1=a.ObserveDate
AND x.AverTemperature<-350
)
AND EXISTS(SELECT 1 FROM tb AS x
WHERE x.Station=a.Station
AND x.ObserveDate-2=a.ObserveDate
AND x.AverTemperature<-350
)
AND a.AverTemperature<-350
GROUP BY 
a.Station/*
站点        次数
----------- -----------
50136       2
*/

Answer 3

USE test
GO
 
 
-->生成表tb
 
if object_id('tb') is not null 
    drop table tb
Go
Create table tb([Station] int,[ObserveDate] datetime,[AverTemperature] smallint)
Insert into tb
Select 50136,'1958-1-1',-315
Union all Select 50136,'1958-1-2',-305
Union all Select 50136,'1958-1-3',-294
Union all Select 50136,'1958-1-4',-270
Union all Select 50136,'1958-1-5',-181
Union all Select 50136,'1958-1-6',-194
Union all Select 50136,'1958-1-7',-271
Union all Select 50136,'1958-1-8',-334
Union all Select 50136,'1958-1-9',-324
Union all Select 50136,'1958-1-10',-349
Union all Select 50136,'1958-1-11',-385
Union all Select 50136,'1958-1-12',-383
Union all Select 50136,'1958-1-13',-355
Union all Select 50136,'1958-1-14',-354
Union all Select 50136,'1958-1-15',-287
Union all Select 50136,'1958-1-16',-282
Union all Select 50136,'1958-1-17',-339
Union all Select 50136,'1958-1-18',-359
Union all Select 50136,'1958-1-19',-356
Union all Select 50136,'1958-1-20',-349
Union all Select 50136,'1958-1-21',-297
Union all Select 50136,'1958-1-22',-279
Union all Select 50136,'1958-1-23',-314
Union all Select 50136,'1958-1-24',-279
Union all Select 50136,'1958-1-25',-294
Union all Select 50136,'1958-1-26',-290
Union all Select 50136,'1958-1-27',-332
 
GO
;WITH t AS (
SELECT 
* 
,ROW_NUMBER()OVER(PARTITION BY Station ORDER BY ObserveDate) AS row
FROM tb
WHERE AverTemperature<-350
),t2 AS (
SELECT 
*
,1 AS row2
FROM t
WHERE row=1
UNION ALL
SELECT 
a.*
,CAST(CASE WHEN a.ObserveDate=b.ObserveDate+1 THEN b.row2 ELSE b.row2+1 END AS INT)
FROM t AS a
INNER JOIN t2 AS b ON a.row=b.row+1 AND a.Station=b.Station
)
SELECT 
Station AS [站点]
,COUNT(DISTINCT row2) AS [次数] 
FROM t2
GROUP BY 
Station
,row2
HAVING COUNT(row2)>3

/*
站点          次数
----------- -----------
50136       1
*/