什么是 PostgreSQL 横向子查询?
2021-07-25
来源:CSDN
一般来说,SQL 子查询只能引用外查询中的字段,而不能使用同一层级中其他表中的字段。例如:
-- 错误示例
SELECT d.dept_name,
t.avg_salary
FROM department d
JOIN (SELECT avg(e.salary) AS avg_salary
FROM employee e
WHERE e.dept_id = d.dept_id) t;
SQL 错误 [42601]: ERROR: syntax error at end of input
位置:183
由于 JOIN 子句中的查询语句 t 引用了左侧 department 表中的字段,因此产生了语法错误。
为了解决以上问题,我们可以使用PostgreSQL提供的横向子查询(LATERAL subquery)。不过在介绍 LATERAL 关键字之前,我们先来回顾一下 SELECT 和 FROM 子句的含义。例如:
SELECT dept_id, dept_name
FROM department;
简单来说,我们可以将以上查询看作一个循环处理语句。使用伪代码实现的以上 SQL 语句如下:
for dept_id, dept_name in department
loop
print dept_id, dept_name
end loop
对于 department 中的每一条记录,都执行 SELECT 语句指定的操作,以上示例简单的输出了每行记录。
SELECT 就像一个循环语句,而 LATERAL 就像是一个嵌套循环语句,对于左侧表中的每行记录执行一次子查询操作。例如,通过增加 LATERAL 关键字,我们可以修改第一个示例:
SELECT d.dept_name,
t.avg_salary
FROM department d
CROSS JOIN LATERAL
(SELECT avg(e.salary) AS avg_salary
FROM employee e
WHERE e.dept_id = d.dept_id) t;
dept_name |avg_salary |
-----------+----------------------+
行政管理部 | 26666.666666666667|
人力资源部 |13166.6666666666666667|
财务部 | 9000.0000000000000000|
研发部 | 7577.7777777777777778|
销售部 | 5012.5000000000000000|
保卫部 | |
CROSS JOIN LATERAL 右侧的查询可以引用左侧表中的字段,以上语句为 JOIN 左侧的每个部门返回了月薪总和。
LATERAL 可以帮助我们实现一些有用的分析功能,例如以下查询返回了每个部门月薪最高的 3 名员工:
SELECT d.dept_name, t.emp_name, t.salary
FROM department d
LEFT JOIN LATERAL
(SELECT emp_name, salary
FROM employee e
WHERE e.dept_id = d.dept_id
ORDER BY salary DESC
LIMIT 3) t
ON TRUE;
dept_name |emp_name|salary |
-----------+--------+--------+
行政管理部 |刘备 |30000.00|
行政管理部 |关羽 |26000.00|
行政管理部 |张飞 |24000.00|
人力资源部 |诸葛亮 |24000.00|
人力资源部 |黄忠 | 8000.00|
人力资源部 |魏延 | 7500.00|
财务部 |孙尚香 |12000.00|
财务部 |孙丫鬟 | 6000.00|
研发部 |赵云 |15000.00|
研发部 |周仓 | 8000.00|
研发部 |关兴 | 7000.00|
销售部 |法正 |10000.00|
销售部 |简雍 | 4800.00|
销售部 |孙乾 | 4700.00|
保卫部 | | |
对于 department 中的每个部门,子查询 t 最多返回 3 个员工信息。我们使用了 LEFT JOIN LATERAL,从而保证了“保卫部”也会返回一条数据。
同样使用伪代码表示以上查询语句:
for d in department
loop
for e in employee order by salary desc
loop
cnt++
if cnt <= 3
then
return e
else
goto next d
end
end loop
end loop
通过 EXPLIAN 命令查看以上语句的执行计划:
EXPLAIN
SELECT d.dept_name, t.emp_name, t.salary
FROM department d
LEFT JOIN LATERAL
(SELECT emp_name, salary
FROM employee e
WHERE e.dept_id = d.dept_id
ORDER BY salary DESC
LIMIT 3) t
ON TRUE;
QUERY PLAN |
-------------------------------------------------------------------------------------------------+
Nested Loop Left Join (cost=8.174439.35 rows=540 width=250) |
-> Seq Scan on department d (cost=0.0015.40 rows=540 width=122) |
-> Limit (cost=8.178.17 rows=1 width=132) |
-> Sort (cost=8.178.17 rows=1 width=132) |
Sort Key: e.salary DESC |
-> Index Scan using idx_emp_dept on employee e (cost=0.148.16 rows=1 width=132)|
Index Cond: (dept_id = d.dept_id) |
Nested Loop Left Join 说明 PostgreSQL 使用的就是嵌套循环算法。