PostgreSQL-常用函数和操作符-2

盖盖衍上_染染熊

已于 2024-05-19 20:38:34 修改

阅读量758

点赞数 10

分类专栏：中间件文章标签： postgresql 数据库

于 2024-05-13 10:53:34 首次发布

本文链接：https://blog.csdn.net/m0_72560900/article/details/138792989

版权

中间件专栏收录该内容

13 篇文章 0 订阅

订阅专栏

0. WITH

在 PL/pgSQL 中，WITH 子句通常用于创建一个临时结果集，这个结果集在执行 SQL 查询时使用。这个临时结果集通常被称为一个公共表表达式（Common Table Expression, CTE）。CTE 允许您在查询中引用它，就像引用一个表一样。

基本语法：

WITH [RECURSIVE] cte_name AS (
  [query]
)
-- 使用
SELECT * FROM cte_name;

cte_name：您为 CTE 指定的名称。
[RECURSIVE]：可选的，用于标记递归 CTE。
[query]：定义 CTE 的查询。

案例：

下面是一个使用 WITH 语句的简单具体案例。假设我们有一个名为orders的表，它记录了订单的信息，包括订单ID、客户ID、订单日期和订单金额。我们想要找出每个客户的总订单金额，并找出订单金额超过1000的客户。
不使用 WITH 语句的查询可能会像这样：

SELECT customer_id, SUM(order_amount) AS total_amount  
FROM orders  
GROUP BY customer_id  
HAVING SUM(order_amount) > 1000;

要求：

如果我们想要进一步筛选这些客户，比如只选择那些在某个日期之后下过订单的客户，我们可以使用 WITH 语句来使查询更清晰：

WITH customer_totals AS (  
    SELECT customer_id, SUM(order_amount) AS total_amount  
    FROM orders  
    GROUP BY customer_id  
)  
SELECT ct.customer_id, ct.total_amount  
FROM customer_totals ct  
JOIN orders o ON ct.customer_id = o.customer_id  
WHERE o.order_date > '2023-01-01' -- 假设我们只关心2023年1月1日之后的订单  
AND ct.total_amount > 1000; -- 订单金额超过1000

解析：

在这个例子中，我们首先使用 WITH 语句创建了一个名为customer_totals的CTE，它包含了每个客户的总订单金额。然后，在主查询中，我们从这个CTE中选择数据，并与orders表进行连接，以找出在指定日期之后下过订单且总订单金额超过1000的客户。
使用 WITH 语句可以使查询更加模块化，并提高可读性，特别是当查询变得复杂时。

1. WITH RECURSIVE - 递归结果集

具体案例：with recursive

问题：
我们有一个简单的员工表employees，其中包含了员工的ID、姓名、职位和经理ID（表示上级经理的ID）。
我们想要构建一个CTE来找到某个员工的所有上级经理，直到CEO（假设CEO没有上级经理）。
实现：
表的结构可能如下：

employees table:
+----+-------+---------+----------+
| id | name  | title   | manager_id |
+----+-------+---------+----------+
| 1  | Alice | CEO     | NULL     |
| 2  | Bob   | Manager | 1        |
| 3  | Carol | Staff   | 2        |
| 4  | Dave  | Staff   | 2        |
| 5  | Eve   | Manager | 1        |
| 6  | Frank | Staff   | 5        |
+----+-------+---------+----------+

我们想要找到员工Carol的所有上级经理。我们可以使用WITH RECURSIVE语句和UNION来构建一个CTE，如下所示：

-- 创建查询过程
WITH RECURSIVE managers_chain AS (
    -- 非递归部分：找到Carol的直接上级
    SELECT manager_id
    FROM employees
    WHERE id = 3 -- 假设Carol的ID是3

    UNION

    -- 递归部分：找到上级经理的上级，直到CEO
    SELECT e.manager_id
    FROM employees e
    INNER JOIN managers_chain mc ON e.id = mc.manager_id
    WHERE e.manager_id IS NOT NULL
)

-- 查询
SELECT * FROM managers_chain;

解析：
这个CTE的工作原理如下：
非递归部分（种子查询）选择了Carol的直接上级的ID。
递归部分通过UNION与非递归部分合并，它会反复执行，每次都使用前一次查询结果中的manager_id来找到下一级的上级经理，直到达到CEO（即manager_id为NULL）。
最终，CTE的结果将是一个包含Carol所有上级经理ID的列表。当我们执行SELECT * FROM managers_chain;时，我们将得到以下结果：

manager_id
-----------
2
1

这表示Carol的直接上级是Bob（ID为2），Bob的上级是Alice（ID为1），即CEO。

2. UNION

UNION 是 SQL（结构化查询语言）中的一个操作符，用于合并两个或多个 SELECT 语句的结果集。这些 SELECT 语句必须选择相同数量的列，并且这些列的数据类型也必须相似或兼容。UNION 会自动去除重复的行，而如果你想要包含重复的行，可以使用 UNION ALL。

基本语法：

SELECT column_name(s) FROM table1  
UNION  
SELECT column_name(s) FROM table2;

案例：
假设我们有两个表，employees_ny 和 employees_sf，分别存储纽约和旧金山的员工信息。这两个表的结构是相同的，都有 id、name 和 salary 三个字段。

表结构

employees_ny 表：

id	name	salary
1	Alice	5000
2	Bob	6000
3	Carol	5500

employees_sf 表：

