Python與資料庫交互——窗口函數

2022-01-01 Stata and Python數據分析

本文作者:張孟晗,中南財經政法大學統計與數學學院

本文編輯:趙一帆

技術總編:陳   鼎

       為了感謝大家長久以來的支持和信任,爬蟲俱樂部為大家送福利啦!!!Stata&Python特惠課程雙雙上線騰訊課堂~爬蟲俱樂部推出了Python編程培訓課程Stata基礎課程Stata進階課程Stata文本分析正則表達式網絡爬蟲基本字符串課程。報名課程即可加入答疑群,對報名有任何疑問歡迎在公眾號後臺和騰訊課堂留言哦。我們在這篇推文的最後提供了每門課程的課程二維碼,大家有需要的話可以直接掃描二維碼查看課程詳情並進行購買哦~

首先,我們將excel中的數據導入mysql資料庫。大致可以分為兩步,先將excel中的數據轉換成DataFrame格式,然後再將DataFrame格式的數據導入到資料庫中。
import pandas as pdfrom sqlalchemy import create_engine# 讀入數據df = pd.read_excel('F:\python\shuju.xlsx')# 建立驅動引擎zzz = create_engine('mysql+pymysql://root:121215zmh@localhost:3306/zzz?charset=utf8')# 將數據導入資料庫df.to_sql('datas',con = zzz, index= False,if_exists= 'replace')

通過登錄Navicat查看,我們可以看到數據已成功導入到了資料庫中。

如果我們想對導入的數據根據銷量對各品牌進行排序編號,想對銷量進行一階滯後,想取出各品牌最高銷量的那條記錄,想計算一階移動平均數,想……別想了,強大的窗口函數,一切問題輕鬆解決!MySQL從8.0版本開始支持窗口函數。窗口函數的作用類似於在查詢中對數據進行分組,不同的是,分組操作會把分組的結果聚合成一條記錄,而窗口函數是將結果置於每一條數據記錄中。窗口函數可以分為靜態窗口函數和動態窗口函數 。靜態窗口函數的窗口大小是固定的,不會因為記錄的不同而不同;動態窗口函數的窗口大小會隨著記錄的不同而變化。(Tips:計算移動平均值特別有用)窗口函數的語法為:函數 OVER([PARTITION BY 欄位名 ORDER BY 欄位名 ASC|DESC ROWS BETWEEN … AND …])●  PARTITION BY子句:指定窗口函數按照哪些欄位進行分組。分組後,窗口函數可以在每個分組中分別執行。●  ORDER BY子句:指定窗口函數按照哪些欄位進行排序。執行排序操作使窗口函數按照排序後的數據記錄的順序進行編號。● ROWS BETWEEN … AND …子句:通常用來作為滑動窗口使用,進行逐期遞移。窗口函數總體上可以分為序號函數、分布函數、前後函數、首尾函數和其他函數,具體函數及其說明如下表:

import pymysqlconn=pymysql.connect(host='localhost',user='root',password='121215zmh',database='zzz',charset='utf8')

下面我們挑幾個重要的窗口函數向大家演示其用法,幫助大家更好的理解窗口函數是如何對數據進行處理的。1.序號函數

(1)ROW_NUMBER()

result = pd.read_sql("select *,row_number() over(partition by name order by number desc) as rownumber from datas;",con=conn)result

結果可見最後增加了名為rownumber的一列,這一列是按照name類別進行排序,並且序號無間斷且不重複,我們可以看出apple第2季度銷量最高為1200,huawei第4季度銷量最高為1100。
a = pd.read_sql("select *,rank() over(partition by name order by number desc) as ranks,dense_rank() over(partition by name order by number desc) as denserank from datas;",con=conn)a

從上表的定義我們可以得出,根據RANK()排序後,huawei對應四行得出的結果順序應該為1,2,2,4,而根據DENSE_RANK()排序後,huawei對應四行得出的結果順序應該為1,2,2,3,與結果顯示一致。

