整合營銷服務商
電腦端+手機端+微信端=數據同步管理
免費咨詢熱線：
電腦端+手機端+微信端=數據同步管理
免費咨詢熱線：
SQL，發音為“ sequel”(或SQL，如果愿意的話)，是數據科學家的重要工具。實際上，它可以說是獲取數據工作中最重要的語言。在共享單車數據分析的SQL設計中，我們將從入門者的角度深入研究SQL基礎知識，以使您入門并掌握這一關鍵技能。
讓我們從回答一個簡單的問題開始：
什么是SQL?
SQL代表結構化查詢語言。查詢語言是一種編程語言，旨在促進從數據庫中檢索特定信息，而這正是SQL所做的。簡而言之，SQL是數據庫的語言。
這很重要，因為大多數公司將其數據存儲在數據庫中。盡管數據庫類型很多(例如MySQL，PostgreSQL，Microsoft SQL Server)，但是大多數數據庫都使用SQL，因此一旦掌握了SQL基礎知識，便可以使用其中的任何一個。
即使您打算使用Python之類的另一種語言進行分析，在大多數公司中，您仍可能需要使用SQL從公司的數據庫中檢索所需的數據。在撰寫共享單車數據分析的SQL設計時，僅在美國，Indeed上就列出了80,000多個SQL作業。
因此，讓我們開始學習SQL!
(如果您希望通過瀏覽器進行交互學習，編寫和運行SQL查詢，則應查看我們的SQL基礎課程，該課程免費)
為了避免廣告的嫌疑，我們選擇國外的一個共享單車來舉例子，在共享單車數據分析的SQL設計中，我們將使用自行車共享服務Hubway的數據集，其中包括使用該服務進行的超過150萬次旅行的數據。
在開始用SQL編寫我們自己的一些查詢之前，我們將首先看一下數據庫，它們是什么以及為什么使用它們。
如果您想繼續，可以在這里下載hubway.db文件(130 MB)。
SQL基礎：關系數據庫
關系數據庫是一種數據庫，該數據庫存儲跨多個表的相關信息，并允許您同時查詢多個表中的信息。
通過思考一個例子，更容易理解它是如何工作的。假設您是一家企業，并且想要跟蹤銷售信息。您可以在Excel中設置一個電子表格，在其中您要跟蹤的所有信息都以單獨的列顯示：訂單號，日期，到期金額，裝運跟蹤號，客戶名，客戶地址和客戶電話號碼。
此設置可以很好地跟蹤所需的信息，但是當您開始從同一位客戶那里獲得重復訂單時，您會發現他們的姓名，地址和電話號碼存儲在電子表格的多行中。
隨著業務的增長和要跟蹤的訂單數量的增加，這些冗余數據將占用不必要的空間，并通常會降低銷售跟蹤系統的效率。您可能還會遇到數據完整性問題。例如，不能保證每個字段都將填充正確的數據類型，或者每次都以完全相同的方式輸入名稱和地址。
與上圖中的關系數據庫一樣，使用關系數據庫可以避免所有這些問題。您可以設置兩個表，一個用于訂單，一個用于客戶。“客戶”表將包括每個客戶的唯一ID號，以及我們已經跟蹤的姓名，地址和電話號碼。“訂單”表將包括您的訂單號，日期，應付金額，跟蹤號，并且在每個客戶數據項中沒有一個單獨的字段，而是一個客戶ID列。
這使我們能夠提取任何給定訂單的所有客戶信息，但是我們只需要在數據庫中存儲一次即可，而不必為每個訂單再次列出它。
我們的數據集
讓我們開始看看我們的數據庫。該數據庫有兩個表，trips和stations。首先，我們只看trips表。它包含以下列：
1)id —用作每次旅行的參考的唯一整數
2)duration —行程時間，以秒為單位
3)start_date —旅行開始的日期和時間
4)start_station—一個整數，與該行開始于的車站的表中的id列相對應stations
5)end_date —旅行結束的日期和時間
6)end_station —行程終點站的“ id”
7)bike_number —旅途中所用自行車的Hubway唯一標識符
8)sub_type—用戶的訂閱類型。"Registered"對于具有成員資格"Casual"的用戶，對于沒有成員資格的用戶
9)zip_code —用戶的郵政編碼(僅適用于注冊會員)
10)birth_date —用戶的出生年份(僅適用于注冊會員)
11)gender —用戶的性別(僅適用于注冊會員)
我們的分析
有了這些信息和我們將很快學習的SQL命令，以下是我們在共享單車數據分析的SQL設計中將嘗試回答的一些問題：
1)最長旅行的持續時間是多少?
2)“注冊”用戶進行了多少次旅行?
3)平均旅行時間是多少?
4)注冊用戶或臨時用戶旅行更長嗎?
5)大多數旅行中使用哪輛自行車?
6)30歲以上的用戶平均旅行時間是多少?
我們將用來回答這些問題的SQL命令是：
1)SELECT
2)WHERE
3)LIMIT
4)ORDER BY
5)GROUP BY
6)AND
7)OR
8)MIN
9)MAX
10)AVG
11)SUM
12)COUNT
安裝與設定
就共享單車數據分析的SQL設計而言，我們將使用一個名為SQLite3的數據庫系統。從2.5版開始，SQLite已經成為Python的一部分，因此，如果您安裝了Python，則幾乎肯定也會安裝SQLite。如果尚未安裝Python和SQLite3庫，則可以使用Anaconda輕松進行安裝和設置。
使用Python運行我們的SQL代碼可以使我們將結果導入到Pandas數據框中，從而更易于以易于閱讀的格式顯示結果。這也意味著我們可以對從數據庫中提取的數據進行進一步的分析和可視化，盡管這超出了共享單車數據分析的SQL設計的范圍。
另外，如果我們不想使用或安裝Python，則可以從命令行運行SQLite3。只需從SQLite3網頁下載“預編譯的二進制文件”，然后使用以下代碼打開數據庫：
在這里，我們只需鍵入要運行的查詢，我們將在終端窗口中看到返回的數據。
使用終端的另一種方法是通過Python連接到SQLite數據庫。這將使我們能夠使用Jupyter筆記本，以便我們可以在格式整齊的表中查看查詢的結果。
為此，我們將定義一個函數，該函數將查詢(存儲為字符串)作為輸入并將結果顯示為格式化的數據框：
當然，我們不必在SQL中使用Python。如果您已經是R程序員，那么我們的R用戶SQL基礎知識課程將是一個不錯的起點。
選擇
我們將使用的第一個命令是SELECT。SELECT將幾乎是我們編寫的每個查詢的基礎-它告訴數據庫我們要查看哪些列。我們既可以按名稱指定列(用逗號分隔)，也可以使用通配符*返回表中的每一列。
除了要檢索的列之外，我們還必須告訴數據庫從哪個表獲取它們。為此，我們使用關鍵字，FROM后跟表名。例如，如果我們想看到的start_date，并bike_number在每行trips表中，我們可以使用下面的查詢：
在此示例中，我們從SELECT命令開始，以便數據庫知道我們希望它為我們找到一些數據。然后，我們告訴數據庫我們對start_date和bike_number列感興趣。最后，我們過去FROM使數據庫知道我們要查看的列是trips表的一部分。
編寫SQL查詢時要意識到的重要一件事是，我們希望每個查詢都以分號(;)結尾。并非每個SQL數據庫實際上都需要這樣做，但是有些確實需要，所以最好養成這種習慣。
限制
開始在Hubway數據庫上運行查詢之前，我們需要知道的下一個命令是LIMIT。LIMIT只是告訴數據庫您希望它返回多少行。
SELECT我們在上一節中查看的查詢將為表中的每一行返回所請求的信息trips，但是有時這可能意味著大量數據。我們可能不想要所有這些。相反，如果我們想看到的start_date，并bike_number在數據庫中的第一個五年的旅行，我們可以添加LIMIT到我們的查詢，如下所示：
我們僅添加了LIMIT命令，然后添加了一個數字，該數字表示我們要返回的行數。在本例中，我們使用5，但您可以將其替換為任何數字，以獲取正在處理的項目的適當數據量。
LIMIT在共享單車數據分析的SQL設計中，我們將在Hubway數據庫中的查詢中使用很多–該trips表包含超過150萬行數據，我們當然不需要顯示所有數據!
讓我們在Hubway數據庫上運行第一個查詢。首先，我們將查詢存儲為字符串，然后使用我們先前定義的函數在數據庫上運行它。看下面的例子：
該查詢*用作通配符，而不是指定要返回的列。這意味著該SELECT命令已為我們提供了trips表中的每一列。我們還使用該LIMIT函數將輸出限制為表的前五行。
您會經常看到人們在查詢中使用大寫的逗號(這是我們在共享單車數據分析的SQL設計中將遵循的約定)，但這主要是優先考慮的問題。大寫字母使代碼更易于閱讀，但實際上絲毫不影響代碼的功能。如果您希望使用小寫命令編寫查詢，則查詢仍將正確執行。
我們前面的示例返回trips表中的每一列。如果只對duration和start_date列感興趣，則可以按如下所示用列名替換通配符：
訂購
在回答第一個問題之前，我們需要知道的最終命令是ORDER BY。此命令使我們可以對給定列上的數據庫進行排序。
要使用它，我們只需指定要排序的列的名稱。默認情況下，ORDER BY按升序排序。如果我們想指定數據庫應該排序的順序，我們可以添加關鍵字ASC以升序或DESC降序。
例如，如果我們想將trips表從最短duration到最長排序，我們可以在查詢中添加以下行：
有了SELECT，LIMIT和ORDER BY命令之后，我們現在可以嘗試回答第一個問題：最長旅行的持續時間是多少?
要回答這個問題，將其分為幾個部分并確定我們需要解決每個部分的命令會很有幫助。
首先，我們需要從表的duration列中提取信息trips。然后，要找出最長的行程，我們可以duration按降序對列進行排序。我們可能會通過以下方式提出一個查詢，該查詢將獲取我們正在尋找的信息：
1)使用SELECT檢索duration列FROM的trips表
2)使用ORDER BY排序的duration列，并使用DESC關鍵字來指定要在降序排序
3)用于LIMIT將輸出限制為1行
以這種方式使用這些命令將返回持續時間最長的單行，這將為我們提供問題的答案。
需要注意的另一件事-隨著查詢添加更多命令并變得更加復雜，如果將它們分成多行，您可能會更容易閱讀。就像大寫一樣，這是個人喜好問題。它不會影響代碼的運行方式(系統只是從頭開始讀取代碼，直到到達分號為止)，但它可以使您的查詢更清晰，更易于理解。在Python中，我們可以使用三引號將字符串分隔為多行。
讓我們繼續運行此查詢，找出最長的旅程持續了多長時間。
現在我們知道最長的旅程持續了9999秒，或者說是166分鐘多一點。但是，最大值為9999時，我們不知道這是否真的是最長行程的長度，或者數據庫是否僅設置為允許四位數的數字。
如果確實由數據庫縮短了特別長的行程，那么我們可能期望在9999秒處看到很多行程，它們達到了極限。讓我們嘗試運行與之前相同的查詢，但是將調整LIMIT為返回10個最長持續時間，以查看是否為這種情況：
我們在這里看到的是，在9999年并沒有一整趟旅行，因此看起來我們并沒有切斷持續時間的高端，但是仍然很難判斷這是否是真正的行程跳閘或最大允許值。
Hubway會為30分鐘以上的騎行收取額外費用(某人保持9999秒的自行車將不得不支付25美元的額外費用)，因此他們認為4位數字足以追蹤大多數騎行是合理的。
哪里
前面的命令非常適合提取特定列的排序信息，但是如果我們要查看數據的特定子集，該怎么辦?就是這樣WHERE。WHERE命令允許我們使用邏輯運算符指定應返回的行。例如，您可以使用以下命令返回bike的每次旅行B00400：
您還會注意到，我們在此查詢中使用引號。那是因為bike_number儲存為字串。如果該列包含數字數據類型，則不需要引號。
讓我們編寫一個查詢，該查詢WHERE用于返回trips表中每一行的每一列，這些查詢的duration時間超過9990秒：
如我們所見，此查詢返回了14個不同的行程，每個行程持續9990秒或更長。關于此查詢的突出之處是，除一個結果外，所有結果都具有sub_type的"Casual"。也許這表明"Registered"用戶更了解長途旅行的額外費用。也許Hubway可以更好地向休閑用戶傳達其價格結構，以幫助他們避免超額收費。
我們已經知道，即使是SQL的初學者級命令也可以如何幫助我們回答業務問題并在數據中尋找見解。
返回到WHERE，我們也可以WHERE使用AND或在子句中組合多個邏輯測試OR。例如，如果在我們之前的查詢中，共享單車數據分析的SQL數據庫設計https://www.aaa-cg.com.cn/data/2586.html我們只想返回duration超過9990秒的行程，并且還具有已sub_type注冊的行程，則可以AND用來指定這兩個條件。
這是另一個個人喜好建議：使用括號分隔每個邏輯測試，如下面的代碼塊所示。這不是代碼正常運行所必需的，但是括號會隨著您增加復雜性而使您的查詢更容易理解。
現在運行該查詢。我們已經知道它只能返回一個結果，因此應該容易檢查我們是否正確：
我們在帖子開頭提出的下一個問題是“'注冊'用戶進行了多少次旅行?” 為了回答這個問題，我們可以運行與上面相同的查詢，并修改WHERE表達式以返回sub_type等于的所有行，'Registered'然后對它們進行遞增計數。
但是，SQL實際上有一個內置命令來為我們進行計數COUNT。
COUNT使我們可以將計算轉移到數據庫，從而省去了編寫額外腳本來計算結果的麻煩。要使用它，我們只需要添加(COUNT(column_name)而不是添加)您想要的列即可SELECT，如下所示：
在這種情況下，我們選擇對哪一列進行計數都沒有關系，因為每一列都應該有查詢中每一行的數據。但是有時查詢可能缺少某些行的值(或“空”)。如果不確定一列是否包含空值，則可以COUNT在該id列上運行-該id列永遠不會為空，因此我們可以確保計數不會遺漏任何內容。
我們還可以使用COUNT(1)或COUNT(*)來計數查詢中的每一行。值得注意的是，有時我們實際上可能想COUNT在具有空值的列上運行。例如，我們可能想知道數據庫中有多少行缺少一列的值。
讓我們看一個查詢來回答我們的問題。我們可以SELECT COUNT(*)用來計算返回的總行數，并WHERE sub_type="Registered"確保只計算注冊用戶的旅行次數。
該查詢有效，并且已返回我們問題的答案。但是列標題不是特別描述性的。如果其他人看這張桌子，他們將無法理解它的含義。如果我們想使結果更具可讀性，可以使用AS別名(或昵稱)作為輸出。讓我們重新運行上一個查詢，但給我們的列標題加上別名Total Trips by Registered Users：
匯總功能
COUNT這不是SQL掌握的唯一數學技巧。我們也可以使用SUM，AVG，MIN和MAX分別返回列的求和，平均值，最小值和最大值。這些與COUNT一起被稱為集合函數。
因此，要回答我們的第三個問題，“平均旅行持續時間是多少?” ，我們可以使用列AVG上的函數duration(再次使用AS，為輸出列起一個更具描述性的名稱)：
事實證明，平均旅行持續時間是912秒，大約是15分鐘。這是有道理的，因為我們知道Hubway會對30分鐘以上的行程收取額外費用。該服務是為騎手短途單程旅行而設計的。
接下來的問題是，注冊用戶或臨時用戶旅行更長的時間呢?我們已經知道一種解決此問題的方法-我們可以SELECT AVG(duration) FROM trips使用WHERE子句運行兩個查詢，這些子句將一個限制到"Registered"一個，一個限制到"Casual"用戶。
不過，讓我們以不同的方式來做。SQL還包括使用GROUP BY命令在單個查詢中回答此問題的方法。
通過...分組
GROUP BY 根據特定列的內容將行分為幾組，并允許我們在每個組上執行聚合函數。
為了更好地了解其工作原理，讓我們看一下該gender專欄。每行可以有三個可能的值一個gender列，"Male"，"Female"或Null(丟失;我們沒有gender對普通用戶的數據)。
當使用時GROUP BY，數據庫將根據gender列中的值將每一行分成不同的組，就像我們將一副紙牌分成不同的花色一樣。我們可以想象制造兩堆，所有雄性之一，所有雌性之一。
一旦我們擁有兩個獨立的堆，數據庫將依次對它們中的每一個執行查詢中的任何聚合函數。COUNT例如，如果使用，則查詢將計算每個堆中的行數，并分別返回每個堆的值。
讓我們詳細介紹如何編寫查詢來回答我們的問題，即注冊用戶或臨時用戶是否需要更長的行程。
1)與到目前為止的每個查詢一樣，我們將從SELECT告訴數據庫想要查看哪些信息開始。在這種情況下，我們需要sub_type和AVG(duration)。
2)我們還將包括GROUP BY sub_type按訂閱類型分離數據，并分別計算注冊用戶和臨時用戶的平均值。
當我們將它們放在一起時，代碼如下所示：
完全不同!平均而言，注冊用戶的出行時間約為11分鐘，而休閑用戶每次出行的時間則接近25分鐘。注冊用戶可能會進行更短，更頻繁的旅行，這可能是他們上下班的一部分。另一方面，休閑用戶每次旅行花費的時間大約是兩倍。
休閑用戶可能來自人口統計學(例如旅游者)，他們更傾向于長途旅行，以確保他們四處逛逛并看到所有景點。一旦我們發現了數據的差異，公司便可以通過多種方式對其進行調查，以更好地了解造成這種情況的原因。
但是，出于共享單車數據分析的SQL設計的目的，讓我們繼續。我們的下一個問題是旅行次數最多的是哪輛自行車?我們可以使用非常相似的查詢來回答這個問題。看下面的示例，看看是否可以弄清楚每行的內容-我們將逐步進行操作，以便檢查是否正確：
從輸出中可以看到，自行車B00490出行最多。讓我們來看看如何到達那里：
1)第一行是一個SELECT子句，告訴數據庫我們要查看bike_number列和每行的計數。它還AS用于告訴數據庫以更有用的名稱顯示每一列。
2)第二行用于FROM指定我們要查找的數據在trips表中。
3)第三行是開始有些棘手的地方。我們GROUP BY用來告訴第COUNT1行的函數分別計算每個值bike_number。
4)在第四行，我們有一個ORDER BY子句對表格進行降序排序，并確保最常用的自行車在頂部。
5)最后，我們LIMIT將輸出限制為第一行，因為我們如何對第四行的數據進行排序，所以我們知道這將是旅行次數最多的自行車。
算術運算符
我們的最后一個問題比其他問題更加棘手。我們想知道30歲以上注冊會員的平均旅行時間。
我們可以算出30歲以下的人出生的那一年，然后再插入，但是一個更優雅的解決方案是直接在查詢中使用算術運算。SQL允許我們使用+，-，*和/在一次對整個列執行運算。
加入
到目前為止，我們一直在研究僅從trips表中提取數據的查詢。但是，SQL之所以如此強大是因為它使我們能夠從同一查詢中的多個表中提取數據。
我們的自行車共享數據庫包含第二個表stations。該stations表包含有關Hubway網絡中每個站點的信息，并包括id該trips表引用的列。
不過，在開始研究該數據庫中的一些實際示例之前，讓我們回顧一下較早的假設訂單跟蹤數據庫。在該數據庫中，我們有兩個表orders和customers，它們通過customer_id列連接。
假設我們要編寫一個查詢，該查詢返回數據庫中的每個訂單的order_number和name。如果它們都存儲在同一個表中，則可以使用以下查詢：
不幸的是，order_number列和name列存儲在兩個不同的表中，因此我們必須添加一些額外的步驟。讓我們花一點時間考慮一下數據庫在返回所需信息之前需要了解的其他事項：
1)該order_number列在哪個表中?
2)該name列在哪個表中?
3)orders表中的信息如何與表中的信息連接customers?
要回答這些問題中的前兩個，我們可以在SELECT命令中包括每列的表名。我們這樣做的方法就是簡單地寫一個表名和列名，用.。分隔。例如，代替SELECT order_number, name我們會寫SELECT orders.order_number, customers.name。在此處添加表名稱可以通過告訴數據庫要查找的表來幫助數據庫查找我們要查找的列。
為了告訴數據庫orders和customers表如何連接，我們使用JOIN和ON。JOIN指定應該連接的表，并ON指定每個表中的哪些列相關。
我們將使用內部聯接，這意味著將僅在中指定的列匹配的地方返回行ON。在此示例中，我們將要JOIN在FROM命令中未包含的任何表上使用。因此，我們可以使用FROM orders INNER JOIN customers或FROM customers INNER JOIN orders。
如前所述，這些表連接customer_id在每個表的列上。因此，我們將要用來ON告訴數據庫，這兩列引用的是這樣的東西：
我們再次使用.來確保數據庫知道這些列中的每一個都在哪個表中。因此，當我們將所有這些放在一起時，我們得到的查詢如下所示：
該查詢將返回數據庫中每個訂單的訂單號以及與每個訂單相關聯的客戶名稱。
回到我們的Hubway數據庫，我們現在可以編寫一些查詢以JOIN進行實際操作。
在開始之前，我們應該看一下表中的其余列stations。這是一個查詢，向我們顯示了前5行，因此我們可以看到stations表的外觀：
1)id—每個工作站的唯一標識符(對應于表中的start_station和end_station列trips)
2)station —站名
3)municipality —車站所在的城市(波士頓，布魯克林，劍橋或薩默維爾)
4)lat —車站的緯度
5)lng —車站的經度
6)哪些車站最經常往返?
7)在不同的城市中有多少次旅行開始和結束?
與以前一樣，我們將嘗試回答數據中的一些問題，從哪個站是最頻繁的起點開始?讓我們逐步進行操作：
1)首先，我們要使用SELECT返回表中的station列stations和COUNT行數。
2)接下來，我們指定我們想要的表JOIN并告訴數據庫連接它們ON的start_station列trips表和id列stations的表。
3)然后我們進入查詢的內容-我們在表格中GROUP BY的station列，stations以便我們COUNT將分別計算每個車站的行程次數
4)最后，我們可以ORDER BY我們COUNT并LIMIT輸出到結果的管理的數量
如果您熟悉波士頓，您將了解為什么這些是最受歡迎的電臺。南站是該市主要的通勤火車站之一，查爾斯街沿河延伸，靠近一些風景優美的路線，博伊爾斯頓和信標街就在市中心，靠近許多辦公大樓。
我們要看的下一個問題是往返最常用的車站是?我們可以使用與以前幾乎相同的查詢。我們將以相同的方式SELECT使用相同的輸出列和JOIN表，但是這次我們將添加一個WHERE子句以限制我們COUNT的行程start_station與相同end_station。
我們可以看到，這些站點的數量與上一個問題相同，但數量要低得多。最繁忙的站點仍然是最繁忙的站點，但是總體而言，較低的站點表明人們通常在使用Hubway自行車從A點到達B點，而不是在返回起點之前先騎自行車一會兒。
這里有一個明顯的不同-Esplande并不是我們第一個查詢中總體上最繁忙的車站之一，它似乎是往返行程中最繁忙的車站。為什么?好吧，一張圖片值一千個字。當然，這看起來像是騎自行車的好地方：
接下來的問題是：在不同的城市開始和結束多少次旅行?這個問題使事情更進一步。我們想知道在不同的地方開始和結束了多少次旅行municipality。為了實現這一目標，我們需要JOIN的trips表到stations表的兩倍。一旦ON該start_station列，然后ON在end_station列。
為此，我們必須為該stations表創建一個別名，以便能夠區分與關聯的start_station數據和與關聯的數據end_station。我們可以使用與為各個列創建別名以使其更直觀地顯示名稱的方式完全相同AS。
例如，我們可以使用下面的代碼JOIN的stations表到trips使用的“開始”別名表。然后，我們可以將“開始”與我們的列名稱結合使用，.以引用來自此特定對象的數據JOIN(而不是第二個，JOIN我們將處理ON該end_station列)：
這是我們運行最終查詢時的樣子。請注意，我們曾經<>表示“不等于”，但!=也可以使用。
這表明，在150萬次旅行中，大約有300,000次(或20%)在與開始的城市不同的地方結束–進一步的證據表明，人們大多使用Hubway自行車進行相對較短的旅行，而不是在城鎮之間進行較長的旅行。
如果您已經做到了，那么恭喜!您已經開始掌握SQL的基礎知識。我們已經討論了許多重要的命令，SELECT，LIMIT，WHERE，ORDER BY，GROUP BY和JOIN，以及骨料和算術功能。這些將為您繼續SQL之旅提供堅實的基礎。
下一步
在完成本入門SQL教程之后，您現在應該能夠找到自己感興趣的數據庫并編寫查詢以提取信息。好的第一步可能是繼續使用Hubway數據庫，以了解您還能找到什么。以下是您可能想嘗試回答的其他一些問題：
1)有多少趟旅程產生了額外的費用(持續時間超過30分鐘)?
2)哪輛自行車使用的時間最長?
3)注冊用戶或臨時用戶是否往返更多?
4)哪個城市的平均停留時間最長?
如果您想更進一步，請查看我們的交互式SQL課程，該課程涵蓋了您從入門到高級SQL所需的一切知識，適用于數據分析師和數據科學家的工作。在數據科學課程頁面上的SQL菜單下查找所有交互式SQL課程產品的完整列表。
相關推薦
言
對于Redis而言，很多小伙伴只關注其關鍵的五大基礎類型：string、hash、list、set、sorted set(有序集合)，其實還有三種特殊類型在很多應用場景也比較適合使用，分別是：bitmap、geospatial、hyperloglog；上一篇(跟我一起學Redis之五種基本類型及其應用場景舉例(干了6個小時))對五種類型進行分享，接下來結合應用場景來說說三種特殊類型的使用方式；
正文
geospatial(地理空間)
該類型在Redis3.2.0版本中加入，其本質是將經緯度通過geohash技術轉換成一個值，使用sorted set將其存儲；具體內部實現在這里不進行研究，這次主要說應用。
經度(longitude)、維度(latitude)小伙伴肯定不陌生(流淚，當初地理差的一批)：
該類型只有六個命令，先簡單介紹一下各命令的功能和關鍵參數：
GEOADD key 經度1 維度1 member1 [經度2 維度2 member2 ...]
GEODIST key member1 member2 [單位]
單位可以指定，如下幾種：
m：指定單位為米；
km：指定單位為千米；
mi：指定單位為英里；
ft：指定單位為英尺；
GEOHASH key member1 [member2 ...]
GEOPOS key member1 [member2 ...]
GEORADIUS key 經度 維度 半徑 m|km|ft|mi [WITHCOORD] [WITHDIST] [WITHHASH] [COUNT count]
半徑后面指定一個單位，m|km|ft|mi指定其中一個；
WITHDIST：在返回查找到位置元素， 同時對應位置元素與中心之間的距離也一并返回。
WITHCOORD：將位置元素的經度和維度也一并返回。
WITHHASH：以 52 位有符號整數的形式返回， 返回位置元素經過原始 geohash 編碼的有序集合分值，即一串字符串；
COUNT：代表返回位置信息的條數，類似于分頁條數；
GEORADIUSBYMEMBER key member 半徑 m|km|ft|mi [WITHCOORD] [WITHDIST] [WITHHASH] [COUNT count]
命令功能及參數說明就簡單說這么多(我怕說理論)，欲知詳情，請小伙伴去官網瞅瞅；
接下來結合應用場景進行命令實戰，不然小伙伴要忍不住啦↓↓↓
應用場景實戰1
綠色出行，小黃、小藍、青桔、哈羅各種共享自行車應該已經是很多小伙伴們出行必用了吧，來，先截個哈羅的圖看看：
圖中顯示定位附近的小車，小伙伴可以先想想，如果這個需求給自己，怎么實現？
如果用Redis，這樣搞試試：
每一輛共享單車肯定有定位功能，將其定位信息(經緯度)存儲到后臺管理系統中，這里我們模擬定位信息存儲，我們用百度地圖坐標拾取器可以取得地圖上任意一點的經緯度信息，如下圖：
網頁地址為：http://api.map.baidu.com/lbsapi/getpoint/index.html
模擬共享單車定位信息存儲到后臺Redis：
當我們打開手機時，App同樣會通過手機進行定位，比如得到對應位置的經緯度信息為：113.768365(經度),34.724814(緯度)，這樣就可以指定用戶的定位信息為中心，去查找附近指定半徑的單車，然后將其標注在地圖上：
應用場景實戰2
再來一個需求，比如說微信附近的人，直播APP附近直播的播友，還有某陌附近的美女，如下圖：
如果用Redis實現，同樣是通過APP(比如微信)將用戶的定位信息保存到后臺Redis中，這里還是使用百度地圖拾取器的方式取得位置信息模擬將定位信息保存在Redis：
附近餐館、附近加油站、附近酒店同樣的原理，最后關于具體的信息可以通過得到信息(比如說用戶ID、共享單車標識等)去存儲詳細信息的數據庫中查詢。
bitmap
bitmap在Redis2.2.0版本加入，其并不是一種實際的數據類型，而是在字符串類型基礎上定義的一組面向位的操作。因為字符串是二進制安全的blob，對應value能存儲的最大長度是512 MB，即可以設置2^32個不同的位；大概的結構如下：
實踐出真理，看看其類型到底是不是string類型：
常用命令如下：
SETBIT key offset value
GETBIT key offset
BITCOUNT key [start end]
BITOP operation destkey key [key ...]
operation 可以指定對應的運算方式，運算的結果存入destkey中；
BITPOS key bit [start] [end]
模擬應用場景實戰：
bitmap由于存儲的值只能是0或1，所以很適合兩種狀態的數據記錄和分析，比如說是否簽到，是否登錄，然后通過簽到記錄的數據可以統計周、月、年簽到數，通過登錄的數據可以分析用戶是否活躍；
應用場景用戶簽到
比如某多多連續簽到送現金、運動打卡App(比如Keep)、學習打卡App或者小程序，都會有對打卡數據的統計，如下圖某音極速版連續簽到送積分：
如果用Redis實現，如下：
應用場景用戶登錄即用戶活躍分析：
如下統計邏輯：
bitmap適合數據量比較大的場景，如果數據幾十上千，用bitmap反而相對比較占空間，直接使用用戶ID作為Key使用set存儲，但是如果數據量大時，Key值所占空間比較大，比較浪費，而bitmap用512M內存就能標識40億數據狀態；當然，不介意bitmap的位數很大，設置或分析數據的時候可能會導致堵塞，所以當位數很大時，建議將其拆分為多個Key，保證分析的高效。
先休息一下，晃晃頭，眨巴眨巴眼睛，然后繼續往下↓↓↓
hyperloglog
先說說什么是基數？
基數（cardinal number）在數學上，是集合論中刻畫任意集合大小的一個概念。兩個能夠建立元素間一一對應的集合稱為互相對等集合。例如3個人的集合和3匹馬的集合可以建立一一對應，是兩個對等的集合。 百度百科 比如數據集 {1, 3, 5, 7, 5, 7, 8}， 那么這個數據集的基數集為 {1, 3, 5 ,7, 8}, 基數(不重復元素)為5。 基數估計就是在誤差可接受的范圍內，快速計算基數。 菜鳥教程
通俗一點就是不重復元素的個數(這是個人通俗的理解)，先到這，實戰再來回顧這句話；
hyperloglog是一種概率數據統計結構，適合超大數據量的基數統計，速度快，而且空間占用小，；但是統計的結果存在低于1%的誤差，如果需要的是精確統計的話，使用基礎類型set進行統計也不錯，但當統計元素比較多時，內存占用空間會增大，畢竟用空間換精確度嘛。
和bitmap感覺很像，都適合大數據量的統計，但是bitmap還關注統計對象，比如說誰簽到多少次，而hyperloglog只進行基數統計，不關注是誰進行簽到；比如常聽到UV統計場景，即一個頁面，一個用戶訪問多次，也只算一次，最后只關注這個頁面被多少用戶訪問，而不在乎是誰訪問；微信公眾號文章的統計也是針對用戶統計的，同一個用戶不管訪問幾次，只算一次閱讀；
常用命令：
PFADD key element [element ...]
PFCOUNT key [key ...]
如果指定多個HyperLogLog ，返回它們并集近似基數，存在低于1%的誤差；
PFMERGE destkey sourcekey [sourcekey ...]
模擬應用場景
模擬頁面UV統計，即用戶訪問數，不注重是誰訪問：
模擬App應用市場下載數，比如某音下載量都好幾億了，這個肯定不關心是誰下載吧，只關心下載數量來衡量應用參數；
總結
先到這吧，這篇總耗時又搞了幾個小時，上文中應用場景模擬只是給小伙伴提供思路，小伙伴可以根據需求進行設計；下一遍說說配置文件；
一個被程序搞丑的帥小伙，關注"Code綜藝圈"，識別關注跟我一起學~~~
目前最新的共享單車公開數據可能只有深圳市政府數據開放平臺[1]發布的共享單車企業每日訂單表了，數據量包含2017-04-03到2021-08-30的 2.4 億條數據，總計約 24G 大小：
《深圳共享單車企業每日訂單表》在數據庫中情況
深圳真不愧是改革先行者，公開的數據量如此龐大。
如此大量的數據，肯定不能直接下載。直接下載的文件僅包含本數據集的前 10 萬條數據。
每條數據包含的字段：
USER_ID用戶 idCOM_ID企業 idSTART_TIME開始時間START_LNG開始經度START_LAT開始緯度END_TIME結束時間END_LNG結束經度END_LAT結束緯度
深圳數據開放平臺也提供調用數據接口的方式進行下載：
數據接口（APIs） 是現代編程的基礎，可以通過多種編程語言和工具實現。JavaScript、Python等多種編程語言和工具都可以調用 Api。
每種方法都有其特定的使用場景和優勢。選擇哪種方法取決于你的項目需求、所用技術棧以及個人或團隊的熟悉程度。例如，如果你正在開發一個 Web 應用，JavaScript 可能是最直接的選擇；如果是企業級后端系統，Java 或 C#可能更合適。對于腳本或自動化任務，Python 或 Shell 命令可能是最簡單的方法。
我們選用簡潔性、受歡迎的 Python 來進行數據獲取，使用的方法是requests庫，或者可以使用http.client或第三方庫如aiohttp（異步請求）。
具體調用說明見網站[2]上的【調用說明】。
如此龐大的數據我們肯定不能全保存在 Json 或者 csv 文件中，對于共享單車訂單分析這樣的大數據量場景（2.7 億條數據），我們需要選擇合適的數據庫。
訂單數據包括時間序列和地理空間數據，因此需要一個既能高效處理大規模數據集，又支持時間和空間查詢的數據庫。
以下是一些推薦的數據庫選項：
另外根據我對數據庫的熟悉情況，我選擇MongoDB來儲存我的數據庫。
所以我們只需要用 Python 寫一個requests請求，然后將數據儲存到數據庫。
花了一點時間（實際在我的樹莓派上靜靜地跑了一天程序）之后，獲取了全部 24G 的數據。
若有按時間獲取數據的需求，可通過增加 startDate 和 endDate 參數對數據進行篩選。你可以
接下來簡單來探索下深圳市共享單車服務的使用情況。
這次先取一天——2021 年 8 月 5 號的數據。
將坐標統一為 GPS 坐標后我們進行簡單的可視化，如下圖，每個紅色點代表一個共享單車的出發點：
這天的訂單數據共計超過一百萬條，并且每條數據包含訂單的開始和結束的時間以及經緯度信息，當然其中有很多離群值，需要進一步篩選，也就是數據清洗。
由于沒有提供開關鎖的狀態，無法從訂單狀態篩選數據，通過觀察發現可以從訂單的距離、平均速度、起終點是否在深圳市進行數據清洗。
我直接刪除了訂單距離為 0 米以及訂單距離大于 40km 的數據，總的數據量從 1158199 減少到 1132736。
我們用這部分數據進行共享單車的使用模式分析，還能結合空間分布，進行一定程度的空間數據分析。
所以，我們的先對數據進行描述，可以重點關注了騎行時間、騎行次數、以及騎行距離的分布情況。
觀察共享單車的使用時間，我們發現了城市生活的節奏。早晚高峰時段的數據高峰，不僅反映了上下班的人潮，還揭示了車輛分配和維護的最佳時機。
這張圖是一個柱狀圖，顯示了 2021 年 8 月 5 日深圳市共享單車訂單在 24 小時內的分布情況。橫軸代表一天中的小時（從 0 點到 23 點），縱軸代表訂單數量。
從圖中可以觀察到，在早上的某個時段（8 點左右）有一個明顯的訂單數量峰值，大家開始上班了。隨后的幾個小時內，訂單數量逐漸下降，直到下午時段（16 點到 17 點之間）訂單數量再次上升，形成第二個高峰，這可能與人們下班的時間相對應。到了晚上，訂單數量逐漸減少，直到深夜時段達到最低點。
這樣的分析有助于共享單車運營商了解用戶的使用模式，從而更有效地調配車輛，確保在需求高峰時有足夠的車輛可供使用。
通過統計不同區域的騎行次數，我們繪制出了城市的熱力圖。
將熱力圖保存為 html 網頁，此網頁有 30M 大小，在線訪問速度可能很慢，想要的可以關注微信公眾號“renhai-lab”發送“深圳共享單車熱力圖 1”獲取網頁的下載鏈接。
熱力圖演示
3.出行距離分布
出行距離分布
這張圖包含了四個子圖，它們顯示了不同規模的數據分布情況。每個子圖都有一個 X 軸，表示“騎行距離（米）”，和一個 Y 軸，表示“概率”。這些圖似乎是概率密度函數（PDF）的圖形表示，每個子圖覆蓋不同的騎行距離范圍，展示了共享單車騎行距離的分布情況。
在這些子圖中，可以觀察到隨著騎行距離的增加，概率逐漸下降，這表明短距離騎行的頻率較高。這是符合預期的，因為人們通常傾向于使用共享單車進行短途出行。隨著距離的增加，選擇騎行的用戶可能會減少，從而導致概率下降。這些圖可能用于分析騎行距離的典型值，從而幫助共享單車公司優化他們的車輛分布和調度策略。
以上僅為一部分數據分析工作，我們還可以繪制平均出行時間圖、交通小區之間的出行量、共享單車的起點-終點圖（OD 圖），例如繪制全市 OD 圖獲取、單個站點的 OD 圖，例如下圖是上海某日共享單車到達某地鐵站點銜接圖——圖源自《交通時空大數據分析、挖掘與可視化(Python版)[15]，描述的是同濟大學地鐵站周邊共享單車換乘地鐵的分布圖。
今天先寫到這，此文章會在我的博客[16]更新，可以點擊閱讀原文[17]訪問。
如果你對本文章有什么意見、對如何制作文中的圖表感興趣、或者有其它任何問題可以在博客的評論區留言，或者通過微信公眾號發送私信給我。
renhailab
1.深圳市共享單車企業每日訂單表
數據簡介：深圳市政府數據開放平臺[22]發布的共享單車企業每日訂單表了，數據量包含 2.4 億條數據。
時間：2017-04-03到2021-08-30
鏈接：https://opendata.sz.gov.cn/data/dataSet/toDataDetails/29200_00403627
數據預覽：
搜索數據的時候發現有人已經整合過了：以內容下由微信公眾號【立方數據學社】發表在【數據分享】8 個城市的共享單車數據（數據量約 10 億條\免費獲取），本文選了國內的城市，國外的數據可以訪問原文查看。
時間： 2016年8月
數據簡介： 和鯨社區用戶分享的共享單車數據，包括上海市2016年8月的摩拜單車數據，共十萬余條。
數據字段： 訂單編號、車輛編號、用戶編號、起始時間、起始位置、結束時間、結束位置、軌跡。
數據簡介：2017 摩拜算法挑戰賽公布的共享單車數據，包括北京市 2017 年 5 月兩周之內 40 余萬共享單車被 30 多萬用戶使用的情況，包括 300 余萬條出行記錄。
數據字段：訂單號、用戶 ID、車輛 ID、車輛類型、騎行起始日期時間、騎行起始區塊位置、騎行目的地區塊位置
數據簡介：2021 數字中國創新大賽公布的廈門市 2020 年 12 月 21 日-2020 年 12 月 25 日五天的共享單車軌跡數據。
數據字段：共享單車 ID、定位時間、經度（WGS84 坐標）、緯度（WGS84 坐標）、共享單車來源
*請認真填寫需求信息，我們會在24小時內與您取得聯系。
