Блог

Главная - Вопросы по программированию - запрос получает каждую последнюю дату за месяц

запрос получает каждую последнюю дату за месяц

#sql #postgresql

#sql #postgresql

Вопрос:

у меня есть такая таблица

Дата сумма
2021-01-01 100
2021-01-15 200
2021-01-31 300
2021-02-01 400
2021-02-15 500
2021-02-28 600
2021-03-01 700
2021-03-02 800
2021-03-03 900

и я хочу создать такой отчет

Дата сумма
2021-01-31 300
2021-02-28 600
2021-03-03 900

Я запутался, как создать запрос для предоставления подобных данных, получить сумму за каждую последнюю дату месяца.

Допустим, я ввел сумму на 4 марта 2021 года, в отчете будут показаны такие данные

Дата сумма
2021-01-31 300
2021-02-28 600
2021-03-04 1000

Я использую PostgreSQL

Ответ №1:

демонстрация: db<>скрипка

Чтобы найти самую последнюю запись группы, вы можете использовать DISTINCT ON :

 SELECT DISTINCT ON (date_trunc('month', my_date))
    *
FROM mytable
ORDER BY date_trunc('month', my_date), my_date DESC
  1. date_trunc('month', ...) нормализует дату до первого числа месяца. Таким образом, каждая дата в феврале будет преобразована в '2021-02-01' . Это можно использовать для группы месяцев
  2. Упорядочивание этих групп по их фактическому date DESC , что упорядочивает самую последнюю дату в начало группы месяца. DISTINCT ON возвращает именно эти записи.

Комментарии:

1. добавлено объяснение

Ответ №2:

Вы можете сделать это с помощью row_number() :

 **Schema (PostgreSQL v13)**

CREATE TABLE tablename (
    date date,
    amount int
);

INSERT INTO tablename VALUES
('2021-01-01',  100),
('2021-01-15',  200),
('2021-01-31',  300),
('2021-02-01',  400),
('2021-02-15',  500),
('2021-02-28',  600),
('2021-03-01',  700),
('2021-03-02',  800),
('2021-03-03',  900),
('2021-03-04',  1000);

Запрос #1

 select date,amount from 
(
  select date,amount ,
  row_number()over (partition by (extract (year from date)),(extract (month from date )) order by date desc)RN
       from tablename
)T
WHERE RN=1;
Дата сумма
2021-01-31T00:00:00.000Z 300
2021-02-28T00:00:00.000Z 600
2021-03-04T00:00:00.000Z 1000

Просмотр на скрипке DB

Комментарии:

1. Извините. Это была опечатка. Исправлено.

2. Поскольку один и тот же месяц может существовать в течение нескольких лет в реальных данных, я использовал год и месяц вместе для разделения по предложениям.

Ответ №3:

 select date_trunc('month', date   '01 Months'::interval) - '01 Days'::interval, sum(amount) 
from tableName
group by date_trunc('month', date   '01 Months'::interval) - '01 Days'::interval
order by date_trunc('month', date   '01 Months'::interval) - '01 Days'::interval desc