顾名思义,本节重点是使用SQL将两个或多个数据库表连接到一个表中,这是数据科学家的基本技能。内部联接(inner join),和左联接可能是两种最常见的联接。
本文内容整理自 Datacamp| Joining Data in PostgreSQL
Inner Join介绍
初始数据图(Initial data diagram)
我们可以看到id
字段的匹配值用相同的颜色着色。id字段被称为键字段(key field),因为它可以用来将一个表引用到另一个表。left_table和right_table都有另一个名为val的字段。
内部连接图
内部联接只包括键在两个表中的记录。这里可以看到id
字段只匹配值1和4。使用内部联接,我们在right_table中查找与left_table中键字段中的所有条目对应的匹配项。
所以这里只关注那些id
字段匹配的记录, 而不属于内部连接的记录已经消失了。下面是由INNER JOIN得到的表,它给出了right_table的val字段,其中的记录只对应于id
值为1或4的记录,分别被涂成了黄色和紫色。
用以实现这一过程的代码为:
SELECT left_table.id AS L_id
left_table.val AS L_val
right_table.val AS R_val
FROM left_table
INNER JOIN right_table
ON left_table.id = right_table.id
例子
这里,我们给出两个表, Prime Minister 表 和 President 表,如下:
Prime Minister
country | continent | prime_minister |
---|---|---|
Egypt | Africa | Sherif Ismail |
Portugal | Europe | Antonio Costa |
Vietnam | Aisa | Nguyen Xuan Phuc |
Haiti | North America | Jack Guy Lafontant |
India | Asia | Narendra Modi |
Australia | Oceania | Malcolm Turnbull |
Norway | Europe | Erna Solberg |
Brunei | Asia | Hassanal Bolkiah |
Oman | Asia | Qaboos bin Said al Said |
Spain | Europe | Mariano Rajoy |
President
country | continent | president |
---|---|---|
Egypt | Africa | Abdel Fattah el-Sisi |
Portugal | Europe | Marcelo Rebelo de Sousa |
Haiti | North America | Jovenel Moise |
Uruguay | South America | Jose Mujica |
Liberia | Africa | Ellen Johnson Sirleaf |
Chile | South America | Michelle Bachelet |
Vietnam | Aisa | Tran Dai Quang |
用以下代码, 我们能够实现两个表依据country的inner join。
SELECT p1.country, p1.continent, prime_minister, president
FROM prime_ministers AS p1
INNER JOIN presidents AS p2
ON p1.country = p2.country;
请注意,首相和总统的别名分别为p1和p2,对于较长的表名,这样有助于简化代码。SELECT
语句用于从两个表中选择特定字段。在这种情况下,由于country
在这两个表中都存在,因此必须写入p1
和.
以避免SQL错误。接下来,我们在FROM
之后列出内部联接左侧的表,然后在INNER JOIN
之后列出右侧的表。最后,我们在两个表中指定要匹配的键。
连接两张表
在单个查询中组合多个联接的能力是SQL的一个强大功能,例如
SELECT *
FROM left_table
INNER JOIN right_table
ON left_table.id = right_table.id
INNER JOIN another_table
ON left_table.id = another_table.id;
INNER JOIN via USING
如果要联接的键字段在两个表中的名称相同,则可以使用USING
子句,而不是迄今为止看到的on
子句。因为id
在left_table和right_table中都是相同的名称,所以我们可以在这里使用USING
而不是ON
。需要注意的是,关键字字段周围需要使用括号。
那么在上述president和prime_minister的例子中,代码也可写为:
SELECT p1.country, p1.continent, prime_minister, president FROM presidents AS p1
INNER JOIN prime_ministers AS p2
USING (country);
得到结果为:
country | continent | prime_minister | president |
---|---|---|---|
Egypt | Africa | Sherif Ismail | Abdel Fattah el-Sisi |
Portugal | Europe | Antonio Costa | Marcelo Rebelo de Sousa |
Vietnam | Asia | Nguyen Xuan Phuc | Tran Dai Quang |
Haiti | North America | Jack Guy Lafontant | Jovenel Moise |
由于inner join在两个表中都包含条目,并且两个表都包含列出的country,因此如果选择这些列,则将表放置在联接中的顺序无关紧要。但是通常我们需要明确在左边和右边分别使用哪张表,以避免混淆。
Self Join 自连接
自联接用于将字段中的值与同一表中同一字段的其他值进行比较。
我们可以再次用prime_minister表对此进行探究。一个可能的问题是:如何两两匹配属于同一大洲的国家?
SELECT p1.country AS country1, p2.country AS country2, p1.continent
FROM prime_ministers AS p1
INNER JOIN prime_ministers AS p2
ON p1.continent = p2.continent
LIMIT 14;
country1 | country2 | continent |
---|---|---|
Egypt | Egypt | Africa |
Portugal | Spain | Europe |
Portugal | Norway | Europe |
Portugal | Portugal | Europe |
Vietnam | Oman | Asia |
Vietnam | Brunei | Asia |
Vietnam | India | Asia |
Vietnam | Vietnam | Asia |
Haiti | Haiti | North America |
India | Oman | Asia |
India | Brunei | Asia |
India | India | Asia |
India | Vietnam | Asia |
Australia | Australia | Oceania |
我们得到以上结果,它确实是每个国家与同一个大陆上其他国家的配对。但是,我们通常不想把国家和它自己列在一起。因此我们可以利用and
,将代码可以调整为:
SELECT p1.country AS country1, p2.country AS country2, p1.continent
FROM prime_ministers AS p1
INNER JOIN prime_ministers AS p2
ON p1.continent = p2.continent AND p1.country <> p2.country
LIMIT 13;
CASE WHEN, 和 THEN
CASE是一种在SQL中,以简化的方式执行多个if-then-else语句的方法。
我们引入一个新的表 states来举例, states表包含世界六大洲不同国家的numeric数据,其内容如下:
name | continent | indep_year |
---|---|---|
Australia | Oceania | 1901 |
Brunei | Asia | 1984 |
Chile | South America | 1810 |
Egypt | Africa | 1922 |
Haiti | North America | 1804 |
India | Asia | 1947 |
Liberia | Africa | 1847 |
Norway | Europe | 1905 |
Oman | Asia | 1951 |
Portugal | Europe | 1143 |
Spain | Europe | 1492 |
Uruguay | South America | 1828 |
Vietnam | Asia | 1945 |
假设我们想把独立年分成1900年之前,1900年到1930年之间,以及1930年之后:
SELECT name, continent, indep_year,
CASE WHEN indep_year < 1900 THEN 'before 1900'
WHEN indep_year <= 1930 THEN 'between 1900 and 1930'
ELSE 'after 1930' END
AS indep_year_group
FROM states
ORDER BY indep_year_group;
在第一个WHEN
之后,我们要检查indep_year
是否小于1900。接下来,我们希望indep_year_group
获取“between 1900 and 1930”的那些记录。最后,不符合这些条件的任何其他记录将被指定为indep_year_group
的值 ’ after 1930’。得到结果如下:
name | continent | indep_year | indep_year_group |
---|---|---|---|
Brunei | Asia | 1984 | after 1930 |
India | Asia | 1947 | after 1930 |
Oman | Asia | 1951 | after 1930 |
Vietnam | Asia | 1945 | after 1930 |
Liberia | Africa | 1847 | before 1900 |
Chile | South America | 1810 | before 1900 |
Haiti | North America | 1804 | before 1900 |
Portugal | Europe | 1143 | before 1900 |
Spain | Europe | 1492 | before 1900 |
Uruguay | South America | 1828 | before 1900 |
Norway | Europe | 1905 | between 1900 and 1930 |
Australia | Oceania | 1901 | between 1900 and 1930 |
Egypt | Africa | 1922 | between 1900 and 1930 |
此外, 我们还可以使用INTO
保存上一个查询的结果到一张新的表。
例如,有表populations
, 前6行如下:
pop_id | country_code | year | fertility_rate | life_expectancy | size |
---|---|---|---|---|---|
20 | ABW | 2010 | 1.704 | 74.9535 | 101597 |
19 | ABW | 2015 | 1.647 | 75.5736 | 103889 |
2 | AFG | 2010 | 5.746 | 58.9708 | 27962200 |
1 | AFG | 2015 | 4.653 | 60.7172 | 32526600 |
12 | AGO | 2010 | 6.416 | 50.6542 | 21220000 |
11 | AGO | 2015 | 5.996 | 52.6661 | 25022000 |
根据他们的人口2015年的size, 我们将其分为‘large’,‘small’, ‘medium’三个组,
SELECT country_code, size,
CASE WHEN size > 50000000 THEN 'large'
WHEN size > 1000000 THEN 'medium'
ELSE 'small' END
AS popsize_group
-- 1. Into table
into pop_plus
FROM populations
WHERE year = 2015;
-- 2. Select all columns of pop_plus
Select *
from pop_plus
结果如下:
country_code | size | popsize_group |
---|---|---|
ABW | 103889 | small |
AFG | 32526600 | medium |
AGO | 25022000 | medium |
ALB | 2889170 | medium |
AND | 70473 | small |
更多推荐
SQL新手入门 详细总结笔记(三)| Joining Data in PostgreSQL
发布评论