(3)python基礎語法實現

作為比較,我們演示一下用pandas裡的rank方法對銷量進行排序
"""加一列排序序號的兩種方法"""df = pd.concat([df,df.groupby(by='name',as_index=False)['number'].rank(ascending=False,method='dense').rename(columns={'number': 'rank'})],axis=1) df = pd.concat([df,df.groupby(by='name',as_index=False)[['number']].apply(lambda x :x.rank(ascending=False,method='dense')).rename(columns={'number': 'rank'})],axis=1) def sort(df,column='rank'):    return df.sort_values(by=column)df.groupby(by='name',group_keys=False).apply(sort)

可以看到,使用pandas來進行分組排序比較複雜,首先得調用groupby函數進行分組,之後調用rank函數進行排序,由於是聚合之後的結果,只有rank一列,所以還要藉助concat函數將結果加在原列表的最後面,並定義一個函數對分組後的rank進行排序,才能達到我們所要的效果。兩者一對比,不難發現利用sql的窗口函數語句非常的方便快捷。
result = pd.read_sql("select *,cume_dist() over(partition by name order by number) as cumedist from datas;",con=conn)result

CUME_DIST()函數主要用於查詢小於等於某個值的比例,我們用name分類後根據number升序排列,新加的cumedist一列展示了各類別中小於等於各number值的比例。此函數可以用於生成滯後或者提前n數據,從而可以鮮明的對比出不同時期的數量差異。
result = pd.read_sql("select *,lag(number,1) over(partition by name) as lags from datas;",con=conn)result

從結果上我們可以看到,新生成的lags列是number列滯後一期的結果,從中我們可以清楚地對比本季度與上一季度的銷量差異。其實累計函數就是以聚合函數作為窗口函數並配上order by子句,例如我們想計算各品牌手機本年內各季度的累計銷售額,用累計函數實現十分方便。
reresult = pd.read_sql("select *,sum(number) over(partition by name order by quarter) cunsum from datas;",con=conn)result

可以看到,新生成的a列為各各品牌從第一季度到第四季度的累計銷售額,apple的前半年銷售額為1700,全年的銷售額為2900。
動態窗口是窗口函數比較高級的用法,具體來說就是我們可以控制每次要聚合的列,這對於求移動平均值來說十分的方便。
result = pd.read_sql("select *,avg(number) over(partition by name rows between 1 preceding and current row) as moveavg from datas;",con=conn)result

我們以apple類為例進行講解,我們生成的moveavg列是本行所對應的number及其上一行結合起來的平均值,如850=(500+1200)/2。需要注意的是,由於第一行沒有上一行,所以第一行輸出的是500/1=500。大家有沒有覺得用窗口函數完成一些小任務十分的方便呢,不妨趕快動手去試一試吧!

最後,我們為大家揭秘雪球網(https://xueqiu.com/)最新所展示的滬深證券和港股關注人數增長Top10。

騰訊課堂課程二維碼








            

 對我們的推文累計打賞超過1000元,我們即可給您開具發票,發票類別為「諮詢費」。用心做事,不負您的支持!










   微信公眾號「Stata and Python數據分析」分享實用的Stata、Python等軟體的數據處理知識,歡迎轉載、打賞。我們是由李春濤教授領導下的研究生及本科生組成的大數據處理和分析團隊。

   武漢字符串數據科技有限公司一直為廣大用戶提供數據採集和分析的服務工作,如果您有這方面的需求,請發郵件到statatraining@163.com,或者直接聯繫我們的數據中臺總工程司海濤先生,電話:18203668525,wechat: super4ht。海濤先生曾長期在香港大學從事研究工作,現為知名985大學的博士生,爬蟲俱樂部網絡爬蟲技術和正則表達式的課程負責人。


此外,歡迎大家踴躍投稿,介紹一些關於Stata和Python的數據處理和分析技巧。

投稿郵箱:statatraining@163.com投稿要求:
1)必須原創,禁止抄襲;
2)必須準確,詳細,有例子,有截圖;
注意事項:
1)所有投稿都會經過本公眾號運營團隊成員的審核,審核通過才可錄用,一經錄用,會在該推文裡為作者署名,並有賞金分成。

