8

psql窗口函数 ​​ROW_NUMBER的应用

 8 months ago
source link: https://blog.51cto.com/u_14480405/9195453
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

我可以通过一个简单的例子来说明使用窗口函数 ROW_NUMBER() 结合 LEFT JOIN 来只映射（mapping）第一个匹配项的用法。

假设我们有两个表：orders 和 customers。我们想要连接这两个表，但只想要每个订单对应的第一个客户（如果有多个客户与同一订单相关联的话）。

使用窗口函数的情况：

  1. 表结构示例：
  • orders 表:

order_id

product

Apple

Banana

Cherry

  • customers 表:

customer_id

order_id

Alice

Carol

  1. 目标：
    对于每个订单，我们只想连接第一个客户。例如，对于订单1，虽然有Alice和Bob两个客户，但我们只想选择Alice（基于某种排序标准，例如customer_id）。
  2. SQL查询：
    我们首先使用ROW_NUMBER()窗口函数为customers表中的每一行生成一个序号，按order_id分区，按customer_id排序。然后，我们将此结果与orders表进行LEFT JOIN，但只选择每个订单的第一个客户（即序号为1的行）。
WITH RankedCustomers AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY customer_id) AS rn
    FROM customers
)
SELECT o.order_id, o.product, rc.name
FROM orders o
LEFT JOIN RankedCustomers rc ON o.order_id = rc.order_id AND rc.rn = 1;
  1. 结果：
    这个查询会返回每个订单和对应的第一个客户的名字。如果某个订单没有对应的客户，则该订单的客户名字为NULL。

order_id

product

Apple

Alice

Banana

Carol

Cherry

这个方法主要用于在进行LEFT JOIN时控制每个分组只映射一个匹配项，非常适用于处理一对多关系时只需获取一条记录的场景。

如果你不使用窗口函数，而是直接进行 LEFT JOIN，结果将会不同，尤其是在处理一对多关系时。在你的例子中，如果直接使用 LEFT JOIN 连接 orders 表和 customers 表，结果将包括每个订单与每个相应客户的所有匹配项。

不使用窗口函数的情况：

假设你执行以下查询：

SELECT o.order_id, o.product, c.name
FROM orders o
LEFT JOIN customers c ON o.order_id = c.order_id;

这将产生以下结果：

order_id

product

Apple

Alice

Apple

Banana

Carol

Cherry

  • 对于 order_id 1，有两个匹配的客户：Alice 和 Bob。因此，这个订单会出现两次，每次与不同的客户。
  • 对于 order_id 2，只有一个匹配的客户：Carol。
  • 对于 order_id 3，没有匹配的客户，所以客户名为 NULL。

结果对比：

  • 使用窗口函数：你只得到每个订单的第一个客户，总共3条记录。
  • 不使用窗口函数：你得到所有匹配的订单和客户组合，总共4条记录。

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK