sql server 資料排名

標籤：not   where   rank   pm2   from   ast   北京   .sh   server   

城市排名列表

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)

 

sql server 資料排名