db2计算小时之间的差

最新推荐文章于 2022-12-09 09:58:09 发布

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

最新推荐文章于 2022-12-09 09:58:09 发布

阅读量898

点赞数

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

版权

该SQL查询用于在DB2数据库中计算两个时间点之间相差的小时数，特别是在空气质量达到500的时段。查询首先找出特定城市和时间段内AQI大于等于500的小时，然后计算这些小时与最近一次AQI超过500的时间点之间的差值。最后，根据地区对结果进行聚合，并结合地理位置信息进行展示。

摘要由CSDN通过智能技术生成

SELECT AREA,SUM_HOUR AS AQIBBSC,CNT_HOUR AS AQICXBBCS,LONGITUDE,LATITUDE FROM (
SELECT AREA,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,CITYCODE,SUM(HOUR_CNT) SUM_HOUR,COUNT(*) CNT_HOUR
FROM (
SELECT AREA,CITYCODE,HOURSDIF,COUNT(*) HOUR_CNT
FROM (
SELECT AREA,CITYCODE,
HOUR(ATIMEPOINT)-HOUR(BTIMEPOINT) + (DAYS(ATIMEPOINT) - DAYS(BTIMEPOINT)) * 24 -ROWNUM AS HOURSDIF
FROM (
SELECT TIMEPOINT ATIMEPOINT,AREA,CITYCODE,(
SELECT TIMEPOINT
FROM TENV.AIR_CITYHOURAQI_PUBLISH B WHERE AQI >= 500
AND TO_CHAR(TIMEPOINT,'YYYY-MM') >= '2016-01' AND TO_CHAR(TIMEPOINT,'YYYY-MM') <= '2017-11'
AND B.CITYCODE = A.CITYCODE
AND ROWNUM = 1
) BTIMEPOINT
FROM TENV.AIR_CITYHOURAQI_PUBLISH A
WHERE AQI >= 500