SQL 分析函数与聚合函数的组合应用
目标:掌握 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_id | user_id | sale_amount | sale_date |
|---|---|---|---|
| 1 | 101 | 500 | 2023-01-15 |
| 2 | 101 | 600 | 2023-02-10 |
| 3 | 101 | 700 | 2024-01-20 |
| 4 | 102 | 400 | 2023-01-18 |
| 5 | 102 | 450 | 2024-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_id | sale_month | total_sales | month_over_month_growth | year_over_year_growth |
|---|---|---|---|---|
| 101 | 2023-01 | 500 | NULL | NULL |
| 101 | 2023-02 | 600 | 20.00 | NULL |
| 101 | 2024-01 | 700 | 16.67 | 40.00 |
4. 亮点解读
- 环比计算:通过
LAG()直接获取上个月数据,无需自联表。 - 同比计算:利用
LAG()向前偏移12个月,直观且高效。 - 窗口函数优势:保留所有行数据,且在不改变原始行的基础上计算额外指标。
5. 扩展思考
- 可以使用
LEAD()预测未来趋势或计算未来一个月的数据变化。 - 结合
PERCENT_RANK()分析各用户在销售额中的排名,实现销售精英筛选。 - 使用
NTILE(4)将用户按季度或销售额分组,分析不同等级用户的增长趋势。
这种 SQL 方案适合在业务系统中监控用户销售趋势,适用于电商、金融和 SaaS 产品的业务数据分析。
相关文章:
SQL 分析函数与聚合函数的组合应用
目标:掌握 SQL 中分析函数(窗口函数)与聚合函数的组合使用,通过实际案例实现复杂业务需求,如同比、环比和趋势分析。 1. 分析函数与聚合函数的区别 聚合函数(Aggregate Functions):…...
【项目】基于趋动云平台的Stable Diffusion开发
【项目】基于趋动云平台的Stable Diffusion开发 (一)登录趋动云(二)创建项目:(三)初始化开发环境:(四)运行代码(五)运行模型 …...
游戏如何检测iOS越狱
不同于安卓的开源生态,iOS一直秉承着安全性更高的闭源生态,系统中的硬件、软件和服务会经过严格审核和测试,来保障安全性与稳定性。 据FairGurd观察,虽然iOS系统具备一定的安全性,但并非没有漏洞,如市面上…...
【AIGC-ChatGPT进阶提示词指令】AI美食助手的设计与实现:Lisp风格系统提示词分析
引言 在人工智能助手的应用领域中,美食烹饪是一个既专业又贴近生活的方向。本文将详细分析一个基于Lisp风格编写的美食助手系统提示词,探讨其结构设计、功能实现以及实际应用效果。 提出你的菜系,为你分析,并生成图片卡片 提示词…...
库伦值自动化功耗测试工具
1. 功能介绍 PlatformPower工具可以自动化测试不同场景的功耗电流,并可导出为excel文件便于测试结果分析查看。测试同时便于后续根据需求拓展其他自动化测试用例。 主要原理:基于文件节点 coulomb_count 实现,计算公式:电流&…...
Javascript数据结构——图Graph
当然,让我们深入探讨一下JavaScript中的图数据结构,并列出一些常见的面试题及其代码示例。 图数据结构详解 图(Graph)是一种非线性的数据结构,由节点(也称为顶点)和连接这些节点的边组成。节点…...
搭建nginx文件服务器
方法一:通过docker方式搭建 1、创建一个nginx配置文件/etc/nginx/nginx.conf user nginx; worker_processes 1;error_log /var/log/nginx/error.log warn; pid /var/run/nginx.pid;events {worker_connections 1024; }http {include mime.types;default_typ…...
Ubuntu Server安装谷歌浏览器
背景 服务器上跑爬虫服务器需要安装谷歌浏览器 安装 wget https://dl.google.com/linux/direct/google-chrome-stable_current_amd64.deb### sudo apt install ./google-chrome-stable_current_amd64.deb...
Vue项目结构推荐(复杂国际化项目与一般项目结构)
Vue项目结构推荐 一、一般项目结构二、复杂国际化项目结构总结/建议 下面结构是基于Vue和TypeScript开发的项目结构下src包下的结构,若只用到vue与js。则去掉typescript部分的包即可。 一、一般项目结构 assets:存放静态资源,如图片、字体、样…...
hive-sql 连续登录五天的用户
with tmp as (select 梁牧泽 as uid, 2023-03-03 as dt union allselect 梁牧泽 as uid, 2023-03-04 as dt union allselect 梁牧泽 as uid, 2023-03-05 as dt union allselect 梁牧泽 as uid, 2023-03-07 as dt union allselect 梁牧泽 as uid, 2023-03-08 as dt union allsel…...
FPGA 4x4矩阵键盘 实现
1原理 FPGA(现场可编程门阵列)4x4矩阵键盘的实现原理主要基于行列扫描法,通过FPGA对键盘的扫描和识别,实现对键盘输入信号的采集和处理。以下是对FPGA 4x4矩阵键盘实现原理的详细解释: 一、矩阵键盘的基本原理 结构:4x4矩阵键盘由4行和4列组成,共16个按键。每个按键位…...
ruoyi开发学习
将若依框架中的若依元素删掉 1.删除主目录中的“若依官网”: 在后端项目中,idea里借助mysql管理工具,找到sys_menu数据表,双击打开,找到4 若依官网,选中点击减号,绿色上箭头刷新,删…...
MacBook_Xcode_Swift雨燕
Swift Swift Swift Swift是苹果公司开发的现代化编程语言, 专为Apple平台设计。其简洁语法、类型安全、Optionals处理、Playgrounds交互式环境、泛型编程、协议与扩展、闭包功能、枚举与关联值、结构体与类的高效内存管理、异步编程的async/await语法、Swift Packa…...
ABAQUS三维Voronoi晶体几何建模
材料晶体塑性理论与细观尺度上晶体几何模型相融合的模拟方法为探究材料在塑性变形过程中的行为机制以及晶体材料优化开辟了新途径。本案例演示在CAD软件内通过Voronoi建立晶体三维模型,并将模型导入到Abaqus CAE内,完成晶体材料的有限元建模。 在AutoC…...
.Net加密与Java互通
.Net加密与Java互通 文章目录 .Net加密与Java互通前言RSA生成私钥和公钥.net加密出数据传给Java端采用java方给出的公钥进行加密采用java方给出的私钥进行解密 .net 解密来自Java端的数据 AES带有向量的AES加密带有向量的AES解密无向量AES加密无向量AES解密 SM2(国密)SM2加密Sm…...
MySQL 06 章——多表查询
多表查询,也称为关联查询,是指两个表或多个表一起完成查询操作 前提条件,这些一起查询的表之间是有关系的(一对一、一对多),它们之间一定是有关联字段的。这个关联字段可能建立了外键,也可能没…...
猴子吃桃.
本节通过学习解决一个有趣的问题来加深对递归的理解. 问题描述: 有一个猴子摘了桃子吃,第一天吃一半多一个,第二天吃第一天剩余的一半多一个,第三天吃第二天剩余的一半多一个..以此类推,当第n天时,恰好只剩下一个桃子.求猴子一共摘了多少桃子. 思路解析: 解读题目,第n天的桃子…...
游戏引擎学习第72天
无论如何,我们今天有一些调试工作要做,因为昨天做了一些修改,结果没有时间进行调试和处理。我们知道自己还有一些需要解决的问题,却没有及时完成,所以我们想继续进行这些调试。对我们来说,拖延调试工作总是…...
element-ui dialog 组件源码分享
简单分享 dialog 组件源码,主要从以下三个方面: 1、dialog 页面结构。 2、dialog 组件属性。 3、dialog 组件挂载。 4、dialog 组件事件。 一、dialog 页面结构: 二、组件属性: 2.1 visible 是否显示 Dialog,支持…...
unity开发之shader 管道介质流动特效
效果 shader graph 如果出现下面的效果,那是因为你模型的问题,建模做贴图的时候没有设置好UV映射,只需重新设置下映射即可...
3个关键步骤:在电视盒子上完美运行Armbian系统的终极指南
3个关键步骤:在电视盒子上完美运行Armbian系统的终极指南 【免费下载链接】amlogic-s9xxx-armbian Supports running Armbian on Amlogic, Allwinner, and Rockchip devices. Support a311d, s922x, s905x3, s905x2, s912, s905d, s905x, s905w, s905, s905l, rk358…...
一站式融合赋能,企业级私有化视频会议系统EasyDSS助力企业培训全流程闭环管理
传统企业培训往往面临诸多痛点,线下培训受地域、时间限制,直播培训错过即无,核心内容无法有效沉淀,会议、直播、点播多平台切换,操作繁琐效率低,EasyDSS企业级私有化视频会议系统,打破单一功能局…...
单细胞测序入门(一):技术概览与数据获取实战
1. 单细胞测序技术全景解读 第一次接触单细胞测序时,我被这项技术的精妙设计深深震撼。想象一下,传统测序就像把水果榨成混合果汁检测成分,而单细胞测序则是把每个苹果、香蕉、葡萄分开榨汁分析——这就是单细胞分辨率带来的革命性视角。 核心…...
YOLOv8与SenseVoice-Small的多模态安防监控系统设计
YOLOv8与SenseVoice-Small的多模态安防监控系统设计 1. 系统设计背景与价值 在现代安防监控领域,单纯依靠视频分析已经无法满足复杂场景下的安全需求。传统的监控系统往往需要人工实时监控,不仅效率低下,而且容易遗漏关键信息。特别是在夜间…...
Wan2.2-T2V-A5B常见错误排查:运行失败、生成卡顿的解决方法
Wan2.2-T2V-A5B常见错误排查:运行失败、生成卡顿的解决方法 1. 问题概述与快速诊断 Wan2.2-T2V-A5B作为一款轻量级文本到视频生成模型,虽然在资源消耗和响应速度上具有优势,但在实际使用过程中仍可能遇到运行失败或生成卡顿的问题。这些问题…...
在树莓派4B上编译运行Speedtest-CLI:手把手解决curl和expat库的交叉编译难题
树莓派4B实战:从零构建Speedtest-CLI测速工具全流程指南 1. 环境准备与工具链配置 在树莓派4B上构建Speedtest-CLI测速工具,首先需要搭建完整的交叉编译环境。不同于x86平台的直接编译,ARM架构下的开发需要特别注意工具链的选择和配置。 必备…...
避坑指南:通达信DLL加密常见的5大误区与替代方案
通达信指标加密实战:5种DLL开发陷阱与零代码解决方案 在量化交易领域,指标公式的保护一直是开发者面临的棘手问题。最近三个月内,某金融开发者社区关于"通达信DLL加密失败"的求助帖增长了47%,暴露出传统加密方案存在显…...
别让import.*拖慢你的Spring Boot项目!IDEA优化导入配置详解
别让import.*拖慢你的Spring Boot项目!IDEA优化导入配置详解 在微服务架构盛行的今天,Spring Boot项目的启动速度已经成为开发者关注的焦点。一个常见的性能陷阱就隐藏在那些看似无害的import.*语句中——它们会强制JVM加载整个包的类,即使你…...
5个维度深度评估:哪款内容解锁工具真正值得投入时间?
5个维度深度评估:哪款内容解锁工具真正值得投入时间? 【免费下载链接】bypass-paywalls-chrome-clean 项目地址: https://gitcode.com/GitHub_Trending/by/bypass-paywalls-chrome-clean 在数字信息时代,付费墙已成为内容获取的主要障…...
如何永久保存微信聊天记录:免费工具实现数据可视化与年度报告生成
如何永久保存微信聊天记录:免费工具实现数据可视化与年度报告生成 【免费下载链接】WeChatMsg 提取微信聊天记录,将其导出成HTML、Word、CSV文档永久保存,对聊天记录进行分析生成年度聊天报告 项目地址: https://gitcode.com/GitHub_Trendi…...