2)郵件請註明投稿,郵件名稱為「投稿+推文名稱」。
3)應廣大讀者要求,現開通有償問答服務,如果大家遇到有關數據處理、分析等問題,可以在公眾號中提出,只需支付少量賞金,我們會在後期的推文裡給予解答。


相關焦點

  • Stata-Python交互-3:如何安裝Python宏包
    -9:將python數據導入StataStata-Python交互-8:將Stata數據導入PythonStata-Python交互-7:在Stata中實現機器學習-支持向量機Stata-Python交互-6:調用APIs和JSON數據Stata-Python交互-5:邊際效應三維立體圖示Stata-Python交互-4:如何調用Python宏包Stata-Python交互-3:如何安裝Python
  • Python 操作MySQL資料庫
    安裝python即pycharm環境。mysqlclient-1.3.12-cp36-cp36m-win_amd64.whl安裝命令:pipinstall mysqlclient-1.3.12-cp36-cp36m-win_amd64.whl 第三步:python
  • 【python】Tkinter窗口可視化二
    這個Button的運行函數為print_selection,其中有一個命令:curselection()是獲取選中的值。本質;要素;要點;必需品推薦閱讀:精彩知識回顧【python】Tkinter可視化窗口(一)【珍藏版】長文詳解python正則表達式這些神經網絡調參細節,你都了解了嗎
  • 【python】Tkinter可視化窗口(三)
    】Tkinter可視化窗口一【python】Tkinter窗口可視化二這次你將會學到Scale,Checkbutton控制項。from_和to:表示從from_的值到to的值,用from_而不用from的原因是from是python的關鍵字!orient:方向,這裡的HORIZONTAL是橫向的意思,如果不設置該參數,默認是橫向。
  • Python數據科學實踐 | 資料庫1
    本章所要講解的內容是如何通過Python與資料庫交互完成數據科學實踐項目。具體內容將會通過Python的SQLAIchemy模塊講解。為什麼使用SQLAlchemy?在回答這個問題之前,得先回答另一個問題:為什麼要使用SQL?試想一下,在第七章網絡爬蟲中,我們直接使用excel、txt和csv文件作為數據存儲的載體,這樣做會遇到什麼問題?
  • python網頁爬蟲實戰:PEER資料庫地震波批量下載
    python大火的原因得益於簡單易懂的語句結構和豐富強大的功能包。隨著人工智慧、機器學習和大數據科學日益興起和火爆,python迅速進入大眾視野,甚至推動了少兒編程。地震工程同樣是一門基於大數據的科學,全球數以萬計的學者的研究資料和時刻發生的地震的地面運動數據構成了極其龐大的資料庫。因此,python可以幫助從事地震工程研究的學者輕鬆愉快地獲得大量數據。
  • 鞏固SQL - 窗口函數&變量&數據透視圖
    (工作寫的是hive,為方便演示,本文章均使用Mysql8.0.16版本)一、窗口函數1、什麼是窗口函數窗口函數,也叫OLAP函數(Online Anallytical Processing,聯機分析處理),可以對資料庫數據進行實時分析處理。
  • Python編寫裝飾器,給任意函數計時
    對應這個調用,我們可以自己在函數開頭列印參數、末尾列印結果、結尾時間減去開始時間計時。然而這樣的代碼,其實任何函數都一樣,有沒有辦法寫一次代碼多次調用呢。這其實就是「給任何函數,增加一個公共的附加特性」,可以用Python的解釋器解決。
  • 【編程】第一期:Python Tkinter圖形化教學-基本窗口
    開始的話python屬實是一個很好的語言,功能很全,上手簡單,下面來看看python
  • python將腳本與交互模式結合
    將腳本與交互模式結合也可以將腳本文件與交互模式結合起來,以將預先編寫的腳本合併到交互程序中。入門為了使用腳本文件,您必須為計算機準備使用它們,或者必須採用 稍後描述的 替代方法 之一 。python.exe的位置首次安裝Python軟體時,應該在硬碟驅動器上的某個位置創建一個目錄,其中包含一個名為 python.exe 的文件 。您將需要找到該文件。
  • 【Python-資料庫】Numpy資料庫
    Numpy是一個內置的數學資料庫,很多知識應該是涉及到高數的,但是無奈很多我已經不記得了。只能介紹一些我了解的了。
  • 量化交易之《Python數據分析》
    模塊xlrd能用來解析讀取.xls和.xlsx文件中的數據使用Rest web服務和Json使用feedparser解析Rss文件使用beautiful soup解析HTML使用pickle解析pkl文件 箱形圖、對數圖、散點圖、泡式圖、三維圖、時滯圖、自相關圖 移動平均值、窗口函數
  • 「技術文章」《Python 資料庫 GUI CGI編程》
    1.寫在前邊上一次,我們介紹了Python的入門的文章,今天我們就來介紹下Python的資料庫,GUI, CGI的編程,以及Python 2.x 與Python 3.x的區別。2.連接資料庫 Python 標準資料庫接口為 Python DB-API,MySQLdb 是用於Python連結Mysql資料庫的接口。
  • 用 python 做一個動態可視化的交互大屏
    今天我們就來用 python 做一個動態可視化的交互大屏圖。隨著科技的飛速發展,數據呈現爆發式的增長,任何人都擺脫不了與數據打交道,社會對於「數據」方面的人才需求也在不斷增大。因此了解當下企業究竟需要招聘什麼樣的人才?需要什麼樣的技能?不管是對於在校生,還是對於求職者來說,都顯得很有必要。
  • Python | Python學習之Redis交互詳解
    本篇為redis篇,包含實例演示,主從服務配置,python交互等內容。nosql與redis介紹nosql資料庫:不支持SQL語法存儲結構跟傳統關係型資料庫中的那種關係表完全不同,nosql中存儲的數據都是KV形式NoSQL的世界中沒有一種通用的語言,每種nosql資料庫都有自己的api和語法,以及擅長的業務場景NoSQL中的產品種類相當多:Mongodb,Redis,Hbase hadoop,Cassandra
  • 我的實戰經驗分享:深入淺出Python資料庫操作
    默認配置下,點開後會出現一個黑底白字的控制臺窗口,然後自動調出系統默認瀏覽器彈出Notebook的主界面窗口。圖:Jupyter Notebook的網頁界面和控制臺界面Notebook黑底白字的控制臺窗口,在用的過程中千萬不能關閉它,最小化它即可,不必太多理會。
  • SFI:Stata與Python的數據交互手冊(一)
    本文作者:張學人文字編輯:錢夢璇技術總編:張   邯Stata與Python的交互難題
  • SQL、Pandas、Spark:窗口函數的3種實現
    窗口函數是資料庫查詢中的一個經典場景,在解決某些特定問題時甚至是必須的。
  • python內置函數format的使用方法
    前言其實在Java中,我們已經解除過format的使用,例如:當我們要把昨天的訂單金額統計後顯示到前臺時,由於資料庫存儲的是double格式的數據,在統計完昨天的訂單金額後仍然是double類型的數據,但是,之前遇到過此類問題的小夥伴都知道,這個訂單額有時候會喪失精度,變成一個很長的
  • 通俗易懂的學會:SQL窗口函數
    窗口函數,也叫OLAP函數(Online Anallytical Processing,聯機分析處理),可以對資料庫數據進行實時分析處理。窗口函數的基本語法如下:<窗口函數> over (partition by <用於分組的列名>                order by <用於排序的列名>)那麼語法中的<窗口函數>都有哪些呢?