LabHub
学习 学习路径 课程

SQL 实战

用聚合做出汇总

在 LabHub 中继续学习

目标

使用 GROUP BY、HAVING、FILTER 和 date_trunc，把原始行折叠成有意义的汇总结果。

为什么重要

聚合是 SQL 中最常用、也最容易悄悄出错的领域，因为它通常不会报错，只会产生异常数字。

有三点尤其需要注意。第一，聚合函数会忽略 NULL，因此平均值的分母可能与预期不同。第二，本数据中退货单以负数量混入，直接求和会减少销量。第三，对带时区的列按月截断时，若不明确指定基准时区，结果会随服务器配置而变化。

记住处理顺序可以消除大部分困惑：FROM、WHERE、GROUP BY、聚合、HAVING、SELECT、ORDER BY。只要理解 WHERE 在分组前执行、HAVING 在分组后执行，就能解释“为什么不能在 WHERE 中使用 count”。

步骤

  1. 创建视图 v_status_count，保存各订单状态的数量。列为 status、order_count。
  2. 只针对 status 为 paid、shipped、delivered 的订单，创建按渠道汇总销售额的视图 v_channel_revenue。列为 channel、revenue。
  3. 创建只包含订单数不少于 5 的客户的视图 v_big_customers。列为 customer_id、order_count。
  4. 创建视图 v_status_split，一次性保存每个渠道的已支付订单数和已取消订单数。列为 channel、paid_count、cancelled_count，并且所有渠道都必须保留。
  5. 创建视图 v_monthly_revenue，保存已完成订单的月度销售额。列为 month（date 类型）、revenue；按月截断时，以 ordered_at AT TIME ZONE 'Asia/Seoul' 为基准。
  6. 创建视图 v_category_avg，保存各类别平均价格并四舍五入到无小数位。列为 category、avg_price。
  7. 创建视图 v_top_products，保存销量最高的 10 个商品。列为 product_id、name、sold_qty；只对数量为正的行求和，先按数量降序排列，数量相同时按 product_id 升序排列。
  8. 创建视图 v_tier_arpu，保存各等级的客户数、销售额和人均销售额。列为 tier、customer_count、revenue、arpu；销售额只统计已完成订单，但没有订单的客户也要计入分母。arpu 四舍五入到小数点后两位。

参考

按状态统计订单数

创建视图 v_status_count，保存各订单状态的数量。列为 status、order_count。

确定按什么字段分组，并使用统计行数的聚合函数。

按渠道汇总销售额

只针对 status 为 paid、shipped、delivered 的订单，创建按渠道汇总销售额的视图 v_channel_revenue。列为 channel、revenue。

已取消、已退款和待处理订单不应计入销售额。想一想应使用哪个子句在分组之前过滤。

只保留订单较多的客户

创建只包含订单数不少于 5 的客户的视图 v_big_customers。列为 customer_id、order_count。

不能用 WHERE 约束聚合结果。分组完成后有专门用于过滤的子句。

一次扫描完成两种计数

创建视图 v_status_split，一次性保存每个渠道的已支付订单数和已取消订单数。列为 channel、paid_count、cancelled_count，并且所有渠道都必须保留。

有一个子句可以为聚合函数附加条件。所有渠道都必须保留。

汇总月度销售额

创建视图 v_monthly_revenue，保存已完成订单的月度销售额。列为 month（date 类型）、revenue；按月截断时，以 ordered_at AT TIME ZONE 'Asia/Seoul' 为基准。

截断带时区的列时，必须明确基准时区，才能确保在任何环境运行都得到相同答案。

按类别计算平均价格

创建视图 v_category_avg，保存各类别平均价格并四舍五入到无小数位。列为 category、avg_price。

使用不保留小数位的四舍五入函数。不指定位数时会舍入为整数。

销量最高的 10 个商品

创建视图 v_top_products，保存销量最高的 10 个商品。列为 product_id、name、sold_qty；只对数量为正的行求和，先按数量降序排列，数量相同时按 product_id 升序排列。

本数据中退货以负数量记录。直接求和会改变排名。

按等级计算人均销售额

创建视图 v_tier_arpu，保存各等级的客户数、销售额和人均销售额。列为 tier、customer_count、revenue、arpu；销售额只统计已完成订单，但没有订单的客户也要计入分母。arpu 四舍五入到小数点后两位。

没有订单的客户也必须计入分母。避免因连接导致客户被重复计数。