【PGCCC】使用 Postgres 进行数据分析的窗口函数

SQL 在处理单行数据时，甚至在聚合多行数据时都很有意义。但是，当您想比较已计算的行之间的数据时会发生什么？或者创建数据组并进行查询？输入窗口函数。

窗口函数往往会让人感到困惑 - 但它们是 SQL 中用于数据分析的非常棒的工具。最好的部分是，您不需要图表、花哨的 BI 工具或 AI 即可为利益相关者获取一些可操作且有用的数据。窗口函数可让您快速：

  • 计算累计总数
  • 提供数据组/分区的汇总统计信息
  • 创建排名
  • 执行滞后/超前分析，即将两组独立的数据相互比较
  • 计算移动/滚动平均值

本文将通过简单的电子商务数据架构，演示各种窗口函数及其实际应用。

什么是窗口函数？

Window 函数的部分OVER是创建窗口。令人讨厌的是，window 这个词在任何函数中都没有出现。通常，OVER 部分由另一个函数（聚合函数或数学函数）开头。通常还有一个框架，用于指定您正在查看的行ROWS BETWEEN 6 PRECEDING AND CURRENT ROW。

窗口函数 vs where 子句

窗口函数乍一看有点像 where子句，因为它们查看的是一组数据。但它们确实不同。窗口函数更适合于需要查看多组数据或多组数据的情况。有些情况下您可以使用其中任何一种。一般来说：

当您需要根据条件过滤行时使用WHERE子句。

当您需要对过滤后剩余的行执行计算，而不从结果集中删除任何行时，请使用窗口函数。

窗口函数的实际应用

1.累计

这是一个简单的入门步骤。让我们请求订单、客户数据、订单总额，然后是订单的累计总额。这将向我们显示某个日期范围内的订单总额。

SELECTSUM(total_amount) OVER (ORDER BY order_date) AS running_total,order_date,order_id,customer_id,total_amount
FROMorders
ORDER BYorder_date;

 running_total |     order_date      | order_id | customer_id | total_amount
---------------+---------------------+----------+-------------+--------------349.98 | 2024-08-21 10:00:00 |       21 |           1 |       349.981249.96 | 2024-08-22 11:30:00 |       22 |           2 |       899.981284.94 | 2024-08-23 09:15:00 |       23 |           3 |        34.981374.93 | 2024-08-24 14:45:00 |       24 |           4 |        89.991524.92 | 2024-08-25 08:25:00 |       25 |           5 |       149.991589.90 | 2024-08-26 12:05:00 |       26 |           6 |        64.98

这里发生的事情是，每个数据帧都是现有行加上之前的行。这种计算一次只进行一个切片，您可能在文档中看到它被称为虚拟表。下面是一个图表，可以让您大致了解每个数据帧是如何成为一组行的，这些行由 函数聚合而成SUM OVER。
在这里插入图片描述

2.第一个值和最后一个值

窗口函数可以查看数据组，因此说出特定的客户 ID 并为您提供一些信息，例如他们的第一笔和最后一笔订单、总额以及最近 10 笔订单的信息。

SELECTFIRST_VALUE(o.order_date) OVER (PARTITION BY o.customer_id ORDER BY o.order_date) AS first_order_date,LAST_VALUE(o.order_date) OVER (PARTITION BY o.customer_id ORDER BY o.order_date) AS last_order_date,o.order_id,o.customer_id,o.order_date,o.total_amount
FROMorders o
ORDER BYo.order_date DESC;

 first_order_date   |   last_order_date   | order_id | customer_id |     order_date      | total_amount
---------------------+---------------------+----------+-------------+---------------------+--------------2024-08-30 17:50:00 | 2024-09-19 18:50:00 |       50 |          10 | 2024-09-19 18:50:00 |       149.982024-08-29 13:10:00 | 2024-09-18 14:10:00 |       49 |           9 | 2024-09-18 14:10:00 |       199.982024-08-28 10:20:00 | 2024-09-17 11:20:00 |       48 |           8 | 2024-09-17 11:20:00 |       139.992024-08-27 16:35:00 | 2024-09-16 17:35:00 |       47 |           7 | 2024-09-16 17:35:00 |       249.982024-08-26 12:05:00 | 2024-09-15 13:05:00 |       46 |           6 | 2024-09-15 13:05:00 |        89.98

3.使用 date_trunc GROUP BY CTE 和窗口函数

date_trunc是一个非常方便的 Postgres 函数，可以汇总时间单位、小时、天、周、月。与GROUP BYCTE 中的结合使用时，您可以按日、月、周、年等创建非常简单的汇总统计数据。

