目錄
前言 .1
第1 章 用SQL 來做數據分析 7
1.1 什麼是數據分析 . 7
1.2 為什麼用SQL 10
1.2.1 SQL 是什麼 10
1.2.2 SQL 的優勢 13
1.2.3 SQL 與R 和Python 的對比 14
1.2.4 SQL 作為數據分析流程中的一部分 . 16
1.3 數據庫類型以及如何使用 19
1.3.1 行存儲數據 20
1.3.2 列存儲數據 22
1.3.3 其他的數據結構類型 23
1.4 總結 25
第2 章 為數據分析做準備 26
2.1 數據類型 27
2.1.1 數據庫的數據類型 . 27
2.1.2 結構化和非結構化數據 29
2.1.3 定量和定性數據 . 30
2.1.4 第一方、第二方和第三方數據 31
2.1.5 稀疏數據. 32
2.2 SQL 查詢結構 33
2.3 數據剖析：分布 36
2.3.1 直方圖和頻率 37
2.3.2 分箱 40
2.3.3 n-Tiles 43
2.4 數據剖析：數據質量 . 46
2.4.1 檢測重復數據 47
2.4.2 用GROUP BY 和DISTINCT 來處理重復數據 49
2.5 準備：數據清理 50
2.5.1 通過CASE 轉換來清理數據 51
2.5.2 數據類型轉換 54
2.5.3 處理空值：coalesce, nulliff, nvl 函數 57
2.5.4 缺失的數據 61
2.6 準備：數據構形 66
2.6.1 你需要怎樣的輸出：BI，可視化，統計，機器學習 67
2.6.2 用CASE 語句進行數據透視 68
2.6.3 用UNION 語句來取消數據透視 70
2.6.4 pivot（透視）和unpivot（取消透視）函數 73
2.7 總結 75
第3 章 時間序列分析 .76
3.1 日期、日期時間和時間操作 77
3.1.1 時區轉換. 78
3.1.2 日期和時間戳的格式轉換 . 80
3.1.3 日期相關的計算 . 85
3.1.4 時間相關的計算 . 88
3.1.5 連接不同來源的數據 90
3.2 零售銷售數據集 91
3.3 對數據進行趨勢分析 . 92
3.3.1 簡單的趨勢 93
3.3.2 比較時間序列的組成部分 . 95
3.3.3 計算占總數的百分比 . 104
3.3.4 運用索引以查看隨時間變化的百分比 108
3.4 滾動時間窗口 . 113
3.4.1 計算滾動時間窗口 115
3.4.2 稀疏數據的滾動時間窗口 121
3.4.3 計算累計值 . 124
3.5 季節性分析 127
3.5.1 同期比較：YoY 和MoM . 129
3.5.2 同期比較：與去年的同月進行對比 132
3.5.3 與多個以前的周期做對比 137
3.6 總結 . 140
第 4 章 同期群分析 141
4.1 同期群：一種有用的分析框架 142
4.2 立法者數據集 . 145
4.3 留存 . 146
4.3.1 基本留存曲線的 SQL 148
4.3.2 調整時間序列以提高留存率的準確性 152
4.3.3 從時間序列數據構建同期群 158
4.3.4 從單獨的表構建同期群 164
4.3.5 處理稀疏同期群 168
4.3.6 用除第一個日期以外的其他日期定義同期群 173
4.4 相關同期群分析 176
4.4.1 生存 177
4.4.2 返回或重復購買行為 . 181
4.4.3 累積計算 187
4.5 透過同期群看橫斷面分析 . 191
4.6 總結 . 200
第 5 章 文本分析 201
5.1 為什麼使用 SQL 進行文本分析 . 201
5.1.1 什麼是文本分析 202
5.1.2 為什麼 SQL 是文本分析的好選擇 202
5.1.3 什麼情況下 SQL 不是一個好的選擇 . 204
5.2 UFO 目擊數據集 205
5.3 文本特征 206
5.4 解析文本 208
5.5 文本轉換 214
5.6 在較大的文本塊素 223
5.6.1 通配符匹配：LIKE，ILIKE 223
5.6.2 精確匹配：IN，NOT IN . 229
5.6.3 正則表達式 . 232
5.7 構建與重塑文本 250
5.7.1 拼接 250
5.7.2 重塑文本 254
5.8 總結 . 258
第6 章 異常檢測 259
6.1 SQL 異常檢測的能力和限制 260
6.2 數據集 261
6.3 檢測異常值 262
6.3.1 通過排序查找異常 263
6.3.2 通過計算百分比和標準偏差發現異常 266
6.3.3 通過作圖可視化查找異常 274
6.4 異常的形式 283
6.4.1 異常值 283
6.4.2 異常的計數或頻率 287
6.4.3 數據缺失引起的異常 . 292
6.5 處理異常 294
6.5.1 探查 294
6.5.2 刪除 295
6.5.3 替代值替換 . 297
6.5.4 縮放 299
6.6 總結 . 301
第 7 章 實驗分析 302
7.1 用 SQL 進行實驗分析的優勢與局限性 . 303
7.2 數據集 305
7.3 實驗的類型 307
7.3.結果實驗：卡方檢驗 307
7.3.2 具有連續結果的實驗：t 檢驗 310
7.4 實驗的挑戰和拯救有缺陷的實驗的方法 312
7.4.1 變體分配 312
7.4.2 異常值 314
7.4.3 時間盒 315
7.4.4 重復暴露實驗 317
7.5 當無法進行控制實驗時：替代分析 . 318
7.5.1 前/ 後分析 319
7.5.2 自然實驗分析 321
7.5.3 閾值附近的群體分析 . 322
7.6 總結 . 323
第8 章 創建用於分析的復雜數據集 324
8.1 何時對復雜數據集使用 SQL 324
8.1.1 使用 SQL 的優點 . 325
8.1.2 什麼時候構建ETL . 326
8.1.3 何時將邏輯放入其他工具中 328
8.2 代碼組織 329
8.2.1 注釋 330
8.2.2 大寫，縮進，括號和其他格式技巧 331
8.2.3 存儲代碼 334
8.3 組織計算 335
8.3.1 理解SQL 子句的計算順序 . 335
8.3.2 子查詢 339
8.3.3 臨時表 341
8.3.4 公共表表達式 343
8.3.5 grouping sets 344
8.4 管理數據集大小和隱私問題 349
8.4.1 使用 % 、mod 進行抽樣 . 349
8.4.2 降低維數 351
8.4.3 PII 和數據隱私 . 355
8.5 總結 . 357
第9 章 結論 358
9.1 漏鬥分析 358
9.2 流失、中止和其他離開的定義 360
9.3 購物籃分析 365
9.4 資源 . 367
9.4.1 書籍和博客 . 368
9.4.2 數據集 369
9.5 最終的想法 370

