拼多多大数据面试SQL-累加刚好超过各省GDP40%的地市名称.md

数据仓库晨曦

修改于 2024-06-20 08:12:05

900

修改于 2024-06-20 08:12:05

文章被收录于专栏：数据仓库技术数据仓库技术

一、题目

现有各省地级市的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 t_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 t_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 t_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 t_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 t_gdp (
prov string COMMENT '省份',
city string COMMENT '城市',
gdp_amt decimal(10,2) comment  'GDP'
) COMMENT '各省地市GDP'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
;
--插入数据
insert  into t_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);

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2024-06-05，如有侵权请联系 cloudcommunity@tencent.com 删除

数据