POSTGRESQL中如何利用SQL语句快速的进行同环比？

1. 引言

在数据驱动的时代，了解销售、收入或任何业务指标的同比和环比情况对企业决策至关重要。本文将深入介绍如何利用 PostgreSQL 和 SQL 语句快速、准确地进行这两种重要分析。

2. 数据准备

为了演示，假设我们有一张 sales 表，存储了销售数据，包括 date（日期）、product_id（产品ID）、revenue（收入）等字段。首先，确保数据准备工作：

CREATE TABLE sales (
    date DATE,
    product_id INT,
    revenue DECIMAL(10, 2)
);

INSERT INTO sales VALUES
    ('2020-01-01', 1, 400),
    ('2020-01-02', 1, 300),
    ('2020-01-01', 2, 3000),
    ('2020-01-02', 2, 3200),
    ('2022-01-01', 1, 500),
    ('2022-01-02', 1, 600),
    ('2022-01-01', 2, 1200),
    ('2022-01-02', 2, 1900),
    ('2023-01-01', 1, 1000),
    ('2023-01-02', 1, 1200),
    ('2023-01-01', 2, 800),
    ('2023-01-02', 2, 900);

插入上述数据后，进行数据查询：

SELECT
	* 
FROM
	sales 
ORDER BY
	product_id,
	DATE;

查询结果如下：
1

3. 时间序列数据处理

处理时间序列数据是同比和环比分析的关键。确保日期字段以正确的数据类型存储：

ALTER TABLE sales
ALTER COLUMN date SET DATA TYPE DATE;

4. 同比分析

同比分析是比较同一时间段内不同年份数据的变化情况。

4.1 对两年的数据进行对比

比如我们现在想看各年的总收入和平均收入。

SELECT
    EXTRACT(YEAR FROM date) AS year,
		sum(revenue) as sum_revenue,
		count(revenue) as count_revenue,
    AVG(revenue) AS avg_revenue
FROM sales
GROUP BY year
ORDER BY year;

运行后，结果如下：
2

4.2 计算两年的差额和同比

不考虑日期不连续的情况，即销售数据在原始序列中是每年连续的，如数据源中的2022年和2023年收入数据。代码如下：

--计算同比
WITH yearly_revenue AS (
    SELECT
        EXTRACT(YEAR FROM date) AS year,
				sum(revenue) as year_total_revenue,
        AVG(revenue) AS year_avg_revenue
    FROM sales
		WHERE EXTRACT(YEAR FROM date) in (2022,2023)
    GROUP BY year
)
select 
year,
year_total_revenue,
year_avg_revenue,
lag(year_total_revenue) over (partition by null order by year ) as pre_year_total_revenue, --计算去年的收入
COALESCE(year_total_revenue - LAG(year_total_revenue) OVER (ORDER BY year) , 0) AS yoy_growth_value, --计算各年之间的收入差额
COALESCE((year_total_revenue - LAG(year_total_revenue) OVER (ORDER BY year)) / NULLIF(LAG(year_total_revenue) OVER (ORDER BY year), 0) * 100, 0) AS yoy_growth_rate, --计算两年之间的增长比例
lag(year_avg_revenue) over (partition by null order by year ) as pre_year_avg_revenue, --计算去年的平均收入
COALESCE((year_avg_revenue - LAG(year_avg_revenue) OVER (ORDER BY year)) / NULLIF(LAG(year_avg_revenue) OVER (ORDER BY year), 0) * 100, 0) AS yoy_avg_growth_rate --计算平均收入增长比例
from yearly_revenue;

运行上述代码后，可以直接进行计算收入的同比数据，上述代码考虑了去年收入为0和为null的情况，运行后结果如下：

3

考虑日期不连续的情况，即销售数据在原始序列中是每年连续的，如数据源中的2020年和2022年收入数据。代码如下：

