从窗口函数中选择顶部结果的最简单方法

Question

所以，假设我有一个客户列表，我想为所有客户选择详细信息，以及他们从特定类别的产品中购买最多的产品。即使他们还没有购买这些产品，我也希望选择客户详细信息，同时简单地显示该类中购买次数最多的产品的null。

我会从以下作为CTE或临时表开始：

SELECT
CUST_NUMBER
,PRODUCT
,ROW_NUMBER() OVER (PARTITION BY CUST_NUMBER ORDER BY COUNT(ORDER_NUM) DESC) [ProdRank]
FROM ORDERS
WHERE PROD_CLASS = 'x'
GROUP BY 
CUST_NUMBER
,PRODUCT

事情是这样的——在这个产品类中可以有很多不同的产品，我只想选择ProdRank=1的地方。不过，正如您可能知道的，我不能在WHERE或HAVING子句中指定ProdRank为=1。

我收到错误消息"窗口函数只能出现在SELECT或ORDER BY子句中。">

许多客户可能没有订购该产品类别中的任何产品，这一事实使情况更加复杂。因此，我不能简单地将join客户列表保留到上面并指定WHERE ProdRank=1，否则它将模拟内部联接，并删除ProdRank为Null的任何客户。

为了解决这个问题，我想出的方法是首先创建一个临时表，上面的代码为#Products，其中包括客户和每个具有相应排名的产品。然后，我创建了第二个名为#TopProducts的临时表，其中我简单地说：

SELECT * FROM 
#Products WHERE 
ProdRank = 1

在那之后，我就离开了，加入了我的客户表中的#TopProducts。

不过，似乎应该有一种更简单的方法来处理这个问题。有没有什么方法可以在一步中选择ROW_NUMBER()或RANK()的顶部分区结果，而不是创建两个临时表？

Answer 1

使用通用表表达式

WITH topProducts AS (
SELECT
CUST_NUMBER
,PRODUCT
,ROW_NUMBER() OVER (PARTITION BY CUST_NUMBER ORDER BY COUNT(ORDER_NUM) DESC) [ProdRank]
FROM ORDERS
WHERE PROD_CLASS = 'x'
GROUP BY 
CUST_NUMBER
,PRODUCT
)
SELECT *
FROM CustomerDetails c
LEFT JOIN TopProducts p ON (ProdRank = 1 AND c.CUST_NUMBER = p.CUST_NUMBER)

使用子查询：

SELECT *
FROM CustomerDetails c
LEFT JOIN (
SELECT
CUST_NUMBER
,PRODUCT
,ROW_NUMBER() OVER (PARTITION BY CUST_NUMBER ORDER BY COUNT(ORDER_NUM) DESC) [ProdRank]
FROM ORDERS
WHERE PROD_CLASS = 'x'
GROUP BY 
CUST_NUMBER
,PRODUCT
) p ON (ProdRank = 1 AND c.CUST_NUMBER = p.CUST_NUMBER)

Answer 2

我会在您的场景中使用外部应用和顶部。这有道理吗？这里的几个例子现实生活中的例子，什么时候在SQL 中使用OUTER/CROSS APPLY

我会写一段代码，但我在手机上，这真的很不舒服。。。