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