WITH yearly_revenue AS (
    SELECT
        EXTRACT(YEAR FROM date) AS year,
        SUM(revenue) AS year_total_revenue,
        AVG(revenue) AS year_avg_revenue
    FROM sales
    GROUP BY year
)
SELECT
  current_year.year,
  current_year.year_total_revenue,
  previous_year.year_total_revenue AS last_year_total_revenue,
  previous_year.year_avg_revenue AS last_year_avg_revenue,
	COALESCE(current_year.year_total_revenue - previous_year.year_total_revenue,0)   yoy_growth_value,
	COALESCE(current_year.year_total_revenue / nullif(previous_year.year_total_revenue,0)-1,0) * 100  yoy_growth_rate
--   ,CASE
--     WHEN previous_year.year_total_revenue IS NOT NULL THEN
--       (current_year.year_total_revenue - previous_year.year_total_revenue) / previous_year.year_total_revenue * 100
--     ELSE
--       NULL
--   END AS year_on_year_growth
FROM
  yearly_revenue current_year
LEFT JOIN
  yearly_revenue previous_year ON current_year.year = previous_year.year + 1
-- WHERE 
-- 	previous_year.year_total_revenue is not null
ORDER BY
  current_year.year;

运行代码后，结果如下：
4

4.3 细分后的同比计算

我们只需要将上述的代码进行简单的修改后，就可以统计细分到任意维度的同比计算。代码如下：

	WITH yearly_revenue AS (
    SELECT
        EXTRACT(YEAR FROM date) AS year,
				product_id,
        SUM(revenue) AS year_total_revenue,
        AVG(revenue) AS year_avg_revenue
    FROM sales
    GROUP BY year,product_id
)
SELECT
  current_year.year,
  current_year.product_id,
  current_year.year_total_revenue,
  previous_year.year_total_revenue AS last_year_total_revenue,
  previous_year.year_avg_revenue AS last_year_avg_revenue,
	COALESCE(current_year.year_total_revenue - previous_year.year_total_revenue,0)   yoy_growth_value,
	COALESCE(current_year.year_total_revenue / NULLIF(previous_year.year_total_revenue, 0) - 1, 0) * 100  yoy_growth_rate
--   ,CASE
--     WHEN previous_year.year_total_revenue IS NOT NULL THEN
--       (current_year.year_total_revenue - previous_year.year_total_revenue) / previous_year.year_total_revenue * 100
--     ELSE
--       NULL
--   END AS year_on_year_growth
FROM
  yearly_revenue current_year
LEFT JOIN
  yearly_revenue previous_year ON current_year.year = previous_year.year + 1 and current_year.product_id = previous_year.product_id
-- WHERE 
-- 	previous_year.year_total_revenue is not null
ORDER BY
  current_year.year,current_year.product_id;

运行上述代码后，结果如下：
5

5. 环比分析

环比分析是比较相邻时间段的数据变化情况。

5.1 简单的日期环比计算

不考虑数据缺失的情况下，如果要对2023年product_id为1的产品进行环比计算，可以使用以下代码进行简单的环比计算：

SELECT
    date,
    revenue,
    LAG(revenue) OVER (ORDER BY date) AS prev_revenue,
    (revenue - LAG(revenue) OVER (ORDER BY date)) / LAG(revenue) OVER (ORDER BY date) * 100 AS growth_rate
FROM sales
WHERE
		extract(year from date) in (2023) and product_id in (1);

筛选后的数据：
5.1.1

进行计算后的数据：
5.1.2

5.2 先聚合再进行环比计算

在不考虑日期缺失情况下，如果我们要计算2023年的收入环比，那么我们就需要先按照日期进行聚合，然后再进行环比计算。这里有两种方法，代码如下：

-- 计算写法1
WITH daily_revenue AS (
    SELECT
        date,
				sum(revenue) as day_total_revenue
    FROM sales
    GROUP BY date
)
select 
*,
LAG(day_total_revenue) OVER (ORDER BY day_total_revenue) AS prev_revenue,
COALESCE((day_total_revenue - LAG(day_total_revenue) OVER (ORDER BY date)),0) day_growth_value,
COALESCE((day_total_revenue - LAG(day_total_revenue) OVER (ORDER BY date)) / LAG(day_total_revenue) OVER (ORDER BY date) * 100,0) AS day_growth_rate
from daily_revenue
WHERE EXTRACT(YEAR FROM date) in (2023);