前言在過去的20 年裡，我主要在科技公司工作，涵蓋了各類消費者和B2B( 即企業對企業) 行業，而這些日子中我大部分的工作時間都在用SQL 來處理和分析數據。在那段時間裡，數據量急劇增加，我所使用的技術也有了突飛猛進的進步。數據庫比以往任何時候都快，用於傳達數據含義的報告和可視化工具也比以往都更強大。然而，有一件事一直保持不變，那就是SQL 是我常用的數據分析工具箱的關鍵部分。我還清楚地記得剛開始接觸和學習SQL 時候的場景。我的職業生涯始於金融業，那時候電子表格占據了行業的主導地位，我在寫公式和記住所有鍵盤快捷鍵方面都已經非常熟練了。有一天，我發瘋似的按住Ctrl 和Alt 鍵並單擊了鍵盤上的每一個鍵，隻想看看究竟會發生什麼（然後為我的同事創建了一個速查表）。這既帶給我樂趣也是為了更好地工作：我處理電子表格的速度越快，就越有可能在午夜前完成工作，這樣就可以回家睡覺了。精通電子表格讓我進入了下一個崗位，這是我第一次接觸數據庫和SQL 的初創公司。我的一部分工作職責是處理電子表格中的庫存數據，由於早期的互聯網規模，數據集有時有上萬行。這在當時是一個“大數據”，至少對我來說是這樣。我養成了在電腦CPU 忙於運行它的vlookup 魔法時去喝杯咖啡或喫午飯的習慣。有一天，我的經理去度假，讓我幫忙管理他用Access 在筆記本電腦上建立的數據倉庫。刷新數據需要一繫列步驟：在門戶中運行SQL 查詢，將生成的csv 文件加載到數據庫中，然後刷新電子表格報告。第一次成功加載後，我開始修訂，試圖了解它是如何工作的，並纏著工程師向我展示如何修改SQL 查詢。我被深深的迷住了，甚至當我認為我可能會改變職業方向時，我還是不斷地回歸到數據上來。操縱數據、回答問題、幫助我的同事更好、更聰明地工作，以及通過數據集了解企業和世界，我從未停止享受其中帶給我的樂趣並為此感到興奮不已。當我剛開始使用SQL 時，還沒有太多的學習資源。我買了一本關於基本語法的書，花了一晚上就看完了，從那之後大部分的知識都是通過反復試驗來學習的。在我還在學習的階段，我就直接查詢生產數據庫，並用我過於雄心勃勃（或者更可能隻是寫得不好）的SQL 不止一次地使網站崩潰。幸運的是，我的技能有所提高，這些年來我學會了從表中的數據向前工作，從所需的輸出向後工作，解決技術和邏輯的各種挑戰和難題，以編寫返回正確數據的查詢。我最終設計並構建了數據倉庫，以從不同來源收集數據，並避免破壞關鍵的生產數據庫。在編寫SQL 查詢之前，我學到了很多關於應該何時以及如何聚合數據的知識，還有什麼時候應該以更原始的形式保留數據。我將筆記與差不多同一時間進入數據領域行業的其他人進行了比較，很明顯，我們主要是以有針對性的方式來學習的。我們中那些幸運的人有同齡人與他們分享技術。大多數SQL 文本要麼是介紹性的，要麼是基礎性的（肯定有這些！）或者是針對數據庫開發人員的。對於專注於分析工作的高級SQL 用戶來說，資源很少。知識往往被困在個人或小團隊中，而不是被廣泛地交流與分享。本書的一個目標是改變這一點，為從業者提供如何使用SQL 解決常見分析問題的參考，並且我希望使用你可能從未見過的技術，激發對數據的新探索。O’Reilly 在線學習平臺（O’Reilly Online Learning）近40 年來，O’Reilly Media 致力於提供技術和商業培訓、知識和卓越見解，來幫助眾多公司取得成功。我們擁有獨一無二的專家和革新者組成的龐大網絡，他們通過圖書、文章、會議和我們的在線學習平臺分享他們的知識和經驗。O’Reilly 的在線學習平臺允許你按需訪問現場培訓課程、深入的學習路徑、交互式編程環境，以及O’Reilly 和200 多家其他出版商提供的大量文本和視頻資源。有關的更多信息，請訪問http://oreilly.com。聯繫我們任何有關本書的意見或疑問，請按照以下地址聯繫出版社。美國：O’Reilly Media, Inc.1005 Gravenstein Highway NorthSebastopol, CA 95472中國：北京市西城區西直門南大街2號成銘大廈C座807室（100035）奧萊利技術咨詢（北京）有限公司本書有專門的網頁，其中列出了勘誤表、示例和任何其他信息。 你可以訪問此頁面https://oreil.ly/sql-data-analysis。對本書的評論或技術疑問，可以發電子郵件到errata@oreilly.com.cn。欲了解本社圖書和課程的新聞和信息，請訪問我們的網站http://oreilly.com。我們的Facebook：http://facebook.com/oreilly。我們的Twitter：http://twitter.com/oreillymedia。我們的YouTube：http://www.youtube.com/oreillymedia。致謝如果沒有O’Reilly 的許多人的努力，這本書是不可能完成和出版的。感謝Andy Kwan 最開始招募我參與這個項目；Amelia Blevins 和Shira Evans 指導我完成了整個過程，並在過程中提供了有益的反饋；Kristen Brown 指導了這本書的制作過程；Arthur Johnson 提高了文本的質量和清晰度，無意中也讓我對SQL 有了更深入的思考。多年來，許多同事在我的SQL 之旅中發揮了重要作用，感謝他們的指導、技巧和共享代碼，以及多年來花時間集思廣益解決分析問題的方法。Sharon Lin 讓我對正則表達式大開眼界。Elyse Gordon 給了我很多寫書的建議。Dave Hoch 和我們關於實驗分析的對話啟發了我寫下第7 章。來自Star Chamber 的Dan、Jim 和Stu 一直以來都是我最喜歡一起交流探討的學者們。我也很感謝這些年來所有提出尖銳問題的同事不斷給我啟發，一旦這些問題得到回答，他們又會提出更有挑戰的問題。我要感謝我的丈夫Rick、兒子Shea、女兒Lily 和Fiona，以及媽媽Janet，感謝他們的愛和鼓勵，最重要的是，感謝他們給我時間來寫這本書。Amy、Halle、Jessi 和the Den of Slack 在長達數月的寫作和疫情封鎖期間讓我身心保持良好的狀態和帶給我歡笑。