CODEWITHSUNDEEP
mysql
Jeff Vipul
Jeff Vipul

Reputation: 45

Relation Between 2 columns

In MYSQL there are 2 columns in table column A and column B and if in column A continuously comes one 10th time and in column B 11th time comes true(B can be 1 or 0 between these 10 times ) so I want that column id of B.

+----+---+---+
| id | A | B |
+----+---+---+
|  1 | 1 | 0 |
|  2 | 0 | 1 |
|  3 | 1 | 0 |
|  4 | 1 | 0 |
|  5 | 1 | 0 |
|  6 | 1 | 1 |
|  7 | 1 | 0 |
|  8 | 1 | 1 |
|  9 | 1 | 1 |
| 10 | 1 | 0 |
| 11 | 1 | 1 |
| 12 | 1 | 0 |
| 13 | 0 | 1 |
+----+---+---+

I need this (column B id) (Where Column A continuously come 1 (10 times) and Column B (11th id after contenious 10 time 1 in column A )

Upvotes: 1

Views: 101

Answers (2)

GMB
GMB

Reputation: 222512

This is a complicated one. Using window functions available in MySQL 8.0, I would proceed in 3 steps:

  • first compute row numbers in the overall group and in groups of A values
  • then do a cumulative sum of A values within groups of consecutive A values (using the difference between the 2 above groups), while using LEAD() to recover the id and B value of the next record
  • finally, filter on the record whose cumulative SUM is 10 and whose next B value is 1; the id of the next record is what you are looking for

Query:

SELECT leadID id
FROM (
    SELECT 
        id,
        SUM(A) OVER(PARTITION BY rn1 - rn2 ORDER BY id) sm,
        LEAD(id) OVER(ORDER BY id) leadID,
        LEAD(B) OVER(ORDER BY id) leadB
    FROM (
        SELECT
            id,
            A,
            B,
            ROW_NUMBER() OVER(ORDER BY id) rn1,
            ROW_NUMBER() OVER(PARTITION BY A ORDER BY id) rn2
        FROM mytable
    ) x
) x
WHERE sm = 10 AND leadB = 1

This demo on DB Fiddle with your sample data yields:

| id  |
| --- |
| 13  |

Upvotes: 0

P.Salmon
P.Salmon

Reputation: 17640

You could use a running total in a sub query to help you with this on versions prior to mysql 8.0

drop table if exists t;
create table t
(id int,A int,B int);
insert into t values
(1,  1 ,0),
(2,  0 ,1),
(3,  1 ,0),
(4,  1 ,0),
(5,  1 ,0),
(6,  1 ,1),
(7,  1 ,0),
(8,  1 ,1),
(9,  1 ,1),
(10, 1 ,0),
(11, 1 ,1),
(12, 1 ,0),
(13, 1 ,1),
(14, 1 ,1),
(15, 1 ,1),
(16, 0 ,1);

select t1.id,t1.a,t1.b
from 
(
select t.*,
        if(t.a = 1, @rt:=@rt+1,@rt:=0) rt
from t
cross join (select @rt:=0) r
order by t.id
) t1 
where t1.rt >= 10;

+------+------+------+
| id   | a    | b    |
+------+------+------+
|   12 |    1 |    0 |
|   13 |    1 |    1 |
|   14 |    1 |    1 |
|   15 |    1 |    1 |
+------+------+------+
4 rows in set (0.00 sec)

Upvotes: 2

Related Questions