学习编程,你可能会犹豫选择 C++ 还是 Java;入门数据科学,你可能会纠结于选择 Python 还是 R;但无论如何,SQL 都是 IT 从业人员不可或缺的技能!
GDP(国内生产总值)是指按市场价格计算的一个国家/地区所有常住单位在一定时期内生产活动的所有最终产品和服务的市场价格。GDP 通常被公认为是衡量国家/地区经济状况的最佳指标。
01
初步探索数据
所有的 GDP 和人口数据都存储在 gdp_data 表中,我们先熟悉一下该表中的数据:
SELECT *
FROM gdp_data;
该查询的结果如下(显示部分内容):
该表包含了 4 个字段:国家/地区名称、年度、人口以及 GDP。接下来统计一下表中包含的行数:
SELECT COUNT(*)
FROM gdp_data;
COUNT(*)|
--------|
1953|
该表中共计有 1953 条数据。再按照国家/地区进行统计:
SELECT country_name AS "国家/地区", COUNT(*) AS "数据量"
FROM gdp_data
GROUP BY country_name;
查询的结果如下(显示部分内容):
共计有 217 个国家/地区,每个国家/地区有 9 条数据。试试按照年度进行统计:
SELECT year AS "年度", COUNT(*) AS "数据量"
FROM gdp_data
GROUP BY year;
年度统计的结果如下:
如果想要查看具体国家/地区或者年度的数据,可以通过查询条件实现过滤。以下语句查询中国大陆 2018 年度的数据:
SELECT *
FROM gdp_data
WHERE country_name ='中国'
AND year =2018;
country_name|year|population|gdp |
------------|----|----------|-----------------|
中国|2018|1392730000|13608200000000.00|
在进一步分析之前,需要确认数据中是否存在缺失值。
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;
缺失是由于没有统计或者公布相应的数据,我们在后续的分析过程中需要注意空值的处理。例如,在分析之前过滤掉这些无效的数据。
03
数据汇总
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 函数用于对数据进行取整。
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 实现相同的功能,大家可以自行尝试一下。
再来看一下 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;
该查询的结果如下:
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;
该查询的结果如下(显示部分内容):
接下来统计每个国家/地区在全球所占的比例。在前面我们已经得到了全球人口数量(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;
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;
GDP 增长率高的基本都是经济体量比较小的国家/地区。
以上我们分析了世界银行公布的全球 GDP 和人口数据,使用了《SQL 从入门到精通》基础篇讲解过的数据分析和统计方法。