#计算写法2
SELECT
    date,
    sum(revenue),
		LAG(sum(revenue)) OVER (ORDER BY date) AS prev_revenue,
		COALESCE((sum(revenue) - LAG(sum(revenue)) OVER (ORDER BY date)),0) day_growth_value,
    COALESCE((sum(revenue) - LAG(sum(revenue)) OVER (ORDER BY date)) / LAG(sum(revenue)) OVER (ORDER BY date) * 100,0) AS growth_rate
FROM sales
WHERE
		extract(year from date) in (2023)
		group by date;

无论那个代码都可以，运行后结果如下：
5.2.1

5.3 考虑日期不连续的环比计算

然而在现实统计中，我们的日期往往是不连续的，因此可以考虑下面的思路：

  • 1、先按照所需维度进行如何；
  • 2、进行日期拼接和计算

代码如下：

-- 1.先聚合到指定维度		
WITH daily_revenue AS (
		SELECT 
				DATE, 
				SUM ( revenue )	AS day_total_revenue 
		FROM sales 
		GROUP BY DATE 
) 
-- 2.再进行拼接
SELECT
		current_day.DATE,
		current_day.day_total_revenue,
		prev_day.day_total_revenue prev_day_total_revenue,
		COALESCE ( current_day.day_total_revenue - prev_day.day_total_revenue, 0 ) day_growth_value,
		COALESCE ( current_day.day_total_revenue / NULLIF ( prev_day.day_total_revenue, 0 ) - 1, 0 ) * 100 day_growth_rate  --处理异常情况
FROM
	daily_revenue current_day
	LEFT JOIN daily_revenue prev_day ON DATE_TRUNC( 'day', current_day.DATE ) = DATE_TRUNC( 'day', prev_day.DATE ) + INTERVAL '1 day' 
-- WHERE 
-- prev_day.day_total_revenue is not null
	
ORDER BY
	DATE;

运行后，效果如下：
5.3.1

6. 性能优化技巧

数据库性能是关键，特别是在处理大量数据时。

-- 为 date 列创建索引
CREATE INDEX idx_date ON sales (date);
-- 向上方一样，采用视图
WITH daily_revenue AS (
		SELECT 
				DATE, 
				SUM ( revenue )	AS day_total_revenue 
		FROM sales 
		GROUP BY DATE 
) SELECT *
FROM
	daily_revenue;

7. 注意事项与常见问题

数据规范性和异常值处理是关键。确保日期格式正确，避免数据异常对分析造成的影响。

8. 结语

本文介绍了在 PostgreSQL 中利用 SQL 进行同比和环比分析的方法。从数据准备到复杂场景下的 SQL 查询，每一步都经过详细解释和示例演示。这些技能不仅能提升数据分析效率，还能为业务决策提供重要支持。利用这些方法，你可以更加准确、快速地分析业务数据，为企业带来更大价值。

希望这篇文章能帮助你更好地利用 SQL 在 PostgreSQL 中进行同比和环比分析！

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

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

微信订阅号和服务号的区别

微信订阅号和服务号的区别

服务号和订阅号有什么区别？服务号转为订阅号有哪些作用？我们都知道，服务号一个月只能发4次文章，但是订阅号每天都能发文章。不过在接收消息这一方面，服务号群发的消息有消息提醒，并显示在对话框&#xff1b…
阅读更多...
重新认识Word——样式

重新认识Word——样式

重新认识Word Word样式给所有一级标题加上一级标题样式修改标题一样式，符合要求 正文样式标题前的小黑点导航窗格样式的相互复制Word一键转PPT 话说回来，一个程序员平时可能还看不起office全家桶的软件，但是，在实际的生活运用中&a…
阅读更多...
音视频的功耗优化

音视频的功耗优化

前言 在应用中，录制与音视频模块往往是高耗能的模块，设备容易发热，影响体验。 什么是功耗优化 手机有多个耗电模块， SOC(CPU，GPU，DDR)，Display，Audio，Video&#xff0…
阅读更多...
thinkphp 5.1 对数据库查出来的字段进行预处理

