当前位置：首页 > news >正文

SQL 分析函数与聚合函数的组合应用

news 来源：原创 2025/5/17 23:36:38

目标：掌握 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）：…...

编程日记 2025/5/17 23:36:38

【Elasticsearch入门到落地】5、安装IK分词器

接上篇《4、Elasticsearch的安装》上一篇我们进行了Elasticsearch以及Kibana的环境准备及软件安装，本篇我们安装最后一个支持软件IK分词器。一、IK分词器概念我们再来回顾一下上一张IK分词器的概念： IK分词器（IK Analyzer）是…...

编程日记 2025/5/17 23:01:17

8、RAG论文笔记（Retrieval-Augmented Generation检索增强生成）

RAG论文笔记 1、 **研究背景与动机**2、方法概述3、RAG 模型架构3.1总体架构3.2 Generator（生成器）3.3 检索器（Retriever）3.4训练（Training）3.5**解码方法**（求近似 ）3.6微调的参数 …...

编程日记 2025/5/13 4:38:31

【论文笔记】Contrastive Learning for Sign Language Recognition and Translation

🍎个人主页：小嗷犬的个人主页 🍊个人网站：小嗷犬的技术小站 🥭个人信条：为天地立心，为生民立命，为往圣继绝学，为万世开太平。基本信息标题: Contrastive Learning for…...

编程日记 2025/5/17 23:13:32

《C++设计模式》策略模式

文章目录 1、引言1.1 什么是策略模式1.2 策略模式的应用场景1.3 本文结构概览 2、策略模式的基本概念2.1 定义与结构2.2 核心角色解析2.2.1 策略接口（Strategy）2.2.2 具体策略实现（ConcreteStrategy）2.2.3 上下文（Cont…...

编程日记 2025/5/17 23:16:39

细说STM32F407单片机轮询方式CAN通信

目录一、项目介绍二、项目配置 1、时钟、DEBUG、USART6、NVIC、GPIO、CodeGenerator 2、CAN1 （1）Bit Timings Parameters组，位时序参数 （2）Basic Parameters组，基本参数 （3&#xff09…...

编程日记 2025/5/17 23:18:01

perf：对hutool的BeanUtil工具类做补充

分享一个自定义的BeanUtil，继承的是hutool的工具类，然后自己扩充了几个方法； 1、实现了两个对象覆盖非空属性的功能（经常使用），不需要设置CopyOptions； 2、两个对象，对指定前缀的属…...

编程日记 2025/5/17 7:37:39

【数据结构】栈与队列（FIFO）

在阅读该篇文章之前，可以先了解一下堆栈寄存器和栈帧的运作原理：<【操作系统】堆栈寄存器sp详解以及栈帧>。栈(FILO) 特性: 栈区的存储遵循着先进后出的原则。例子: 枪的弹夹，最先装进去的子弹最后射出来，最后装入的子弹…...

编程日记 2025/5/12 6:57:11

02.01、移除重复节点

02.01、[简单] 移除重复节点 1、题目描述编写代码，移除未排序链表中的重复节点。保留最开始出现的节点。 2、解题思路为了实现这一目标，我们可以使用一个哈希表（或集合）来记录已经遇到的节点值，逐步遍历链表并删…...

编程日记 2025/5/15 5:57:39

Spring thymeleaf 的快速默认搭建使用 thymeleaf 的搭建Pom 文件 thymeleaf 的使用Controller返回参数String资源文件路径访问端点显示HTML页面 thymeleaf 的搭建 Pom 文件 Pom 文件引入 spring-boot-starter-thymeleaf 依赖 <dependency><groupId>org.springfra…...

编程日记 2025/5/17 9:19:54

unity学习3：如何从github下载开源的unity项目

目录 1 网上别人提供的一些github的unity项目 2 如何下载github上的开源项目呢？ 2.1.0 下载工具 2.1.1 下载方法1 2.1.2 下载方法2（适合内部项目） 2.1.3 第1个项目和第4项目的比较第1个项目第2个项目第3个项目 2.1.4 下载方法…...

编程日记 2025/5/12 1:58:13

印象笔记07——试一试PDF标注

印象笔记07——试一试PDF标注 [!CAUTION] 根据第六期，我再次查询了资料，印象笔记还是有一些可圈可点的功能的（当然部分有平替），针对会员作用，开发使用场景虽然是逆向的，但我坚信这是一部分人的现…...

编程日记 2025/5/17 22:31:52

Logback的使用

1、基本认识 logback官方文档：http://logback.qos.ch 具体样例：https://www.baeldung.com/logback 从下面依赖关系图可以看见，Springboot的核心启动器spring-boot-stater依赖了spring-boot-starter-looging，而这个就是日志的启动器…...

