英文:
SQL query to obtain average price on most recent invoices by company and product
问题
以下是您要翻译的内容：
表格设置如下：
我正在尝试按产品代码和公司获取最近7张发票的平均值。
| 公司 | 产品代码 | 价格 | 发票日期 |
|---|---|---|---|
| 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 |
我尝试了类似以下的内容：
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张发票？
英文:
Table is set up as below:
I'm trying to get an average of the last 7 invoices by product code and by company.
| 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 |
I tried something like:
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'
But this is just returning the last 7 invoices from the table-- how can I get the last 7 invoices by company and product code?
答案1
得分: 2
以下是已翻译的内容：
这将对所有行按“发票日期”的最后7行执行：
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
这将对每个“公司/产品代码”组合的最后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
英文:
This will do it for the last 7 by Invoice Date for all rows:
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
This will do it for last 7 for each company/product code combo:
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
答案2
得分: 0
请使用窗口函数！这可能比自连接或子查询更高效，因为它只扫描表一次（...并且语法更整洁）：
select 公司, 产品代码, avg(价格) as 平均价格
from (
select a.*
from a
qualify row_number() over(partition by 公司, 产品代码 order by 发票日期 desc) <= 7
) a
group by 公司, 产品代码
英文:
Use window functions! This is likely to be more efficient than self-joins or subqueries, since it scans the table only once (... and the syntax is neater):
select company, product_code, avg(price) as avg_price
from (
select a.*
from a
qualify row_number() over(partition by company, product_code order by invoice_date desc) <= 7
) a
group by company, product_code
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论