thinkphp 5.1 对数据库查出来的字段进行预处理

比如数据库的设计是下面这样子&#xff1a; 我想展示的是这个样子&#xff1a; 前端可以处理。 Think PHP的处理方式&#xff1a; 定义属性 &#xff1a; $this->customize 任意值;//这里的之没有作用 <?phpnamespace app\hs\controller\shop;use app\daogou\mo…
阅读更多...
Windows用户相关Dos命令演示

Windows用户相关Dos命令演示

Windows用户相关Dos命令演示 1、查看当前用户 命令&#xff1a;whoami 2、查看主机名 命令: hostname 3、查看所有用户 命令&#xff1a;net user 4、查看指定的用户 命令&#xff1a;net user 用户名 5、添加用户 命令&#xff1a;net user 用户名 密码 /add 注…
阅读更多...
数据链路层之VLAN基本概念和基本原理

数据链路层之VLAN基本概念和基本原理

学习的最大理由是想摆脱平庸&#xff0c;早一天就多一份人生的精彩&#xff1b;迟一天就多一天平庸的困扰。各位小伙伴&#xff0c;如果您&#xff1a; 想系统/深入学习某技术知识点… 一个人摸索学习很难坚持&#xff0c;想组团高效学习… 想写博客但无从下手&#xff0c;急需…
阅读更多...
ASP.NET Core MVC过滤器

ASP.NET Core MVC过滤器

1、过滤器分为授权过滤、资源访问过滤、操作方法&#xff08;Action&#xff09;过滤、结果过滤、异常过滤、终结点过滤。上一次咱们没有说异常过滤和终结点过滤&#xff0c;不过老周后面会说的。对这些过滤器&#xff0c;你有印象就行了。 2、所有过滤器接口都有同步版本和异…
阅读更多...
css 3D背景反转实现

css 3D背景反转实现

body{/* 透视 */perspective: 800px; } div{transform-style:preserve-3d;width:259px;height:396px;margin: 100px auto;position: relative; } div img{position: absolute;width:259px;height:396px;left:0;top:0;transition: all linear 2s;z-index: 0; } div img:nth-chil…
阅读更多...
[前 5 名] 最顶级的数据恢复软件解决方案列表

[前 5 名] 最顶级的数据恢复软件解决方案列表

您是否在互联网上找到适用于 Windows PC 的前 5 名最受好评的数据恢复软件解决方案&#xff1f;嗯&#xff0c;在线市场上有很多工具可以恢复已删除的文件。但并不是所有的应用程序都值得使用它。值得信赖的文件恢复工具将有助于快速检索丢失、删除、格式化的数据并从计算机恢复…
阅读更多...
flink源码分析之功能组件(四)-slot管理组件II

flink源码分析之功能组件(四)-slot管理组件II

简介 本系列是flink源码分析的第二个系列&#xff0c;上一个《flink源码分析之集群与资源》分析集群与资源&#xff0c;本系列分析功能组件&#xff0c;kubeclient&#xff0c;rpc&#xff0c;心跳&#xff0c;高可用&#xff0c;slotpool&#xff0c;rest&#xff0c;metrics&…
阅读更多...
机器学习笔记 - 异常检测之OneClass SVM算法简述

机器学习笔记 - 异常检测之OneClass SVM算法简述

一、异常检测是什么？ 如下图，理想中我们可以找到一个框住大部分正常样本的决策边界，而在边界外部的数据点（蓝点）即视为异常。 但实际情况下数据都没有标签，因此很难定义正常还是不正常。异常检测的主要挑战如下：正常与异常行为之间的界限往往并不明确、不同的应…
阅读更多...
WEB安全之Python

WEB安全之Python

WEB安全之python python-pyc反编译 python类似java一样&#xff0c;存在编译过程&#xff0c;先将源码文件*.py编译成 *.pyc文件&#xff0c;然后通过python解释器执行 生成pyc文件 创建一个py文件随便输入几句代码(1.py) 通过python交互终端 >>>import py_compil…
阅读更多...
测试Centos上用Gunicorn启动的Django-Web服务在Django源文件有改变的情况下能否自动重载最新源码下的web服务

