LeetCode.601.Human_Traffic_of_Stadium

大数据工程师-公子

发布于 2019-06-15 15:18:23

42400

代码可运行

文章被收录于专栏：大数据仓库建设大数据仓库建设

运行总次数：0

代码可运行

（Creative Commons）

601.Human_Traffic_of_Stadium

https://leetcode.com/problems/human-traffic-of-stadium/

Solution-for-3_or_more consecutive_visit_date

Create table temp_visit_date
 select
	b.id,
	b.visit_date,
	b.people,
	DATE_SUB(visit_date,INTERVAL b.rk DAY) as diffdate
from (
	select
	a.id, 
	a.visit_date, 
	a.people, 
	(select count(distinct visit_date) from stadium b where b.people >= 100 and b.visit_date <= a.visit_date) as rk
	from stadium a 
	where a.people >= 100
	order by visit_date ASC
) b;

select
id,
visit_date,
people
from (
	select diffdate, count(1) as cnt from temp_visit_date group by diffdate having cnt >= 3
)tt left join temp_visit_date aa on aa.diffdate = tt.diffdate
;

Solutionp-for-3_or_more consecutive_rows

# Write your MySQL query statement below
select
    zz.id,
    zz.visit_date,
    zz.people
from (
    select diff_row, count(1) as cnt from (
        select
        b.id,
        b.visit_date,
        b.people,
        (id-rk) as diff_row
    from (
        select
            a.id, 
            a.visit_date, 
            a.people, 
            (select count(distinct id) from stadium b where b.people >= 100 and b.id <= a.id) as rk
        from stadium a 
        where a.people >= 100
        order by id ASC
    ) b
    ) xx group by diff_row having cnt >= 3
) tt left join (
    select
        b.id,
        b.visit_date,
        b.people,
        (id-rk) as diff_row
    from (
        select
            a.id, 
            a.visit_date, 
            a.people, 
            (select count(distinct id) from stadium b where b.people >= 100 and b.id <= a.id) as rk
        from stadium a 
        where a.people >= 100
        order by id ASC
    ) b
) zz on zz.diff_row = tt.diff_row
;