【PostgreSQL】PostgreSQL SQL探索之旅：查询基础与进阶分析

目录

PostgreSQL SELECT查询的艺术

基础查询

别名与计算列

LIMIT与OFFSET

数据筛选与排序：精准获取所需信息

WHERE子句的妙用

ORDER BY的魔力

聚合函数与分组：数据汇总与分析

聚合函数的应用

分组函数与复杂查询

---

PostgreSQL SELECT查询的艺术

PostgreSQL的SELECT语句是数据检索的核心，下面将从基础出发，逐步深入到更复杂的查询技巧。

基础查询

• 简单查询：展示如何从单一表中选择指定列。

SELECT product_name, price FROM products;

• 通配符*的使用：选取所有列。

  SELECT * FROM customers;

  别名与计算列

• 列别名：为输出列指定更易读的名称。

SELECT product_name AS "Product Name", price * 1.1 AS "Price with Tax" FROM products;

• 计算列：直接在查询中进行算术运算。

  SELECT id, (price - cost) AS profit_margin FROM inventory;

  LIMIT与OFFSET

• 分页查询：使用LIMIT限制返回结果的数量，OFFSET指定起始位置。

  SELECT * FROM orders LIMIT 10 OFFSET 20;  -- 返回第21到第30条记录

  数据筛选与排序：精准获取所需信息

  掌握WHERE子句和ORDER BY子句，是实现精确数据检索和有序结果呈现的关键。

  WHERE子句的妙用

基本条件筛选：基于等值、不等值、比较运算符筛选数据。

SELECT * FROM products WHERE price > 50 AND stock > 0;

IN、NOT IN与BETWEEN：处理集合匹配和范围筛选。

SELECT product_name FROM products WHERE category IN ('Electronics', 'Books');
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

LIKE与正则表达式：进行模糊匹配和复杂模式查找。

• SELECT product_name FROM products WHERE product_name LIKE '%phone%';
  SELECT * FROM users WHERE email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$';

ORDER BY的魔力

• 升序与降序排序：基于单列或多列排序结果。

SELECT product_name, price FROM products ORDER BY price DESC;
SELECT * FROM orders ORDER BY order_date, customer_id;

• NULL值处理：利用NULLS FIRST或NULLS LAST控制NULL值的排序位置。

  SELECT * FROM inventory ORDER BY last_stocked DESC NULLS LAST;

  聚合函数与分组：数据汇总与分析

     聚合函数是数据分析的强有力工具，结合GROUP BY可以实现数据的分组统计。

  聚合函数的应用

• COUNT、SUM、AVG、MAX、MIN：基础聚合函数的应用场景。

SELECT COUNT(*) FROM orders;
SELECT AVG(price), SUM(quantity) FROM order_details;

• GROUP BY与HAVING：分组后进行聚合计算，并筛选组。

  SELECT category, COUNT(*) as product_count FROM products GROUP BY category;
  SELECT customer_id, SUM(total_amount) AS total_spent 
  FROM orders GROUP BY customer_id HAVING SUM(total_amount) > 1000;

  分组函数与复杂查询

• 窗口函数：在分组基础上提供更精细的数据分析，如RANK()、DENSE_RANK()、ROW_NUMBER()。

SELECT customer_id, order_date, 
       RANK() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as rank
FROM orders;

• WITH语句与子查询：利用临时结果集进行复杂查询。

  WITH monthly_sales AS (
      SELECT EXTRACT(YEAR FROM order_date) AS year, 
             EXTRACT(MONTH FROM order_date) AS month, 
             SUM(total_amount) AS sales
      FROM orders
      GROUP BY year, month
  )
  SELECT * FROM monthly_sales WHERE sales > 5000;