Блог

Мне нужно написать запрос в PostgreSQL для агрегирования данных из таблицы на основе диапазонов дат не более 3 диапазонов. Предположим, у нас есть следующая таблица:

 CREATE TABLE Purchases (
    ID int,
    PriceCents int,
    PurchaseDate date
);
 

Я хочу написать запрос на :

1. возьмите самую раннюю дату покупки и самую последнюю дату покупки и разбейте их на 3 отдельных интервала дат

2- агрегируйте цену всех покупок, которые произошли между рассчитанными временными диапазонами, и отобразите ее

другими словами, предположим, что у нас были следующие данные в таблице:

  ---- ------------- --------------- 
| id | price_cents | purchase_date |
 ---- ------------- --------------- 
|  1 |         200 | 2020-01-11    |
|  2 |         300 | 2020-01-14    |
|  3 |         100 | 2020-02-02    |
|  4 |         500 | 2020-03-13    |
|  5 |         200 | 2020-07-01    |
|  6 |         300 | 2020-11-17    |
|  7 |         100 | 2021-01-01    |
|  8 |         500 | 2021-01-02    |
 ---- ------------- --------------- 
 

в этом случае данные находятся между 2020-01-11 и 2021-01-02
при разбивке на 3 интервала это дает нам:

от 2020-01-11 до 2020-05-09

от 2020-05-09 до 2020-09-05

от 2020-09-05 до 2021-01-02

и мы ожидаем, что результат будет

  ------------ -------------------------- 
| total_cost |        date_range        |
 ------------ -------------------------- 
|       1100 | 2020-01-11 to 2020-05-09 |
|        200 | 2020-06-10 to 2020-09-05 |
|        900 | 2020-09-06 to 2021-01-02 |
 ------------ -------------------------- 
 

Эта проблема была бы тривиальной, если бы я заранее знал диапазоны дат и мог «жестко закодировать» даты в запросе, но я этого не делаю.

Если вам нужны диапазоны одинакового размера, используйте ntile() :

 select tile, min(purchase_date), max(purchase_date), sum(price_cents)
from (select p.*,
             ntile(3) over (order by purchase_date) as tile
      from purchases p
     ) p
group by tile;
 

Это не удовлетворяет на 100%, поскольку диапазоны могут перекрываться (одна и та же дата может находиться в двух диапазонах). Основное внимание уделяется равному размеру в ячейках. Итак, если вам нужны ячейки ширины, вы можете использовать арифметику даты:

 select ceiling((purchase_date - min_pd) / (max_pd - min_pd)) as tile, sum(price_cents)
from (select p.*,
             min(purchase_date) over () as min_pd,
             max(purchase_date) over () as max_pd
      from purchases p
     ) p
group by tile;