PostgreSQL

Appearance

🧩 PostgreSQL 数组构造函数完全指南：像搭乐高一样玩转数据 ​

数组就像**魔法收纳盒**！

想象一下：一个变量就能存储多个值，像抽屉一样整齐收纳数据。数组构造函数就是打造这种"魔法收纳盒"的神奇工具！

什么是数组构造函数？ ​

数组构造函数是 PostgreSQL 中创建数组的快捷工具，让你在 SQL 语句中直接创建和操作数组，无需预先定义。就像即用即拼的乐高积木，随时组合出你需要的数据结构。

基础语法结构 ​

sql
ARRAY[元素1, 元素2, 元素3...]

🛠️ 基础使用技巧 ​

一维数组创建 ​

sql
-- 创建包含数字的数组
SELECT ARRAY[1, 2, 3+4] AS result; 

-- 输出: {1,2,7}

自动类型推导

PostgreSQL 会自动推导数组元素的共同类型：

sql
SELECT ARRAY[1, 2.5, 3]::float[]; -- 显式转换为浮点数组 --
-- 输出: {1.0,2.5,3.0}

🧱 多维数组构建 ​

sql
-- 简化写法 (推荐)
SELECT ARRAY[[1,2],[3,4]]; 

-- 输出: {{1,2},{3,4}}

重要规则

多维数组必须是矩形结构：

sql
SELECT ARRAY[[1,2],[3]]; 
-- ERROR: 多维数组必须具有匹配维度的数组元素

🚫 创建空数组 ​

sql
SELECT ARRAY[]::integer[]; -- 空整数数组 --
-- 输出: {}

🔍 从查询结果构建数组 ​

子查询转数组 ​

sql
SELECT ARRAY(
    SELECT oid 
    FROM pg_proc 
    WHERE proname LIKE 'bytea%'
) AS bytea_functions;

子查询限制

  1. 子查询必须返回单列
  2. 所有结果维度必须相同

💼 三大实际应用场景 ​

场景1：电商产品标签管理系统 ​

业务背景 ​

电商平台需要为商品存储多个动态标签，传统方案需要创建关联表，导致复杂查询和性能问题。

解决方案 ​

sql
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    tags TEXT[] -- 标签数组 --
);

INSERT INTO products (name, tags)
VALUES 
    ('智能手机', ARRAY['电子产品','旗舰机型','5G']), 
    ('咖啡机', ARRAY['厨房电器','家用','自动研磨']);

-- 查询包含特定标签的产品
SELECT name 
FROM products 
WHERE '厨房电器' = ANY(tags);

处理结果 ​

   name   |           tags            
----------+--------------------------
 智能手机 | {电子产品,旗舰机型,5G}
 咖啡机   | {厨房电器,家用,自动研磨}

方案优势 ​

✅ 减少关联表数量
✅ 简化标签查询逻辑
✅ 避免多表JOIN性能开销

场景2：销售数据多维分析 ​

业务背景 ​

销售部门需要按地区-季度维度统计销售额，传统透视表方案难以进行复杂计算。

解决方案 ​

sql
CREATE TABLE regional_sales (
    region TEXT PRIMARY KEY,
    q_sales INTEGER[] -- 季度销售数组 --
);

INSERT INTO regional_sales VALUES
    ('North', ARRAY[12000,15000,18000,16000]), 
    ('South', ARRAY[9000,11000,13000,14000]);

-- 计算每区域全年平均季度销售额
SELECT 
    region,
    ROUND(AVG(quarter_sales)) AS avg_sales
FROM (
    SELECT region, UNNEST(q_sales) AS quarter_sales 
    FROM regional_sales
) expanded
GROUP BY region;

处理结果 ​

 region | avg_sales 
--------+-----------
 North  |     15250
 South  |     11750

技术价值 ​

⚡️ 将季度数据压缩到单列
⚡️ 使用UNNEST轻松展开分析
⚡️ 简化跨季度计算逻辑

场景3：用户兴趣画像分析 ​

业务背景 ​

社交平台需要分析用户兴趣分布，传统方案需要多次JOIN查询，响应速度慢。

解决方案 ​

sql
-- 创建用户兴趣表
CREATE TABLE user_interests (
    user_id INT PRIMARY KEY,
    interests TEXT[] -- 兴趣标签数组 --
);

INSERT INTO user_interests VALUES
    (101, ARRAY['旅游','摄影','美食']),
    (102, ARRAY['编程','游戏','科技']),
    (103, ARRAY['美食','电影','音乐']);

-- 分析兴趣关联度（查找有共同兴趣的用户）
SELECT 
    a.user_id AS user1,
    b.user_id AS user2,
    ARRAY(
        SELECT UNNEST(a.interests) 
        INTERSECT 
        SELECT UNNEST(b.interests) 
    ) AS common_interests
FROM user_interests a
JOIN user_interests b ON a.user_id < b.user_id;

处理结果 ​

 user1 | user2 | common_interests 
-------+-------+------------------
   101 |   102 | {}
   101 |   103 | {美食}
   102 |   103 | {}

业务价值 ​

🔍 快速发现用户兴趣关联
💡 支持个性化推荐系统
🚀 大幅减少JOIN操作成本

⚡ 性能优化技巧 ​

sql
-- 创建固定大小数组
SELECT ARRAY(
    SELECT 0 
    FROM generate_series(1,1000) 
) AS preallocated_array;
sql
DO $$
DECLARE
    arr INT[] = '{}'; -- 初始空数组
BEGIN
    FOR i IN 1..1000 LOOP
        arr := arr || i; -- 每次迭代扩展数组
    END LOOP;
END $$;

最佳实践建议

  1. 大型数据集使用ARRAY(SELECT...)代替迭代扩展
  2. 多维数组保持矩形结构
  3. 查询使用ANY()运算符
  4. 避免在频繁更新的列使用大型数组

✅ 知识要点总结 ​

功能语法示例应用场景
一维数组ARRAY[1,2,3]标签管理、选项集合
多维数组ARRAY[[1,2],[3,4]]矩阵计算、多维数据
空数组ARRAY[]::int[]占位初始化
子查询数组ARRAY(SELECT...)结果集转换
类型转换ARRAY[...]::type[]强制元素类型

💡 核心价值：数组构造函数让 PostgreSQL 能够原生处理复杂数据结构，在简化数据模型的同时提供强大的分析能力！

"给我一个数组，我能存储整个世界！" —— PostgreSQL 开发者宣言