SQL Server Data Ranking

List of city rankings

SELECTC.sheng, M.city, RANK () Over(ORDER  by round(avg(cast(Indexs as float)),3)) aspx,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 fromMonitor_city_hour m,cd_city CwhereM.city=C.city and          datetime='2017-03-04 12:00:00'  andAQi!=0  andPm25!=0  andPm25 is  not NULL          Group  byc.sheng,m.cityOrder  byIndexs

The ranking curve of a city for some time

 SelectFDate Time,px,aqi from(        SELECTFdate,city,rank () Over(Partition byFDateORDER  byAQi asPx,aqi fromMonitor_city_daywhereAQi!=0  andPm25!=0  andPm25 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)) awhereCity='Beijing'          Order  by cast(FDate as datetime)

SQL Server Data Ranking