這篇文章主要講解了“如何利用SQL和Python分別實(shí)現(xiàn)人流量查詢”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“如何利用SQL和Python分別實(shí)現(xiàn)人流量查詢”吧！

10年的烏翠網(wǎng)站建設(shè)經(jīng)驗(yàn)，針對(duì)設(shè)計(jì)、前端、開(kāi)發(fā)、售后、文案、推廣等六對(duì)一服務(wù)，響應(yīng)快，48小時(shí)及時(shí)工作處理。全網(wǎng)營(yíng)銷推廣的優(yōu)勢(shì)是能夠根據(jù)用戶設(shè)備顯示端的尺寸不同，自動(dòng)調(diào)整烏翠建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調(diào)整網(wǎng)站的寬度，無(wú)論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設(shè)計(jì)，從而大程度地提升瀏覽體驗(yàn)。創(chuàng)新互聯(lián)從事“烏翠網(wǎng)站設(shè)計(jì)”,“烏翠網(wǎng)站推廣”以來(lái)，每個(gè)客戶項(xiàng)目都認(rèn)真落實(shí)執(zhí)行。

案例介紹

案例來(lái)源于LeetCode，這樣的需求在時(shí)間序列數(shù)據(jù)中還是較為常見(jiàn)的。

某市體育館每日人流量信息被記錄在stadium表的三列信息中：序號(hào) (id)、日期 (visit_date)、 人流量  (people)，找出至少連續(xù)三行人流量不少于100的記錄。

思路分析

最簡(jiǎn)單的思路肯定是對(duì)stadium表進(jìn)行三次笛卡爾積連接，但這種方式在數(shù)據(jù)量大時(shí)不可取，而且也不具備泛化性(譬如需求改成至少連續(xù)十行)。網(wǎng)上也流傳著阿里的編程規(guī)范——禁止三表以上的連接。

總之，這種思路不是我們?cè)摬扇〉?，我們需要尋找其它思路?/p>

(1)構(gòu)建等差數(shù)列

從上圖中我們能發(fā)現(xiàn)一個(gè)規(guī)律，滿足條件的數(shù)據(jù)區(qū)域在原始表和結(jié)果表中的行編號(hào)均是等差數(shù)列，兩個(gè)等差數(shù)列的差值是固定的。譬如，數(shù)列A1和B1的差值均為1;數(shù)列A2和B2的差值均為2。

只要我們保證每塊區(qū)域等差數(shù)列的差值各不相等，那我們就可以通過(guò)篩選差值出現(xiàn)的次數(shù)來(lái)篩選滿足條件的區(qū)域。例如，差值2出現(xiàn)了4次，滿足條件，那該差值對(duì)應(yīng)的記錄就是我們需要的數(shù)據(jù)。

構(gòu)建差值的方式除了通過(guò)行編號(hào)外，也還有其它方式，大家可以想一想。

(2)數(shù)據(jù)切片

從圖中可看出，if_true是輔助列，表示是否滿足條件，1為True，0為False。我們要選擇滿足條件的區(qū)域，可通過(guò)用0對(duì)該列進(jìn)行切片，得到的是全為1的不同長(zhǎng)度的小數(shù)列，根據(jù)每個(gè)小數(shù)列的長(zhǎng)度來(lái)篩選滿足條件的區(qū)域。

在圖中就是得到了長(zhǎng)度為a和b的數(shù)列，通過(guò)計(jì)算數(shù)列的長(zhǎng)度來(lái)找出滿足條件的區(qū)域。

程序?qū)崿F(xiàn)

上節(jié)我們選擇了兩種思路，其中Python兩種思路都可以實(shí)現(xiàn)，SQL可實(shí)現(xiàn)第一種思路。本節(jié)用SQL實(shí)現(xiàn)第一種思路，用Python實(shí)現(xiàn)第二種思路。

(1)SQL

select id,visit_date,people from (select t2.*,count(1) over(partition by rn2) rn3 from  (selectt1.*,rn1 - row_number() over(order by visit_date) rn2 from (select *,row_number() over() as rn1 from stadium order by visit_date)t1 #t1表對(duì)日期升序排列后生成行編號(hào) where people>=100) t2 #t2表篩選人數(shù)不低于100的數(shù)據(jù)，并用原行編號(hào)減去新生成的行編號(hào)得到差值 where 1=1) t3 #t3表統(tǒng)計(jì)每類差值出現(xiàn)的次數(shù) where rn3>2 #篩選次數(shù)大于2的數(shù)據(jù)即為所需要的數(shù)據(jù)

因?yàn)閷?shí)際中表中的ID幾乎都不是連續(xù)的數(shù)字，所以為了保證泛化性就先生成了行編號(hào)，這樣就不用依賴于ID了。

除此之外也還可以通過(guò)用戶變量等方式實(shí)現(xiàn)，大家可以試著想一想。

(2)Python

import pandas as pd dt=pd.DataFrame({"id":range(1,9),                   "visit_date":pd.date_range(start="2017-01-01",periods=8),                   "people":[10,109,150,99,145,1455,199,188]}) dt["col1"]=dt["people"].apply(lambda x : 1 if x>=100 else 0) #生成人數(shù)是否不低于100的新列 dt['counter'] = (dt["col1"]==0).cumsum() #按照col1列是否為0計(jì)算累計(jì)和，標(biāo)記每個(gè)連續(xù)區(qū)域 dt = dt[dt["col1"] !=0] #剔除人數(shù)低于100的記錄 gb=dt.groupby("counter")["id"].count() # 統(tǒng)計(jì)各標(biāo)記值的次數(shù) result=dt[dt["counter"].isin(gb[gb>2].index)] #篩選滿足條件的數(shù)據(jù)

這里有一點(diǎn)需要注意，如果直接將col1列轉(zhuǎn)為字符串按0進(jìn)行切片的話，雖然可以求出滿足條件的區(qū)域數(shù)量和長(zhǎng)度，但很難再尋找到具體的區(qū)域。

split_col1="".join([str(i) for i in dt["col1"]]).split("0")

原本是按照的這種思路，但發(fā)現(xiàn)尋找長(zhǎng)度符合字符串在原列表中的索引時(shí)會(huì)比較麻煩，尤其是當(dāng)需要查找多個(gè)索引值時(shí)。

但此種思路還是非常重要，因?yàn)樵谥皇怯?jì)算連續(xù)區(qū)域的最大值時(shí)會(huì)非常簡(jiǎn)單。

感謝各位的閱讀，以上就是“如何利用SQL和Python分別實(shí)現(xiàn)人流量查詢”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)如何利用SQL和Python分別實(shí)現(xiàn)人流量查詢這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！