如何在PostgreSQL中创建带特征计数的标签对比图表？

方案一：生成互斥的标签组合统计（适合无重叠的对比图表） #

这个方案会自动统计name/name:en/name:bg三个标签所有可能的存在/不存在组合，每个记录仅归属一个分组，避免重复计数，更适合做对比图表：

SELECT
  -- 将布尔状态转换为+/-符号
  CASE WHEN has_name THEN '+' ELSE '-' END AS "name",
  CASE WHEN has_name_en THEN '+' ELSE '-' END AS "name:en",
  CASE WHEN has_name_bg THEN '+' ELSE '-' END AS "name:bg",
  record_count
FROM (
  -- 先按三个标签的存在状态分组计数
  SELECT
    exist(tags, 'name') AS has_name,
    exist(tags, 'name:en') AS has_name_en,
    exist(tags, 'name:bg') AS has_name_bg,
    COUNT(*) AS record_count
  FROM public.ways
  -- 可选：过滤掉三个标签都不存在的记录
  WHERE exist(tags, 'name') OR exist(tags, 'name:en') OR exist(tags, 'name:bg')
  GROUP BY has_name, has_name_en, has_name_bg
) AS status_groups
-- 按标签优先级排序，方便查看
ORDER BY has_name DESC, has_name_en DESC, has_name_bg DESC;

方案二：匹配原手动查询的重叠统计 #

如果需要和你原来的UNION查询结果完全一致（统计重叠的子集，比如所有带name的记录，包括同时带其他标签的），可以用FILTER子句简化查询，只需要扫描一次表，性能更优：

SELECT
  '+' AS "name", NULL AS "name:en", NULL AS "name:bg", COUNT(*) FILTER (WHERE exist(tags, 'name')) AS record_count
UNION ALL
SELECT NULL, '+', NULL, COUNT(*) FILTER (WHERE exist(tags, 'name:en'))
UNION ALL
SELECT NULL, NULL, '+', COUNT(*) FILTER (WHERE exist(tags, 'name:bg'))
UNION ALL
SELECT '+', '+', NULL, COUNT(*) FILTER (WHERE exist(tags, 'name') AND exist(tags, 'name:en'))
UNION ALL
SELECT '+', NULL, '+', COUNT(*) FILTER (WHERE exist(tags, 'name') AND exist(tags, 'name:bg'))
UNION ALL
SELECT '+', '-', NULL, COUNT(*) FILTER (WHERE exist(tags, 'name') AND NOT exist(tags, 'name:en'))
UNION ALL
SELECT '-', '+', NULL, COUNT(*) FILTER (WHERE NOT exist(tags, 'name') AND exist(tags, 'name:en'))
-- 按原查询的id顺序排序
ORDER BY
  CASE
    WHEN "name" = '+' AND "name:en" IS NULL AND "name:bg" IS NULL THEN 1
    WHEN "name" IS NULL AND "name:en" = '+' AND "name:bg" IS NULL THEN 2
    WHEN "name" IS NULL AND "name:en" IS NULL AND "name:bg" = '+' THEN 3
    WHEN "name" = '+' AND "name:en" = '+' AND "name:bg" IS NULL THEN 4
    WHEN "name" = '+' AND "name:en" IS NULL AND "name:bg" = '+' THEN 5
    WHEN "name" = '+' AND "name:en" = '-' AND "name:bg" IS NULL THEN 6
    WHEN "name" = '-' AND "name:en" = '+' AND "name:bg" IS NULL THEN 7
  END;

关于你尝试的crosstab问题 #

crosstab函数的作用是行转列，适合将行数据转换为列格式，并不适用于这种分组统计场景。你的需求核心是按标签的存在状态分组计数，正确的方向应该是：

1. 用exist()函数（hstore内置，比正则匹配高效）判断每个标签是否存在
2. 要么按布尔状态分组生成互斥统计，要么用FILTER子句实现条件计数生成重叠统计

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