电子商务网站开发与管理实验报告,电子商务网站建设运行环境,5g空间大吗企业网站,广告投放行业目标#xff1a;掌握 SQL 中分析函数#xff08;窗口函数#xff09;与聚合函数的组合使用#xff0c;通过实际案例实现复杂业务需求#xff0c;如同比、环比和趋势分析。 1. 分析函数与聚合函数的区别
聚合函数#xff08;Aggregate Functions#xff09;#xff1a;…目标掌握 SQL 中分析函数窗口函数与聚合函数的组合使用通过实际案例实现复杂业务需求如同比、环比和趋势分析。 1. 分析函数与聚合函数的区别
聚合函数Aggregate Functions对多行数据进行汇总返回一个结果。常见的有 SUM、AVG、COUNT、MAX 等。分析函数Analytic/Window Functions在不缩减行数的前提下基于某个窗口执行计算。常见的有 SUM() OVER、RANK()、LEAD()、LAG() 等。 2. 核心函数介绍
SUM() OVER在特定窗口内累加数据返回每一行对应窗口的累积值。AVG() OVER在窗口内计算平均值常用于移动平均。PERCENT_RANK()计算当前行在窗口内的百分比排名。 3. 案例计算用户每月销售额及同比、环比增长率
需求描述
计算每个用户在每个月的总销售额。计算每个月的环比增长率本月与上月相比。计算每个月的同比增长率本月与去年同月相比。 示例数据
sales 表结构
sale_iduser_idsale_amountsale_date11015002023-01-1521016002023-02-1031017002024-01-2041024002023-01-1851024502024-01-25 SQL 实现
WITH monthly_sales AS (SELECT user_id,DATE_FORMAT(sale_date, %Y-%m) AS sale_month,SUM(sale_amount) AS total_salesFROM salesGROUP BY user_id, DATE_FORMAT(sale_date, %Y-%m)
),
sales_with_trends AS (SELECT user_id,sale_month,total_sales,LAG(total_sales, 1) OVER (PARTITION BY user_id ORDER BY sale_month) AS previous_month_sales,LAG(total_sales, 12) OVER (PARTITION BY user_id ORDER BY sale_month) AS last_year_salesFROM monthly_sales
)
SELECT user_id,sale_month,total_sales,ROUND((total_sales - previous_month_sales) / NULLIF(previous_month_sales, 0) * 100, 2) AS month_over_month_growth,ROUND((total_sales - last_year_sales) / NULLIF(last_year_sales, 0) * 100, 2) AS year_over_year_growth
FROM sales_with_trends
ORDER BY user_id, sale_month;代码解析
第一步monthly_sales按用户和月份汇总销售数据计算每月销售总额。第二步sales_with_trends 使用 LAG() 计算前一个月的销售额计算环比。使用 LAG() 结合 12 个月偏移量计算去年的同月销售额实现同比。 最终结果计算环比、同比增长率NULLIF 防止除零错误。 结果示例
user_idsale_monthtotal_salesmonth_over_month_growthyear_over_year_growth1012023-01500NULLNULL1012023-0260020.00NULL1012024-0170016.6740.00 4. 亮点解读
环比计算通过 LAG() 直接获取上个月数据无需自联表。同比计算利用 LAG() 向前偏移12个月直观且高效。窗口函数优势保留所有行数据且在不改变原始行的基础上计算额外指标。 5. 扩展思考
可以使用 LEAD() 预测未来趋势或计算未来一个月的数据变化。结合 PERCENT_RANK() 分析各用户在销售额中的排名实现销售精英筛选。使用 NTILE(4) 将用户按季度或销售额分组分析不同等级用户的增长趋势。
这种 SQL 方案适合在业务系统中监控用户销售趋势适用于电商、金融和 SaaS 产品的业务数据分析。
