SQL 连续度统计
请问下各位大小虾们，我想获取下图数据时间段中：连续3天平均温度小于-350的次数，用sql能够获取吗？中间一列是日期，第三列是平均温度。
想获取的结果如下：
站点 次数
50136 2 连续度 统计 连续日期
[解决办法]
又仔细看了看, 原来你要的"连续三天的温度"都是小于-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"的次数就行了.
给你个思路, 希望能帮到你.
[解决办法]
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
-------- 1.
SELECT
a.StationAS [站点]
,COUNT(1)AS [次数]
FROM
tbAS 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.StationAS [站点]
,COUNT(1)AS [次数]
FROM
tbAS 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
*/
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
*/
