大数据面试SQL（七）：累加刚好超过各省GDP40%的地市名称

文章目录

累加刚好超过各省GDP40%的地市名称

一、题目

二、分析

三、SQL实战

四、样例数据参考

累加刚好超过各省GDP40%的地市名称

一、题目

现有各省地级市的gdp数据,求从高到低累加刚好超过各省GDP40%的地市名称，临界地市也需要。例如：

浙江省的杭州24% 宁波 20% ,杭州+宁波=44% 大于40% 取出杭州、宁波
江苏省的苏州19% 南京 14% 无锡 12%，苏州+南京=33% ，苏州+南京+无锡=45%，取出苏州、南京、无锡

样例数据：

目标结果：

二、分析

1、考察的是聚合函数开窗、聚合函数开窗时使用order by 进行累积求和。

2、要求包含临界地市，这里属于技巧的考察，这种使用补集的方式计算。

维度	评分
题目难度	⭐️⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

三、SQL实战

1、计算每个城市占比，累积占比。

使用sum()开窗计算每个城市的gdp总额，以及使用sum()over(order by )计算累积占比。

查询语句：

sql">select prov,city,gdp_amt,total_gpd_amt,ord_sum_gdp_amt,round(gdp_amt / total_gpd_amt,2) as city_percnt,round(ord_sum_gdp_amt / total_gpd_amt,2) as lj_city_percent
from (select prov,city,gdp_amt,sum(gdp_amt) over (partition by prov)                       as total_gpd_amt,sum(gdp_amt) over (partition by prov order by gdp_amt desc) as ord_sum_gdp_amtfrom t1_gdp) t;

查询结果：

2、求各省地市累积求和>40%的记录。

由于要求包含临界值，直接求取十分不方便，所以我们改变策略，gdp从低到高累加求和，求取累加求和 小于60% 的数据。

查询语句：

sql">select prov,city,gdp_amt,total_gpd_amt,ord_sum_gdp_amt,round(gdp_amt / total_gpd_amt,2) as city_percnt,round(ord_sum_gdp_amt / total_gpd_amt,2) as lj_city_percent
from (select prov,city,gdp_amt,sum(gdp_amt) over (partition by prov)                       as total_gpd_amt,sum(gdp_amt) over (partition by prov order by gdp_amt asc) as ord_sum_gdp_amtfrom t1_gdp) t
where round(ord_sum_gdp_amt / total_gpd_amt,2) <0.6;

查询结果：

3、求补集，得到最后结果。

使用各省市全量数据，计算出不在上述结果的数据，即目标结果。

查询语句：

sql">select t1.prov,t1.city
from t1_gdp t1left join(select prov,city,gdp_amt,total_gpd_amt,ord_sum_gdp_amt,round(gdp_amt / total_gpd_amt, 2)         as city_percnt,round(ord_sum_gdp_amt / total_gpd_amt, 2) as lj_city_percentfrom (select prov,city,gdp_amt,sum(gdp_amt) over (partition by prov)                      as total_gpd_amt,sum(gdp_amt) over (partition by prov order by gdp_amt asc) as ord_sum_gdp_amtfrom t1_gdp) twhere round(ord_sum_gdp_amt / total_gpd_amt, 2) < 0.6) tton t1.prov = tt.provand t1.city = tt.city
where tt.city is null;

查询结果：

四、样例数据参考

sql">--建表语句
CREATE TABLE t1_gdp (prov string COMMENT '省份',city string COMMENT '城市',gdp_amt decimal(10,2) comment  'GDP'
) COMMENT '各省地市GDP';
--插入数据
insert  into t1_gdp(prov,city,gdp_amt)
values('浙江','杭州',20059),('浙江','宁波',16452.8),('浙江','温州',8730.6),('浙江','绍兴',7791),('浙江','嘉兴',7062.45),('浙江','台州',6240.68),('浙江','金华',6011.27),('浙江','湖州',4015.1),('浙江','衢州',2125.2),('浙江','舟山',2100.8),('浙江','丽水',1964.4),('江苏','苏州',24653.37),('江苏','南京',17421.4),('江苏','无锡',15456.19),('江苏','南通',11813.27),('江苏','常州',10116.36),('江苏','徐州',8900.44),('江苏','扬州',7423.26),('江苏','盐城',7403.87),('江苏','泰州',6731.66),('江苏','镇江',5264.07),('江苏','淮安',5015.06),('江苏','宿迁',4398.07),('江苏','连云港',4363.61);