如何合并单表衍生虚拟表，保留全量年龄数据适配可视化工具

原表数据 #

需求 #

将数据转换为age为唯一列，每个name对应单独一列存储其height；每行仅保留age和对应name的height，其余列填充NULL。

现有问题 #

使用FULL JOIN的查询会丢失部分age数据，因为原查询仅取aaa的age，而lily的部分age不在aaa中，导致这部分age无法显示在结果的age列里。原查询：

with aaa as (
  SELECT age, height as "george" from table1 where name in ($GRAFANA_VARIABLE1),
    bbb as SELECT age, height as "lily" from table1 where name in ($GRAFANA_VARIABLE2)
)
SELECT aaa.age, george, lily from aaa FULL JOIN bbb on bbb.age = aaa.age

解决方案 #

方案1：修复FULL JOIN的age列 #

通过COALESCE合并两个子查询的age，确保所有age都被保留：

WITH aaa AS (
  SELECT age, height AS "george" FROM table1 WHERE name IN ($GRAFANA_VARIABLE1)
),
bbb AS (
  SELECT age, height AS "lily" FROM table1 WHERE name IN ($GRAFANA_VARIABLE2)
)
SELECT 
  COALESCE(aaa.age, bbb.age) AS age,
  aaa.george,
  bbb.lily
FROM aaa 
FULL JOIN bbb ON bbb.age = aaa.age
ORDER BY age;

COALESCE会优先取aaa的age，若为NULL则取bbb的age，这样所有age都会显示在同一列。

方案2：条件聚合（通用透视方法，更简洁） #

无需JOIN，直接用CASE WHEN或FILTER（根据SQL方言）实现透视，适配Grafana变量：

SELECT
  age,
  MAX(CASE WHEN name IN ($GRAFANA_VARIABLE1) THEN height END) AS "george",
  MAX(CASE WHEN name IN ($GRAFANA_VARIABLE2) THEN height END) AS "lily",
  MAX(CASE WHEN name IN ($GRAFANA_VARIABLE3) THEN height END) AS "phil" -- 可扩展更多name
FROM table1
GROUP BY age
ORDER BY age;

如果是支持FILTER的数据库（如PostgreSQL），可以简化为：

SELECT
  age,
  MAX(height) FILTER (WHERE name IN ($GRAFANA_VARIABLE1)) AS "george",
  MAX(height) FILTER (WHERE name IN ($GRAFANA_VARIABLE2)) AS "lily",
  MAX(height) FILTER (WHERE name IN ($GRAFANA_VARIABLE3)) AS "phil"
FROM table1
GROUP BY age
ORDER BY age;

这种方法会自动聚合同一age下的height（若同一age同一name有多条数据，MAX取最后/最大值，也可根据需求用AVG等），同时保留所有age，空值自动填充NULL，完全符合可视化工具的x轴要求。

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