SQL Server:查找大于5的最近连续记录 [英] SQL Server : find recent consecutive records that are greater than 5
问题描述
我需要编写一个查询，以显示根据最新的LogDate值大于5的按FormID细分的结果.
基于最新的LogDate，如果该值小于5，则应该显示该点的大于5的值，因为如果小于5，该值将被重置". /p>
我实质上是在查看最近连续的大于5的LogDate记录.
说我们有以下记录集:
FormID Value LogDate
--------------------------
Form2 6 10/12/19
Form2 7 10/13/19
Form1 8 10/12/19
Form1 12 10/12/19
Form1 3 10/14/19
Form1 8 10/15/19
Form1 6 10/21/19
以下内容将返回以下内容(请注意，我也想显示row_num:
FormID Value LogDate row_num
----------------------------------
Form2 6 10/12/19 1
Form2 7 10/13/19 2
Form1 8 10/15/19 1
Form1 6 10/21/19 2
请注意，在上面的示例中，由于以下记录的最近值小于5(值3)，因此我们需要获取大于5的记录.
另一个例子:
FormID Value LogDate
Form1 8 10/15/19
Form1 3 10/21/19
结果:不会显示任何结果，因为最近的记录中有大于5
另一个例子:
FormID Value LogDate
Form2 4 10/12/19
Form2 3 10/13/19
Form1 16 10/12/19
Form1 3 10/12/19
Form1 3 10/14/19
Form1 8 10/15/19
Form1 12 10/21/19
结果将是:
FormID Value LogDate row_num
Form1 8 10/15/19 1
Form1 12 10/21/19 2
另一个例子:
FormID Value LogDate
Form1 12 10/12/19
Form2 13 10/13/19
结果:
FormID Value LogDate row_num
Form1 12 10/12/19 1
Form2 13 10/13/19 2
据我了解，这可以通过LAG函数完成，但不确定如何将其完全整合.
我们可以执行以下操作:
DECLARE @mytable TABLE
(
FormID VARCHAR(50),
[Value] INT,
LogDate DATETIME
)
select t.*,
lag(value) over(partition by formid order by logdate) lag_value
from @mytablet
但不确定如何将它们组合在一起.
如果我正确地关注了您，则可以使用以下窗口函数来做到这一点:
select
from (
select t.*,
row_number() over(partition by formid order by logdate desc) rn,
sum(case when value > 5 then 1 else 0 end) over(partition by formid order by logdate desc) grp
from mytable t
) t
where rn = grp
想法是将5以上的值的数目与行号进行比较，从最近的值开始计数.可以保留两个值相等的行.
I need to write a query that shows the result broken down by FormID that have a value greater than 5 based on the most recent LogDate.
Based on the most recent LogDate, if there was a value that was less than 5, it should display values from that point that are greater than 5 as the values under 5 is a 'reset' if you will.
I am essentially looking at recent consecutive LogDate records that are greater than 5.
Say we have the following record set:
FormID Value LogDate
--------------------------
Form2 6 10/12/19
Form2 7 10/13/19
Form1 8 10/12/19
Form1 12 10/12/19
Form1 3 10/14/19
Form1 8 10/15/19
Form1 6 10/21/19
The following would return the following (please note I like to show the row_num as well:
FormID Value LogDate row_num
----------------------------------
Form2 6 10/12/19 1
Form2 7 10/13/19 2
Form1 8 10/15/19 1
Form1 6 10/21/19 2
Note in the example above, since the following record had a recent value under 5 (value of 3), we need to get the records that are above 5.
Another example:
FormID Value LogDate
Form1 8 10/15/19
Form1 3 10/21/19
RESULT: No result would be shown as there are in recent record that is greater than 5
Another example:
FormID Value LogDate
Form2 4 10/12/19
Form2 3 10/13/19
Form1 16 10/12/19
Form1 3 10/12/19
Form1 3 10/14/19
Form1 8 10/15/19
Form1 12 10/21/19
Result here would be:
FormID Value LogDate row_num
Form1 8 10/15/19 1
Form1 12 10/21/19 2
Another example:
FormID Value LogDate
Form1 12 10/12/19
Form2 13 10/13/19
Result:
FormID Value LogDate row_num
Form1 12 10/12/19 1
Form2 13 10/13/19 2
From my understanding, this can be done with the LAG function but not sure how to put it altogether.
We can do something like the following:
DECLARE @mytable TABLE
(
FormID VARCHAR(50),
[Value] INT,
LogDate DATETIME
)
select t.*,
lag(value) over(partition by formid order by logdate) lag_value
from @mytablet
But not sure how to pull it all together.
If I follow you correctly, you can do this with window functions like this:
select
from (
select t.*,
row_number() over(partition by formid order by logdate desc) rn,
sum(case when value > 5 then 1 else 0 end) over(partition by formid order by logdate desc) grp
from mytable t
) t
where rn = grp
The idea is to compare the number of values above 5 to a row number, counting from the most recent value. Rows where the two values are equal can be retained.
这篇关于SQL Server:查找大于5的最近连续记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
