福州企业建站系统模板公司管理软件免费版
文章目录
累加刚好超过各省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 )计算累积占比。
查询语句：
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% 的数据。
查询语句：
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、求补集，得到最后结果。
使用各省市全量数据，计算出不在上述结果的数据，即目标结果。
查询语句：
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;查询结果：
四、样例数据参考
--建表语句
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博客🙉
- 📢停下休息的时候不要忘了别人还在奔跑，希望大家抓紧时间学习，全力奔赴更美好的生活✨