sql server 数据排名

最新推荐文章于 2025-06-21 14:56:12 发布

weixin_34120274

最新推荐文章于 2025-06-21 14:56:12 发布

阅读量265

点赞数

本文通过SQL查询展示了2017年3月4日中国各城市的空气质量排名，并提供了北京从2017年2月1日至3月5日的空气质量指数(AQI)变化曲线。通过对PM2.5、PM10等指标的平均值计算，揭示了不同城市间的空气质量差异。

城市排名列表

SELECT c.sheng ,m.city, RANK() OVER (ORDER BY round(avg(cast(indexs as float)),3)) AS px,
         round(avg(cast(pm25 as float)),0) pm25, round(avg(cast(pm10 as float)),0) pm10, round(avg(cast(co as float)),3) co, 
        round(avg(cast(no2 as float)),0) no2, round(avg(cast(so2 as float)),0) so2,round(avg(cast(o3_8 as float)),0) o3_8,round(avg(cast(indexs as float)),3) indexs,round(avg(cast(aqi as float)),0) aqi
          FROM monitor_city_hour m,cd_city c
          where m.city=c.city and
          datetime='2017-03-04 12:00:00' and aqi!=0 and pm25!=0 and pm25 is not null
          group by c.sheng,m.city
          order by indexs

某城市一段时间的排名曲线

 select fdate time,px,aqi from(
        SELECT fdate,city,RANK() OVER (partition by fdate ORDER BY aqi) AS px,aqi
          FROM monitor_city_day 
          where  aqi!=0 and pm25!=0 and pm25 is not null
          and cast(fdate as datetime)>=cast('2017-02-01' as datetime) and
          cast(fdate as datetime)<=cast('2017-03-05' as datetime)
          )a where city='北京'
          order by cast(fdate as datetime)