PostgreSQL11快速入门(七)-常用的数据查询

前面一个章节中 , 有提到如何使用基本的 SQL SELECT 语句来查询数据。

但是在实际的项目开发中，几乎不可能会有这么简单的查询 SQL 来写，所以很有必要学习下常用的数据查询语法。主要包括以下几种：

• 单条件查询
• 组合条件查询
• 分组查询
• 连接
• 子查询
• 聚合函数
• 窗口函数

1. 示例数据库表定义

首先需要定义数据库表来演示下面的查询，这里计划使用 2 张表以及模拟一部分数据。 业务场景为电力采集行业的电表设备以及电表采集的数据：

1.1 电表档案

CREATE TABLE meter (
    id SERIAL PRIMARY KEY,       -- 电表主键ID 
    meter_no CHAR(8) NOT NULL,   -- 电表编号,8位数字
    location VARCHAR(255),       -- 电表安装位置
    installation_date DATE       -- 安装日期
);

造一点数据：

INSERT INTO meter (id, meter_no, location, installation_date) VALUES
(1, '00000001', '3号楼101', '2019-01-01'),
(2, '00000002', '3号楼102', '2019-01-15'),
(3, '00000003', '3号楼103', '2019-02-01'),
(4, '00000004', '3号楼104', '2019-03-01'),
(5, '00000005', '3号楼105', '2019-04-01');

表中的数据：

1.2 电表采集的电量数据

CREATE TABLE meter_data (
    id SERIAL PRIMARY KEY,   -- 自增的主键 , 用于唯一标识每条记录，无业务含义
    meter_id INTEGER NOT NULL, -- 电表的ID。 meter表的主键
    timestamp TIMESTAMP NOT NULL, -- 采集电表数据的时间戳 
    value NUMERIC(10,2) NOT NULL -- 电表读数 , 最多 10 位数字,其中 2 位小数。
);

造一点数据：

INSERT INTO meter_data (id, meter_id, timestamp, value) VALUES
(10001, 1, '2019-05-01 00:00:00', 150.00),
(10003, 1, '2019-05-01 01:00:00', 153.00),
(10007, 2, '2019-05-01 00:00:00', 75.00),
(10015, 3, '2019-05-01 00:00:00', 200.00),
(10020, 6, '2019-05-01 00:00:00', 120.00);

表中的数据：

2. 查询语法分析

首先看下 select 语句的语法规则：

SELECT column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column
HAVING condition
ORDER BY column
LIMIT count;

上面的语法为：

• column1 , column2 等是要查询的列名,也可以使用 * 号表示所有列。
• table_name 是要查询的表名。
• WHERE 子句指定查询条件 , 只返回满足条件的行。
• GROUP BY 子句用于对结果集进行分组。
• HAVING 子句用于对分组后的结果进行过滤。
• ORDER BY 子句用于对结果集进行排序。
• LIMIT 子句用于限制返回的行数。

3. 常规查询

第一个小节，先从比较简单的又比较常见的查询语句开始。

• 查询 meter_data 表中所有的数据:

SELECT * FROM meter_data;

• 查询指定列的数据:

SELECT meter_id, timestamp, value FROM meter_data;

• 查询满足条件的数据,例如查询电表 1001 的数据:

SELECT * FROM meter_data WHERE meter_id = 1001;

• 使用比较运算符和逻辑运算符来查询电表 1001 在指定时间范围内的数据:

SELECT * FROM meter_data
WHERE meter_id = 1001 
   AND timestamp BETWEEN '2023-06-01 09:00:00' AND '2023-06-01 10:00:00';

• 查询读数大于 1000 的电表数据:

SELECT * FROM meter_data WHERE value > 1000;

• 将查询结果按时间排序:

SELECT * FROM meter_data ORDER BY timestamp;

• 将查询结果按电表 ID 分组,计算每个电表的平均读数:

SELECT meter_id, AVG(value) AS avg_value
FROM meter_data
GROUP BY meter_id;

• 查询平均读数大于 2000 的电表 ID:

SELECT meter_id, AVG(value) AS avg_value
FROM meter_data
GROUP BY meter_id
HAVING AVG(value) > 2000;

