How to group by on consecutive values in SQL

问题:

I have a table in SQL Server 2014 with sample data as follows.

WK_NUM | NET_SPRD_LCL 
10       0 
11       1500 
12       3600 
13       3800 
14       4000

I am trying to code a bonus structure at work where I need to group on WK_NUM. So, if I see NET_SPRD_LCL > 3500 for two consecutive WK_NUMs WHERE WK_NUM < 27, I need to output 2000. In this example, since NET_SPRD_LCL for WK_NUM 12 and 13 are both greater than 3500, the SQL should output 2000 and exit. So, it should ignore the fact that WK_NUM 13 and 14 also satisfy the condition that NET_SPRD_LCL > 3500.

I would appreciate any help with this.

回答1:

First of all, when you say you want your query to 'output' and 'exit', it makes me think you are approaching t-sql as a procedural language, which it is not. Good t-sql queries are nearly always set based.

In any case, before the query, let me add what is helpful for others to work with the data to build queries:

DECLARE @t TABLE (WK_NUM INT, NET_SPRD_LCL INT);
INSERT INTO @t VALUES 
(10, 0),
(11, 1500),
(12, 3600),
(13, 3800),
(14, 4000);

You say you are using SQL Server 2014, which means you have relevant window functions at your disposal. The one I am using (LAG) will have superior performance to using subqueries, which, if you insist on using, can be greatly improved by using TOP (1) with ORDER BY and an appropriate index instead of using a MIN function over the whole dataset. With tiny amounts of data you won't notice a difference, but on a real business system it will be obvious.

Adjusted to provide the 2000 bonus on the correct line after OP's clarification:

WITH cteTemp AS
    (
    SELECT  WK_NUM
        ,   thisValue = NET_SPRD_LCL
        ,   lastValue = LAG(NET_SPRD_LCL) OVER(ORDER BY WK_NUM)
    FROM @t
    WHERE WK_NUM < 27
    )
, cteBonusWeek AS
    (
    SELECT  TOP (1)
            WK_NUM
        ,   bonus = 2000
    FROM cteTemp
    WHERE thisValue > 3500 AND lastValue > 3500
    ORDER BY WK_NUM
    )
SELECT  t.WK_NUM
    ,   t.NET_SPRD_LCL
    ,   bonus = COALESCE(b.bonus, 0)
FROM @t AS t
LEFT JOIN cteBonusWeek AS b
    ON b.WK_NUM = t.WK_NUM;

回答2:

Assuming you mean consecutive line 1, 2, 3, 4, 5 ... etc. and NOT 1, 3, 5, 8, 12, etc.

then, if you don't need to know which pair of consecutive records it was:

Select case when exists
   (Select * from table f
       join table n
          on n.Wk_Num = f.Wk_Num + 1
             and n.NET_SPRD_LCL > 3500
             and f.NET_SPRD_LCL > 3500
             and n.Wk_Num < 27
    then 2000 else null end

If you do need to identify the pair of records, then:

Select f.wk_Num firstWorkNbr, f.NET_SPRD_LCL firstNetSpread,
       n.wk_Num nextWorkNbr, n.NET_SPRD_LCL nextNetSpread
from table f
   join table n
       on n.Wk_Num = f.Wk_Num + 1
         and n.NET_SPRD_LCL > 3500
         and f.NET_SPRD_LCL > 3500
          and n.Wk_Num < 27
 Where not exists 
    (Select * from table f0
       join table n0 
           on n0.Wk_Num = f0.wk_Num + 1
              and n0.WkNum < f.Wk_Num))

on the other hand if the consecutive is simply increasing, then it's a bit harder. You need to use a subquery to determine the next consecutive record...

Select case when exists
   (Select * from table f
       join table n
          on n.Wk_Num = (Select Min(Wk_Num) from table
                         Where Wk_Num > f.Wk_Num)
             and n.NET_SPRD_LCL > 3500
             and f.NET_SPRD_LCL > 3500
             and n.Wk_Num < 27
    then 2000 else null end

and if you need to fetch the data for the specific first pair of records that qualify (the 2000 at the end is unnecessary since if there is no qualifying pair nothing will be returned.)

Select f.wk_Num firstWorkNbr, f.NET_SPRD_LCL firstNetSpread,
       n.wk_Num nextWorkNbr, n.NET_SPRD_LCL nextNetSpread, 2000 outValue
from table f
   join table n
       on n.Wk_Num = (Select Min(Wk_Num) from table
                      Where Wk_Num > f.Wk_Num)
         and n.NET_SPRD_LCL > 3500
         and f.NET_SPRD_LCL > 3500
          and n.Wk_Num < 27
 Where not exists 
    (Select * from table f0
       join table n0 
           on n0.Wk_Num = (Select Min(Wk_Num) from table
                           Where Wk_Num > f0.Wk_Num)
              and n0.WkNum < f.Wk_Num))