Oracle SQL 中的 MAX() OVER PARTITION BY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/49436161/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MAX() OVER PARTITION BY in Oracle SQL
提问by wundermahn
I am trying to utilize the MAX() OVER PARTITION BY function to evaluate the most recent receipt for a specific part that my company has bought. Below is an example table of the information for a few parts from the last year:
我正在尝试利用 MAX() OVER PARTITION BY 函数来评估我公司购买的特定零件的最新收据。以下是去年的几个部分的信息示例表：
| VEND_NUM | VEND_NAME | RECEIPT_NUM | RECEIPT_ITEM | RECEIPT_DATE |
|----------|--------------|-------------|----------|--------------|
| 100 | SmallTech | 2001 | 5844HAJ | 11/22/2017 |
| 100 | SmallTech | 3188 | 5521LRO | 12/31/2017 |
| 200 | RealSolution | 5109 | 8715JUI | 05/01/2017 |
| 100 | SmallTech | 3232 | 8715JUI | 11/01/2017 |
| 200 | RealSolution | 2101 | 4715TEN | 01/01/2017 |
As you can see, the third and fourth row show two different vendors for the SAME part number.
如您所见，第三行和第四行显示相同部件号的两个不同供应商。
Here is my current query:
这是我当前的查询：
WITH
-- various other subqueries above...
AllData AS
(
SELECT VEND_NUM, VEND_NAME, RECEIPT_NUM, RECEIPT_ITEM, RECEIPT_DATE
FROM tblVend
INNER JOIN tblReceipt ON VEND_NUM = RECEIPT_VEND_NUM
WHERE
VEND_NUM = '100' OR VEND_NUM = '200' AND RECEIPT_DATE >= '01-Jan-2017'
),
SELECT MAX(RECEIPT_DATE) OVER PARTITION BY(RECEIPT_ITEM) AS "Recent Date", RECEIPT_ITEM
FROM AllData
My return set looks like:
我的返回集如下所示：
| Recent Date | RECEIPT_ITEM |
|-------------|--------------|
| 11/22/2017 | 5844HAJ |
| 12/31/2017 | 5521LRO |
| 11/01/2017 | 8715JUI |
| 11/01/2017 | 8715JUI |
| 01/01/2017 | 4715TEN |
However, it should look like this:
但是，它应该如下所示：
| Recent Date | RECEIPT_ITEM |
|-------------|--------------|
| 11/22/2017 | 5844HAJ |
| 12/31/2017 | 5521LRO |
| 11/01/2017 | 8715JUI |
| 01/01/2017 | 4715TEN |
Can anybody please offer advice as to what I'm doing wrong? It looks like it is simply replacing the most recent date, not giving me just the row I want that is most recent.
有人可以就我做错了什么提供建议吗？看起来它只是在替换最近的日期，而不是只给我我想要的最近的行。
Ultimately, I would like for my table to look like this. However, I don't know how to use the MAX() or MAX() OVER PARTITION BY() functions properly to allow for this:
最终，我希望我的桌子看起来像这样。但是，我不知道如何正确使用 MAX() 或 MAX() OVER PARTITION BY() 函数来实现这一点：
| VEND_NUM | VEND_NAME | RECEIPT_NUM | RECEIPT_ITEM | RECEIPT_DATE |
|----------|--------------|-------------|----------|--------------|
| 100 | SmallTech | 2001 | 5844HAJ | 11/22/2017 |
| 100 | SmallTech | 3188 | 5521LRO | 12/31/2017 |
| 100 | SmallTech | 3232 | 8715JUI | 11/01/2017 |
| 200 | RealSolution | 2101 | 4715TEN | 01/01/2017 |
回答by kc2018
Use window function ROW_NUMBER() OVER (PARTITION BY receipt_item ORDER BY receipt_date DESC)to assign a sequence number to each row. The row with the most recent receipt_datefor a receipt_itemwill be numbered as 1.
使用窗口函数ROW_NUMBER() OVER (PARTITION BY receipt_item ORDER BY receipt_date DESC)为每一行分配一个序列号。最新receipt_date的 areceipt_item行将被编号为 1。
WITH
-- various other subqueries above...
AllData AS
(
SELECT VEND_NUM, VEND_NAME, RECEIPT_NUM, RECEIPT_ITEM, RECEIPT_DATE,
ROW_NUMBER() OVER (PARTITION BY RECEIPT_ITEM ORDER BY RECEIPT_DATE DESC ) AS RN
FROM tblVend
INNER JOIN tblReceipt ON VEND_NUM = RECEIPT_VEND_NUM
WHERE
VEND_NUM IN ( '100','200') AND RECEIPT_DATE >= '01-Jan-2017'
)
SELECT VEND_NUM, VEND_NAME, RECEIPT_NUM, RECEIPT_ITEM, RECEIPT_DATE
FROM AllData WHERE RN = 1
回答by David Faber
I see a couple of issues here. One, the syntax for using the aggregate function MAX()as an analytic function (which is what Oracle helpfully calls a window function) looks like this:
我在这里看到几个问题。一，将聚合函数MAX()用作分析函数（Oracle 有用地称之为窗口函数）的语法如下所示：
MAX(receipt_date) OVER ( PARTITION BY receipt_item )
(note the position of the parentheses). Second, from your desired result set, you don't actually want a window function, you want to aggregate. A window (or analytic) function will always return a row for each row in its partition; that's just the way it works. So I think what you want is this:
（注意括号的位置）。其次，从你想要的结果集中，你实际上并不想要一个窗口函数，你想要聚合。窗口（或解析）函数将始终为其分区中的每一行返回一行；这就是它的工作方式。所以我想你想要的是这个：
WITH
-- various other subqueries above...
AllData AS
(
SELECT VEND_NUM, VEND_NAME, RECEIPT_NUM, RECEIPT_ITEM, RECEIPT_DATE
FROM tblVend
INNER JOIN tblReceipt ON VEND_NUM = RECEIPT_VEND_NUM
WHERE ( VEND_NUM = '100' OR VEND_NUM = '200' ) AND RECEIPT_DATE >= DATE'2017-01-01'
)
SELECT VEND_NUM, VEND_NAME, RECEIPT_NUM, RECEIPT_ITEM, MAX(RECEIPT_DATE)
FROM AllData
GROUP BY VEND_NUM, VEND_NAME, RECEIPT_NUM, RECEIPT_ITEM;
Now I made some small changes to the above, such as wrapping parentheses around the ORconditions (using IN ('100','200')might be even better) since ANDtakes precedence over OR(so your query would have gotten results where VEND_NUM = '100' OR ( VEND_NUM = '200' RECEIPT_DATE >= DATE'2017-01-01' )... but maybe that's what you wanted?).
现在我对上面的内容进行了一些小的更改，例如在OR条件周围加上括号（使用IN ('100','200')可能更好），因为AND优先于OR（因此您的查询会在以下位置获得结果VEND_NUM = '100' OR ( VEND_NUM = '200' RECEIPT_DATE >= DATE'2017-01-01' )......但也许这就是您想要的？）。
回答by Shinya
Just passing by but I think you have to format the date to a 'YYYY-MM-DD' format so that it doesn't consider the "time".
只是路过，但我认为您必须将日期格式化为“YYYY-MM-DD”格式，以便它不考虑“时间”。
回答by Gordon Linoff
THIS ANSWERS THE ORIGINAL VERSION OF THE QUESTION.
这回答了问题的原始版本。
Your whereclause should probably look like this:
您的where子句可能应如下所示：
WHERE VEND_NUM IN ('100', '200') AND RECEIPT_DATE >= DATE '2017-01-01'
It is quite possible that what you want is simply:
您想要的很可能只是：
SELECT DISTINCT RECEIPT_DATE, RECEIPT_ITEM
FROM tblVend INNER JOIN
tblReceipt
ON VEND_NUM = RECEIPT_VEND_NUM
WHERE VEND_NUM IN ('100', '200') AND RECEIPT_DATE >= DATE '2017-01-01';
At the very least, this returns what you want to return.
至少，这会返回您想要返回的内容。
