sql - 获取第 n 个连续组的第一行/最后一行

Question

从第 n 组中选择单个记录/值的最简单方法是什么？该组由 Material 及其价格决定(价格可能会发生变化)。我需要找到最后一个 Material 价格组的第一个日期和倒数第二个 Material 价格组的最后一个日期。所以我想知道价格何时发生变化。

我尝试使用以下查询来获取当前(最后)价格的第一个日期，如果之前使用过该价格，则可能会返回错误的日期:

DECLARE @material VARCHAR(20)
SET @material = '1271-4303'

SELECT TOP 1 Claim_Submitted_Date 
FROM   tabdata
WHERE Material = @material 
AND Price = (SELECT TOP 1 Price FROM tabdata t2 
             WHERE Material = @material
             ORDER BY Claim_Submitted_Date DESC)
ORDER BY Claim_Submitted_Date ASC

这也只返回最后一个，我如何获取前一个？那么最后/第一次使用先前价格的日期是多少？

我简化了架构并创建了 this sql-fiddle与样本数据。这里按时间顺序。因此，ID=7 的行就是我所需要的，因为它具有最新日期的倒数第二个价格。

ID   CLAIM_SUBMITTED_DATE                   MATERIAL    PRICE
5   December, 04 2013 12:33:00+0000         1271-4303   20
4   December, 03 2013 12:33:00+0000         1271-4303   20   <-- current
3   November, 17 2013 10:13:00+0000         1271-4846   40
7   November, 08 2013 12:16:00+0000         1271-4303   18   <-- last(desired)
2   October, 17 2013 09:13:00+0000          1271-4303   18
1   September, 17 2013 08:13:00+0000        1271-4303   10
8   September, 16 2013 12:15:00+0000        1271-4303   17
6   June, 23 2013 14:22:00+0000             1271-4303   18
9   January, 11 2013 12:22:10+0000          1271-4303   20   <-- a problem since this is older than the desired but will be returned by my simply sub-query approach above

如果我想知道第三个价格日期，是否可以参数化这个值，例如nthLatestPriceGroup？请注意，查询位于标量值函数中。

编辑:非常感谢大家。但不幸的是，简单的 ROW_NUMBER 在这里似乎没有帮助，因为我试图获取给定 Material 的当前价格之前的最新价格行。因此，GROUP BY/PARTITION BY Material,price 包含具有相同价格但不属于最近的 Material 价格组的行。

考虑到价格可能会发生变化

Date             Price     Comment
5 months ago     20        original price, note that this is the same as the curent which causes my query to fail!
3 months ago     18        price has changed, i might need the first and last date
2 months ago     20        price has changed, i might need the first and last date
1 month ago      18        previous price, i need the oldest and newest dates 
NOW              20        current price, i need the first/oldest date from this group

所以我想要最后 20 组的最新行的日期，最早的 20 组是不相关的。因此，我必须以某种方式按连续价格进行分组，因为价格在更改后可能会重复。

所以实际上我只需要上面列表中以1个月前...先前价格开头的价格组中的最新Claim_Subscribed_Date，即日期直到先前的价格有效。评论中列出的其他信息很好(nthLatestPriceGroup 子问题)。这是上面示例数据中带有 ID=7 的行。顺便说一句，该价格组中最早的一行将是 ID=2(10 月 17 日)的行，而不是 ID=6(6 月 23 日)的行如果后者年龄较大。之后有不同的价格(10)。这就是为什么我不能使用简单的排名函数的原因。

Answer 1

您需要在子查询中使用窗口函数 ROWNUMBER，...

类似这样的事情会让你到达那里:

ROW_NUMBER() OVER(PARTITION BY Price ORDER BY Claim_Submitted_Date DESC) AS Row 

这是根据您的 fiddle 进行的更新:

DECLARE @material VARCHAR(20)
SET @material = '1271-4303'


SELECT * FROM
(
SELECT  *,
        ROW_NUMBER() OVER(PARTITION BY Material ORDER BY Claim_Submitted_Date ASC) AS rn  
FROM tabdata t2 
WHERE Material = @material
) res
WHERE rn=2

如果 idData 是增量的(因此是按时间顺序排列的)，你可以使用这个:

SELECT * FROM
(
SELECT  *,
        ROW_NUMBER() OVER(PARTITION BY Material ORDER BY idData DESC) AS rn  
FROM tabdata t2 
WHERE Material = @material
) res

看看您的最新要求，我们可能都想太多了(如果我理解正确的话):

DECLARE @MATERIAL AS VARCHAR(9)
SET @MATERIAL = '1271-4303'

SELECT  TOP 1 *
FROM tabdata t2 
WHERE Material = @material
AND PRICE <> (  SELECT TOP 1 Price
                FROM tabdata 
                WHERE Material = @material 
                ORDER BY CLAIM_SUBMITTED_DATE desc)
ORDER BY CLAIM_SUBMITTED_DATE desc

--results
idData  Claim_Submitted_Date        Material    Price
7       2013-11-08 12:16:00.000     1271-4303   18

这是一个fiddle以此为基础。