Postgresql -聚合查询中的聚合查询

Question

我正在为一个使用postgresql表的销售代表佣金报告构建一个select语句。我希望它显示以下列：-Customer No.

-Part号

-月初数量(MTD数量)

-年初至今数量(YTD数量)

-本月至今扩展销售价格(MTD扩展)

-年初至今延长销售价格(YTD延长)

数据包含在两个表中：

Sales_History (每张发票一条记录，此表包括Cust。不是的。和发票日期)

Sales_History_Items (每个部件编号一条记录每个发票，此表包括零件号、数量和单价)。

如果我执行一个结合了这两个表的简单查询，结果如下所示：

日期/客户/零件/数量/单价

Apr 1/ ABC公司/ WIDGET /5/ $11

Apr 4/ ABC公司/ WIDGET /8/ $11.50

Apr 1/ ABC公司/ GADGET /1/ $30

Apr 7/ XYZ公司/ WIDGET /3/ $11.50

等。

这是我想要的最终结果(每个客户每部分一行)：

客户/零件/数量/ MTD数量/ MTD销售额/ YTD数量/ YTD销售额

ABC公司/ WIDGET / 13 / $147 / 1500 / $16,975

ABC公司/ GADGET /1/ $30 /7/ $210

XYZ公司/ WIDGET /3/$34.5/ 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)按cust_no，part_no求和。

任何帮助都将不胜感激。谢谢!

Answer 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

Answer 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

其逻辑是根据当前年份进行过滤，并使用简单聚合来计算“年初至今”数字。要获得"month To date“列，我们只需过滤聚合函数。