什麼是 PostgreSQL 橫向子查詢？

作者 | 不剪髮的 Tony 老師   責編 | 晉兆雨     

出品 | 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.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 使用的就是嵌套循環算法。

版權聲明：本文爲 CSDN 博主「不剪髮的 Tony 老師」的原創文章。

原文鏈接：https://blog.csdn.net/horses/article/details/118769805

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/-IunrEHa9j-8mNjdkki3eA

猜你喜歡