Oracle SQL中的MAX()OVER PARTITION BY

6mzjoqzu 于 2023-06-22 发布在 Oracle

关注(0)|答案(4)|浏览(115)

我试图利用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   |

正如您所看到的，第三和第四行显示了相同部件号的两个不同供应商。
以下是我当前的查询：

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

我的返回集看起来像：

| Recent Date | RECEIPT_ITEM |
|-------------|--------------|
| 11/22/2017  | 5844HAJ      |
| 12/31/2017  | 5521LRO      |
| 11/01/2017  | 8715JUI      |
| 11/01/2017  | 8715JUI      |
| 01/01/2017  | 4715TEN      |

然而，它应该看起来像这样：

| Recent Date | RECEIPT_ITEM |
|-------------|--------------|
| 11/22/2017  | 5844HAJ      |
| 12/31/2017  | 5521LRO      |
| 11/01/2017  | 8715JUI      |
| 01/01/2017  | 4715TEN      |

有没有人能给我点建议，告诉我哪里做错了？它看起来像是简单地替换了最近的日期，而不是只给我想要的最近的行。
最后，我希望我的table看起来像这样。但是，我不知道如何正确使用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   |

oracle

来源：https://stackoverflow.com/questions/49436161/max-over-partition-by-in-oracle-sql

4条答案

按热度按时间

r1wp621o1#

使用窗口函数ROW_NUMBER() OVER (PARTITION BY receipt_item ORDER BY receipt_date DESC)为每一行分配一个序列号。对于receipt_item，具有最近receipt_date的行将编号为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

赞(0）回复(0）举报 2023-06-22

dfuffjeb2#

我看到了几个问题。第一，使用聚合函数MAX()作为分析函数（Oracle将其称为窗口函数）的语法如下所示：

MAX(receipt_date) OVER ( PARTITION BY receipt_item )

(note括号的位置）。第二，从你想要的结果集中，你实际上并不想要一个窗口函数，你想要的是聚合。窗口（或分析）函数将始终为其分区中的每一行返回一行;这就是它的工作方式。所以我想你想要的是这个：

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;

现在我对上面的内容做了一些小的修改，比如在OR条件周围加上括号（使用IN ('100','200')可能更好），因为AND优先于OR（所以你的查询将得到VEND_NUM = '100' OR ( VEND_NUM = '200' RECEIPT_DATE >= DATE'2017-01-01' )...但也许这就是你想要的）.

赞(0）回复(0）举报 2023-06-22

k0pti3hp3#

只是路过，但我认为你必须将日期格式设置为“YYYY-MM-DD”格式，这样它就不会考虑“时间”。

赞(0）回复(0）举报 2023-06-22

hjqgdpho4#

这就是问题的原始答案。
你的where子句应该看起来像这样：

WHERE VEND_NUM IN ('100', '200') AND RECEIPT_DATE >= DATE '2017-01-01'

很可能你想要的只是：

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';

至少，这会返回您想要返回的内容。

赞(0）回复(0）举报 2023-06-22

我来回答

Oracle SQL中的MAX()OVER PARTITION BY

4条答案

相关问题

热门标签

最新问答