Is there a way to get max consecutive counts of 1s across columns in SQL?

Question

Is there a way to get maximum consecutive counts across columns in SQL? I'm looking for longest duration of survival.

For example, if I have a dataset that looks like this

ID  T1  T2  T3  T4  T5  T6  T7  T8  T9
1   1   1   0   0   0   1   1   1   1
2   0   0   0   1   1   1   1   1   0
3   0   1   0   1   0   1   1   0   0
4   0   1   0   0   0   0   0   0   0
5   0   1   1   0   0   0   0   0   0
6   1   0   1   1   0   1   1   1   0

I want to add a column to get the maximum consecutive 1s across the columns T1-T9 so it would look like this

ID  T1  T2  T3  T4  T5  T6  T7  T8  T9  MAX
1   1   1   0   0   0   1   1   1   1   4
2   0   0   0   1   1   1   1   1   0   5
3   0   1   0   1   0   1   1   0   0   2
4   0   1   0   0   0   0   0   0   0   1
5   0   1   1   0   0   0   0   0   0   2
6   1   0   1   1   0   1   1   1   0   3

I guess SQL. I know SAS has an SQL procedure in it so either would work. — Asian_ricecakes, Nov 14 '20 at 03:02

Gordon Linoff · Answer 1 · 2020-11-14T13:19:58.000

0

I think the simplest method in proc sql might be a brute force approach:

select t.*,
       (case when t1||t2||t3||t4||t5||t6||t7||t8||t9 like '%111111111%' then 9
             when t1||t2||t3||t4||t5||t6||t7||t8||t9 like '%11111111%' then 8
             when t1||t2||t3||t4||t5||t6||t7||t8||t9 like '%1111111%' then 7
             when t1||t2||t3||t4||t5||t6||t7||t8||t9 like '%111111%' then 6
             when t1||t2||t3||t4||t5||t6||t7||t8||t9 like '%11111%' then 5
             when t1||t2||t3||t4||t5||t6||t7||t8||t9 like '%1111%' then 4
             when t1||t2||t3||t4||t5||t6||t7||t8||t9 like '%111%' then 3
             when t1||t2||t3||t4||t5||t6||t7||t8||t9 like '%11%' then 2
             when t1||t2||t3||t4||t5||t6||t7||t8||t9 like '%1%' then 1
             else 0
        end) as max
from t;

Here is a db<>fiddle illustrating the logic using Postgres.

edited Nov 14 '20 at 13:19

answered Nov 14 '20 at 03:20

Gordon Linoff

1,242,037
58
646
786

Not it won't give you the answer , it will give you max of 9 for all columns – Basharmal Nov 14 '20 at 03:56
I'm wondering if it will work to concatenate the columns and then look for a way to recognize a max series of 1s. – Asian_ricecakes Nov 14 '20 at 04:03
@MohammadUsman . . . Why would you say that? It is simply not true. – Gordon Linoff Nov 14 '20 at 12:31
First of all, I am sorry if I am disrespectful, and second, regarding your code, I have tried but it only gave me a max of 9 – Basharmal Nov 14 '20 at 12:42
@MohammadUsman . . . I added a db<>fiddle (albeit using Postgres) which shows that the logic *does* work. It does not return `9` for all the rows. – Gordon Linoff Nov 14 '20 at 13:20
@MohammadUsman . . . I don't have SAS on hand, but the code is standard SQL and definitely works as standard SQL (as the fiddle shows). I don't think SAS has a bug in implementing either `||` or `like`. And your answer strongly suggests that you are NOT using SAS, because it has constructs not supported natively in proc sql. – Gordon Linoff Nov 15 '20 at 13:03

Basharmal · Accepted Answer · 2020-12-08T02:26:27.063

**The below code is a way to get maximum consecutive counts across Column in MySQL I think you want across the Row **

create table t(id integer,t1 integer,t2 integer,t3 integer,t4 integer,t5 integer,t6 integer,t7 integer,t8 integer,t9 integer);
insert into t values(1,1,0,1,0,1,1,0,0,0),(2,0,0,1,1,1,0,0,0,0),(3,1,0,1,1,1,1,0,0,0);

WITH CTE1 AS
( 
       SELECT id , ROW_NUMBER() OVER (ORDER BY id) Rnk FROM t 
)
,CTE2 AS
(
       SELECT *,  CASE WHEN id-1 = LAG(id) OVER(ORDER BY rnk) THEN 0
       ELSE 1 END cols FROM CTE1 c2
)
,CTE3 AS
(
       SELECT *,  SUM(cols) OVER(ORDER BY rnk) Grouper FROM CTE2 c2
)
SELECT *  FROM
(
    SELECT COUNT(*) Counts FROM CTE3 GROUP BY Grouper  
)r
ORDER BY Counts DESC ;

Is there a way to get max consecutive counts of 1s across columns in SQL?

2 Answers2