CreateTableIfNotExists Insurance (pid int, tiv_2015 float, tiv_2016 float, lat float, lon float);Truncatetable Insurance;insertinto Insurance (pid, tiv_2015, tiv_2016, lat, lon)values('1','10','5','10','10');insertinto Insurance (pid, tiv_2015, tiv_2016, lat, lon)values('2','20','20','20','20');insertinto Insurance (pid, tiv_2015, tiv_2016, lat, lon)values('3','10','30','20','20');insertinto Insurance (pid, tiv_2015, tiv_2016, lat, lon)values('4','10','40','40','40');
输入
输出
with t1 as(select*,count(tiv_2015)over(partitionby tiv_2015) rn1,count(1)over(partitionby lat,lon) rn2
from Insurance
),-- 求tiv_2015中重复的id
t2 as(select pid
from t1
where rn1>1),-- 两表关联求出tiv_2015重复的所有数据
t3 as(select i.*from t2,insurance i
where t2.pid=i.pid
),-- 求精度和纬度不重复的数据
t4 as(select*from t1
where rn2=1)-- 求2016年的总投资额selectsum(t4.tiv_2016)as TIV_2016
from t4,t3
where t3.pid=t4.pid