如何在SQL SELECT查询中计算pricehistory表特殊格式字段的平均价格

该需求完全可以实现，核心逻辑是拆分字符串提取所有价格后做均值计算，以下是主流数据库的实现示例：

前提说明 #

假设你存储该字段的表名为product，以下所有示例基于该表名编写，可自行替换为实际表名。

MySQL 8.0+ 实现 #

SELECT 
  id, -- 替换为你需要保留的表主键/其他字段
  AVG(CAST(SUBSTRING_INDEX(group_str, ';', -1) AS DECIMAL(10,2))) AS avg_price
FROM product,
     JSON_TABLE(
       CONCAT('["', REPLACE(pricehistory, ',', '","'), '"]'),
       '$[*]' COLUMNS (group_str VARCHAR(255) PATH '$')
     ) AS t
GROUP BY id; -- 对应前面保留的分组字段

如果是MySQL 5.x版本，没有JSON_TABLE函数，可以用自定义变量的方式实现，高版本方案兼容性和可读性更强，优先推荐使用。

PostgreSQL 实现 #

SELECT 
  id,
  AVG(CAST(split_part(group_str, ';', 2) AS NUMERIC)) AS avg_price
FROM product,
     unnest(string_to_array(pricehistory, ',')) AS t(group_str)
GROUP BY id;

SQL Server 2016+ 实现 #

SELECT 
  id,
  AVG(CAST(RIGHT(group_str, LEN(group_str) - CHARINDEX(';', group_str)) AS DECIMAL(10,2))) AS avg_price
FROM product
CROSS APPLY STRING_SPLIT(pricehistory, ',') AS t(group_str)
GROUP BY id;

注意事项 #

• 如果pricehistory字段存在空值、不符合格式的脏数据，可在WHERE子句中加过滤条件，比如添加WHERE group_str LIKE '%;%'过滤掉没有分号的无效分组，避免数值转换报错。
• 如果需要对全表所有行的所有价格计算整体平均值，去掉GROUP BY和对应分组字段即可。

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