SQL Server 查找大于数字的连续列

lsmd5eda 于 2022-12-10 发布在其他

关注(0)|答案(3)|浏览(120)

I have a table that is setup saving data for hours of the day with values attached. The table looks like so (it includes all hours, but I'm only showing a subset here):
| ID | Start | Finish | HR_8 | HR_9 | HR_10 | HR_11 | HR_12 | HR_13 | HR_14 | HR_15 |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | 9 | 15 | 10 | 22 | 25 | 30 | 22 | 24 | 8 | 6 |
| 2 | 12 | 15 | 15 | 18 | 20 | 25 | 15 | 18 | 2 | 6 |
| 3 | 8 | 13 | 7 | 12 | 18 | 25 | 9 | 12 | 34 | 19 |
| 4 | 12 | 15 | 8 | 22 | 25 | 31 | 13 | 22 | 24 | 26 |
If I subtract finish from start I get a number of hours. I need to return an indicator of a 1 or a 0 based on the following rule. Return 1 if 60% of the time from Start - Finish (in 24 hour format) falls into an area with at least 3 consecutive numbers greater than 17.
Example:
ID 1 has a consecutive block in HR_9, HR_10, HR_11, HR_12, and HR_13. In this example, 9 - 15 is 100% inside of that range
ID 3 has two consecutive blocks in HR_9, HR_10, and HR_11, and also in HR_13, HR_14, and HR_15. 12 - 15 is at least 60% inside of HR_13, HR_14, and HR_15. However, it could have also fell inside of the first consecutive block.
I will be updating a separate column in this same table with the 1 or 0 derived from the rule above.
I know this table is setup strangely, but it is what I've been given to work with. Any help would be greatly appreciated.

sql-server

来源：https://stackoverflow.com/questions/74748870/find-consecutive-columns-greater-than-a-number

3条答案

按热度按时间

k4emjkb11#

SELECT
  ID,
  Start,
  Finish,
  HR_8,
  HR_9,
  HR_10,
  HR_11,
  HR_12,
  HR_13,
  HR_14,
  HR_15,
  -- Calculate the number of hours between the Start and Finish times
  (Finish - Start) AS num_hours,
  -- Use a CASE expression to find blocks of numbers that are greater than 17
  -- and return 1 if at least 60% of the hours between Start and Finish fall
  -- within a block
  CASE
    -- Iterate over the values in the row, starting at HR_8
    WHEN HR_8 > 17 THEN
      -- If the value is greater than 17, increment a counter variable
      -- and check if the next value is also greater than 17
      CASE
        WHEN HR_9 > 17 THEN
          -- If the next value is also greater than 17, increment the
          -- counter variable again and continue checking the next value
          -- until we reach a value that is not greater than 17
          CASE
            ...
            -- If we reach a value that is not greater than 17,
            -- check if the counter variable is greater than or equal to 3.
            -- If it is, then we have found a block of at least 3
            -- consecutive numbers that are greater than 17, so we can
            -- return 1. Otherwise, we return 0.
            ELSE
              CASE
                WHEN counter >= 3 THEN 1
                ELSE 0
              END
          END
        ELSE 0
      END
    ELSE 0
  END AS indicator
FROM your_table;

赞(0）回复(0）举报 2022-12-10

a7qyws3x2#

I believe that your first step should be to unpivot your data, either with a single hour per row or progressive groups of three values per row. While a SQL UNPIVOT provides some functionality, it is sometimes more convenient to use a CROSS APPLY , since it is more flexible.
A single-value pivot would be something like:

SELECT D.ID, D.Start, D.Finish, C.*
FROM @Data D
CROSS APPLY (
    VALUES
        ( 8, HR_8),
        ( 9, HR_9),
        (10, HR_10),
        (11, HR_11),
        (12, HR_12),
        (13, HR_13),
        (14, HR_14),
        (15, HR_15)
) C(HR, Value)
ORDER BY D.ID, C.Hr

ID	Start	Finish	HR	Value
1	9	15	8	10
1	9	15	9	22
1	9	15	10	25
1	9	15	11	30
1	9	15	12	22
1	9	15	13	24
1	9	15	14	8
1	9	15	15	6
2	12	15	8	15
2	12	15	9	18
...

Given that, you can apply your over-17 criteria and then use some window function techniques to check for consecutive ranges. See [sql-server] count consecutive for similar questions that may have the answers you need.
If you can reduce your problem down a 3-consecutive over-17 value condition with two inside the start/finish range, you can perhaps use a technique that unpivots your data into groups of three and then applies those tests.
Something like:

SELECT D.ID, D.Start, D.Finish, C.*, T.*
FROM @Data D
CROSS APPLY (
    VALUES
        (9,  HR_8,  HR_9,  HR_10),
        (10, HR_9,  HR_10, HR_11),
        (11, HR_10, HR_11, HR_12),
        (12, HR_11, HR_12, HR_13),
        (13, HR_12, HR_13, HR_14),
        (14, HR_13, HR_14, HR_15)
) C (HRMid, Value1, Value2, Value3)
CROSS APPLY (
    SELECT
        CASE WHEN C.Value1 > 17 AND C.Value2 > 17 AND C.Value3 > 17 THEN 1 ELSE 0 END AS ThreeConsecutive,
        CASE WHEN HrMid BETWEEN D.Start AND D.Finish AND D.Start <> D.Finish THEN 1 ELSE 0 END AS InRange
) T
ORDER BY D.ID, C.HrMid

ID	Start	Finish	HRMid	Value1	Value2	Value3	ThreeConsecutive	InRange
1	9	15	9	10	22	25	0	1
1	9	15	10	22	25	30	1	1
1	9	15	11	25	30	22	1	1
1	9	15	12	30	22	24	1	1
1	9	15	13	22	24	8	0	1
1	9	15	14	24	8	6	0	1
2	12	15	9	15	18	20	0	0
2	12	15	10	18	20	25	1	0
...

See this db<>fiddle for examples of the above.
You would then need to take these intermediate results and feed them into additional logic to GROUP BY ID and test for any satisfying rows in order to calculate your final result.

赞(0）回复(0）举报 2022-12-10

t40tm48m3#

Not sure about the 60% requirements, so I'm following the 3 consecutive over-17 rule. Please feel free to change the code as needed.
Assume that

table schema is (id, start, finish, hr01, hr02..., hr24)
all of the columns can't be NULLs.

Here's one way to do it:
Step 1. Convert the interval (start, finish) to an integer, of which there're 24 bits. Bit 1 indicate 1st hour; bit 2, 2nd hour; ...bit 24, 24th hours.
Step 2. Convert hr01, hr02...hr24 to an integer that bit n is 1 if hr_n > 17; 0, otherwise.
Step 3. Bitwise AND the above two integer and check if there are 3 consecutive 1s. If so return 1; 0, otherwise.
Here are some examples:

ID=1:
sf24bin         11111111111111000 <- start to finish: 3 - 16
hr24bin    1010001010111111000011 <- hr01 to hr24: 1 indicate > 17; 0, otherwise. 
consec_3         1010111111000000 <- bitwise and of the above two

ID=4:
sf24bin        111100000000000000
hr24bin  100011110111110100111111
consec_3       110100000000000000

Query:

-- convert (start, finish) to an integer
drop function sf2int;
create function sf2int(
    @start int,
    @finish int)
returns int
as
begin
   declare @n int = 0;
   declare @i int = @start;
   
   while @i <= @finish
   begin
       set @n = @n + power(2,@i);
       set @i = @i + 1;
   end;
   return @n;
end;

-- check if @x has @n consecutive 1s
create function consecutive_n(
    @x  int,
    @n  int)
returns int
as
begin
   declare @bitn int = dbo.sf2int(0,@n-1);   
   declare @lx   int = @x;
   
   while @lx > 0 
   begin
      if (@lx & @bitn) = @bitn
         return 1;      -- there are n consecutive 1s 
      else
         set @lx = @lx / 2;  -- shift 1 bit to the right
   end;
   return 0;
end;

-- do the job
with cte as (
select *,
       dbo.sf2int(start, finish) as sf24bin,
       case when hr01 > 17 then  power(2, 0) else 0 end 
     + case when hr02 > 17 then  power(2, 1) else 0 end
     + case when hr03 > 17 then  power(2, 2) else 0 end
     + case when hr04 > 17 then  power(2, 3) else 0 end
     + case when hr05 > 17 then  power(2, 4) else 0 end
     + case when hr06 > 17 then  power(2, 5) else 0 end
     + case when hr07 > 17 then  power(2, 6) else 0 end
     + case when hr08 > 17 then  power(2, 7) else 0 end
     + case when hr09 > 17 then  power(2, 8) else 0 end
     + case when hr10 > 17 then  power(2, 9) else 0 end
     + case when hr11 > 17 then  power(2,10) else 0 end
     + case when hr12 > 17 then  power(2,11) else 0 end
     + case when hr13 > 17 then  power(2,12) else 0 end
     + case when hr14 > 17 then  power(2,13) else 0 end
     + case when hr15 > 17 then  power(2,14) else 0 end
     + case when hr16 > 17 then  power(2,15) else 0 end
     + case when hr17 > 17 then  power(2,16) else 0 end
     + case when hr18 > 17 then  power(2,17) else 0 end
     + case when hr19 > 17 then  power(2,18) else 0 end
     + case when hr20 > 17 then  power(2,19) else 0 end
     + case when hr21 > 17 then  power(2,20) else 0 end
     + case when hr22 > 17 then  power(2,21) else 0 end
     + case when hr23 > 17 then  power(2,22) else 0 end
     + case when hr24 > 17 then  power(2,23) else 0 end as hr24bin
 from rs)
select *,
       sf24bin & hr24bin as hr_bin,
       dbo.consecutive_n(sf24bin & hr24bin,3) as consecutive_3_hrs
  from cte;

Some sample output:

id |start|finish|hr01|hr02|hr03|hr04|hr05|hr06|hr07|hr08|hr09|hr10|hr11|hr12|hr13|hr14|hr15|hr16|hr17|hr18|hr19|hr20|hr21|hr22|hr23|hr24|sf24bin |hr24bin |hr_bin  |consecutive_3_hrs|
---+-----+------+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+--------+--------+--------+-----------------+
  1|    3|    16|  26|  38|  11|  17|   4|   5|  26|  25|  34|  33|  44|  45|  15|  34|  15|  34|   9|  15|  12|  22|  14|  41|  13|  14|  131064| 2666435|   44992|                1|
  2|   13|    20|  40|  11|  39|  22|   1|   3|   3|  37|  10|   1|  15|   3|  24|   6|  45|  24|  43|  37|  44|   7|   4|  47|  40|  13| 2088960| 6803597|  507904|                1|
  3|    3|    20|  24|  37|   7|  35|  24|  46|  21|  16|  15|  14|  36|  11|  32|  40|  42|  28|  24|  13|  18|   5|  40|   6|  27|  40| 2097144|14021755| 1438840|                1|
  4|   14|    17|  19|  38|  34|  43|  20|  26|  12|  13|  29|  15|  30|  24|  27|  19|  32|   8|  40|  30|  37|  31|   4|   4|  15|  26|  245760| 9403711|  212992|                0|
  5|    7|    17|  44|   5|  47|  17|  10|  17|  37|  47|  26|  33|  31|  22|  44|   9|  49|  19|  11|  26|  48|  49|  36|  49|  23|  32|  262016|16703429|  188288|                1|
  6|    5|    22|   2|  15|  21|  15|  23|   8|  20|  43|  32|   7|  15|  12|   5|  13|  37|  29|  43|  49|  26|  18|  36|  27|  32|  35| 8388576|16761300| 8372672|                1|
  7|   13|    20|  28|  22|  40|  44|  32|  15|   1|  14|  14|  21|  26|  10|  10|  23|   1|   0|  27|  42|   4|  48|  21|  34|  12|   6| 2088960| 3876383| 1777664|                0|
  8|    2|    16|   5|  47|   9|  24|  33|  36|  22|  34|  29|  48|   3|  37|  25|  49|  10|  40|  48|  15|   3|  32|  38|   3|  16|  36|  131068|10075130|  113656|                1|
  9|    1|    21|  31|  44|  17|  43|  10|  34|  38|   8|  10|   3|   4|  40|  38|  40|  38|  26|  43|   3|  47|  40|  17|   4|  38|  30| 4194302|13498475|  915562|                1|
 10|   11|    23|   3|  24|  35|   1|  31|  23|  24|  43|   4|  27|  16|  36|  37|  19|  48|  16|  39|   7|  36|   8|  26|  13|  43|  12|16775168| 5602038| 5601280|                1|

赞(0）回复(0）举报 2022-12-10

我来回答

SQL Server 查找大于数字的连续列

3条答案

相关问题

热门标签

最新问答

ID	Start	Finish	HR	Value
1	9	15	8	10
1	9	15	9	22
1	9	15	10	25
1	9	15	11	30
1	9	15	12	22
1	9	15	13	24
1	9	15	14	8
1	9	15	15	6
2	12	15	8	15
2	12	15	9	18
...

ID	Start	Finish	HRMid	Value1	Value2	Value3	ThreeConsecutive	InRange
1	9	15	9	10	22	25	0	1
1	9	15	10	22	25	30	1	1
1	9	15	11	25	30	22	1	1
1	9	15	12	30	22	24	1	1
1	9	15	13	22	24	8	0	1
1	9	15	14	24	8	6	0	1
2	12	15	9	15	18	20	0	0
2	12	15	10	18	20	25	1	0
...

ID	Start	Finish	HR	Value
1	9	15	8	10
1	9	15	9	22
1	9	15	10	25
1	9	15	11	30
1	9	15	12	22
1	9	15	13	24
1	9	15	14	8
1	9	15	15	6
2	12	15	8	15
2	12	15	9	18
...

ID	Start	Finish	HRMid	Value1	Value2	Value3	ThreeConsecutive	InRange
1	9	15	9	10	22	25	0	1
1	9	15	10	22	25	30	1	1
1	9	15	11	25	30	22	1	1
1	9	15	12	30	22	24	1	1
1	9	15	13	22	24	8	0	1
1	9	15	14	24	8	6	0	1
2	12	15	9	15	18	20	0	0
2	12	15	10	18	20	25	1	0
...

ID	Start	Finish	HR	Value
1	9	15	8	10
1	9	15	9	22
1	9	15	10	25
1	9	15	11	30
1	9	15	12	22
1	9	15	13	24
1	9	15	14	8
1	9	15	15	6
2	12	15	8	15
2	12	15	9	18
...

ID	Start	Finish	HRMid	Value1	Value2	Value3	ThreeConsecutive	InRange
1	9	15	9	10	22	25	0	1
1	9	15	10	22	25	30	1	1
1	9	15	11	25	30	22	1	1
1	9	15	12	30	22	24	1	1
1	9	15	13	22	24	8	0	1
1	9	15	14	24	8	6	0	1
2	12	15	9	15	18	20	0	0
2	12	15	10	18	20	25	1	0
...