Блог

Главная - Вопросы по программированию - Postgresql — агрегированные запросы внутри агрегированных запросов

Postgresql — агрегированные запросы внутри агрегированных запросов

#sql #postgresql #datetime #sum #where-clause

#sql #postgresql #дата и время #сумма #where-предложение

Вопрос:

Я работаю над созданием инструкции select для отчета комиссии торгового представителя, который использует таблицы postgresql. Я хочу, чтобы он отображал эти столбцы: -Номер клиента.

-Часть нет.

-Кол-во за месяц (кол-во MTD)

-Кол-во с начала года (кол-во с начала года)

-Расширенная цена продажи за месяц (расширенная MTD)

-Расширенная цена продажи с начала года (расширенная с начала года)

Данные представлены в двух таблицах:

Sales_History (одна запись на счет-фактуру, и эта таблица включает в себя номер и дату счета-фактуры)

Sales_History_Items (по одной записи на номер детали. по счету-фактуре, и эта таблица включает номер детали, количество и цену за единицу).

Если я выполняю простой запрос, который объединяет эти две таблицы, вот как это выглядит:

Дата / Количество / Часть / Кол-во / Цена за единицу

1 апреля / ABC Co. / ВИДЖЕТ / 5 / $ 11

4 апреля / ABC Co. / ВИДЖЕТ / 8 / $11.50

1 апреля / ABC Co. / ГАДЖЕТ / 1 / $ 30

7 апреля / XYZ Co. / ВИДЖЕТ / 3 / $11.50

и т.д.

Это конечный результат, который я хочу (одна строка для каждого клиента на деталь):

Cust / Part / Кол-во / MTD Кол-во / MTD Продажи / С начала ГОДА Кол-во / Продажи с начала года

ABC Co. / ВИДЖЕТ / 13 / $147 / 1500 / $16,975

ABC Co. / ГАДЖЕТ / 1 / $30 / 7 / $210

XYZ Co. / ВИДЖЕТ / 3 / $34.50 / 18 / $203.40

До сих пор я смог придумать этот оператор SQL, который не дает мне расширенные столбцы продаж (committed_qty * unit_price) на строку, а затем суммировать их по номеру заказа / номеру части, и это моя проблема:

 with mtd as
(SELECT sales_history.cust_no, part_no, Sum(sales_history_items.committed_qty) AS MTDQty
    FROM sales_history left JOIN sales_history_items 
    ON sales_history.invoice_no = sales_history_items.invoice_no where  sales_history_items.part_no is not null and sales_history.invoice_date >= '2020-04-01' and sales_history.invoice_date <= '2020-04-30' 
    GROUP BY sales_history.cust_no, sales_history_items.part_no),

ytd as 
(SELECT sales_history.cust_no, part_no, Sum(sales_history_items.committed_qty) AS YTDQty
    FROM sales_history left JOIN sales_history_items 
    ON sales_history.invoice_no = sales_history_items.invoice_no where sales_history_items.part_no is not null and sales_history.invoice_date >= '2020-01-01' and sales_history.invoice_date <= '2020-12-31' GROUP BY sales_history.cust_no, sales_history_items.part_no),

mysummary as
(select MTDQty, YTDQty, coalesce(ytd.cust_no,mtd.cust_no) as cust_no,coalesce(ytd.part_no,mtd.part_no) as part_no
from ytd full outer join mtd on ytd.cust_no=mtd.cust_no and ytd.part_no=mtd.part_no)

select * from mysummary;

Я считаю, что мне нужно вложить сюда еще пару агрегированных запросов, которые будут группироваться по cust_no, part_no, unit_price, но затем эти расширенные суммы цен (кол-во * unit_price) суммируются по cust_no, part_no .

Буду признателен за любую помощь. Спасибо!

Ответ №1:

Сделайте это за один раз с filter помощью выражений:

 with params as (
  select '2020-01-01'::date as year, 4 as month
)
SELECT h.cust_no, i.part_no, 
       SUM(i.committed_qty) AS YTDQty,
       SUM(i.committed_qty * i.unit_price) as YTDSales,
       SUM(i.committed_qty) FILTER 
         (WHERE extract('month' from h.invoice_date) = p.month) as MTDQty,
       SUM(i.committed_qty * i.unit_price) FILTER
         (WHERE extract('month' from h.invoice_date) = p.month) as MTDSales
  FROM params p
       CROSS JOIN sales_history h
       LEFT JOIN sales_history_items i
              ON i.invoice_no = h.invoice_no 
 WHERE i.part_no is not null 
   AND h.invoice_date >= p.year 
   AND h.invoice_date < p.year   interval '1 year' 
 GROUP BY h.cust_no, i.part_no

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

1. Большое спасибо, Майк. Это сработало прекрасно! Теперь, когда у меня есть это, я изменил его, потому что мне действительно нужны еще два столбца: количество за прошлый год и продажи за прошлый год (я пытался создать это поэтапно). Но теперь, когда я это сделал, использование 4 для месяца стало проблемой, потому что добавляло апрельские продажи из LY и TY, поэтому мне пришлось изменить параметры, чтобы использовать дату начала и дату окончания для апреля, и это работает как шарм. Еще раз спасибо за поддержку!

Ответ №2:

Если я правильно вас понял, вы можете выполнить условную агрегацию:

 select sh.cust_no, shi.part_no,
    sum(shi.qty)  mtd_qty, 
    sum(shi.qty * shi.unit_price) ytd_sales,
    sum(shi.qty) filter(where sh.invoice_date >= date_trunc('month', current_date) mtd_qty, 
    sum(shi.qty * shi.unit_price) filter(where sh.invoice_date >= date_trunc('month', current_date) mtd_sales 
from sales_history sh
left join sales_history_items shi on sh.invoice_no = shi.invoice_no 
where  shi.part_no is not null and sh.invoice_date >= date_trunc('year', current_date)
group by sh.cust_no, shi.part_no

Логика заключается в фильтрации по текущему году и использовании простой агрегации для вычисления показателей «с начала года до настоящего времени». Чтобы получить столбцы «от месяца до даты», мы можем просто отфильтровать агрегированные функции.