sql---partition的使用

partition分组函数，简化分组

原来语句

select ROUND(SUM(TIV_2016),2) TIV_2016
from (select a.*,b.TIV_2015_count,c.LAT_LON_count from insurance a
left join (select TIV_2015,count(*) TIV_2015_count from insurance group by TIV_2015) b
on a.TIV_2015=b.TIV_2015
left join (select CONCAT(LAT,LON) LAT_LON,count(*) LAT_LON_count from insurance group by LAT,LON) c
on CONCAT(a.LAT,a.LON)=c.LAT_LON ) d
where d.TIV_2015_count>1 and d.LAT_LON_count=1

使用分组函数

select ROUND(sum(TIV_2016),2) as TIV_2016 from(
select *,
count(PID) over (partition by TIV_2015) as num_tiv,
count(PID) over(partition by LAT,LON) as num_city
from insurance)h
where h.num_tiv >1 and num_city=1