别再死记硬背SQL聚合函数了！用这5个真实业务场景（电商/用户分析）帮你彻底搞懂AVG、SUM、COUNT

5个电商业务场景实战用SQL聚合函数解决真实数据分析问题刚入行数据分析时我最头疼的就是SQL聚合函数。背了一堆AVG、COUNT的语法一到实际业务就懵——到底该用哪个函数GROUP BY该怎么写HAVING和WHERE有什么区别直到参与了几个电商项目后才明白脱离业务场景学SQL就像背单词不学造句。今天就用5个真实案例带你彻底掌握聚合函数的实战用法。1. 用户价值分析用AVG计算客单价去年双十一大促后运营总监问我这次活动的客单价是多少比平时高了多少这个问题用AVG函数就能解决但要注意区分订单均价和用户客单价-- 订单均价所有订单金额的平均值 SELECT AVG(order_amount) AS avg_order_value FROM orders WHERE create_date BETWEEN 2023-11-11 AND 2023-11-12; -- 用户客单价每个用户消费金额的平均值 SELECT AVG(total_spent) AS avg_customer_value FROM ( SELECT user_id, SUM(order_amount) AS total_spent FROM orders WHERE create_date BETWEEN 2023-11-11 AND 2023-11-12 GROUP BY user_id ) user_stats;关键区别订单均价反映单次交易规模用户客单价反映用户消费能力大促期间前者通常增长更明显提示在MySQL中AVG函数会自动忽略NULL值。如果某些订单金额为NULL它们不会被计入分母。2. 库存预警用COUNT统计商品销量做库存管理时我们经常需要统计商品销量排名。COUNT函数在这里有3种典型用法-- 统计总销量前10的商品 SELECT product_id, COUNT(*) AS sales_count FROM order_items WHERE created_at 2023-01-01 GROUP BY product_id ORDER BY sales_count DESC LIMIT 10; -- 统计有销量的商品总数去重计数 SELECT COUNT(DISTINCT product_id) AS active_products FROM order_items WHERE created_at 2023-01-01; -- 统计零销量商品LEFT JOIN COUNT条件 SELECT p.product_id, p.product_name FROM products p LEFT JOIN order_items oi ON p.product_id oi.product_id GROUP BY p.product_id, p.product_name HAVING COUNT(oi.item_id) 0;业务应用场景爆款商品识别COUNT(*)SKU效率分析COUNT DISTINCT滞销品清理HAVING COUNT03. 促销效果评估用SUM计算GMV和优惠成本每次大促后最关键的三个指标指标SQL查询示例业务意义GMVSELECT SUM(order_amount) FROM orders WHERE promo_id123总交易额反映活动规模优惠总成本SELECT SUM(discount_amount) FROM order_coupons WHERE promo_id123营销投入的直接成本实际支付金额SELECT SUM(payment_amount) FROM orders WHERE promo_id123扣除优惠后的真实收入-- 完整促销效果分析报表 SELECT promo_id, COUNT(DISTINCT user_id) AS user_count, COUNT(*) AS order_count, SUM(order_amount) AS gmv, SUM(payment_amount) AS net_sales, SUM(discount_amount) AS total_discount, SUM(order_amount)/COUNT(DISTINCT user_id) AS arpu FROM orders WHERE promo_id IN (123, 124, 125) GROUP BY promo_id;注意SUM函数在遇到NULL值时会自动跳过。如果需要将NULL视为0可以用COALESCESUM(COALESCE(column,0))4. 用户分层用MAX/MIN识别消费极值RFM模型中的消费金额维度Monetary就需要极值分析-- 找出消费最高/最低的10个用户 SELECT user_id, MAX(payment_amount) AS max_order, MIN(payment_amount) AS min_order, SUM(payment_amount) AS total_spent FROM orders GROUP BY user_id ORDER BY total_spent DESC LIMIT 10; -- 结合时间范围的极值分析 SELECT product_id, MAX(price) AS highest_price, MIN(price) AS lowest_price, MAX(price) - MIN(price) AS price_range FROM product_price_history WHERE date BETWEEN 2023-01-01 AND 2023-12-31 GROUP BY product_id HAVING COUNT(*) 5; -- 只分析价格变动超过5次的商品业务决策支持识别高净值用户MAX值高发现异常低价订单MIN值异常监控商品价格波动MAX-MIN区间5. 漏斗分析组合使用多个聚合函数用户购买漏斗是电商分析的核心场景需要组合多个聚合函数-- 七日购买漏斗分析 SELECT COUNT(DISTINCT visit_id) AS visitors, COUNT(DISTINCT CASE WHEN page_typecart THEN visit_id END) AS cart_visitors, COUNT(DISTINCT CASE WHEN page_typecheckout THEN visit_id END) AS checkout_visitors, COUNT(DISTINCT o.order_id) AS paid_orders, ROUND(100.0 * COUNT(DISTINCT o.order_id) / COUNT(DISTINCT visit_id), 2) AS conversion_rate FROM user_sessions s LEFT JOIN orders o ON s.visit_id o.session_id WHERE s.visit_time NOW() - INTERVAL 7 days GROUP BY DATE(s.visit_time);进阶技巧使用CASE WHEN配合COUNT实现条件计数通过LEFT JOIN确保漏斗基数正确日期分组观察趋势变化避坑指南GROUP BY的常见错误刚开始写分组查询时我经常遇到这两个报错非聚合列未包含在GROUP BY中-- 错误写法product_name不在GROUP BY中 SELECT product_id, product_name, AVG(price) FROM products GROUP BY product_id; -- 正确写法 SELECT product_id, product_name, AVG(price) FROM products GROUP BY product_id, product_name; -- MySQL允许这种写法混淆WHERE和HAVING的使用时机WHERE在分组前过滤原始数据HAVING在分组后过滤聚合结果-- 找出订单数超过10且平均金额大于500的用户 SELECT user_id, COUNT(*) AS order_count, AVG(payment_amount) AS avg_payment FROM orders WHERE status completed -- 先筛选已完成订单 GROUP BY user_id HAVING COUNT(*) 10 AND AVG(payment_amount) 500; -- 再筛选聚合结果不同数据库对GROUP BY的处理略有差异MySQL允许SELECT出现非聚合列PostgreSQL严格要求SELECT列必须出现在GROUP BY或聚合函数中SQL Server支持GROUPING SETS等高级分组