postgresql - Calculate quarterly percentage increase and decrease in sql - Stack Overflow

admin管理员组

文章数量:1334943

I can simply use LAG() to calculate the percentage change from my table (see below) of this month compared to the data 3 months ago or 1 year ago.

But now, I want to get this result: calculate the sum of each quarter, and then get the percentage change (Q1 of the following year needs to be calculated with reference to Q4 of the previous year.)

For example:

year	quarter	total_num	quarter_change_pct
2021	1	1088030	0.00%
2021	2	1077857	-0.93%
2021	3	1048368	-2.74%
2021	4	992279	-5.35%
2022	1	1026123	3.41%
2022	2	1074024	4.67%
2022	3	1054501	-1.82%
2022	4	1080568	2.47%
2023	1	1001410	-7.33%
2023	2	961672	-3.97%
2023	3	979835	1.89%
2023	4	982167	0.24%

I can simply use LAG() to calculate the percentage change from my table (see below) of this month compared to the data 3 months ago or 1 year ago.

But now, I want to get this result: calculate the sum of each quarter, and then get the percentage change (Q1 of the following year needs to be calculated with reference to Q4 of the previous year.)

For example:

year	quarter	total_num	quarter_change_pct
2021	1	1088030	0.00%
2021	2	1077857	-0.93%
2021	3	1048368	-2.74%
2021	4	992279	-5.35%
2022	1	1026123	3.41%
2022	2	1074024	4.67%
2022	3	1054501	-1.82%
2022	4	1080568	2.47%
2023	1	1001410	-7.33%
2023	2	961672	-3.97%
2023	3	979835	1.89%
2023	4	982167	0.24%

I don't know how to solve this problem, it would be greatly appreciated if anyone can help.(BTW, the database I use supports the majority of PostgreSQL syntax, so you can also use PostgreSQL for demo, thanks!)

sample data:

create table tb1(
  date date,
  num int
);

insert into tb1 values
('2021-01-31', 359738),
('2021-02-28', 378564),
('2021-03-31', 349728),
('2021-04-30', 368945),  
('2021-05-31', 321456),
('2021-06-30', 387456),
('2021-07-31', 310567),
('2021-08-31', 342189),
('2021-09-30', 395612),
('2021-10-31', 278945),
('2021-11-30', 365478),
('2021-12-31', 347856),
('2022-01-31', 319478),
('2022-02-28', 382456),
('2022-03-31', 324189),
('2022-04-30', 395612),  
('2022-05-31', 367845),
('2022-06-30', 310567),
('2022-07-31', 382456),
('2022-08-31', 347856),
('2022-09-30', 324189),
('2022-10-31', 395612),
('2022-11-30', 319478),
('2022-12-31', 365478),
('2023-01-31', 302856),
('2023-02-28', 334531),
('2023-03-31', 364023),
('2023-04-30', 334534),  
('2023-05-31', 313678),
('2023-06-30', 313460),
('2023-07-31', 357281),
('2023-08-31', 314578),
('2023-09-30', 307976),
('2023-10-31', 304567),
('2023-11-30', 311378),
('2023-12-31', 366222);

My table looks like this(simplified):

date	num
2021-01-31	359738
2021-02-28	378564
2021-03-31	349728
2021-04-30	368945
2021-05-31	321456
2021-06-30	387456
2021-07-31	310567
2021-08-31	342189
2021-09-30	395612
2021-10-31	278945
2021-11-30	365478
2021-12-31	347856
2022-01-31	319478
2022-02-28	382456
2022-03-31	324189
2022-04-30	395612
2022-05-31	367845
2022-06-30	310567
2022-07-31	382456
2022-08-31	347856
2022-09-30	324189
2022-10-31	395612
2022-11-30	319478
2022-12-31	365478
2023-01-31	302856
2023-02-28	334531
2023-03-31	364023
2023-04-30	334534
2023-05-31	313678
2023-06-30	313460
2023-07-31	357281
2023-08-31	314578
2023-09-30	307976
2023-10-31	304567
2023-11-30	311378
2023-12-31	366222

• sql
• postgresql
• pieclouddb

Share Improve this question Follow edited Nov 21, 2024 at 10:19 Luuk 15k55 gold badges2727 silver badges4343 bronze badges asked Nov 21, 2024 at 6:21 JoeJoe 8955 bronze badges 1

• 1 I did edit your question. Now it starts with the question, and the long list of data is put add the end, because it's less important than the question. Because it's only needed when the problem is clear.... – Luuk Commented Nov 21, 2024 at 10:21

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

You have to use LAG and Quarter function to solve the issue in ProgreSQL.

QUARTER function return one of the following values:

• 1 if the date falls in January, February or March.
• 2 if the date falls in April, May or June.
• 3 if the date falls in July, August or September.
• 4 if the date falls in October, November or December.

LAG() function is a powerful window function that allows you to access data from a previous row within the same result set. It’s particularly useful for comparing values in the current row with values in the preceding row.

Finally, to get your result set the SQL script will be .

SELECT Year, QUARTER, total_num,  
to_char((total_num-(LAG(total_num,1) OVER (ORDER BY Year, QUARTER))) * 100 /(LAG(total_num,1) OVER (ORDER BY Year,QUARTER)), 'S990D99%')  as PCT 
FROM 
(SELECT EXTRACT('Year' FROM DATE) AS Year, 
EXTRACT(QUARTER FROM DATE) AS QUARTER, 
CAST (SUM(NUM) AS DECIMAL) AS total_num FROM tb1 GROUP BY Year, QUARTER 
ORDER BY Year,QUARTER);

Implement this work on PostgreSQL 17.

Thanks

This query may be a little complicated, but the result meets your requirements. I hope it can help you.

test on PostgreSQL 16: https://dbfiddle.uk/7W1SW7KH

WITH QuarterlyTotals AS (
  SELECT
    EXTRACT(YEAR FROM date) AS year,
    EXTRACT(QUARTER FROM date) AS quarter,
    SUM(num) AS total_num
  FROM tb1
  GROUP BY year,quarter
),
QuarterlyChanges AS (
  SELECT
    t1.year,
    t1.quarter,
    t1.total_num,
    COALESCE(t2.total_num, t1.total_num) AS prev_quarter_total
  FROM QuarterlyTotals t1
  LEFT JOIN QuarterlyTotals t2 ON (
    t1.year = t2.year AND t1.quarter = t2.quarter + 1
    OR (t1.year = t2.year + 1 AND t1.quarter = 1 AND t2.quarter = 4)
  )
)
SELECT
  year,
  quarter,
  total_num,
  ROUND((total_num - prev_quarter_total)::numeric / prev_quarter_total * 100, 2) || '%' AS quarter_change_pct
FROM QuarterlyChanges
ORDER BY  year, quarter;

本文标签： postgresqlCalculate quarterly percentage increase and decrease in sqlStack Overflow