测试Centos上用Gunicorn启动的Django-Web服务在Django源文件有改变的情况下能否自动重载最新源码下的web服务

01-先上传最新的源码文件 参考博文 https://blog.csdn.net/wenhao_ir/article/details/134762966 进行 02-先在Django直接开web服务下修改源码测试 这是没有问题的&#xff0c;会自己重置。 03-开启gunicorn服务 cd /djangoproject/mmdj01/ gunicorn -c /djangoproject/mm…
阅读更多...
泊车功能专题介绍 ———— 汽车全景影像监测系统性能要求及试验方法（国标未公布）

泊车功能专题介绍 ———— 汽车全景影像监测系统性能要求及试验方法（国标未公布）

文章目录 术语和定义一般要求功能要求故障指示 性能要求响应时间图像时延单视图视野范围平面拼接视图视野平面拼接效果总体要求行列畸变拼接错位及拼接无效区域 试验方法环境条件仪器和设备车辆条件系统响应时间试验图像时延试验单视图视野范围试验平面拼接视图视野试验平面拼接…
阅读更多...
【算法专题】二分查找

【算法专题】二分查找

二分查找 二分查找1. 二分查找2. 在排序数组中查找元素的第一和最后一个位置3. 搜索插入位置4. x 的平方根5. 山脉数组的峰顶索引6. 寻找峰值7. 寻找旋转排序数组中的最小值8. 点名 二分查找 1. 二分查找 题目链接 -> Leetcode -704.二分查找 Leetcode -704.二分查找 题…
阅读更多...
【SpringBoot】讲清楚日志文件lombok

【SpringBoot】讲清楚日志文件lombok

文章目录 前言一、日志是什么&#xff1f;二、⽇志怎么⽤&#xff1f;三.自定义打印日志3.1在程序中得到日志对象3.2使用日志打印对象 四.⽇志级别4.1日志级别有什么用4.2 ⽇志级别的分类与使⽤ 五.日志持久化六.lombok6.1添加lobok依赖注意&#xff1a;使⽤ Slf4j 注解&#x…
阅读更多...
vue权限管理解决方案

vue权限管理解决方案

一. 什么是权限管理 权限控制是确保用户只能访问其被授权的资源和执行其被授权的操作的重要方面。而前端权限归根结底是请求的发起权&#xff0c;请求的发起可能有下面两种形式触发 页面加载触发页面上的按钮点击触发 总体而言&#xff0c;权限控制可以从前端路由和视图两个…
阅读更多...
【算法刷题】Day11

【算法刷题】Day11

文章目录 面试题 08.01. 三步问题题干&#xff1a;算法原理&#xff1a;1、状态表示2、状态转移方程3、初始化4、填表顺序5、返回值 代码&#xff1a; 209. 长度最小的子数组题干&#xff1a;算法原理&#xff1a;1、暴力枚举出所有的子数组的和2、利用单调性&#xff0c;使用“…
阅读更多...
通过查看ThreadLocal的源码进行简单理解

通过查看ThreadLocal的源码进行简单理解

目录 为什么要使用ThreadLocal&#xff1f; 简单案例 ThreadLocal源码分析 断点跟踪 为什么要使用ThreadLocal 在多线程下&#xff0c;如果同时修改公共变量可能会存在线程安全问题&#xff0c;JDK虽然提供了同步锁与Lock等方法给公共访问资源加锁&#xff0c;但在高并发…
阅读更多...
光学3D表面轮廓仪超0.1nm纵向分辨能力，让显微形貌分毫毕现

光学3D表面轮廓仪超0.1nm纵向分辨能力，让显微形貌分毫毕现

在工业应用中&#xff0c;光学3D表面轮廓仪超0.1nm的纵向分辨能力能够高精度测量物体的表面形貌&#xff0c;可用于质量控制、表面工程和纳米制造等领域。 与其它表面形貌测量方法相比&#xff0c;光学3D表面轮廓仪达到纳米级别的相移干涉法(PSI)和垂直扫描干涉法(VSI)&#x…
阅读更多...
最新文章

Copyright @ 2022~2023