获取Oracle中每个客户的最后订单ID和日期

Question

这是一个orcale数据库，我正在尝试获取每个客户的最后一个订单ID及其日期。我对MySQL非常熟悉，能够在MySQL系统中编写类似以下内容的代码：

https://www.w3schools.com/SQL/trysql.asp?filename=trysql_select_all

SELECT CustomerID, COUNT(OrderID), MAX(OrderDate) as Last_Order_Date,
    (SELECT OrderID FROM Orders O2 
    WHERE O2.CustomerId = O1.CustomerId
    ORDER BY OrderDate DESC LIMIT 1)
    AS LAST_ORDER_ID
FROM [Orders] O1
GROUP BY CustomerID
ORDER BY CustomerID

但是，我还不能为Oracle数据库编写等同的代码。让我犯错的部分是，我必须为order by编写一个子查询，然后选择前1个...但这也需要进入我的更大的查询，即为每个客户获取最后一个订单。

Answer 1

使用MAX( ... ) KEEP ( DENSE_RANK LAST ... )

SELECT CustomerID,
       COUNT( OrderID ),
       MAX( OrderDate ) AS Last_Order_Date,
       MAX( OrderID ) KEEP ( DENSE_RANK LAST ORDER BY OrderDate ) AS Last_Order_ID
FROM   Orders
GROUP BY CustomerID
ORDER BY CustomerID;

或者使用分析函数：

SELECT CustomerID,
       Num_Orders,
       OrderDate AS Last_Order_Date,
       OrderID AS Last_Order_ID
FROM   (
  SELECT o.*,
         COUNT( OrderID ) OVER ( PARTITION BY CustomerID ) AS Num_Orders,
         ROW_NUMBER() OVER ( PARTITION BY CustomerID
                             ORDER BY OrderDate DESC, OrderID DESC ) AS rn
  FROM   orders o
)
WHERE  rn = 1
ORDER BY CustomerID;