文章目录
累加刚好超过各省GDP40%的地市名称
一、题目
现有各省地级市的gdp数据,求从高到低累加刚好超过各省GDP40%的地市名称，临界地市也需要。 例如：
- 浙江省的杭州24% 宁波 20% ,杭州+宁波=44% 大于40% 取出杭州、宁波
- 江苏省的苏州19% 南京 14% 无锡 12%，苏州+南京=33% ，苏州+南京+无锡=45%，取出 苏州、南京、无锡
样例数据：
目标结果：
二、分析
1、考察的是聚合函数开窗、聚合函数开窗时使用order by 进行累积求和。
2、要求包含临界地市，这里属于技巧的考察，这种使用补集的方式计算。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
三、SQL实战
1、计算每个城市占比，累积占比。
使用sum()开窗计算每个城市的gdp总额，以及使用sum()over(order by )计算累积占比。
查询语句：
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_amt
from t1_gdp) t;查询结果：
2、求各省地市累积求和>40%的记录。
由于要求包含临界值，直接求取十分不方便，所以我们改变策略，gdp从低到高累加求和，求取累加求和 小于60% 的数据。
查询语句：
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_amt
from t1_gdp) t
where round(ord_sum_gdp_amt / total_gpd_amt,2) <0.6;查询结果：
3、求补集，得到最后结果。
使用各省市全量数据，计算出不在上述结果的数据，即目标结果。
查询语句：
select t1.prov,
t1.city
from t1_gdp t1
left 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_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_amt
from t1_gdp) t
where round(ord_sum_gdp_amt / total_gpd_amt, 2) < 0.6) tt
on t1.prov = tt.prov
and t1.city = tt.city
where tt.city is null;查询结果：
四、样例数据参考
--建表语句
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);- 📢博客主页：https://lansonli.blog.csdn.net
- 📢欢迎点赞 👍 收藏 ⭐留言 📝 如有错误敬请指正！
- 📢本文由 Lansonli 原创，首发于 CSDN博客🙉
- 📢停下休息的时候不要忘了别人还在奔跑，希望大家抓紧时间学习，全力奔赴更美好的生活✨