编程日记 2025/5/17 6:24:47

沙箱模拟支付宝支付3--支付的实现

1 支付流程实现演示案例主要参考程序员青戈的视频【支付宝沙箱支付快速集成版】支付宝沙箱支付快速集成版_哔哩哔哩_bilibili 对应的源码在 alipay-demo: 使用支付宝沙箱实现支付功能 - Gitee.com 以下是完整的实现步骤 1.首先导入相关的依赖 <?xml version"1…...

编程日记 2025/5/16 22:50:30

微信小程序滑动解锁、滑动验证

微信小程序简单滑动解锁效果通过 movable-view （可移动的视图容器，在页面中可以拖拽滑动）实现的简单微信小程序滑动验证 movable-view 官方说明：https://developers.weixin.qq.com/miniprogram/dev/component/movable-view.ht…...

编程日记 2025/5/12 22:06:47

Redis的常用命令

Redis中文字典网站 redis 命令手册https://redis.com.cn/commands.html Keys * 查看当前库所有的key exists ke 判断某个key是否存在 type key查看你的key是什么类型 Del key删除执行的key数据 unlink key非阻塞删除，仅仅将keys从keyspace元数据中删除&#xf…...

编程日记 2025/5/14 10:55:31

国内Ubuntu环境Docker部署 ComfyUI

国内Ubuntu环境Docker部署 ComfyUI 趁着这两天用docker部署了 Stable Diffusion，顺手也安排上 ComfyUI。 ComfyUI相比 Stable Diffusion 原生的 WEB UI，更容易让人了解其出图的过程，极其适合学习与研究。拼接其强大的插件节点、不仅能够实现文…...

编程日记 2025/5/17 1:14:28

Meta 的新策略，将 AI 生成的角色整合到其社交媒体平台

一、Meta新年规划及引人注目的举措多元规划背景：在新的一年，Meta制定了多维度的战略规划，旨在巩固并拓展其在科技领域的影响力。增强现实与元宇宙是其长期布局的重点方向，期望借此塑造未来互联网的交互形态；面对TikTo…...

编程日记 2025/5/14 10:44:30

玩转OCR | 腾讯云智能结构化OCR初次体验

目录一、什么是OCR（需要了解） 二、产品概述与核心优势产品概述智能结构化能做什么举例说明（选看） 1、物流单据识别 2、常见证件识别 3、票据单据识别 4、行业材料识别三、产品特性高精度泛化性易用性四、…...

编程日记 2025/5/14 9:48:50

蓝桥杯JAVA--003

