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

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