kaiyun官方注册
您所在的位置: 首页> 通信与网络> 业界动态> 什么是 PostgreSQL 横向子查询?

什么是 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 使用的就是嵌套循环算法。




电子技术图片.png

本站内容除特别声明的原创文章之外,转载内容只为传递更多信息,并不代表本网站赞同其观点。转载的所有的文章、图片、音/视频文件等资料的版权归版权所有权人所有。本站采用的非本站原创文章及图片等内容无法一一联系确认版权者。如涉及作品内容、版权和其它问题,请及时通过电子邮件或电话通知我们,以便迅速采取适当措施,避免给双方造成不必要的经济损失。联系电话:010-82306116;邮箱:aet@chinaaet.com。
Baidu
map