Oracle SQL 中的 MAX() OVER PARTITION BY

Question

我正在尝试使用 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      |

任何人都可以就我做错了什么提供建议吗？看起来它只是在替换最近的日期，而不是只给我想要的最近的行。

最终，我希望我的桌子看起来像这样。但是，我不知道如何正确使用 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   |

原文由 artemis 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

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

原文由 kc2018 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

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

原文由 Shinya 发布，翻译遵循 CC BY-SA 4.0 许可协议