按公司和产品查询最近发票的平均价格的SQL查询

发布于06月29日

表格设置如下:

我正在试着按产品代码和公司得到最近7张发票的平均值.

Company	Product_Code	Price	Invoice Date
A	XYZ	10	01012023
B	XYZ	11	01012023
C	XYZ	12	01012023
A	ABC	12	01022023
B	ABC	13	01022023
C	ABC	14	01022023
A	LMN	15	01022023

我try 了一些类似的方法:

SELECT 
    t1.company, t1.product_code, AVG(price) 
FROM 
   (SELECT 
        * 
    FROM
        Table
    ORDER BY invoice_date DESC 
    LIMIT 7) AS t1
GROUP BY t1.company, t1.product_code, t1.price
HAVING t1.company = 'A' or t1.company = 'B' or t1.company = 'C'

但这只是返回表中的最后7张发票--我如何才能按公司和产品代码获得最后7张发票？

推荐答案

这将对所有行的最后7xInvoice Date行执行此操作:

SELECT company, product_code, AVG(price) 
FROM a
WHERE "Invoice Date" IN (SELECT TOP 7 "Invoice Date" FROM a ORDER BY "Invoice Date" DESC)
GROUP BY company, product_code

这将对每company/product code个组合的最后7个组合执行此操作:

SELECT a.company, a.product_code, AVG(a.price) FROM a
JOIN (SELECT company, product_code, MIN("Invoice Date") "Invoice Date" FROM a WHERE "Invoice Date" IN (SELECT TOP 7 "Invoice Date" FROM a ORDER BY "Invoice Date" DESC) GROUP BY company, product_code) b
    ON a.company = b.company AND a.product_code = b.product_code AND a."Invoice Date" = b."Invoice Date"
GROUP BY a.company, a.product_code