当您将 date_trunc GROUP BY 分区与窗口函数结合使用时，会发生一些非常神奇的事情，您可以直接从数据库中获取现成的汇总统计数据。在我看来，这是 Postgres 窗口函数最强大的功能之一，它真正让您更上一层楼。

以下是一个以 CTE 开头的示例查询，调用 date_trunc 按每日总数对订单进行汇总。查询的第二部分是窗口函数，按降序排列销售额，并按销售额最高的日期进行排序。

WITH DailySales AS (SELECTdate_trunc('day', o.order_date) AS sales_date,SUM(o.total_amount) AS daily_total_salesFROMorders oGROUP BYdate_trunc('day', o.order_date)
)
SELECTsales_date,daily_total_sales,RANK() OVER (ORDER BY daily_total_sales DESC) AS sales_rank
FROMDailySales
ORDER BYsales_rank;

  sales_date      | daily_total_sales | sales_rank
---------------------+-------------------+------------2024-09-02 00:00:00 |           2419.97 |          12024-09-01 00:00:00 |           1679.94 |          22024-08-22 00:00:00 |            899.98 |          32024-09-07 00:00:00 |            699.95 |          42024-09-10 00:00:00 |            659.96 |          52024-09-09 00:00:00 |            499.94 |          62024-09-06 00:00:00 |            409.94 |          72024-08-30 00:00:00 |            349.99 |          8

RANK我应该注意到，这使用了窗口函数中一个非常有用的数学函数。

4.滞后分析 (LAG)

让我们使用 date_trunc CTE 做更多事情。现在我们知道我们有按天划分的数据，我们可以使用窗口函数来计算这些组之间的变化。例如，我们可以查看与前一天相比的销售额差异。在此示例中，LAG查看销售日期并与前一天进行比较。

WITH DailySales AS (SELECTdate_trunc('day', o.order_date) AS sales_date,SUM(o.total_amount) AS daily_total_salesFROMorders oGROUP BYdate_trunc('day', o.order_date)
)
SELECTsales_date,daily_total_sales,LAG(daily_total_sales) OVER (ORDER BY sales_date) AS previous_day_sales,daily_total_sales - LAG(daily_total_sales) OVER (ORDER BY sales_date) AS sales_difference
FROMDailySales
ORDER BYsales_date;

     sales_date      | daily_total_sales | previous_day_sales | sales_difference
---------------------+-------------------+--------------------+------------------2024-08-21 00:00:00 |            349.98 |                    |2024-08-22 00:00:00 |            899.98 |             349.98 |           550.002024-08-23 00:00:00 |             34.98 |             899.98 |          -865.002024-08-24 00:00:00 |             89.99 |              34.98 |            55.012024-08-25 00:00:00 |            149.99 |              89.99 |            60.002024-08-26 00:00:00 |             64.98 |             149.99 |           -85.01

LEAD以相同的方式工作，在数据集中向前看。 5.计算滚动平均值

使用同一天的组，我们还可以计算出滚动平均值。该函数接受7 天的滚动销售平均值作为AVG输入。ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

