用 SQL 玩转世界银行全球 GDP 数据

喜欢ヅ旅行 2023-06-14 14:54 6阅读 0赞

SQL（Structured Query Language，结构化查询语言）作为访问和操作关系数据库的标准语言，不但应用广泛，而且简单易学，因为它在设计之初就考虑了非技术人员的使用需求。

**SQL 语句全都是由简单的英语单词组成；我们只需要说明自己想要的结果，然后将具体的实现交给数据库管理系统。**

学习编程，你可能会犹豫选择 C++ 还是 Java；入门数据科学，你可能会纠结于选择 Python 还是 R；但无论如何，**SQL 都是 IT 从业人员不可或缺的技能**！

我们以世界银行公布的全球 GDP 和人口数据为例，使用 SQL 数据分析的常用方法，自己动手来分析一下。

> GDP（国内生产总值）是指按市场价格计算的一个国家/地区所有常住单位在一定时期内生产活动的所有最终产品和服务的市场价格。GDP 通常被公认为是衡量国家/地区经济状况的最佳指标。

本文数据来源为世界银行公开数据中的 GDP（现价美元）和人口总数。为了方便使用，我们将数据转换成了 SQL 脚本，摘取了全球 217 个国家和地区 2010-2018 年的 GDP 数据（按照现价美元进行换算）以及相应的人口数据。案例的初始化脚本 gdp\_data.sql 放在了 GitHub 上，可以在专栏文章中进行下载。

### **01** ###

### **初步探索数据** ###

所有的 GDP 和人口数据都存储在 gdp\_data 表中，我们先熟悉一下该表中的数据：

SELECT *	
      FROM gdp_data;

该查询的结果如下（显示部分内容）：

![640?wx\_fmt=png][640_wx_fmt_png]

该表包含了 4 个字段：国家/地区名称、年度、人口以及 GDP。接下来统计一下表中包含的行数：

SELECT COUNT(*)	
      FROM gdp_data;	
    COUNT(*)|	
    --------|	
    1953|

该表中共计有 1953 条数据。再按照国家/地区进行统计：

SELECT country_name AS "国家/地区", COUNT(*) AS "数据量"	
      FROM gdp_data	
     GROUP BY country_name;

查询的结果如下（显示部分内容）：

![640?wx\_fmt=png][640_wx_fmt_png 1]

共计有 217 个国家/地区，每个国家/地区有 9 条数据。试试按照年度进行统计：

SELECT year AS "年度", COUNT(*) AS "数据量"	
      FROM gdp_data	
     GROUP BY year;

年度统计的结果如下：

![640?wx\_fmt=png][640_wx_fmt_png 2]

表中总共有 9 年的数据，每个年度包含 217 个国家/地区的数据。

如果想要查看具体国家/地区或者年度的数据，可以通过查询条件实现过滤。以下语句查询中国大陆 2018 年度的数据：

SELECT *	
      FROM gdp_data	
     WHERE country_name ='中国'	
       AND year =2018;	
    country_name|year|population|gdp              |	
    ------------|----|----------|-----------------|	
    中国|2018|1392730000|13608200000000.00|

2018 年中国大陆将近有 14 亿人口，GDP 约为 13.6 万亿美元。

在进一步分析之前，需要确认数据中是否存在缺失值。

**02**

### **查找缺失值** ###

以下查询可以统计人口和 GDP 缺失的记录数：

SELECT COUNT(*)- COUNT(population) AS "人口数据缺失",	
           COUNT(*)- COUNT(gdp) AS "GDP 数据缺失"	
      FROM gdp_data;	
    人口数据缺失|GDP 数据缺失|	
    ----------|-----------|	
    7|131|

看看具体哪些记录缺失：

SELECT *	
      FROM gdp_data	
     WHERE population IS NULL	
        OR gdp IS NULL;

该查询的结果如下（显示部分内容）：

![640?wx\_fmt=png][640_wx_fmt_png 3]

缺失是由于没有统计或者公布相应的数据，我们在后续的分析过程中需要注意空值的处理。例如，在分析之前过滤掉这些无效的数据。

### **03** ###

### **数据汇总** ###

通过汇总可以对全球总人口和 GDP 数据进行整体了解。以下查询统计了 2018 年的数据：

SELECT SUM(population) AS "总人口",	
           ROUND(AVG(population),0) AS "平均人口",	
           MAX(population) AS "最多人口",	
           MIN(population) AS "最少人口"	
      FROM gdp_data	
     WHERE year =2018;	
    总人口|平均人口|最多人口|最少人口|	
    ----------|-------------|----------|------|	
    7567243296|35033534|1392730000|11508|

