如何实现双层级平均值计算？先按周次再按分组ID求平均

开发者阿泽

2026-8-11

如何实现多层级平均值计算？

我目前仅能完成单层级平均值计算，无法实现多层级计算：

原SQL语句：

SELECT group_id,
STRFTIME("%W", date) as week_num,
AVG(count_value) as value FROM counts
GROUP BY week_num;

执行该语句后得到的结果：

仅按周（week_num）分组计算了平均值，结果未区分group_id，所有组的同周数据被合并计算，无法得到每个group_id对应的最终平均值。

我的需求是：先在week_num层级计算每个group_id每周的count_value平均值，再基于这些周平均值，在group_id层级求平均，最终得到每个group_id的平均值。预期结果为：

按group_id分组展示，每行对应一个group_id和其最终的平均值（即该group_id下所有周平均值的均值）。

解决方案

可以通过嵌套子查询或**CTE（公共表表达式）**实现两步分组计算，分阶段完成层级平均：

方法1：嵌套子查询

先在子查询中按group_id和week_num分组，计算每个组每周的平均值；再在外层查询中按group_id分组，计算这些周平均值的最终平均：

SELECT group_id, AVG(weekly_avg) AS final_avg
FROM (
    SELECT group_id,
           STRFTIME("%W", date) AS week_num,
           AVG(count_value) AS weekly_avg
    FROM counts
    GROUP BY group_id, week_num
) AS weekly_groups
GROUP BY group_id;

方法2：CTE（公共表表达式）

用CTE先提取每个组每周的平均值，再基于CTE计算最终结果，可读性更强：

WITH weekly_avg_data AS (
    SELECT group_id,
           STRFTIME("%W", date) AS week_num,
           AVG(count_value) AS weekly_avg
    FROM counts
    GROUP BY group_id, week_num
)
SELECT group_id, AVG(weekly_avg) AS final_avg
FROM weekly_avg_data
GROUP BY group_id;