如何在BigQuery步数活动统计查询中添加各活动占比计算

我通过智能手表收集步行步数，将体力活动按步数划分为极低、低、中等、高、极高五个等级。现有BigQuery查询可统计各等级活动的发生次数及总步数：

SELECT
 (case when Step_count < 1000 then 'Very Low Physical Activity'
             when Step_count >= 1000 and Step_count <2500 then 'Low Physical Activity'
             when Step_count >= 2500 and Step_count< 5000 then 'Moderate Physical Activity'
             when Step_count >= 5000 and Step_count <10000 then 'High Physical Activity'
             when Step_count > 10000 then 'Very High Physical Activity'
             else 'no_physical_activity'
        end) as Type_of_Physical_Activity,
       COUNT(*) as num_of_times,
       SUM(Step_count) as Total_steps       
                     from `my-second-project-370721.my_activity_2022.my_activity`
              Where Step_count is not NULL
       group by Type_of_Physical_Activity
       order by MIN(Step_count)

我希望在查询中嵌入各体力活动的占比计算（例如：极低体力活动=5%、低体力活动=10%等），但尝试以下写法后报错Unrecognized name: Total_steps：

SELECT
 (case when Step_count < 1000 then 'Very Low Physical Activity'
             when Step_count >= 1000 and Step_count <2500 then 'Low Physical Activity'
             when Step_count >= 2500 and Step_count< 5000 then 'Moderate Physical Activity'
             when Step_count >= 5000 and Step_count <10000 then 'High Physical Activity'
             when Step_count > 10000 then 'Very High Physical Activity'
             else 'no_physical_activity'
        end) as Type_of_Physical_Activity,
       COUNT(*) as num_of_times,
       SUM(Step_count) as Total_steps,
       ROUND(avg((Step_count)/(Total_steps-Step_count))*100,3) as ActivityPercentage
                     from `my-second-project-370721.my_activity_2022.my_activity`
              Where Step_count is not NULL
       group by Type_of_Physical_Activity
       order by MIN(Step_count)

请告知正确的实现方式。

错误原因 #

报错是因为同一SELECT层级无法直接引用聚合后的别名Total_steps，且原占比计算逻辑不符合需求——实际需要的是该等级总步数占全局总步数的比例，或该等级发生次数占总次数的比例。

正确实现方式 #

方式1：计算各等级总步数占全局总步数的比例 #

用窗口函数SUM(Total_steps) OVER ()获取全局总步数，再计算当前等级占比：

WITH activity_groups AS (
  SELECT
    CASE 
      WHEN Step_count < 1000 THEN 'Very Low Physical Activity'
      WHEN Step_count >= 1000 AND Step_count < 2500 THEN 'Low Physical Activity'
      WHEN Step_count >= 2500 AND Step_count < 5000 THEN 'Moderate Physical Activity'
      WHEN Step_count >= 5000 AND Step_count < 10000 THEN 'High Physical Activity'
      WHEN Step_count > 10000 THEN 'Very High Physical Activity'
      ELSE 'no_physical_activity'
    END AS Type_of_Physical_Activity,
    COUNT(*) AS num_of_times,
    SUM(Step_count) AS Total_steps
  FROM `my-second-project-370721.my_activity_2022.my_activity`
  WHERE Step_count IS NOT NULL
  GROUP BY Type_of_Physical_Activity
)
SELECT
  *,
  ROUND((Total_steps / SUM(Total_steps) OVER ()) * 100, 3) AS Step_Percentage
FROM activity_groups
ORDER BY MIN(Step_count) OVER (PARTITION BY Type_of_Physical_Activity)

方式2：计算各等级发生次数占总次数的比例 #

如果需要统计次数占比，用窗口函数SUM(num_of_times) OVER ()获取全局总次数：

WITH activity_groups AS (
  SELECT
    CASE 
      WHEN Step_count < 1000 THEN 'Very Low Physical Activity'
      WHEN Step_count >= 1000 AND Step_count < 2500 THEN 'Low Physical Activity'
      WHEN Step_count >= 2500 AND Step_count < 5000 THEN 'Moderate Physical Activity'
      WHEN Step_count >= 5000 AND Step_count < 10000 THEN 'High Physical Activity'
      WHEN Step_count > 10000 THEN 'Very High Physical Activity'
      ELSE 'no_physical_activity'
    END AS Type_of_Physical_Activity,
    COUNT(*) AS num_of_times,
    SUM(Step_count) AS Total_steps
  FROM `my-second-project-370721.my_activity_2022.my_activity`
  WHERE Step_count IS NOT NULL
  GROUP BY Type_of_Physical_Activity
)
SELECT
  *,
  ROUND((num_of_times / SUM(num_of_times) OVER ()) * 100, 3) AS Count_Percentage
FROM activity_groups
ORDER BY MIN(Step_count) OVER (PARTITION BY Type_of_Physical_Activity)

方式3：同时展示步数占比和次数占比 #

如果需要同时输出两种占比：

WITH activity_groups AS (
  SELECT
    CASE 
      WHEN Step_count < 1000 THEN 'Very Low Physical Activity'
      WHEN Step_count >= 1000 AND Step_count < 2500 THEN 'Low Physical Activity'
      WHEN Step_count >= 2500 AND Step_count < 5000 THEN 'Moderate Physical Activity'
      WHEN Step_count >= 5000 AND Step_count < 10000 THEN 'High Physical Activity'
      WHEN Step_count > 10000 THEN 'Very High Physical Activity'
      ELSE 'no_physical_activity'
    END AS Type_of_Physical_Activity,
    COUNT(*) AS num_of_times,
    SUM(Step_count) AS Total_steps
  FROM `my-second-project-370721.my_activity_2022.my_activity`
  WHERE Step_count IS NOT NULL
  GROUP BY Type_of_Physical_Activity
)
SELECT
  *,
  ROUND((Total_steps / SUM(Total_steps) OVER ()) * 100, 3) AS Step_Percentage,
  ROUND((num_of_times / SUM(num_of_times) OVER ()) * 100, 3) AS Count_Percentage
FROM activity_groups
ORDER BY MIN(Step_count) OVER (PARTITION BY Type_of_Physical_Activity)

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