sql server 数据排名

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 数据排名

标签：not   where   rank   pm2   from   ast   北京   .sh   server