在SQL中组合3个表并求平均年龄

Question

我需要合并三个表，并在使用SQL合并这三个表后找到平均年龄。这三个表中age列的名称分别为pr16pnk.age、pr16puf.age和pr16yag.age。我已经成功地找到了表分离时的结果，但我在合并结果时遇到了麻烦。下面列出了我使用的代码。

SELECT AVG(pr16pnk.age) AS MeanAge
FROM pr16pnk
UNION ALL
SELECT AVG(pr16puf.age) AS MeanAge
FROM pr16puf
UNION ALL
SELECT AVG(pr16yag.age) AS MeanAge
FROM pr16yag

Answer 1

您可以使用两个级别的聚合。平均值的平均值：

SELECT AVG(MeanAge)
FROM (SELECT AVG(pr16pnk.age) AS MeanAge
      FROM pr16pnk
      UNION ALL
      SELECT AVG(pr16puf.age) AS MeanAge
      FROM pr16puf
      UNION ALL
      SELECT AVG(pr16yag.age) AS MeanAge
      FROM pr16yag
     ) a;

但是，您可能真正想要的是总体平均值，其计算方法如下：

SELECT AVG(MeanAge),
       SUM(sum_age) / SUM(cnt)
FROM (SELECT AVG(pr16pnk.age) AS MeanAge, SUM(age) as sum_age, COUNT(*) as cnt
      FROM pr16pnk
      UNION ALL
      SELECT AVG(pr16puf.age) AS MeanAge, SUM(age) as sum_age, COUNT(*) as cnt
      FROM pr16puf
      UNION ALL
      SELECT AVG(pr16yag.age) AS MeanAge, SUM(age) as sum_age, COUNT(*) as cnt
      FROM pr16yag
     ) a;