拼多多大数据面试SQL-累加刚好超过各省GDP40%的地市名称
一、题目
现有各省地级市的gdp数据,求从高到底累加刚好超过各省GDP40%的地市名称，临界地市也需要。 例如:
浙江省的杭州24% 宁波 20% ,杭州+宁波=44% 大于40% 取出杭州、宁波
江苏省的苏州19% 南京 14% 无锡 12%，苏州+南京=33% ，苏州+南京+无锡=45%，取出 苏州、南京、无锡
+-------+-------+-----------+
| prov | city | gdp_amt |
+-------+-------+-----------+
| 浙江 | 杭州 | 20059.00 |
| 浙江 | 宁波 | 16452.80 |
| 浙江 | 温州 | 8730.60 |
| 浙江 | 绍兴 | 7791.00 |
| 浙江 | 嘉兴 | 7062.45 |
| 浙江 | 台州 | 6240.68 |
| 浙江 | 金华 | 6011.27 |
| 浙江 | 湖州 | 4015.10 |
| 浙江 | 衢州 | 2125.20 |
| 浙江 | 舟山 | 2100.80 |
| 浙江 | 丽水 | 1964.40 |
| 江苏 | 苏州 | 24653.37 |
| 江苏 | 南京 | 17421.40 |
| 江苏 | 无锡 | 15456.19 |
| 江苏 | 南通 | 11813.27 |
| 江苏 | 常州 | 10116.36 |
| 江苏 | 徐州 | 8900.44 |
| 江苏 | 扬州 | 7423.26 |
| 江苏 | 盐城 | 7403.87 |
| 江苏 | 泰州 | 6731.66 |
| 江苏 | 镇江 | 5264.07 |
| 江苏 | 淮安 | 5015.06 |
| 江苏 | 宿迁 | 4398.07 |
| 江苏 | 连云港 | 4363.61 |
+-------+-------+-----------+二、分析
- 考察的是聚合函数开窗、聚合函数开窗时使用order by 进行累积求和；
- 要求包含临界地市，这里属于技巧的考察，这种使用补集的方式计算；
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
三、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_amt
from t1_gdp) t执行结果
+-------+-------+-----------+----------------+------------------+--------------+------------------+
| prov | city | gdp_amt | total_gpd_amt | ord_sum_gdp_amt | city_percnt | lj_city_percent |
+-------+-------+-----------+----------------+------------------+--------------+------------------+
| 江苏 | 苏州 | 24653.37 | 128960.63 | 24653.37 | 0.19 | 0.19 |
| 江苏 | 南京 | 17421.40 | 128960.63 | 42074.77 | 0.14 | 0.33 |
| 江苏 | 无锡 | 15456.19 | 128960.63 | 57530.96 | 0.12 | 0.45 |
| 江苏 | 南通 | 11813.27 | 128960.63 | 69344.23 | 0.09 | 0.54 |
| 江苏 | 常州 | 10116.36 | 128960.63 | 79460.59 | 0.08 | 0.62 |
| 江苏 | 徐州 | 8900.44 | 128960.63 | 88361.03 | 0.07 | 0.69 |
| 江苏 | 扬州 | 7423.26 | 128960.63 | 95784.29 | 0.06 | 0.74 |
| 江苏 | 盐城 | 7403.87 | 128960.63 | 103188.16 | 0.06 | 0.80 |
| 江苏 | 泰州 | 6731.66 | 128960.63 | 109919.82 | 0.05 | 0.85 |
| 江苏 | 镇江 | 5264.07 | 128960.63 | 115183.89 | 0.04 | 0.89 |
| 江苏 | 淮安 | 5015.06 | 128960.63 | 120198.95 | 0.04 | 0.93 |
| 江苏 | 宿迁 | 4398.07 | 128960.63 | 124597.02 | 0.03 | 0.97 |
| 江苏 | 连云港 | 4363.61 | 128960.63 | 128960.63 | 0.03 | 1.00 |
| 浙江 | 杭州 | 20059.00 | 82553.30 | 20059.00 | 0.24 | 0.24 |
| 浙江 | 宁波 | 16452.80 | 82553.30 | 36511.80 | 0.20 | 0.44 |
| 浙江 | 温州 | 8730.60 | 82553.30 | 45242.40 | 0.11 | 0.55 |
| 浙江 | 绍兴 | 7791.00 | 82553.30 | 53033.40 | 0.09 | 0.64 |
| 浙江 | 嘉兴 | 7062.45 | 82553.30 | 60095.85 | 0.09 | 0.73 |
| 浙江 | 台州 | 6240.68 | 82553.30 | 66336.53 | 0.08 | 0.80 |
| 浙江 | 金华 | 6011.27 | 82553.30 | 72347.80 | 0.07 | 0.88 |
| 浙江 | 湖州 | 4015.10 | 82553.30 | 76362.90 | 0.05 | 0.93 |
| 浙江 | 衢州 | 2125.20 | 82553.30 | 78488.10 | 0.03 | 0.95 |
| 浙江 | 舟山 | 2100.80 | 82553.30 | 80588.90 | 0.03 | 0.98 |
| 浙江 | 丽水 | 1964.40 | 82553.30 | 82553.30 | 0.02 | 1.00 |
+-------+-------+-----------+----------------+------------------+--------------+------------------+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_amt
from t1_gdp) t
where round(ord_sum_gdp_amt / total_gpd_amt,2) <0.6执行结果
+-------+-------+-----------+----------------+------------------+--------------+------------------+
| prov | city | gdp_amt | total_gpd_amt | ord_sum_gdp_amt | city_percnt | lj_city_percent |
+-------+-------+-----------+----------------+------------------+--------------+------------------+
| 江苏 | 连云港 | 4363.61 | 128960.63 | 4363.61 | 0.03 | 0.03 |
| 江苏 | 宿迁 | 4398.07 | 128960.63 | 8761.68 | 0.03 | 0.07 |
| 江苏 | 淮安 | 5015.06 | 128960.63 | 13776.74 | 0.04 | 0.11 |
| 江苏 | 镇江 | 5264.07 | 128960.63 | 19040.81 | 0.04 | 0.15 |
| 江苏 | 泰州 | 6731.66 | 128960.63 | 25772.47 | 0.05 | 0.20 |
| 江苏 | 盐城 | 7403.87 | 128960.63 | 33176.34 | 0.06 | 0.26 |
| 江苏 | 扬州 | 7423.26 | 128960.63 | 40599.60 | 0.06 | 0.31 |
| 江苏 | 徐州 | 8900.44 | 128960.63 | 49500.04 | 0.07 | 0.38 |
| 江苏 | 常州 | 10116.36 | 128960.63 | 59616.40 | 0.08 | 0.46 |
| 江苏 | 南通 | 11813.27 | 128960.63 | 71429.67 | 0.09 | 0.55 |
| 浙江 | 丽水 | 1964.40 | 82553.30 | 1964.40 | 0.02 | 0.02 |
| 浙江 | 舟山 | 2100.80 | 82553.30 | 4065.20 | 0.03 | 0.05 |
| 浙江 | 衢州 | 2125.20 | 82553.30 | 6190.40 | 0.03 | 0.07 |
| 浙江 | 湖州 | 4015.10 | 82553.30 | 10205.50 | 0.05 | 0.12 |
| 浙江 | 金华 | 6011.27 | 82553.30 | 16216.77 | 0.07 | 0.20 |
| 浙江 | 台州 | 6240.68 | 82553.30 | 22457.45 | 0.08 | 0.27 |
| 浙江 | 嘉兴 | 7062.45 | 82553.30 | 29519.90 | 0.09 | 0.36 |
| 浙江 | 绍兴 | 7791.00 | 82553.30 | 37310.90 | 0.09 | 0.45 |
| 浙江 | 温州 | 8730.60 | 82553.30 | 46041.50 | 0.11 | 0.56 |
+-------+-------+-----------+----------------+------------------+--------------+------------------+3.求补集，得到最后结果
使用各省市全量数据，计算出不在上述结果的数据，即目标结果
执行SQL
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执行结果
+----------+----------+
| t1.prov | t1.city |
+----------+----------+
| 浙江 | 杭州 |
| 浙江 | 宁波 |
| 江苏 | 苏州 |
| 江苏 | 南京 |
| 江苏 | 无锡 |
+----------+----------+四、建表语句和数据插入
--建表语句
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);本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术 (opens in a new tab)“发表；