How to find 7 consecutive non zero values in SQL?-CodePudding

If we have a table of 15 columns, the first one is id, and the other columns have numeric data type, in MySQL or a similar languages, how can I find if the record have 7 consecutive non zero values, meaning that 7 of the contiguous columns have a value not equals to zero?

We can write a query has a lot of OR operations to check that each 7 columns don't have 0 value, but I want to know if there is shorter way

CodePudding user response：

Try the following using CONCAT and INSTR functions, explanation is within query comments:

/*
check if the col value is not equal to 0, 
this will return 1 if true and 0 if false,
and concatenate all of the returned values.
*/
with concat_cols as
(
  select *, 
   concat(             
     col1<>0, col2<>0, col3<>0, col4<>0, col5<>0, 
     col6<>0, col7<>0, col8<>0, col9<>0, col10<>0, 
     col11<>0, col12<>0, col13<>0, col14<>0, col15<>0
   ) as con_col
  from table_name
)
/* 
using the instr function, check if the concatenated
result contains 7 consecutive 1s (7 not 0 values).
*/
select * from concat_cols
where instr(con_col, repeat('1', 7))

See demo

CodePudding user response：

One way:

field(0,col1,col2,col3,col4,col5,col6,col7) = 0 or
field(0,col2,col3,col4,col5,col6,col7,col8) = 0 or
field(0,col3,col4,col5,col6,col7,col8,col9) = 0 or
field(0,col4,col5,col6,col7,col8,col9,col10) = 0 or
field(0,col5,col6,col7,col8,col9,col10,col11) = 0 or
field(0,col6,col7,col8,col9,col10,col11,col12) = 0 or
field(0,col7,col8,col9,col10,col11,col12,col13) = 0 or
field(0,col8,col9,col10,col11,col12,col13,col14) = 0 or
field(0,col9,col10,col11,col12,col13,col14,col15) = 0

CodePudding user response：

SELECT id
 FROM your_table
 WHERE SUM(CASE WHEN column_2 > 0 THEN 1 ELSE 0 END  
        CASE WHEN column_3 > 0 THEN 1 ELSE 0 END  
        CASE WHEN column_4 > 0 THEN 1 ELSE 0 END  
        CASE WHEN column_5 > 0 THEN 1 ELSE 0 END  
        CASE WHEN column_6 > 0 THEN 1 ELSE 0 END  
        CASE WHEN column_7 > 0 THEN 1 ELSE 0 END  
        CASE WHEN column_8 > 0 THEN 1 ELSE 0 END) >= 7