如何在BigQuery中按分支、部门嵌套分组并输出指定格式结果？

原查询问题分析 #

你原来的SQL存在两个核心问题：

1. GROUP BY branch, department 但SELECT里包含item和totalAmt，这不符合BigQuery的分组规则（非分组字段必须用聚合函数处理），实际执行会报错；
2. 没有处理分支、部门仅首次显示的逻辑，也没计算部门总销售额。

实现预期效果的SQL代码 #

以下代码可以实现你要的格式：分支、部门仅在首次出现时显示，其余行对应字段为空，同时包含部门总销售额：

WITH sales_data AS (
  SELECT 
    branch,
    department,
    item,
    totalAmt,
    -- 计算每个部门的总销售额
    SUM(totalAmt) OVER (PARTITION BY branch, department) AS dept_total
  FROM project.dataset.table
),
-- 生成部门总计行
dept_totals AS (
  SELECT 
    branch,
    department,
    '总计' AS item,
    dept_total AS totalAmt
  FROM (
    SELECT DISTINCT branch, department, dept_total
    FROM sales_data
  )
),
-- 合并商品明细行和部门总计行
combined_data AS (
  SELECT branch, department, item, totalAmt FROM sales_data
  UNION ALL
  SELECT branch, department, item, totalAmt FROM dept_totals
),
-- 标记分支、部门的首次出现行
ranked_data AS (
  SELECT 
    *,
    -- 标记每个分支的第一行
    ROW_NUMBER() OVER (PARTITION BY branch ORDER BY 
      CASE WHEN item = '总计' THEN 1 ELSE 0 END, item) AS branch_row_num,
    -- 标记每个部门的第一行
    ROW_NUMBER() OVER (PARTITION BY branch, department ORDER BY 
      CASE WHEN item = '总计' THEN 1 ELSE 0 END, item) AS dept_row_num
  FROM combined_data
)
-- 最终输出，控制分支、部门的显示
SELECT
  CASE WHEN branch_row_num = 1 THEN branch ELSE '' END AS branch,
  CASE WHEN dept_row_num = 1 THEN department ELSE '' END AS department,
  item,
  totalAmt
FROM ranked_data
ORDER BY branch, department, 
  CASE WHEN item = '总计' THEN 1 ELSE 0 END, item
LIMIT 100;

代码逻辑说明 #

1. sales_data：用窗口函数计算每个部门的总销售额，让每条商品记录都携带所属部门的总计金额；
2. dept_totals：生成单独的部门总计行，item字段标记为“总计”；
3. combined_data：把商品明细行和部门总计行合并，确保每个部门下既有明细又有总计；
4. ranked_data：用ROW_NUMBER()标记每个分支、部门的第一行，同时通过排序规则让总计行显示在部门所有商品的最后；
5. 最终查询：通过CASE判断，仅在分支、部门的第一行显示名称，其余行留空，实现嵌套分组的视觉效果。

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