上面这些场景基本包含了大部分简单的 SQL 应用，复杂的 SQL 也就是在这些的基础上稍微改动。 有点类似于英语的长难句，简单句搞懂了，长难句进行分解的话也可以比较快的看得懂。

2. 连接查询

连接查询是将两个或多个表按照某个条件合并起来,形成一个新的结果集的查询方式。

换句话来说，就是查询的时候先确定一张主表作为最后的结果集，然后被关联的表选择性的连接到主表的结果集上。 在实际的业务开发过程中，关联查询是最常用，也是最需要熟练掌握的查询方式。

PG 支持多种类型的连接查询 , 包括内连接、外连接、交叉连接等。

2.1 内连接 INNER JOIN

内连接 ( INNER JOIN ) 是比较常用的一种连接类型 , 它根据连接条件从两个表中返回满足条件的记录。

内连接也可以称作等值连接，就是两张表都要满足条件的部分，才会作为最终的结果输出。 inner join 跟 join 是等价的。

语法:

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON condition;

例如 , 查询所有有电表读数的电表的表号、位置和读数数据:

SELECT m.meter_no, m.location, md.timestamp, md.value
FROM meter m
INNER JOIN meter_data md ON m.id = md.meter_id;

查询结果如下：

内连接只会返回在 meter 和 meter_data 表中都存在的记录。结果包括电表的表号、位置以及相应的读数数据。

没有读数数据的电表00000004和00000005不会出现在结果中 , 因为它们在 meter_data 表中没有对应的记录。

meter_data 表中 meter_id 为6的记录也查不出来 , 因为 meter 表中没有对应的电表信息。

2.2 左外连接 LEFT JOIN

左外连接是以左边的表作为主表，查询出来符合条件的内容。 最终查询出来的结果是左表全部符合条件的内容，右表符合条件的也将查询出来，但是可能出现右表的内容为空的现象。

语法:

-- 左外连接
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON condition;

例如使用 LEFT JOIN 查询来获取所有电表的表号、安装位置 , 以及它们的读数数据：

SELECT m.meter_no, m.location, md.timestamp, md.value
FROM meter m
LEFT JOIN meter_data md ON m.id = md.meter_id;

查询结果如下：

左外连接查询返回左表 ( meter ) 中的所有记录 , 以及右表(meter_data)中匹配的记录。对于左表中存在但右表中找不到匹配的记录,右表的字段将返回 NULL。

在结果中 , 电表00000004和00000005 虽然在 meter_data 表中没有对应的读数数据 , 但因为它们存在于 meter 表中,所以也会出现在查询结果里,只是对应的 timestamp 和 value 字段为 NULL。

可以看出左外连接以左表为主 , 保留左表的所有记录这个特点。

2.3 右外连接 RIGHT JOIN

右外连接跟左外连接是相反的过程。以右边边的表作为主表，查询出来符合条件的内容。 最终查询出来的结果是右表全部符合条件的内容，左表符合条件的也将查询出来，但是可能出现左表的内容为空的现象。

在实际项目开发过程中，右外连接我使用的比较少，左外连接使用的更多一点。

语法:

-- 右外连接
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON condition;

举例使用右外连接 来查询有读数数据的 电表信息 , 包括电表可能不存在于 meter 表中的情况，这种情况通常是脏数据：

SELECT m.meter_no, m.location, md.timestamp, md.value  
FROM meter m
RIGHT JOIN meter_data md ON m.id = md.meter_id;

查询结果如下：

meter_id = 6 的读数数据出现在查询结果里 , 但对应的 meter_no 和 location 字段为 NULL , 因为在 meter 表中没有 id = 6 的电表信息。

2.4 全外连接 FULL OUTER JOIN

全外连接查询返回左表和右表中的所有记录。如果其中一个表中的记录在另一个表中没有匹配 , 则缺失的字段将返回 NULL。

语法:

SELECT column1, column2, ...
FROM table1
FULL OUTER JOIN table2 ON condition;

使用全外连接查询两张表 :

