postgresql 迭代数据并计算每月最后三天的总和

m4pnthwp 于 2023-02-22 发布在 PostgreSQL

关注(0)|答案(2)|浏览(192)

- 一个

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    event_date DATE,
    sales_volume INT
);

INSERT INTO sales
(event_date, sales_volume)
VALUES 
('2023-01-27', '900'),
('2023-02-28', '500'),
('2023-01-29', '100'),
('2023-01-30', '600'),
('2023-01-31', '450'),
  
('2023-02-25', '300'),
('2023-02-26', '250'),
('2023-02-27', '845'),
('2023-02-28', '520'), 

('2023-03-26', '750'),
('2023-03-27', '750'),
('2023-03-28', '625'),
('2023-03-29', '885'),
('2023-03-30', '120'),
('2023-03-31', '400');

- 预期结果：**

| 行|事件时间|销售量|
| - ------|- ------|- ------|
| 1个|1个|小行星1150|
| 第二章|第二章|小行星211|
| 三个|三个|小行星1405|
我想迭代整个表，并计算每个月的sales_volume的和。
目前，我可以使用以下查询来实现这一点：

SELECT
DATE_PART('month', s.event_date) AS event_time,
SUM(s.sales_volume) AS sales_volume
FROM sales s
WHERE s.event_date BETWEEN '2023-01-29' AND '2023-01-31'
OR s.event_date BETWEEN '2023-02-26' AND '2023-02-28'
OR s.event_date BETWEEN '2023-03-29' AND '2023-03-31'
GROUP BY 1
ORDER BY 1;

然而，我的原始数据库比示例大得多，所以我需要很多OR条件。
我需要如何修改查询，以便它在不使用OR条件的情况下遍历表？

postgresql

来源：https://stackoverflow.com/questions/75510557/iterate-through-data-and-calculate-the-sum-of-the-last-three-days-of-each-month

2条答案

按热度按时间

sdnqo3pr1#

demo:db<>fiddle

SELECT
    date_trunc('month', event_date) as month,
    SUM(sales_volume)
FROM (
    SELECT
        *,
        row_number() OVER (              -- 1
            PARTITION BY date_trunc('month', event_date) 
            ORDER BY event_date DESC
        )
    FROM sales 
) s
WHERE row_number <= 3                    -- 2
GROUP BY 1                               -- 3

1.使用row_number() window function按日降序枚举一个月中的日期，因此最后一天的编号为1，倒数第二天的编号为2，依此类推。
1.筛选row_number值〈= 3的记录，这些记录表示该月的最后三天。
1.按date_trunc()函数计算的月份对这些记录进行分组

赞(0）回复(0）举报 2023-02-22

ippsafx72#

下面的查询查找每个日期范围之后的月份的第一天，然后使用子查询查找该日期之前三天的总量：

select row_number() over (order by t.d), (select sum(case when s.event_date >= t.d - interval '3 day' and s.event_date < t.d then s.sales_volume else 0 end) from sales s)
from (select distinct (extract(year from event_date)||'-'||lpad(extract(month from event_date)::text, 2, '0'::text)||'-'||'01')::date + interval '1 month' d from sales) t

See fiddle

赞(0）回复(0）举报 2023-02-22

我来回答

postgresql 迭代数据并计算每月最后三天的总和

2条答案

相关问题

热门标签

最新问答