day4 crowd in the gym

This question uses MySQL 8.0 and has not been tested in MySQL 5.6, so it is not guaranteed to be correct.

topic

Topic source: The flow of people in the

Find out three or more good records with the number of people in each row greater than 100 and id

create table stadium (
    id int,
    visit_date date,
    people int
)

insert into stadium values
(1, '2017-01-01', 10),
(2, '2017-01-02', 109),
(3, '2017-01-03', 150),
(4, '2017-01-04', 99),
(5, '2017-01-05', 145),
(6, '2017-01-06', 1455),
(7, '2017-01-07', 199),
(8, '2017-01-09', 188);

SQL: Method 1

select * from (
    select *, count(*) over(partition by diff) as counts from (
        select *, id - row_number() over(order by id) as diff from stadium
        where people >= 100
    ) as base
) as s where counts >= 3;

Parse

The crux of the problem is how to know which days of id are consecutive.

The method used here is to first filter out peope greater than or equal to 100 , then rank the data, and then id . If the difference between them is the same, it means that those days are consecutive.

Specific steps:

• First filter out the data people greater than or equal to 100
• Calculate the rank using row_number() versus id
• With id subtracting the rank is calculated id the difference between and the position (as a temporary table base )

• For base query and follow diff group, named counts (as a temporary table s )

  • Using over(partition by diff) here is more accurate group by Because group by needs to correspond one-to-one select
• Query the s table and filter out the data counts greater than or equal to 3

SQL: Method 2

select * from (
    select *, (id - (@rrk:=@rrk + 1)) as diff
    from stadium, (select @rrk:=0) as init where people >= 100
) as s1 where diff in (
    select diff from (
        select (id - (@rk:=@rk + 1)) as diff
        from stadium, (select @rk:=0) as init where people >= 100
    ) as s2 group by diff having count(*) >= 3
);

Parse

The idea is the same as method 1, but the implementation method is different. Here, the grouping group by , so the steps are a little more complicated than the method of partition by

• First filter out the data people greater than or equal to 100
• Then use the variable to calculate diff (you can also use row_number() ) as a temporary table s1 .
• Query the s1 table to filter out the data we want
• Here we think that if there is a diff , we can filter in
• This step is to repeat the above diff , and then use it as a temporary table s2 .

• Here also the outside layer query packet diff , is select diff from s2 , using group by of diff group, and with having screened greater than or equal 3 of diff

  • group by needs to be in select fields. If this query is not made, group by will be problems with using 061f3c5b04635a.
• Query the s1 table and use the in operator to find the data.

Tips

row_number() Syntax

Cannot use two same variables in one SQL

More reference for solving problems: https://github.com/astak16/blog-mysql