SELECT m.meter_no, m.location, md.timestamp, md.value
FROM meter m
FULL OUTER JOIN meter_data md ON m.id = md.meter_id;

查询结果如下：

meter 表中 id 为 4 和 5 的行在 meter_data 表中没有匹配,但这些行仍然会返回,对应的 timestamp 和 value 列用 NULL 填充。

meter_data 表中 meter_id 为 6 的行在 meter 表中没有匹配,但这一行仍然会返回 , 对应的 meter_no 和 location 列用 NULL 填充。

总结下就是 全外连接返回两个表中的所有行 , 无论它们是否有匹配。

2.5 交叉连接 CROSS JOIN

交叉连接 ( CROSS JOIN ) 返回两个表的笛卡尔积 , 即每个表中的每一行与另一个表中的每一行组合。

语法:

SELECT column1, column2, ...
FROM table1
CROSS JOIN table2;

使用交叉连接查询两张表 :

SELECT m.meter_no, m.location, md.timestamp, md.value
FROM meter m
CROSS JOIN meter_data md;

查询结果(只列举一部分，共计 25条记录 )：

meter 表中的每一行与 meter_data 表中的每一行都进行了组合。结果集的行数等于两个表行数的乘积，上面的查询结果条数共计为 5 x 5 =25 条记录。

注意：交叉连接通常与其他条件一起使用 , 如果有大表的话，尽量避免生成过大的结果集，否则有极大的性能问题。

3. 子查询

子查询是指嵌套在另一个 SQL 语句中的 SELECT 语句 , 它可以出现在 SELECT、FROM、WHERE 等子句中,并作为外部查询的数据源或过滤条件。

子查询的分类有好多种，多为入门系列的课程详细就不做详细的展开，举几个常用的子查询方式。

3.1 WHERE 条件中的子查询

比如要 查询读数值大于平均读数值的电表的表号、安装位置和读数数据。

SELECT m.meter_no, m.location, md.timestamp, md.value
FROM meter m
JOIN meter_data md ON m.id = md.meter_id
WHERE md.value > (
    SELECT AVG(value) FROM meter_data  --  这里就是子查询语句
);

这个应该比较好理解，子查询计算了电表读数表中所有读数的平均数值，然后外层查询去使用这个平均值作为条件 , 筛选出读数值大于平均值的电表读数记录。

3.2 子查询作为临时表

查询每个电表最新的读数数据，写 SQL：

SELECT m.meter_no, m.location, md.timestamp, md.value
FROM meter m
JOIN (
    SELECT meter_id, MAX(timestamp) AS latest_timestamp   --  查询每个表最新的一条读数数据
    FROM meter_data
    GROUP BY meter_id
) latest_data ON m.id = latest_data.meter_id
JOIN meter_data md ON latest_data.meter_id = md.meter_id AND latest_data.latest_timestamp = md.timestamp;

子查询的结果作为一个临时表去跟与 meter 表和 meter_data 表进行关联，这样我们可以获取每个电表最新的读数数据。

3.3 SELECT 子句中的子查询

比如查询每个电表的读数数据,并计算每个读数与该电表平均读数的差值。

SELECT 
    m.meter_no,
    m.location,
    md.timestamp,
    md.value,
    (md.value - (SELECT AVG(value) FROM meter_data WHERE meter_id = m.id)) AS diff_from_avg   -- 这个是子查询
FROM meter m
JOIN meter_data md ON m.id = md.meter_id;

这个子查询计算了每个电表的平均读数值，然后用在在 SELECT 子句中给每一行计算读数值与该电表平均读数的差值。

注意，这种子查询的方式查询效率不会太高，平时尽量少用这种写法。

4. 聚合函数

聚合函数对一组值执行计算,返回单个结果值。PG 提供了多种内置的聚合函数 , 如 COUNT、SUM、AVG、MAX、MIN 等，看到这些函数应该大概就知道是什么意思，这些函数在 Excel 中也是类似的用法。

需要注意的是，聚合函数通常与 GROUP BY 子句一起使用 , 对分组后的数据进行聚合计算，单独使用通常没有很大意义。

基本的语法:

