时间戳

首页 > 解决方案 > MAX() OVER PARTITION 未按预期工作

sql - MAX() OVER PARTITION 未按预期工作

问题描述

当我尝试使用一组记录获取字段的 MAX 值时遇到了一些问题，我希望你们中的一些人可以帮助我找出我做错了什么。
我正在尝试在订单中获取最昂贵行的商品的 ID。
鉴于此查询：

SELECT 
       orderHeader.orderKey, orderLines.lineKey, orderLines.itemKey, orderLines.OrderedQty, 
       orderLines.price, (orderLines.price*orderLines.OrderedQty) as LinePrice, 
       ROW_NUMBER() OVER(PARTITION BY orderHeader.orderKey ORDER BY orderLines.lineKey asc) AS [ItemLineNum], 
       ROW_NUMBER() OVER(PARTITION BY orderHeader.orderKey ORDER BY (orderLines.price*orderLines.OrderedQty) DESC) AS [LineMaxPriceNum],
       max(orderLines.itemKey) OVER (PARTITION BY orderHeader.orderKey ORDER BY (orderLines.price*orderLines.OrderedQty) DESC) as [MaxPriceItem]
FROM
       orderHeader inner join orderLines on orderHeader.orderKey=orderLines.orderKey

我得到这个结果： 查询结果

抱歉，由于我不允许在帖子中直接插入图片，我将尝试使用片段来格式化表格。
这些是结果

| orderKey | lineKey | itemKey | OrderedQty | Price | LinePrice | ItemLineNum | LineMaxPriceNum | MaxPriceItem |
|----------|---------|---------|------------|-------|-----------|-------------|-----------------|--------------|
| 176141   | 367038  | 15346   | 3          | 1000  | 3000      | 2           | 1               | 15346        |
| 176141   | 367037  | 15159   | 2          | 840   | 1680      | 1           | 2               | 15346        |
| 176141   | 367039  | 15374   | 5          | 100   | 500       | 3           | 3               | 15374        |

如您所见，对于相同的“orderKey”，我有三行（lineKey），每行都有不同的项目（itemKey）、不同的数量、不同的价格和不同的总成本（LinePrice）。我希望在 MaxPriceItem 列中具有较高“LinePrice”的项目的键，但结果是错误的。三行应该显示 15346 是最昂贵的项目，但最后一行是不正确的，我不明白为什么。此外，由同一表达式 (LineMaxPriceNum) 划分的 ROW_NUMBER 给了我正确的顺序。

如果我在 MAX 中更改 ORDER BY 的表达式，像这样（按“OrderedQty”排序）：

SELECT 
       orderHeader.orderKey, orderLines.lineKey, orderLines.itemKey, orderLines.OrderedQty, 
       orderLines.price, (orderLines.price*orderLines.OrderedQty) as LinePrice, 
       ROW_NUMBER() OVER(PARTITION BY orderHeader.orderKey ORDER BY orderLines.lineKey asc) AS [ItemLineNum], 
       ROW_NUMBER() OVER(PARTITION BY orderHeader.orderKey ORDER BY (orderLines.price*orderLines.OrderedQty) DESC) AS [LineMaxPriceNum],
       max(orderLines.itemKey) OVER (PARTITION BY orderHeader.orderKey ORDER BY orderLines.OrderedQty DESC) as [MaxPriceItem]
FROM
       orderHeader inner join orderLines on orderHeader.orderKey=orderLines.orderKey

然后它工作：

| orderKey | lineKey | itemKey | OrderedQty | Price | LinePrice | ItemLineNum | LineMaxPriceNum | MaxPriceItem |
|----------|---------|---------|------------|-------|-----------|-------------|-----------------|--------------|
| 176141   | 367038  | 15346   | 3          | 1000  | 3000      | 2           | 1               | 15374        |
| 176141   | 367037  | 15159   | 2          | 840   | 1680      | 1           | 2               | 15374        |
| 176141   | 367039  | 15374   | 5          | 100   | 500       | 3           | 3               | 15374        |

“OrderedQty”最高的项目是 15374，因此结果是正确的。

如果我再次更改 MAX 中 ORDER BY 的表达式，如下所示（按“价格”排序）：

SELECT 
       orderHeader.orderKey, orderLines.lineKey, orderLines.itemKey, orderLines.OrderedQty, 
       orderLines.price, (orderLines.price*orderLines.OrderedQty) as LinePrice, 
       ROW_NUMBER() OVER(PARTITION BY orderHeader.orderKey ORDER BY orderLines.lineKey asc) AS [ItemLineNum], 
       ROW_NUMBER() OVER(PARTITION BY orderHeader.orderKey ORDER BY (orderLines.price*orderLines.OrderedQty) DESC) AS [LineMaxPriceNum],
       max(orderLines.itemKey) OVER (PARTITION BY orderHeader.orderKey ORDER BY orderLines.price DESC) as [MaxPriceItem]
FROM
       orderHeader inner join orderLines on orderHeader.orderKey=orderLines.orderKey

然后它发生与第一个示例相同的情况，结果是错误的：

| orderKey | lineKey | itemKey | OrderedQty | Price | LinePrice | ItemLineNum | LineMaxPriceNum | MaxPriceItem |
|----------|---------|---------|------------|-------|-----------|-------------|-----------------|--------------|
| 176141   | 367038  | 15346   | 3          | 1000  | 3000      | 2           | 1               | 15346        |
| 176141   | 367037  | 15159   | 2          | 840   | 1680      | 1           | 2               | 15346        |
| 176141   | 367039  | 15374   | 5          | 100   | 500       | 3           | 3               | 15374        |

最高价格的商品是 15346，但最后一条记录的 MAX 没有显示这一点。

我在这里想念什么？为什么我会得到那些不同的结果？

抱歉，如果格式设置不正确，这是我的第一个问题，我已经尽力了。

提前感谢您能给我的任何帮助。

标签： sqlsql-serverjoinmaxwindow-functions

解决方案

我正在尝试在订单中获取最昂贵行的商品的 ID。

您误解了order by窗口函数子句的用途；它旨在定义窗口框架，而不是比较值；max()为您提供在窗口框架内作为参数给出的表达式的最大值。

另一方面，您想要itemKey最昂贵的订单行。我认为这first_value()会做你想要的：

first_value(orderLines.itemKey) over(
    partition by orderHeader.orderKey 
    order by orderLines.price * orderLines.OrderedQty desc
) as [MaxPriceItem]

推荐阅读