一個SQL查詢連續三天的流量100以上的資料值【SQql Server】

路人阿丙發表於2020-09-10
SQLServer

題目

有一個商場，每日人流量資訊被記錄在這三列資訊中：序號 (id)、日期 (date)、 人流量 (people)。請編寫一個查詢語句，找出高峰期時段，要求連續三天及以上，並且每天人流量均不少於100。

例如，表 stadium：

 

 

 對於上面的示例資料，輸出為：

 

 

 這個題的題意已經很明白了，就是要找出 連續三天（含）以上人流量都在100（含）以上的資料。

我用SQL Server 來做吧（因為別人都用MySql,正所謂：常在河邊走，哪能都穿鞋？）

我做了點測試資料：

 

 方法一：

 

這種題目的思路，就是找到那些資料是即  在100（含）以上，而且又是連續三天對吧？拆分一下思路：
1、每天人流量 >=100

2、連續的天數 要 >=3

你看 我又說了個廢話對吧？ >=100簡單，但是 >=3 這個怎麼來呢？也就是怎麼求出連續天數呢？

舉個例子來總結個規律：

1000   -   1  =999

1001   -   2  =999

1002   -   3  =999

發現了嗎，如果一組“減數”和一組“被減數”都是遞增的，那麼他們的差是相同的哦！有沒有猜到我要幹嘛了？

先看一下滿足第1步需的SQL

select * from stadium where people>=100

 

 

 Q：這樣之後呢？和剛才的規律有毛關係嗎？

 A:嗯  目前是沒有的，但是 如果我給他們增加一列“遞增的行號” rownum，和 一例“日期偏移量" dayOffset呢？只要日期連貫，這個dayOffset是否就也是連貫了呢（也就自增了哇）。看圖吧

 select *,
      row_number()  over(order by [date])  as rownum
      ,datediff(day,'1990-1-1',[date]) dayOffset
    from stadium where people>=100

 

 

 來觀察一下，兩個紅色框的資料 是不是都是符合要求的？怎麼取出來呢？

還記得前邊總結的【如果一組“減數”和一組“被減數”都是遞增的，那麼他們的差是相同】的例子嗎？這會要用上了

看程式碼：

 select *,t1.dayOffset-t1.rownum flag from (
      select *,
          row_number()  over(order by [date])  as rownum
          ,datediff(day,'1990-1-1',[date]) dayOffset
        from stadium where people>=100 
    )t1

 

 

 看到沒有？“日期偏移量" -“遞增的行號” 得到的值，如果是連續日期，得到的值相同，如果日期中斷，就會得到一個新的值了，這個就不解釋了吧

得到了這個值之後，我們是不是可以使用partition來做個分割槽求數量 最後過濾呢？來吧 就這樣了

 select *,count(*) over(partition by t2.flag) conDays from (
     select *,t1.dayOffset-t1.rownum flag from (
          select *,
              row_number()  over(order by [date])  as rownum
              ,datediff(day,'1990-1-1',[date]) dayOffset
            from stadium where people>=100 
        )t1
    )t2

 

 透徹了不？最後過濾一下  是不是就可以了？順便只顯示目標資料列就可以了：

select t3.ID,t3.[date],t3.people from (
     select *,count(*) over(partition by t2.flag) conDays from (
         select *,t1.dayOffset-t1.rownum flag from (
              select *,
                  row_number()  over(order by [date])  as rownum
                  ,datediff(day,'1990-1-1',[date]) dayOffset
                from stadium where people>=100 
            )t1
        )t2
    ) t3 where t3.conDays>=3

 

 

這種型別的題目還可以繼續延伸：

比如：高峰期延續最久的時間段範圍？

比如：高峰期持續最長的天數？

類似這種，都可以根據 日期偏移量和行號差的思路來做 

 

方法二：

這個方法就很好理解了，3天對不？我假設每一天都是連續3天的第一天，如果連著三天的資料都是>=100那麼這將時我的目標資料

    select t1.id id1,t1.[date] date1,t1.people people1,
           t2.id id2,t2.[date] date2,t2.people people2,
           t3.id id3,t3.[date] date3,t3.people people3 
        from (select *,ROW_NUMBER() over(order by id) num from stadium) t1 
        join (select *,ROW_NUMBER() over(order by id) num from stadium) t2 on t1.num+1=t2.num 
        join  (select *,ROW_NUMBER() over(order by id) num from stadium) t3 on t2.num+1=t3.num
        where t1.people>=100 and  t2.people>=100 and  t3.people>=100

方法很簡單，就是聯合查詢三次這個表，是每條資料都和他後邊的兩條資料關聯。當然，最後兩條資料忽略，因為最後兩條資料已經出現在 倒數第三條資料中了，看查詢效果吧

 

 看看 我們要的資料 是不是都在這裡邊了？只是他們在同一個行中了，或者有重複資料了 對吧？沒關係  我們把他們拿出來 就好了! 看程式碼

with tb as 
(
    select t1.id id1,t1.[date] date1,t1.people people1,
           t2.id id2,t2.[date] date2,t2.people people2,
           t3.id id3,t3.[date] date3,t3.people people3 
        from (select *,ROW_NUMBER() over(order by id) num from stadium) t1 
        join (select *,ROW_NUMBER() over(order by id) num from stadium) t2 on t1.num+1=t2.num 
        join  (select *,ROW_NUMBER() over(order by id) num from stadium) t3 on t2.num+1=t3.num
        where t1.people>=100 and  t2.people>=100 and  t3.people>=100
)
select id1,date1,people1 from tb
union
select id2,date2,people2 from tb
union
select id3,date3,people3 from tb

這裡with as 和 union我就不介紹了吧。看一下最終效果是不是一樣的？

 

知識很簡單,方法多的很。歡迎留言分享其他方法

相關文章