SELECT aggregate_function(column)
FROM table
WHERE condition;

4.1 sum 函数

使用 sum 函数 计算每个电表的读数总和

SELECT meter_id, SUM(value) AS total_value
FROM meter_data
GROUP BY meter_id;

4.2 avg 函数

使用 AVG 函数 计算每个电表的平均读数

SELECT meter_id, AVG(value) AS avg_value
FROM meter_data
GROUP BY meter_id;

4.3 max min 函数

使用 MAX 和 MIN 函数 查找每个电表的最大和最小读数。

SELECT 
    meter_id,
    MAX(value) AS max_value,
    MIN(value) AS min_value
FROM meter_data
GROUP BY meter_id;

4.4 组合使用函数

组合使用下这几个函数，查找每个电表的读数记录数量、读数总和、平均读数、最大读数和最小读数。

  SELECT
      meter_id,
      COUNT(*) AS reading_count,
      SUM(value) AS total_value,
      AVG(value) AS avg_value,
      MAX(value) AS max_value,
      MIN(value) AS min_value
  FROM meter_data
  GROUP BY meter_id;

5. 窗口函数

窗口函数是在一组相关的行上执行计算,并返回每一行的结果。它类似于聚合函数,但不会将结果集减少为单个行。

语法:

SELECT column1, column2, ...,
       window_function() OVER (
         [PARTITION BY partition_expression]
         [ORDER BY sort_expression [ASC | DESC] [NULLS {FIRST | LAST}]]
         [frame_clause]
       )
FROM table;

举几个常见的例子：

5.1 ROW_NUMBER 函数

为每个电表的读数记录添加一个按时间戳排序的序号。

SELECT
    meter_id,
    timestamp,
    value,
    ROW_NUMBER() OVER (PARTITION BY meter_id ORDER BY timestamp) AS row_num
FROM meter_data;

查询结果如下：

可以看到窗口函数为每个电的记录生成一个唯一的序号。其中 PARTITION BY meter_id 指定按电表 ID 进行分组 , ORDER BY timestamp 指定按时间戳排序。

5.2 RANK 函数

RANK 函数为给记录按读数值大小生成排名。

举个例子，查询每个电表的读数记录按读数值大小排名,并处理并列排名。

SELECT
    meter_id,
    timestamp,
    value,
    RANK() OVER (PARTITION BY meter_id ORDER BY value DESC) AS rank
FROM meter_data;

查询结果如下：

RANK 函数为每个电表的记录按读数值大小生成排名。其中 PARTITION BY meter_id 指定按电表 ID 进行分组 , ORDER BY value DESC 指定按读数值降序排列。并列的读数值会获得相同的排名,且不会影响后续排名的序号。

5.3 LAG 函数

LAG 函数主要用于计算记录跟记录之间的差值。

SELECT
    meter_id,
    timestamp,
    value,
    value - LAG(value, 1, 0) OVER (PARTITION BY meter_id ORDER BY timestamp) AS diff
FROM meter_data;

LAG (value, 1, 0) 函数返回每个电表分组中当前行的前一行的 value 值 , 如果前一行不存在 , 则返回默认值 0。

PARTITION BY meter_id 指定按电表ID分组 , ORDER BY timestamp 指定按时间戳排序。

查询结果中的 diff 列表示当前读数与前一次读数的差值。

查询结果如下：

通过以上几个例子可以发现，窗口函数有点像 group by 一样，将窗口进行分组，在有些场景下，可以实现多条 SQL 嵌套出来实现的效果。

参考链接

• PG 文档 - 查询: https://www.postgresql.org/docs/11/queries.html

• PG 文档 - 连接查询: https://www.postgresql.org/docs/11/queries-table-expressions.html

• PG 文档 - 聚合函数: https://www.postgresql.org/docs/11/functions-aggregate.html

• PG 文档 - 窗口函数: https://www.postgresql.org/docs/11/tutorial-window.html

• 连接查询图片来源 : https://www.w3schools.com/sql/sql_join.asp

• 维基 - 连接查询：https://zh.wikipedia.org/zh/%E8%BF%9E%E6%8E%A5