结果显示 2018 全球人数已经超过 75 亿。其中，ROUND 函数用于对数据进行取整。

再看一下 2018 年的 GDP 数据：

SELECT SUM(gdp) AS "全球 GDP",	
           ROUND(AVG(gdp),2) AS "平均 GDP",	
           MAX(gdp) AS "最高 GDP",	
           MIN(gdp) AS "最低 GDP"	
      FROM gdp_data	
     WHERE year =2018;	
    全球 GDP          |平均 GDP        |最高 GDP         |最低 GDP    |	
    -----------------|---------------|-----------------|-----------|	
    83886097221674.87|448588755196.12|20494100000000.00|42587778.47|

2018 年全球 GDP 约为 83.9 万亿美元。聚合函数自动忽略 NULL 值，因此统计数据中不包含缺失的记录。

汇总得到的是数据的整体情况。虽然我们知道 GDP 最高值为 20 万亿美元，但不知道是哪个国家/地区；因此还需要针对个体进行分析。

### **04** ###

### **数据排行榜** ###

大家都说中国是世界上人口最多的国家，让我们来用数据验证一下：

--Oracle、SQL Server以及PostgreSQL实现	
    SELECT country_name, year, population	
      FROM gdp_data	
     WHERE year =2018 AND population IS NOT NULL	
     ORDER BY population DESC	
    OFFSET 0 ROWS	
     FETCH FIRST 10 ROWS ONLY;

该查询返回了 2018 年度人口数量排名前 10 的国家/地区，我们在第 7 篇中学习了 FETCH 和 OFFSET 子句。MySQL 数据库中可以使用 LIMIT 10 实现相同的功能，大家可以自行尝试一下。

![640?wx\_fmt=png][640_wx_fmt_png 4]

从数据可以看出中国的人口数量仍然最多，同时印度的人口数量已经非常接近中国。

再来看一下 2018 年度 GDP 的排名信息。除了数据之外，我们还增加了一个柱状图：

--Oracle实现	
    SELECT country_name, year, gdp, RPAD('█', gdp/250000000000,'█')  AS "柱状图"	
      FROM gdp_data	
     WHERE year =2018 AND gdp IS NOT NULL	
     ORDER BY gdp DESC	
    OFFSET 0 ROWS	
     FETCH FIRST 10 ROWS ONLY;	
    --MySQL实现	
    SELECT country_name, year, gdp, rpad('█', gdp/250000000000,'█') AS "柱状图"	
      FROM gdp_data	
     WHERE year =2018 AND gdp IS NOT NULL	
     ORDER BY gdp DESC	
     LIMIT 10;

RPAD(str, length, pad\_str) 是 Oracle 中一个函数，使用 pad\_str 填充字符串 str，直到长度为 length。MySQL 和 PostgreSQL 也支持 RPAD 函数，SQL Server 可以使用 REPLICATE(str, count) 函数。

该查询的结果如下：

![640?wx\_fmt=png][640_wx_fmt_png 5]

图形能够更加直观地感受数据之间的差异。虽然中国的人口数量最多，但是 GDP 和美国之间还存在一些差距。

GDP 反应的是一个国家/地区的生产总值，而每个国家/地区的人口数量不同；因此，我们还需要分析人均和占比数据。

### **05** ###

### **人均、占比以及趋势分析** ###

以下语句计算 2018 年每个国家/地区的人均 GDP 数据：

SELECT country_name AS "国家/地区",	
           year AS "年度",	
           ROUND(gdp/population,1) AS "人均 GDP"	
      FROM gdp_data	
     WHERE year =2018	
       AND population IS NOT NULL	
       AND gdp IS NOT NULL	
     ORDER BY ROUND(gdp/population,1) DESC;

该查询的结果如下（显示部分内容）：

![640?wx\_fmt=png][640_wx_fmt_png 6]

卢森堡的人均 GDP 最高，达到了 11.4 万美元；中国澳门特别行政区排在第 2 名。

接下来统计每个国家/地区在全球所占的比例。在前面我们已经得到了全球人口数量（7567243296）和 GDP 总量（83886097221674.87）。以下查询计算 2018 年各个国家/地区的人口和 GDP 占比：

SELECT country_name,	
           ROUND(population/7567243296,5)*100 AS "人口占比（%）",	
           ROUND(gdp/83886097221674.87,5)*100 AS "GDP 占比（%）"	
      FROM gdp_data	
     WHERE year =2018	
       AND (population IS NOT NULL OR gdp IS NOT NULL)	
     ORDER BY population DESC;

查询的结果中排除了人口数据和 GDP 都缺失的国家/地区。

