如何按UNION ALL中SELECT语句顺序排序BigQuery查询结果？

核心方案 #

给每个SELECT子查询添加固定的排序标识字段，通过该字段显式控制最终结果的排序顺序——因为UNION ALL本身不保证输出顺序，必须依赖自定义标识字段配合ORDER BY实现需求。

修改后的查询语句 #

方式一：使用CTE（更清晰易维护）

WITH union_results AS (
  (SELECT voter_category, count(voter_category) as responses, 1 AS sort_order
   FROM `bootcamp-application-project.voters_survey.survey_responses` 
   where q10_1 = 1      -- Receiving long-term disability 
                        -- 1 is Yes response
   group by voter_category) 
  UNION ALL
  (SELECT voter_category, count(voter_category) as responses, 2 AS sort_order
   FROM `bootcamp-application-project.voters_survey.survey_responses` 
   where Q10_2 = 1      -- Have a chronic illness 
   group by voter_category)
  UNION ALL
  (SELECT voter_category, count(voter_category) as responses, 3 AS sort_order
   FROM `bootcamp-application-project.voters_survey.survey_responses` 
   where Q10_3 = 1      -- Been unemployed for more than a year
   group by voter_category)
  UNION ALL
  (SELECT voter_category, count(voter_category) as responses, 4 AS sort_order
   FROM `bootcamp-application-project.voters_survey.survey_responses` 
   where Q10_4 = 1      -- Have been evicted from your home within the past year 
   group by voter_category)
)
SELECT voter_category, responses
FROM union_results
ORDER BY sort_order;

方式二：直接嵌套子查询

SELECT voter_category, responses
FROM (
  (SELECT voter_category, count(voter_category) as responses, 1 AS sort_order
   FROM `bootcamp-application-project.voters_survey.survey_responses` 
   where q10_1 = 1      -- Receiving long-term disability 
                        -- 1 is Yes response
   group by voter_category) 
  UNION ALL
  (SELECT voter_category, count(voter_category) as responses, 2 AS sort_order
   FROM `bootcamp-application-project.voters_survey.survey_responses` 
   where Q10_2 = 1      -- Have a chronic illness 
   group by voter_category)
  UNION ALL
  (SELECT voter_category, count(voter_category) as responses, 3 AS sort_order
   FROM `bootcamp-application-project.voters_survey.survey_responses` 
   where Q10_3 = 1      -- Been unemployed for more than a year
   group by voter_category)
  UNION ALL
  (SELECT voter_category, count(voter_category) as responses, 4 AS sort_order
   FROM `bootcamp-application-project.voters_survey.survey_responses` 
   where Q10_4 = 1      -- Have been evicted from your home within the past year 
   group by voter_category)
)
ORDER BY sort_order;

关键说明 #

1. 排序标识字段：每个子查询中的sort_order分别赋值1、2、3、4，对应你原UNION ALL中子查询的顺序，确保排序逻辑和你预期的一致。
2. 结果过滤：外层查询可以选择是否返回sort_order字段，示例中仅保留业务所需的voter_category和responses列。
3. 兼容性：该写法完全适配BigQuery免费版，不需要额外权限或付费功能。

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