PostgreSQL如何跨行统计数组中元素的出现次数？

当然可以实现，核心思路是先将JSON格式的COLORS字段拆分为单行的颜色值，再分组统计每个颜色出现的次数。以下是主流数据库的具体实现方案：

1. 视图实现（推荐，适合频繁查询） #

视图可以直接返回统计结果，无需每次调用过程，更简洁高效。

MySQL 8.0+ #

CREATE VIEW color_stats AS
SELECT color, COUNT(*) AS `COUNT`
FROM your_table,
     JSON_TABLE(
         COLORS,
         '$[*]' COLUMNS(color VARCHAR(50) PATH '$')
     ) AS jt
GROUP BY color
ORDER BY `COUNT` DESC;

SQL Server #

CREATE VIEW color_stats AS
SELECT value AS COLOR, COUNT(*) AS [COUNT]
FROM your_table
CROSS APPLY OPENJSON(COLORS)
GROUP BY value
ORDER BY [COUNT] DESC;

PostgreSQL #

CREATE VIEW color_stats AS
SELECT color AS COLOR, COUNT(*) AS "COUNT"
FROM your_table,
     json_array_text(COLORS) AS color
GROUP BY color
ORDER BY "COUNT" DESC;

2. 存储过程实现 #

如果需要封装更复杂的逻辑（比如动态传入表名、过滤条件等），可以用存储过程：

MySQL 8.0+ #

DELIMITER //
CREATE PROCEDURE GetColorStats()
BEGIN
    SELECT color AS COLOR, COUNT(*) AS `COUNT`
    FROM your_table,
         JSON_TABLE(
             COLORS,
             '$[*]' COLUMNS(color VARCHAR(50) PATH '$')
         ) AS jt
    GROUP BY color
    ORDER BY `COUNT` DESC;
END //
DELIMITER ;

-- 调用方式
CALL GetColorStats();

SQL Server #

CREATE PROCEDURE GetColorStats
AS
BEGIN
    SELECT value AS COLOR, COUNT(*) AS [COUNT]
    FROM your_table
    CROSS APPLY OPENJSON(COLORS)
    GROUP BY value
    ORDER BY [COUNT] DESC;
END;

-- 调用方式
EXEC GetColorStats;

PostgreSQL #

CREATE OR REPLACE PROCEDURE GetColorStats()
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT color AS COLOR, COUNT(*) AS "COUNT"
    FROM your_table,
         json_array_text(COLORS) AS color
    GROUP BY color
    ORDER BY "COUNT" DESC;
END;
$$;

-- 调用方式
CALL GetColorStats();

内容的提问来源于stack exchange，提问作者TechGolemn