id	name	salary
4	Dave	7000
5	Eve	5000
6	Frank	6500

问题：
如果我们想要查询纽约和旧金山所有工资超过 5000 的员工，并合并结果，可以使用 UNION：

SELECT name, salary FROM employees_ny WHERE salary >= 5000  
UNION  
SELECT name, salary FROM employees_sf WHERE salary >= 5000;

注：
结果集可能如下（注意结果中的 “Alice” 和 “Eve” 工资都是 5000，但由于 UNION 会去除重复行，所以 “Alice” 只会出现一次）：

结果：

name	salary
Bob	6000
Carol	5500
Dave	7000
Eve	5000
Frank	6500

如果你想要保留所有行，包括重复的行，可以使用 UNION ALL：

SELECT name, salary FROM employees_ny WHERE salary >= 5000  
UNION ALL  
SELECT name, salary FROM employees_sf WHERE salary >= 5000;

这样，“Alice” 将会在两次结果中都出现，因为 UNION ALL 不会去除重复的行。

3. COALESCE函数

COALESCE函数可以接受多个参数，返回第一个非NULL的参数值。如果你的列可能返回NULL，你可以这样使用COALESCE来提供一个默认值。

语法：

COALESCE(value1, value2, ..., valueN)

案例：

假设你有一个员工表employees，其中包含salary（薪水）和bonus（奖金）两列，你想计算每位员工的总报酬（total_compensation），但如果某员工没有奖金，你希望其总报酬等于薪水加上默认奖金500元。

DO $$
DECLARE
   emp_salary numeric;
   emp_bonus numeric;
   total_compensation numeric;
BEGIN
   -- 假设这里是从employees表中获取特定员工的薪水和奖金
   emp_salary := (SELECT salary FROM employees WHERE employee_id = some_id);
   emp_bonus := (SELECT bonus FROM employees WHERE employee_id = some_id);

   -- 使用COALESCE为NULL的奖金提供默认值500
   total_compensation := emp_salary + COALESCE(emp_bonus, 500);

   RAISE NOTICE 'Total compensation for the employee is: %', total_compensation;
END $$;

在这个例子中，如果emp_bonus为NULL，那么COALESCE(emp_bonus, 500)会返回500，因此total_compensation将会是emp_salary加上500元。如果emp_bonus非NULL，则直接加上其真实值。

4. NULLIF函数

NULLIF函数在SQL中用于比较两个表达式，如果这两个表达式的值相等，则NULLIF函数返回NULL，否则返回第一个表达式的值。这个函数常用于避免在计算中出现除以零错误，或是在比较操作中排除掉那些值相等的情况。

语法：

NULLIF(expression1, expression2)

注：expression1 和 expression2 是你想要比较的两个值或表达式。
用途：

避免除零错误: 当你打算执行除法运算，而除数可能是0时，可以使用NULLIF确保除数不为0，避免除零错误。
数据清洗: 在比较数据时，如果两个字段值应该不同但实际上相等（可能是因为都是NULL或相同的错误值），使用NULLIF可以让这些情况在计算中被识别并适当处理。

案例1：

假设有一个销售数据表sales，包含order_amount（订单金额）和discount_amount（折扣金额）两列，现在你想计算每个订单的实际收入（即订单金额减去折扣金额后的值），但需注意，如果discount_amount是NULL或0，应当视为没有折扣，避免在计算中出现问题。

SELECT 
    order_id, 
    order_amount, 
    discount_amount, 
    order_amount - COALESCE(NULLIF(discount_amount, 0), 0) AS actual_revenue
FROM sales;

解析：

NULLIF(discount_amount, 0)首先比较discount_amount是否等于0，如果是，则返回NULL。
之后，COALESCE(NULLIF(discount_amount, 0), 0)会将这个可能的NULL转换为0，这样在减法运算中就不会因为NULL值而出错
确保了当没有折扣（即discount_amount为0或NULL）时，实际收入等于订单金额。

案例2：

假设有一个员工表employees，包含previous_bonus（上一年度奖金）和current_bonus（本年度奖金）两列，我们想计算奖金增长的百分比，但需要处理previous_bonus为0的情况，因为在这种情况下直接计算增长率没有意义（会引发除以零错误）。

SELECT 
    employee_id, 
    current_bonus,
    previous_bonus,
    CASE 
        WHEN previous_bonus = 0 THEN NULL
        ELSE (current_bonus - previous_bonus) / NULLIF(previous_bonus, 0) * 100
    END AS bonus_growth_percentage
FROM employees;

解析：

该案例：通过使用NULLIF(previous_bonus, 0)，我们可以确保在previous_bonus为0的情况下，整个除法操作的结果为NULL，而不是产生错误。

. 持续更新中…

盖盖衍上_染染熊

关注

10
点赞
踩
7

收藏

觉得还不错? 一键收藏
1
评论
PostgreSQL-常用函数和操作符-2

UNION 会自动去除重复的行，而如果你想要包含重复的行，可以使用 UNION ALL。假设我们有一个名为orders的表，它记录了订单的信息，包括订单ID、客户ID、订单日期和订单金额。假设我们有两个表，employees_ny 和 employees_sf，分别存储纽约和旧金山的员工信息。我们有一个简单的员工表employees，其中包含了员工的ID、姓名、职位和经理ID（表示上级经理的ID）。这表示Carol的直接上级是Bob（ID为2），Bob的上级是Alice（ID为1），即CEO。
复制链接

扫一扫