BigQuery中多条件步数分类查询合并方法咨询

你之前用UNION ALL出错的核心原因是：UNION ALL要求所有子查询的列数、列位置、数据类型必须完全匹配。你每个子查询只输出对应分类的列，后续子查询的列会被强行对齐到第一个子查询的列名上，导致结果混乱。

下面提供两种正确的实现方式：

方式一：修正UNION ALL的列对齐 #

每个子查询都输出所有分类列，符合条件的填步数，不符合的填NULL，确保列数和列名一致：

SELECT
  Date,
  Step_count AS very_low_physical_activity,
  NULL AS low_physical_activity,
  NULL AS moderate_physical_activity,
  NULL AS high_physical_activity,
  NULL AS very_high_physical_activity
FROM my-second-project-370721.my_activity_2022.my_activity
WHERE Step_count < 1000

UNION ALL

SELECT
  Date,
  NULL AS very_low_physical_activity,
  Step_count AS low_physical_activity,
  NULL AS moderate_physical_activity,
  NULL AS high_physical_activity,
  NULL AS very_high_physical_activity
FROM my-second-project-370721.my_activity_2022.my_activity
WHERE Step_count BETWEEN 1000 AND 2500

UNION ALL

SELECT
  Date,
  NULL AS very_low_physical_activity,
  NULL AS low_physical_activity,
  Step_count AS moderate_physical_activity,
  NULL AS high_physical_activity,
  NULL AS very_high_physical_activity
FROM my-second-project-370721.my_activity_2022.my_activity
WHERE Step_count BETWEEN 2501 AND 5000

UNION ALL

SELECT
  Date,
  NULL AS very_low_physical_activity,
  NULL AS low_physical_activity,
  NULL AS moderate_physical_activity,
  Step_count AS high_physical_activity,
  NULL AS very_high_physical_activity
FROM my-second-project-370721.my_activity_2022.my_activity
WHERE Step_count BETWEEN 5001 AND 10000

UNION ALL

SELECT
  Date,
  NULL AS very_low_physical_activity,
  NULL AS low_physical_activity,
  NULL AS moderate_physical_activity,
  NULL AS high_physical_activity,
  Step_count AS very_high_physical_activity
FROM my-second-project-370721.my_activity_2022.my_activity
WHERE Step_count > 10000
ORDER BY Date;

这种写法会让每一行数据只在对应分类列显示步数，其他列留空，清晰对应每天的步数所属类别。

方式二：用CASE WHEN实现更高效的分类（推荐） #

不需要多次查询表，一次扫描就能完成分类，性能更优：

SELECT
  Date,
  CASE WHEN Step_count < 1000 THEN Step_count ELSE NULL END AS very_low_physical_activity,
  CASE WHEN Step_count BETWEEN 1000 AND 2500 THEN Step_count ELSE NULL END AS low_physical_activity,
  CASE WHEN Step_count BETWEEN 2501 AND 5000 THEN Step_count ELSE NULL END AS moderate_physical_activity,
  CASE WHEN Step_count BETWEEN 5001 AND 10000 THEN Step_count ELSE NULL END AS high_physical_activity,
  CASE WHEN Step_count > 10000 THEN Step_count ELSE NULL END AS very_high_physical_activity
FROM my-second-project-370721.my_activity_2022.my_activity
ORDER BY Date;

如果你的需求是统计每天各分类的天数（而非单条记录的分类），可以用聚合函数改写：

SELECT
  Date,
  COUNT(CASE WHEN Step_count < 1000 THEN 1 END) AS very_low_days,
  COUNT(CASE WHEN Step_count BETWEEN 1000 AND 2500 THEN 1 END) AS low_days,
  COUNT(CASE WHEN Step_count BETWEEN 2501 AND 5000 THEN 1 END) AS moderate_days,
  COUNT(CASE WHEN Step_count BETWEEN 5001 AND 10000 THEN 1 END) AS high_days,
  COUNT(CASE WHEN Step_count > 10000 THEN 1 END) AS very_high_days
FROM my-second-project-370721.my_activity_2022.my_activity
GROUP BY Date
ORDER BY Date;

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