什么是 PostgreSQL 横向子查询?

PostgreSQL 专栏收录该内容
56 篇文章 19 订阅

大家好,我是只谈技术不剪发的 Tony 老师。

一般来说,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.17..4439.35 rows=540 width=250)                                   |
  ->  Seq Scan on department d  (cost=0.00..15.40 rows=540 width=122)                            |
  ->  Limit  (cost=8.17..8.17 rows=1 width=132)                                                  |
        ->  Sort  (cost=8.17..8.17 rows=1 width=132)                                             |
              Sort Key: e.salary DESC                                                            |
              ->  Index Scan using idx_emp_dept on employee e  (cost=0.14..8.16 rows=1 width=132)|
                    Index Cond: (dept_id = d.dept_id)                                            |

Nested Loop Left Join 说明 PostgreSQL 使用的就是嵌套循环算法。

如果觉得文章有用,欢迎评论📝、点赞👍、推荐🎁

如果你想要学习更多 PostgreSQL 知识,可以参考这篇文章

  • 5
    点赞
  • 2
    评论
  • 12
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2022 CSDN 皮肤主题:酷酷鲨 设计师:CSDN官方博客 返回首页

打赏作者

不剪发的Tony老师

为 5 个 C 币而折腰。

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值