获取第 n 个连续组的第一行/最后一行 [英] Get first/last row of n-th consecutive group

问题描述

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

What's the easiest way to select a single record/value from the n-th group? The group is determined by a material and it's price(prices can change). I need to find the first date of the last and the last date of the next to last material-price-groups. So i want to know when exactly a price changed.

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

I've tried following query to get the first date of the current(last) price which can return the wrong date if that price was used before:

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 also only returns the last, how do i get the previous? So the date when the previous price was used last/first?

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

I have simplified my schema and created this sql-fiddle with sample-data. Here in chronological order. So the row with ID=7 is what i need since it's has the next-to-last price with the latest date.

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?请注意，查询位于标量值函数中.

Is it even possible to parametrize this value, so the nthLatestPriceGroup if i want to know the 3rd last price-date? Note that the query sits in a scalar-valued-function.

编辑:非常感谢大家.但不幸的是，一个简单的 ROW_NUMBER 在这里似乎无济于事，因为我试图获取给定材料当前价格之前的最新价格的行.所以 GROUP BY/PARTITION BY material,price 包括具有相同价格但不属于最近的材料价格组的行.

Edit: Many thanks to all. But unfortunately a simple ROW_NUMBER seems not to help here since i'm trying to get the row with the most recent price before the current price for a given material. So GROUP BY/PARTITION BY material,price includes rows with the same price that don't belong to the last recent material-price group.

考虑价格可以从

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 组是无关紧要的.所以我必须以某种方式按连续价格分组，因为价格可以在已经改变后重复.

So i want the date of the most recent row of the last 20-group, the oldest 20-group is irrelevant. So i must somehow group by consecutive prices since a price can repeat after it has already changed.

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

So actually i only need the most recent Claim_Submitted_Date from the price-group that starts with 1 month ago ... previous price in the list above which is the date until the previous price was valid. The other informations listed in the comments are just nice to have(the nthLatestPriceGroup sub-question). That's the row with ID=7 in the sample data above. By the way, the oldest row of this price-group would be the one with ID=2(October, 17) and not ID=6(June, 23) even if the latter is older. There was a different price(10) after. That's the reason why i can't use simple ranking functions.

推荐答案

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

You will need to use the windowed function ROWNUMBER in a subquery,...

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

something like this will get you there:

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

这是基于您的小提琴的更新:

Here's the update based on your 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 是增量的(因此是按时间顺序排列的)，您可以使用这个:

If idData is incremental(and therefore chronological) you could use this:

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

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

Looking at your latest requirements we could all be over thinking it(if I understand you correctly):

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.

这篇关于获取第 n 个连续组的第一行/最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！