WITH DailySales AS (SELECTdate_trunc('day', o.order_date) AS sales_date,SUM(o.total_amount) AS daily_total_salesFROMorders oGROUP BYdate_trunc('day', o.order_date)
)
SELECTsales_date,daily_total_sales,AVG(daily_total_sales) OVER (ORDER BY sales_dateROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_average_7_days
FROMDailySales
ORDER BYsales_date
LIMIT 10;

     sales_date      | daily_total_sales | rolling_average_7_days
---------------------+-------------------+------------------------2024-08-21 00:00:00 |            349.98 |   349.98000000000000002024-08-22 00:00:00 |            899.98 |   624.98000000000000002024-08-23 00:00:00 |             34.98 |   428.31333333333333332024-08-24 00:00:00 |             89.99 |   343.73250000000000002024-08-25 00:00:00 |            149.99 |   304.98400000000000002024-08-26 00:00:00 |             64.98 |   264.98333333333333332024-08-27 00:00:00 |            249.98 |   262.84000000000000002024-08-28 00:00:00 |            129.99 |   231.41285714285714292024-08-29 00:00:00 |            179.98 |   128.55571428571428572024-08-30 00:00:00 |            349.99 |   173.5571428571428571
(10 rows)

小结

窗口函数是 SQL 中功能强大且灵活的工具，尤其适用于复杂的数据分析任务。通过使用这些函数，您可以轻松执行数据的累计计算、滞后分析、排名生成和滚动统计，而无需借助复杂的外部工具。充分掌握这些函数的使用，将极大地提升您在 SQL 数据分析中的效率和能力。

#PG证书#PG考试#postgresql培训#postgresql考试#postgresql认证

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.xdnf.cn/news/144357.html

如若内容造成侵权/违法违规/事实不符，请联系一条长河网进行投诉反馈，一经查实，立即删除！

相关文章

实验2 Linux文件系统常用操作实践

实验2 Linux文件系统常用操作实践

实验2 Linux文件系统常用操作实践 一、实验介绍 本节实验通过实战Linux文件操作模块的基本操作，需要先掌握linux文件系统的原理以及理解linux文件操作的原理，最后通过实操完成linux文件操作的命令，其中包括改变目录、创建目录及文件、删除文件、复制文件、文件移动和改名、查…
阅读更多...
【大模型入门】零基础入门AI大模型应用开发，你需要一个系统的入门路径！

【大模型入门】零基础入门AI大模型应用开发，你需要一个系统的入门路径！

随着大模型技术的飞速发展，我们正站在一个全新的技术前沿，探索着如何将这些强大的工具应用于实际问题的解决。如果你对AI大模型应用开发充满热情，那么你可以读一下这篇文章——一个系统全面的入门指南，专为渴望深入AI世界的你设计…
阅读更多...
Windows 系统管理

Windows 系统管理

1 安装 Windows Server 2016 操作系统的硬件最低需求？ 处理器 1.4GHz 64 位处理器 RAM 512MB（带桌面体验 2GB） 磁盘空间 32GB 2 Windows Server 2016 操作系统的常见版本？ Windows Server 2016 Standard（ 标 准 版 &a…
阅读更多...
【新手必看】SpringBoot集成Minio实现文件上传、下载和删除

【新手必看】SpringBoot集成Minio实现文件上传、下载和删除

一&#xff0c;前言 安装教程可看我以前的文章&#xff0c;Linux和Windows安装教程都有。安装好minio后今天学习minio的使用。 二&#xff0c;集成Minio 1&#xff0c;导入依赖&#xff0c;版本可自己选择 <!-- https://mvnrepository.com/artifact/io.minio/minio -->…
阅读更多...
LeetCode力扣——并查集：947. 移除最多的同行或同列石头,1971. 寻找图中是否存在路径，2424. 最长上传前缀

LeetCode力扣——并查集：947. 移除最多的同行或同列石头,1971. 寻找图中是否存在路径，2424. 最长上传前缀

947. 移除最多的同行或同列石头 题目描述 947. 移除最多的同行或同列石头 n 块石头放置在二维平面中的一些整数坐标点上。每个坐标点上最多只能有一块石头。 如果一块石头的 同行或者同列 上有其他石头存在&#xff0c;那么就可以移除这块石头。 给你一个长度为 n 的数组 …
阅读更多...
[Golang] Select

[Golang] Select

[Golang] Select 文章目录 [Golang] Select什么是selectselect用法基本用法空select没有default且case永久无法执行单个case和default多个case和default IO多路复用 什么是select select是Golang中一个控制结构&#xff0c;可以用来处理多个channel的发送和接收操作。select会…
阅读更多...
Echats 实现CPK (过程能力)研究报告

Echats 实现CPK (过程能力)研究报告

背景: 实现: Echarts Option 代码示例 option {title: {text: 折线图示例 - X轴为数值},xAxis: {type: value, // X 轴改为数值型min: 0, // 最小值max: 10, // 最大值},yAxis: {type: value},series: [{type: line,data: [[0, 150], [2, 230], [4, 224], [6…
阅读更多...
Photoshop 2021安装教程

Photoshop 2021安装教程

软件介绍 Adobe Photoshop&#xff0c;简称“PS”&#xff0c;是美国Adobe公司旗下最为出名的图像处理软件系列之一。ps 2021新增一键换天空&#xff0c;AI只能滤镜&#xff0c;新增内置的画笔工具极为丰富&#xff0c;成千上万的精致像素、动态和矢量画笔可以满足你的各种绘图…
阅读更多...
论文阅读--Planning-oriented Autonomous Driving（二）

论文阅读--Planning-oriented Autonomous Driving（二）

自动驾驶框架的各种设计比较。 ( a )大多数工业解决方案针对不同的任务部署不同的模型。 ( b )多任务学习方案共享一个具有分割任务头的主干。 ( c )端到端范式将感知和预测模块统一起来。以往的尝试要么采用( c.1 )中对规划的直接优化&#xff0c;要么采用( c.2 )中的部分元…
阅读更多...
【结构型】树形结构的应用王者，组合模式

【结构型】树形结构的应用王者，组合模式

目录 一、组合模式1、组合模式是什么&#xff1f;2、组合模式的主要参与者&#xff1a; 二、优化案例&#xff1a;文件系统1、不使用组合模式2、通过组合模式优化上面代码优化点&#xff1a; 三、使用组合模式有哪些优势1、统一接口&#xff0c;简化客户端代码2、递归结构处理方…
阅读更多...
选址模型 | 基于混沌模拟退火粒子群优化算法的电动汽车充电站选址与定容（Matlab）

选址模型 | 基于混沌模拟退火粒子群优化算法的电动汽车充电站选址与定容（Matlab）

目录 效果一览基本介绍程序设计参考资料 效果一览 基本介绍 基于混沌模拟退火粒子群优化算法的电动汽车充电站选址与定容&#xff08;Matlab&#xff09; 问题建模&#xff1a;首先&#xff0c;需要将电动汽车充电站选址与定容问题进行数学建模&#xff0c;确定目标函数和约束…
阅读更多...
Redis面试真题总结（一）

Redis面试真题总结（一）

文章收录在网站&#xff1a;http://hardyfish.top/ 文章收录在网站&#xff1a;http://hardyfish.top/ 文章收录在网站&#xff1a;http://hardyfish.top/ 文章收录在网站&#xff1a;http://hardyfish.top/ 什么是Redis? Redis是一个高性能的开源内存数据库系统&#xff0…
阅读更多...
Java从入门到精通学习框架（三）

Java从入门到精通学习框架（三）

这一阶段的学习目标是将 Java 的知识从基础提升到实战开发的应用层面&#xff0c;通过对常见的 Java 企业级开发框架的学习和实践&#xff0c;掌握设计模式、分布式系统开发、性能优化等核心技能。在此基础上&#xff0c;学习并应用 Java 的高级特性和最佳实践&#xff0c;使自…
阅读更多...
C#和数据库高级：抽象类和抽象方法

C#和数据库高级：抽象类和抽象方法

文章目录 一、为什么使用抽象类和抽象方法&#xff1f;1.1、父类与子类的相互转换 二、抽象类和抽象方法2.1、抽象类的定义和方法声明规范2.2、使用继承多态的机制解决问题 三、抽象类的概念和使用特点总结 一、为什么使用抽象类和抽象方法&#xff1f; 1.1、父类与子类的相互…
阅读更多...
代码随想录_刷题笔记_第二次

代码随想录_刷题笔记_第二次

链表 — 环形链表 题目链接&#xff1a;142. 环形链表 II - 力扣&#xff08;LeetCode&#xff09; 题目要求&#xff1a; 给定一个链表的头节点 head &#xff0c;返回链表开始入环的第一个节点。 如果链表无环&#xff0c;则返回 null。 如果链表中有某个节点&#xff0c…
阅读更多...
计算机专业的就业方向

计算机专业的就业方向

计算机专业的就业方向 亲爱的新生们&#xff0c;欢迎你们踏上计算机科学的旅程&#xff01;作为一名计算机专业的学生&#xff0c;你们即将进入一个充满无限可能的领域。今天&#xff0c;我将为大家介绍计算机专业的一些主要就业方向&#xff0c;帮助你们了解未来的职业选择。…
阅读更多...
（黑马点评）二、短信登录功能实现

（黑马点评）二、短信登录功能实现

2.1 基于传统Session实现的短信登录及其校验 2.1.1 基于Session登录校验的流程设计 2.1.2 实现短信验证码发送功能 请求接口/user/code请求类型post请求参数phone返回值无 /*** 发送手机验证码*/PostMapping("/code")public Result sendCode(RequestParam("ph…
阅读更多...
Ubunutu 的 Bash 没有颜色

Ubunutu 的 Bash 没有颜色

终端没有颜色&#xff1a; 取消注释 force_color_promptyes &#xff1a; 这时候就有颜色了&#xff1a;
阅读更多...
three.js shader 实现天空中白云

three.js shader 实现天空中白云

three.js shader 实现天空中白云 预览&#xff1a; https://threehub.cn/#/codeMirror?navigationThreeJS&classifyshader&idwhiteCloud 更多案例 可见 预览&#xff1a; https://threehub.cn import * as THREE from "three"; import { OrbitControls …
阅读更多...
按摩上门预约小程序源码系统 在线评价+即时服务 带完整的安装代码包以及搭建部署教程

按摩上门预约小程序源码系统 在线评价+即时服务 带完整的安装代码包以及搭建部署教程

系统概述 按摩上门预约小程序源码系统是一款专为按摩行业量身定制的移动端应用解决方案。它利用先进的互联网技术&#xff0c;将传统按摩服务与线上平台相结合&#xff0c;实现了用户与服务商之间的无缝对接。该系统不仅简化了预约流程&#xff0c;提高了服务效率&#xff0c;…
阅读更多...
最新文章

Copyright @ 2022~2023