需求 2.代码 public class RegularExpressionMatching {public boolean isMatch(String s, String p) {if (p.isEmpty()) {return s.isEmpty();}boolean firstMatch !s.isEmpty() && (s.charAt(0) p.charAt(0) || p.charAt(0) .);if (p.length() > 2 && p…...

编程日记 2025/5/12 10:26:55

STC51和STM32单片机烧录引脚的完整名称

STC51 和 STM32 单片机烧录引脚的完整名称 1. STC51 单片机的烧录引脚 STC51 单片机通过串口（UART） 进行程序下载，主要引脚如下： 引脚名称完整英文名称说明TXDTransmit Data串口发送引脚，用于发送数据。RXDReceive…...

编程日记 2025/5/12 8:51:55

阿里云大模型ACP高级工程师认证模拟试题

阿里云大模型ACP高级工程师认证模拟试题 0. 引言1. 模拟试题单选题多选题单选题多选题单选题多选题单选题多选题单选题多选题单选题多选题单选题多选题单选题多选题单选题多选题单选题多选题单选题多选题单选题多选题单选题多选题单选题单选题单选题多选题多选题单选题多选题单…...

编程日记 2025/5/11 13:04:44

深入理解计算机中的补码、反码、原码

问题： 我们每天用的钟表，其实只有1~12这12个数字，但我们日常会说13点、17点之类的。问：13点在钟表上哪个位置？ 答：很简单嘛，1点的位置。你不觉得奇怪吗，为啥13点会和1点在同一个位…...

编程日记 2025/5/7 23:17:58

调试：用电脑开发移动端网页，然后用手机真机调试

一、背景电脑开发移动端，然后想真机调试... 二、实现 2.1、电脑和手机链接相同局域网 2.2、pnpm run dev 启动项目 2.3、浏览器访问 localhost:3001/login 2.4、Windowsr 输入cmd，在cmd输入 ipconfig 2.5、浏览器访问 ip地址加/login 2.6、手机端…...

编程日记 2025/5/16 5:12:20

深入浅出：Spring Boot 自定义消息转换器的实现与应用

Spring Boot 作为当前最流行的 Java Web 开发框架之一，广泛应用于微服务架构、企业级应用等多个场景。Spring Boot 提供了灵活且易于扩展的架构，其中消息转换器（Message Converter）是其重要组成部分。消息转换器在 Spring Boot 中…...

编程日记 2025/5/14 3:45:04

基于AI大模型的医院SOP优化：架构、实践与展望

一、引言 1.1 研究背景与意义近年来，人工智能（AI）技术取得了迅猛发展，尤其是大模型的出现，为各个领域带来了革命性的变化。在医疗领域，AI 医疗大模型正逐渐崭露头角，展现出巨大的应用潜力。随着医疗数据的海量积累以及计算能力的大幅提升，AI 医疗大模型能够对复杂的…...

编程日记 2025/5/14 6:34:35

Maven项目集成SQL Server的完整教程：从驱动配置到封装优化

前言在最近的系统对接过程中，由于对方团队不熟悉技术，最终选择直接提供 SQL Server 视图。本文详细记录了使用 Maven 集成 SQL Server 驱动的过程，以及从配置到查询的各个关键步骤，还包括注意事项与常见问题，希望对需…...

编程日记 2025/5/13 22:03:06

Java 21 优雅和安全地处理 null

在 Java 21 中，判断 null 依然是开发中常见的需求。通过使用现代 Java 提供的工具和特性，可以更加优雅和安全地处理 null。 1. 使用 Objects.requireNonNull Objects.requireNonNull 是标准的工具方法，用于快速判断并抛出异常。示例 import java.util.Objects;public c…...

编程日记 2025/5/13 0:28:09

Java（四十四）file

Java中的file类：代表文件或者文件夹(目录)类，也就是说将文件或者文件夹通过File类来封装成对象。一：常用的构造方法：使用file类，需要通过构造方法创建一个file对象。 1：public File(String pathname) public static void main(String[] args) {File fl = new File(&…...

编程日记 2025/5/17 13:00:03

【51项目】51单片机自制小霸王游戏机

视频演示效果：纳新作品——小霸王游戏机目录：目录视频演示效果：目录：前言：...

编程日记 2025/4/8 17:33:51

【ArcGISPro/GeoScenePro】检查多光谱影像的属性并优化其外观

数据 https://arcgis.com/sharing/rest/content/items/535efce0e3a04c8790ed7cc7ea96d02d/data 操作其他数据检查影像的属性熟悉检查您正在使用的栅格属性非常重要。...

编程日记 2025/4/29 13:36:11

《新概念模拟电路》-三极管

三极管本系列文章主要学习《新概念模拟电路》中的知识点。在工作过程中，碰到一些问题，于是又翻阅了模电这本书。我翻阅的是ADI出版的，西安交通大学电工中心杨建国老师编写的模电书。 <模电>和《数电》这两本书是电子学的专业基础课&…...

编程日记 2025/5/17 17:46:24

K 近邻算法入门指南：明氏距离与皮尔森距离的基础讲解

1、K近邻算法介绍 K近邻(k-Nearest Neighbor，KNN)分类算法的思路是：在特征空间中，如果一个样本附近的k个最近样本的大多数属于某一个类别，则该样本也属于这个类别。K近邻算法中，所选择的邻居都是已经正确分类的对象。…...

编程日记 2025/5/16 23:57:09

如何验证imap是否生效

要验证您的 Outlook 邮箱是否启用了 IMAP 并且正常工作，可以按照以下步骤进行操作： 1. 确认 Outlook 邮箱是否启用 IMAP 步骤： 登录到您的 Outlook Web 账户： 打开浏览器，访问 Outlook.com 或 Microsoft 365 Outlook…...

编程日记 2025/5/17 15:47:44

MySQL 06 章——多表查询

多表查询，也称为关联查询，是指两个表或多个表一起完成查询操作前提条件，这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段的。这个关联字段可能建立了外键，也可能没…...

编程日记 2025/5/14 23:42:43

转换VMware Esxi 虚拟机到 Windows2019 Hyper-V Server

Hyper-v专用P2V工具disk2vhd实际应用工具介绍 disk2vhd是一个非常小的P2V转换工具，可以将你的物理服务器或Esxi vm 转换成为VHD或者vhdx格式的虚拟硬盘文件，然后在虚拟平台上作为一台虚拟机来使用。目前disk2vhd的最新版本是2.0.1，已经可以…...

编程日记 2025/5/16 10:40:27

头歌实训2-1：面向对象程序设计-基础部分

第1关：定义银行员工类BankEmployee 本关任务：编写银行员工类BankEmployee，要求： 1.银行员工类的属性包括姓名name，工号num，工资salary 2.姓名name和工号num设置为私有属性,并将salay设置为默认参数3000 平…...

编程日记 2025/5/15 12:27:38

超高分辨率图像分割处理

文章大纲制造业半导体领域高分辨率图像半导体数据集开源的高分辨率晶圆图像数据集1. WM-811K数据集2. Kaggle上的WM-811K Clean Subset数据集医疗病理领域高分辨率图像1. Camelyon+2. CAMELYON173. CPIA Dataset4. UCF-WSI-Dataset航拍遥感中的高分辨率图像航拍遥感领域高分…...

编程日记 2025/5/15 21:21:59

使用 apply 方法将其他列的值传入 DataFrame 或 Series 的函数，来进行更灵活的计算或操作

可以使用 apply 方法将其他列的值传入 DataFrame 或 Series 的函数，来进行更灵活的计算或操作。apply 方法允许你逐行或逐列地对 DataFrame 或 Series 的元素进行操作，而且你可以将其他列的值作为参数传递给函数。示例：使用 apply 结合其他…...

编程日记 2025/5/12 8:56:55

[CTF/网络安全] 攻防世界 warmup 解题详析

查看页面源代码，发现source.php 得到一串代码，进行代码审计： <?phpclass emmm{public static function checkFile(&$page){$whitelist ["source">"source.php","hint">"hint.php"];…...

编程日记 2025/5/16 21:36:04

力扣第389题—找不同

class Solution:def findTheDifference(self, s: str, t: str) -> str:# 对字符串 s 和 t 进行排序a sorted(s)b sorted(t)# 比较排序后的两个列表for i in range(len(a)):if a[i] ! b[i]:return b[i]# 如果前面的比较没有找到差异，那么差异字符在 t 的最后一个…...

编程日记 2025/5/7 3:32:38

vite6+vue3+ts+prettier+eslint9配置前端项目（后台管理系统、移动端H5项目通用配置）

很多小伙伴苦于无法搭建一个规范的前端项目，导致后续开发不规范，今天给大家带来一个基于Vite6TypeScriptVue3ESlint9Prettier的搭建教程。目录一、基础配置1、初始化项目2、代码质量风格的统一2.1、配置prettier2.2、配置eslint2.3、配置typescript 3、…...

编程日记 2025/5/11 14:05:48

滴滴数据分析80道面试题及参考答案

如何衡量分类好坏？衡量分类好坏有多种方法，常用的有准确率、精确率、召回率、F1 值、ROC 曲线与 AUC 值等。准确率：是指分类正确的样本数占总样本数的比例，计算公式为：准确率 = （分类正确的样本数）/（总样本数）。准确率越高，说明分类器整体的分类效果越好，但在正负…...

编程日记 2025/5/11 12:53:04

嵌入式应用软件开发中C语言方向面试题

嵌入式应用软件开发中C语言方向面试题随笔前言一、C语言基础二、嵌入式开发相关三、硬件相关知识五、实际编程问题前言做嵌入式开发这么多年了，简单记录下C语言方向常见面试题，这里是应用软件方向的。一、C语言基础 C语言的指针与数组的区别是什么？指针：指针是一个变量…...

编程日记 2025/5/17 16:54:08

vue3中mixins替代方案

使用自定义 Hooks（Composables） 自定义 Hooks 是一种基于函数的代码复用方式，可以在 setup 函数中使用。它允许将组件的逻辑分割成更小的、可复用的部分。 useCounter.js //useCounter.js import { ref, onMounted } from vue;export func…...

编程日记 2025/5/11 7:44:59

线性代数自学资源推荐我的个人学习心得

1.前言自己这个学期的课程基本上就结束了，因此我自己就开始学习下个学期的课程--线性代数，也是我们在大学里面的最后一门数学课程了； 之前有过一些这个线性代数的基础，当时主要是参加这个数学建模比赛去学习这个matlab吗&#…...

编程日记 2025/5/16 6:42:00

WordPress Crypto 插件身份认证绕过漏洞复现(CVE-2024-9989)

0x01 产品简介 WordPress Crypto插件是指那些能够为WordPress网站提供加密货币支付、信息显示或交易功能的插件。这些插件通常与WordPress电子商务插件（如WooCommerce）集成，使网站能够接受多种加密货币支付，或展示加密货币实时信息。支持多种加密货币支付，付款直接进入钱…...

编程日记 2025/5/16 9:34:18