Postgresql unnest案例

在 PostgreSQL 中，可以使用 unnest 函数来展开数组，然后进行聚合操作。例如，如果你有一个数组字段，想要将数组展开为单独的行，并进行聚合统计，可以通过以下步骤来完成。

场景描述：

假设有一个 orders 表，其中每个订单包含一个 product_ids 数组，数组里存储了购买的商品的 ID。你想要统计每个商品被购买的次数。

表结构示例：

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    product_ids INT[]  -- 存储购买的商品ID数组
);

示例数据：

INSERT INTO orders (customer_id, product_ids) VALUES
(1, ARRAY[101, 102, 103]),
(2, ARRAY[102, 104]),
(3, ARRAY[101, 104, 105]);

使用 unnest 进行聚合：

SELECT product_id, COUNT(*) AS purchase_count
FROM (
    SELECT unnest(product_ids) AS product_id
    FROM orders
) AS unnest_products
GROUP BY product_id
ORDER BY purchase_count DESC;

解释：

1. unnest(product_ids)：将每个订单中的 product_ids 数组展开为多行，每个行包含数组中的一个商品 ID。
2. 外层的 SELECT：将展开后的商品 ID 进行聚合（使用 COUNT(*) 来统计每个商品 ID 的出现次数），并按购买次数（purchase_count）降序排序。

结果：

假设执行以上查询后，结果会是：

 product_id | purchase_count
------------+----------------
 101        | 2
 102        | 2
 104        | 2
 103        | 1
 105        | 1

其他注意事项：

• 如果你的表包含较大的数组字段，unnest 函数会扩展每个数组成多个行，因此在进行聚合时会占用更多的计算资源。在大数据量情况下，最好进行适当的索引优化。
• 你可以根据需要使用其他聚合函数，比如 SUM(), AVG(), MAX() 等，根据你的场景需求进行选择。

总结：

这个例子展示了如何使用 unnest 将数组字段展开成多行，并结合聚合函数进行统计操作。unnest 是 PostgreSQL 中处理数组数据的一个非常强大的工具，它能有效地将数组类型的字段转换为常规行，并进行进一步分析和处理。