【PostgreSQL】解锁潜能：PostgreSQL高级SQL查询与数据操作

目录

子查询：构建查询中的查询

单行子查询

多行子查询与IN/ANY/SOME/ALL

相关子查询

联接操作：跨越表的查询

内联接（INNER JOIN）

外联接（LEFT/RIGHT/FULL JOIN）

自联接（SELF JOIN）

窗口函数：分析查询的利器

常用窗口函数

分区与排序

 常用函数与表达式：增强查询的灵活性

---

子查询：构建查询中的查询

子查询是嵌套在另一个查询中的查询，它增强了SQL的表达能力，使得数据检索更加灵活和强大。

单行子查询

• 概念与应用：作为条件使用，返回单个值与外部查询进行比较。

  /*此查询找出价格高于平均价格的产品。*/
  SELECT * FROM products 
  WHERE price > (SELECT AVG(price) FROM products);

  多行子查询与IN/ANY/SOME/ALL

• 多值比较：使用IN、ANY、SOME或ALL与子查询配合，实现与多行值的比较。

  /*此查询找出类别属于任何热门销售品类的产品。*/
  SELECT product_name FROM products 
  WHERE category IN (SELECT DISTINCT category FROM best_sellers);

  相关子查询

• 内外查询依赖：子查询依赖于外部查询的数据。

  /*此查询为每种产品显示其所在类别的平均价格。*/
  SELECT p1.product_name, 
         (SELECT AVG(price) FROM products p2 WHERE p2.category = p1.category) AS avg_category_price
  FROM products p1;

  联接操作：跨越表的查询

  联接是SQL中将来自两个或更多表的数据组合在一起的基本手段。

  内联接（INNER JOIN）

• 基础应用：仅返回两个表中匹配的行。

  /*此查询显示了每个客户的订单日期*/
  SELECT customers.name, orders.order_date 
  FROM customers 
  INNER JOIN orders ON customers.customer_id = orders.customer_id;

  外联接（LEFT/RIGHT/FULL JOIN）

• 完整视图：外联接可以返回左表、右表或两表的所有行，即使没有匹配项。

  /*此查询列出所有客户及其订单日期，即使某些客户没有订单。*/
  SELECT customers.name, orders.order_date 
  FROM customers 
  LEFT JOIN orders ON customers.customer_id = orders.customer_id;

  自联接（SELF JOIN）

• 表自身联接：用于比较表中行与行之间的关系。

  /*此查询找出每种产品在同一类别内的其他相关产品。*/
  SELECT p1.product_name AS product, p2.product_name AS related_product 
  FROM products p1 
  JOIN products p2 ON p1.category = p2.category AND p1.product_id <> p2.product_id;

  窗口函数：分析查询的利器

  窗口函数可以在一组相关行（窗口）上执行计算，无需进行分组操作，极大地增强了数据分析能力。

  常用窗口函数

• RANK(), DENSE_RANK(), ROW_NUMBER()：为结果集中的行分配顺序排名。

/*此查询按销量对产品进行排名。*/
SELECT product_name, 
       RANK() OVER (ORDER BY sales DESC) AS sales_rank
FROM (
  SELECT product_name, SUM(quantity) AS sales 
  FROM order_details 
  GROUP BY product_name
) AS sales_summary;

• LAG(), LEAD()：获取前一行或后一行的值，用于比较。

  /*此查询为每个订单显示上一个订单的日期。*/
  SELECT order_date, 
         LAG(order_date) OVER (ORDER BY order_date) AS previous_order_date
  FROM orders;

  分区与排序

• PARTITION BY与ORDER BY：控制窗口函数的计算范围和顺序。

  /*此查询计算每个客户订单的累计总金额。*/
  SELECT customer_id, order_date, 
         SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
  FROM orders;

   常用函数与表达式：增强查询的灵活性

  了解并应用PostgreSQL提供的丰富函数与表达式，可以进一步提升查询的灵活性和功能性。

• 字符串函数：LOWER(), UPPER(), SUBSTRING()等用于文本处理。
• 日期时间函数：CURRENT_DATE, EXTRACT(), INTERVAL等处理日期时间数据。
• 数学函数：ABS(), SQRT(), RANDOM()等用于数学运算。
• 条件表达式CASE WHEN：根据条件返回不同结果。