![640?wx\_fmt=png][640_wx_fmt_png 7]

最后分析一下 2018 年各个国家/地区的 GDP 增长率：

SELECT country_name AS "国家/地区",	
           SUM(CASE WHEN year =2017 THEN gdp ELSE 0END) AS "2017年GDP",	
           SUM(CASE WHEN year =2018 THEN gdp ELSE 0END) AS "2018年GDP",	
    (SUM(CASE WHEN year =2018 THEN gdp ELSE 0END)	
    - SUM(CASE WHEN year =2017 THEN gdp ELSE 0END))/SUM(CASE WHEN year =2017 THEN gdp ELSE 0END)*100 AS "2018 增长率（%）"	
      FROM gdp_data	
     WHERE year IN (2017,2018) AND gdp IS NOT NULL	
     GROUP BY country_name	
     ORDER BY 4 DESC;

查询中使用了 CASE 表达式和 SUM 函数将行转换为列，然后使用 2018 年的 GDP 和 2017 年的 GDP 进行比较，计算 2018 年度 GDP 的增长率。

![640?wx\_fmt=png][640_wx_fmt_png 8]

GDP 增长率高的基本都是经济体量比较小的国家/地区。

以上我们分析了世界银行公布的全球 GDP 和人口数据，使用了《SQL 从入门到精通》基础篇讲解过的数据分析和统计方法。

**扫码了解《SQL从入门到精通》专栏详情**

![640?wx\_fmt=jpeg][640_wx_fmt_jpeg]

**▼**

如果大家对其中的一些技巧不太熟悉，欢迎订阅我的专栏《SQL 从入门到精通》。 在掌握更多分析和统计方法后，大家可以使用这个数据集进行更多的分析，来巩固已经学习到的知识。

**点击阅读原文，订阅专栏，加入专栏学习群一起探讨交流！**

[640_wx_fmt_png]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/TCHicQEF6XKDftUI6DEMCMm03NicbgVQiaj48HlJD3SQbVIrg3OaG60z2ibIFBezX6aoPGjiawMGMHicYUxAbWuPrWIA/640?wx_fmt=png
[640_wx_fmt_png 1]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/TCHicQEF6XKDftUI6DEMCMm03NicbgVQiajkwvtPK3Tq9FPibMTJ0Rju47s0Le4hZWRfCIJ6yjJia06USvBsrGEQ3ibQ/640?wx_fmt=png
[640_wx_fmt_png 2]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/TCHicQEF6XKDftUI6DEMCMm03NicbgVQiaj1PMOTxOoQOibyturcOu5pOicmBQj8zvqZV0GsAib7ibl7zKCiacIJqGRGgg/640?wx_fmt=png
[640_wx_fmt_png 3]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/TCHicQEF6XKDftUI6DEMCMm03NicbgVQiaj9Tjzic1JANRByEznC8ax2ATicsEYPiahNkNzNwvgtIMbS7mfMa4ufsQ3w/640?wx_fmt=png
[640_wx_fmt_png 4]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/TCHicQEF6XKDftUI6DEMCMm03NicbgVQiajk8NC50wjcWrkI0bAEicxxB7xdY9lNPxwa56xW0Lfbssl6mZxv0gzsBA/640?wx_fmt=png
[640_wx_fmt_png 5]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/TCHicQEF6XKDftUI6DEMCMm03NicbgVQiaj4qpTgBKsPMP33klf5Z3koJB1jv7w16ibnle8SIS0HrrzFqKFQczqEfw/640?wx_fmt=png
[640_wx_fmt_png 6]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/TCHicQEF6XKDftUI6DEMCMm03NicbgVQiajH7PhAXW8ic7ONmicIkPyzKkkGDcbUtyeFcvkTKfb6J5sCZcNt7GjpCrg/640?wx_fmt=png
[640_wx_fmt_png 7]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/TCHicQEF6XKDftUI6DEMCMm03NicbgVQiajyhWRcGGnSJ59cGDK845x1rbUvds5qWB9EDXDLeaElVklS8iaZCrXpsQ/640?wx_fmt=png
[640_wx_fmt_png 8]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/TCHicQEF6XKDftUI6DEMCMm03NicbgVQiajuA2lsTib4HibicwhjXOrT7zKNibkscIz2RV2TUuTHfkBI1BdqNr3v07nXQ/640?wx_fmt=png
[640_wx_fmt_jpeg]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_jpg/0vU1ia3htaaOw5QHibTibPhfsk75BRbNyCCyek3licunMBXJLgb25BibK3qIqVYKzicajwvjQDHiblMOmWD6xia7nJOhJw/640?wx_fmt=jpeg