★//查询连续两个小时的AQI＞=500的数据,而且查询出爆表时长和爆表次数

最新推荐文章于 2024-08-31 10:44:02 发布

把每天当成生命中的最后一天

最新推荐文章于 2024-08-31 10:44:02 发布

阅读量369

点赞数 4

文章标签： java 前端数据库

本文链接：https://blog.csdn.net/L905128009/article/details/139784918

版权

SELECT AREA,
F.CITYCODE,
SUM_HOUR AS AQIBBSC,
CNT_HOUR AS AQICXBBCS
FROM
(SELECT AREA,
E.CITYCODE,
SUM_HOUR,
CNT_HOUR,
C.LONGITUDE,
C.LATITUDE,
ROW_NUMBER()
OVER (PARTITION BY E.AREA
ORDER BY E.AREA DESC) AS RN
FROM
(SELECT AREA,
D.CITYCODE,
SUM(HOUR_CNT) SUM_HOUR,
COUNT(*) CNT_HOUR
FROM
(SELECT AREA,
C.CITYCODE,
HOURSDIF,
COUNT(*) HOUR_CNT
FROM
(SELECT AREA,
B.CITYCODE,
HOUR(ATIMEPOINT)-HOUR(BTIMEPOINT) + (DAYS(ATIMEPOINT) - DAYS(BTIMEPOINT)) * 24 -ROWNUM AS HOURSDIF
FROM
(SELECT TIMEPOINT ATIMEPOINT,
AREA,
A.CITYCODE,

(SELECT TIMEPOINT
FROM DSJYDD.AIR_CITYHOURAQI_PUBLISH B
WHERE AQI >= 500
AND TO_CHAR(TIMEPOINT,'YYYY-MM') >= '2017-10'
AND TO_CHAR(TIMEPOINT,'YYYY-MM') <= '2018-03'
AND B.CITYCODE = A.CITYCODE
AND ROWNUM = 1 ) BTIMEPOINT
FROM DSJYDD.AIR_CITYHOURAQI_PUBLISH A
WHERE AQI >= 500
AND TO_CHAR(TIMEPOINT,'YYYY-MM') >= '2017-10'
AND TO_CHAR(TIMEPOINT,'YYYY-MM') <= '2018-03'
AND A.CITYCODE IN ('110000','120000','130100','130200','131000','130600','130900','131100','130500','130400','140100' ,'140300','140400','140500','370100','370300','370800','371400','371500','371600','371700','410100','410200' ,'410500','410600','410700','410800','410900') ) B
ORDER BY AREA ) C
GROUP BY AREA,C.CITYCODE,HOURSDIF
HAVING COUNT(HOURSDIF) > 1 ) D
GROUP BY AREA,CITYCODE
ORDER BY AREA ) E
INNER JOIN DSJYDD.POINT_INFO C
ON E.CITYCODE = C.CITYCODE
GROUP BY AREA,SUM_HOUR,CNT_HOUR,C.LONGITUDE,C.LATITUDE,E.CITYCODE ) F
WHERE RN = 1