共享單車數(shù)據(jù)分析的SQL數(shù)據(jù)庫設(shè)計(jì)

　　SQL，發(fā)音為“ sequel”(或SQL，如果愿意的話)，是數(shù)據(jù)科學(xué)家的重要工具。實(shí)際上，它可以說是獲取數(shù)據(jù)工作中最重要的語言。在共享單車數(shù)據(jù)分析的SQL設(shè)計(jì)中，我們將從入門者的角度深入研究SQL基礎(chǔ)知識(shí)，以使您入門并掌握這一關(guān)鍵技能。
 

　　讓我們從回答一個(gè)簡(jiǎn)單的問題開始：
 

　　什么是SQL?
 

　　SQL代表結(jié)構(gòu)化查詢語言。查詢語言是一種編程語言，旨在促進(jìn)從數(shù)據(jù)庫中檢索特定信息，而這正是SQL所做的。簡(jiǎn)而言之，SQL是數(shù)據(jù)庫的語言。
 

　　這很重要，因?yàn)榇蠖鄶?shù)公司將其數(shù)據(jù)存儲(chǔ)在數(shù)據(jù)庫中。盡管數(shù)據(jù)庫類型很多(例如MySQL，PostgreSQL，Microsoft SQL Server)，但是大多數(shù)數(shù)據(jù)庫都使用SQL，因此一旦掌握了SQL基礎(chǔ)知識(shí)，便可以使用其中的任何一個(gè)。
 

　　即使您打算使用Python之類的另一種語言進(jìn)行分析，在大多數(shù)公司中，您仍可能需要使用SQL從公司的數(shù)據(jù)庫中檢索所需的數(shù)據(jù)。在撰寫共享單車數(shù)據(jù)分析的SQL設(shè)計(jì)時(shí)，僅在美國(guó)，Indeed上就列出了80,000多個(gè)SQL作業(yè)。
 

　　因此，讓我們開始學(xué)習(xí)SQL!
 

　　(如果您希望通過瀏覽器進(jìn)行交互學(xué)習(xí)，編寫和運(yùn)行SQL查詢，則應(yīng)查看我們的SQL基礎(chǔ)課程，該課程免費(fèi))
 

　　為了避免廣告的嫌疑，我們選擇國(guó)外的一個(gè)共享單車來舉例子，在共享單車數(shù)據(jù)分析的SQL設(shè)計(jì)中，我們將使用自行車共享服務(wù)Hubway的數(shù)據(jù)集，其中包括使用該服務(wù)進(jìn)行的超過150萬次旅行的數(shù)據(jù)。
 

　　在開始用SQL編寫我們自己的一些查詢之前，我們將首先看一下數(shù)據(jù)庫，它們是什么以及為什么使用它們。
 

　　如果您想繼續(xù)，可以在這里下載hubway.db文件(130 MB)。
 

　　SQL基礎(chǔ)：關(guān)系數(shù)據(jù)庫
 

　　關(guān)系數(shù)據(jù)庫是一種數(shù)據(jù)庫，該數(shù)據(jù)庫存儲(chǔ)跨多個(gè)表的相關(guān)信息，并允許您同時(shí)查詢多個(gè)表中的信息。
 

　　通過思考一個(gè)例子，更容易理解它是如何工作的。假設(shè)您是一家企業(yè)，并且想要跟蹤銷售信息。您可以在Excel中設(shè)置一個(gè)電子表格，在其中您要跟蹤的所有信息都以單獨(dú)的列顯示：訂單號(hào)，日期，到期金額，裝運(yùn)跟蹤號(hào)，客戶名，客戶地址和客戶電話號(hào)碼。

　　此設(shè)置可以很好地跟蹤所需的信息，但是當(dāng)您開始從同一位客戶那里獲得重復(fù)訂單時(shí)，您會(huì)發(fā)現(xiàn)他們的姓名，地址和電話號(hào)碼存儲(chǔ)在電子表格的多行中。
 

　　隨著業(yè)務(wù)的增長(zhǎng)和要跟蹤的訂單數(shù)量的增加，這些冗余數(shù)據(jù)將占用不必要的空間，并通常會(huì)降低銷售跟蹤系統(tǒng)的效率。您可能還會(huì)遇到數(shù)據(jù)完整性問題。例如，不能保證每個(gè)字段都將填充正確的數(shù)據(jù)類型，或者每次都以完全相同的方式輸入名稱和地址。

　　與上圖中的關(guān)系數(shù)據(jù)庫一樣，使用關(guān)系數(shù)據(jù)庫可以避免所有這些問題。您可以設(shè)置兩個(gè)表，一個(gè)用于訂單，一個(gè)用于客戶。“客戶”表將包括每個(gè)客戶的唯一ID號(hào)，以及我們已經(jīng)跟蹤的姓名，地址和電話號(hào)碼。“訂單”表將包括您的訂單號(hào)，日期，應(yīng)付金額，跟蹤號(hào)，并且在每個(gè)客戶數(shù)據(jù)項(xiàng)中沒有一個(gè)單獨(dú)的字段，而是一個(gè)客戶ID列。
 

　　這使我們能夠提取任何給定訂單的所有客戶信息，但是我們只需要在數(shù)據(jù)庫中存儲(chǔ)一次即可，而不必為每個(gè)訂單再次列出它。
 

　　我們的數(shù)據(jù)集
 

　　讓我們開始看看我們的數(shù)據(jù)庫。該數(shù)據(jù)庫有兩個(gè)表，trips和stations。首先，我們只看trips表。它包含以下列：

　　1)id —用作每次旅行的參考的唯一整數(shù)

　　2)duration —行程時(shí)間，以秒為單位

　　3)start_date —旅行開始的日期和時(shí)間

　　4)start_station—一個(gè)整數(shù)，與該行開始于的車站的表中的id列相對(duì)應(yīng)stations

　　5)end_date —旅行結(jié)束的日期和時(shí)間

　　6)end_station —行程終點(diǎn)站的“ id”

　　7)bike_number —旅途中所用自行車的Hubway唯一標(biāo)識(shí)符

　　8)sub_type—用戶的訂閱類型。"Registered"對(duì)于具有成員資格"Casual"的用戶，對(duì)于沒有成員資格的用戶

　　9)zip_code —用戶的郵政編碼(僅適用于注冊(cè)會(huì)員)

　　10)birth_date —用戶的出生年份(僅適用于注冊(cè)會(huì)員)

　　11)gender —用戶的性別(僅適用于注冊(cè)會(huì)員)
 

　　我們的分析
 

　　有了這些信息和我們將很快學(xué)習(xí)的SQL命令，以下是我們?cè)诠蚕韱诬嚁?shù)據(jù)分析的SQL設(shè)計(jì)中將嘗試回答的一些問題：
 

　　1)最長(zhǎng)旅行的持續(xù)時(shí)間是多少?

　　2)“注冊(cè)”用戶進(jìn)行了多少次旅行?

　　3)平均旅行時(shí)間是多少?

　　4)注冊(cè)用戶或臨時(shí)用戶旅行更長(zhǎng)嗎?

　　5)大多數(shù)旅行中使用哪輛自行車?

　　6)30歲以上的用戶平均旅行時(shí)間是多少?
 

　　我們將用來回答這些問題的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è)定
 

　　就共享單車數(shù)據(jù)分析的SQL設(shè)計(jì)而言，我們將使用一個(gè)名為SQLite3的數(shù)據(jù)庫系統(tǒng)。從2.5版開始，SQLite已經(jīng)成為Python的一部分，因此，如果您安裝了Python，則幾乎肯定也會(huì)安裝SQLite。如果尚未安裝Python和SQLite3庫，則可以使用Anaconda輕松進(jìn)行安裝和設(shè)置。
 

　　使用Python運(yùn)行我們的SQL代碼可以使我們將結(jié)果導(dǎo)入到Pandas數(shù)據(jù)框中，從而更易于以易于閱讀的格式顯示結(jié)果。這也意味著我們可以對(duì)從數(shù)據(jù)庫中提取的數(shù)據(jù)進(jìn)行進(jìn)一步的分析和可視化，盡管這超出了共享單車數(shù)據(jù)分析的SQL設(shè)計(jì)的范圍。
 

　　另外，如果我們不想使用或安裝Python，則可以從命令行運(yùn)行SQLite3。只需從SQLite3網(wǎng)頁下載“預(yù)編譯的二進(jìn)制文件”，然后使用以下代碼打開數(shù)據(jù)庫：
 

　　在這里，我們只需鍵入要運(yùn)行的查詢，我們將在終端窗口中看到返回的數(shù)據(jù)。
 

　　使用終端的另一種方法是通過Python連接到SQLite數(shù)據(jù)庫。這將使我們能夠使用Jupyter筆記本，以便我們可以在格式整齊的表中查看查詢的結(jié)果。
 

　　為此，我們將定義一個(gè)函數(shù)，該函數(shù)將查詢(存儲(chǔ)為字符串)作為輸入并將結(jié)果顯示為格式化的數(shù)據(jù)框：

　　當(dāng)然，我們不必在SQL中使用Python。如果您已經(jīng)是R程序員，那么我們的R用戶SQL基礎(chǔ)知識(shí)課程將是一個(gè)不錯(cuò)的起點(diǎn)。
 

　　選擇
 

　　我們將使用的第一個(gè)命令是SELECT。SELECT將幾乎是我們編寫的每個(gè)查詢的基礎(chǔ)-它告訴數(shù)據(jù)庫我們要查看哪些列。我們既可以按名稱指定列(用逗號(hào)分隔)，也可以使用通配符*返回表中的每一列。
 

　　除了要檢索的列之外，我們還必須告訴數(shù)據(jù)庫從哪個(gè)表獲取它們。為此，我們使用關(guān)鍵字，F(xiàn)ROM后跟表名。例如，如果我們想看到的start_date，并bike_number在每行trips表中，我們可以使用下面的查詢：

　　在此示例中，我們從SELECT命令開始，以便數(shù)據(jù)庫知道我們希望它為我們找到一些數(shù)據(jù)。然后，我們告訴數(shù)據(jù)庫我們對(duì)start_date和bike_number列感興趣。最后，我們過去FROM使數(shù)據(jù)庫知道我們要查看的列是trips表的一部分。
 

　　編寫SQL查詢時(shí)要意識(shí)到的重要一件事是，我們希望每個(gè)查詢都以分號(hào)(;)結(jié)尾。并非每個(gè)SQL數(shù)據(jù)庫實(shí)際上都需要這樣做，但是有些確實(shí)需要，所以最好養(yǎng)成這種習(xí)慣。
 

　　限制
 

　　開始在Hubway數(shù)據(jù)庫上運(yùn)行查詢之前，我們需要知道的下一個(gè)命令是LIMIT。LIMIT只是告訴數(shù)據(jù)庫您希望它返回多少行。
 

　　SELECT我們?cè)谏弦还?jié)中查看的查詢將為表中的每一行返回所請(qǐng)求的信息trips，但是有時(shí)這可能意味著大量數(shù)據(jù)。我們可能不想要所有這些。相反，如果我們想看到的start_date，并bike_number在數(shù)據(jù)庫中的第一個(gè)五年的旅行，我們可以添加LIMIT到我們的查詢，如下所示：
 

　　我們僅添加了LIMIT命令，然后添加了一個(gè)數(shù)字，該數(shù)字表示我們要返回的行數(shù)。在本例中，我們使用5，但您可以將其替換為任何數(shù)字，以獲取正在處理的項(xiàng)目的適當(dāng)數(shù)據(jù)量。
 

　　LIMIT在共享單車數(shù)據(jù)分析的SQL設(shè)計(jì)中，我們將在Hubway數(shù)據(jù)庫中的查詢中使用很多–該trips表包含超過150萬行數(shù)據(jù)，我們當(dāng)然不需要顯示所有數(shù)據(jù)!
 

　　讓我們?cè)贖ubway數(shù)據(jù)庫上運(yùn)行第一個(gè)查詢。首先，我們將查詢存儲(chǔ)為字符串，然后使用我們先前定義的函數(shù)在數(shù)據(jù)庫上運(yùn)行它?？聪旅娴睦樱?/p>

　　該查詢*用作通配符，而不是指定要返回的列。這意味著該SELECT命令已為我們提供了trips表中的每一列。我們還使用該LIMIT函數(shù)將輸出限制為表的前五行。
 

　　您會(huì)經(jīng)?？吹饺藗?cè)诓樵冎惺褂么髮懙亩禾?hào)(這是我們?cè)?a href="http://5wd995.cn/data/2586.html" target="_blank">共享單車數(shù)據(jù)分析的SQL設(shè)計(jì)中將遵循的約定)，但這主要是優(yōu)先考慮的問題。大寫字母使代碼更易于閱讀，但實(shí)際上絲毫不影響代碼的功能。如果您希望使用小寫命令編寫查詢，則查詢?nèi)詫⒄_執(zhí)行。
 

　　我們前面的示例返回trips表中的每一列。如果只對(duì)duration和start_date列感興趣，則可以按如下所示用列名替換通配符：
 

　　訂購(gòu)
 

　　在回答第一個(gè)問題之前，我們需要知道的最終命令是ORDER BY。此命令使我們可以對(duì)給定列上的數(shù)據(jù)庫進(jìn)行排序。
 

　　要使用它，我們只需指定要排序的列的名稱。默認(rèn)情況下，ORDER BY按升序排序。如果我們想指定數(shù)據(jù)庫應(yīng)該排序的順序，我們可以添加關(guān)鍵字ASC以升序或DESC降序。
 

　　例如，如果我們想將trips表從最短duration到最長(zhǎng)排序，我們可以在查詢中添加以下行：

　　有了SELECT，LIMIT和ORDER BY命令之后，我們現(xiàn)在可以嘗試回答第一個(gè)問題：最長(zhǎng)旅行的持續(xù)時(shí)間是多少?
 

　　要回答這個(gè)問題，將其分為幾個(gè)部分并確定我們需要解決每個(gè)部分的命令會(huì)很有幫助。
 

　　首先，我們需要從表的duration列中提取信息trips。然后，要找出最長(zhǎng)的行程，我們可以duration按降序?qū)α羞M(jìn)行排序。我們可能會(huì)通過以下方式提出一個(gè)查詢，該查詢將獲取我們正在尋找的信息：
 

　　1)使用SELECT檢索duration列FROM的trips表

　　2)使用ORDER BY排序的duration列，并使用DESC關(guān)鍵字來指定要在降序排序

　　3)用于LIMIT將輸出限制為1行
 

　　以這種方式使用這些命令將返回持續(xù)時(shí)間最長(zhǎng)的單行，這將為我們提供問題的答案。
 

　　需要注意的另一件事-隨著查詢添加更多命令并變得更加復(fù)雜，如果將它們分成多行，您可能會(huì)更容易閱讀。就像大寫一樣，這是個(gè)人喜好問題。它不會(huì)影響代碼的運(yùn)行方式(系統(tǒng)只是從頭開始讀取代碼，直到到達(dá)分號(hào)為止)，但它可以使您的查詢更清晰，更易于理解。在Python中，我們可以使用三引號(hào)將字符串分隔為多行。
 

　　讓我們繼續(xù)運(yùn)行此查詢，找出最長(zhǎng)的旅程持續(xù)了多長(zhǎng)時(shí)間。

　　現(xiàn)在我們知道最長(zhǎng)的旅程持續(xù)了9999秒，或者說是166分鐘多一點(diǎn)。但是，最大值為9999時(shí)，我們不知道這是否真的是最長(zhǎng)行程的長(zhǎng)度，或者數(shù)據(jù)庫是否僅設(shè)置為允許四位數(shù)的數(shù)字。
 

　　如果確實(shí)由數(shù)據(jù)庫縮短了特別長(zhǎng)的行程，那么我們可能期望在9999秒處看到很多行程，它們達(dá)到了極限。讓我們嘗試運(yùn)行與之前相同的查詢，但是將調(diào)整LIMIT為返回10個(gè)最長(zhǎng)持續(xù)時(shí)間，以查看是否為這種情況：

　　我們?cè)谶@里看到的是，在9999年并沒有一整趟旅行，因此看起來我們并沒有切斷持續(xù)時(shí)間的高端，但是仍然很難判斷這是否是真正的行程跳閘或最大允許值。
 

　　Hubway會(huì)為30分鐘以上的騎行收取額外費(fèi)用(某人保持9999秒的自行車將不得不支付25美元的額外費(fèi)用)，因此他們認(rèn)為4位數(shù)字足以追蹤大多數(shù)騎行是合理的。
 

　　哪里
 

　　前面的命令非常適合提取特定列的排序信息，但是如果我們要查看數(shù)據(jù)的特定子集，該怎么辦?就是這樣WHERE。WHERE命令允許我們使用邏輯運(yùn)算符指定應(yīng)返回的行。例如，您可以使用以下命令返回bike的每次旅行B00400：

　　您還會(huì)注意到，我們?cè)诖瞬樵冎惺褂靡?hào)。那是因?yàn)閎ike_number儲(chǔ)存為字串。如果該列包含數(shù)字?jǐn)?shù)據(jù)類型，則不需要引號(hào)。
 

　　讓我們編寫一個(gè)查詢，該查詢WHERE用于返回trips表中每一行的每一列，這些查詢的duration時(shí)間超過9990秒：
 

　　如我們所見，此查詢返回了14個(gè)不同的行程，每個(gè)行程持續(xù)9990秒或更長(zhǎng)。關(guān)于此查詢的突出之處是，除一個(gè)結(jié)果外，所有結(jié)果都具有sub_type的"Casual"。也許這表明"Registered"用戶更了解長(zhǎng)途旅行的額外費(fèi)用。也許Hubway可以更好地向休閑用戶傳達(dá)其價(jià)格結(jié)構(gòu)，以幫助他們避免超額收費(fèi)。
 

　　我們已經(jīng)知道，即使是SQL的初學(xué)者級(jí)命令也可以如何幫助我們回答業(yè)務(wù)問題并在數(shù)據(jù)中尋找見解。
 

　　返回到WHERE，我們也可以WHERE使用AND或在子句中組合多個(gè)邏輯測(cè)試OR。例如，如果在我們之前的查詢中，我們只想返回duration超過9990秒的行程，并且還具有已sub_type注冊(cè)的行程，則可以AND用來指定這兩個(gè)條件。
 

　　這是另一個(gè)個(gè)人喜好建議：使用括號(hào)分隔每個(gè)邏輯測(cè)試，如下面的代碼塊所示。這不是代碼正常運(yùn)行所必需的，但是括號(hào)會(huì)隨著您增加復(fù)雜性而使您的查詢更容易理解。
 

　　現(xiàn)在運(yùn)行該查詢。我們已經(jīng)知道它只能返回一個(gè)結(jié)果，因此應(yīng)該容易檢查我們是否正確：

　　我們?cè)谔娱_頭提出的下一個(gè)問題是“'注冊(cè)'用戶進(jìn)行了多少次旅行?” 為了回答這個(gè)問題，我們可以運(yùn)行與上面相同的查詢，并修改WHERE表達(dá)式以返回sub_type等于的所有行，'Registered'然后對(duì)它們進(jìn)行遞增計(jì)數(shù)。
 

　　但是，SQL實(shí)際上有一個(gè)內(nèi)置命令來為我們進(jìn)行計(jì)數(shù)COUNT。
 

　　COUNT使我們可以將計(jì)算轉(zhuǎn)移到數(shù)據(jù)庫，從而省去了編寫額外腳本來計(jì)算結(jié)果的麻煩。要使用它，我們只需要添加(COUNT(column_name)而不是添加)您想要的列即可SELECT，如下所示：

　　在這種情況下，我們選擇對(duì)哪一列進(jìn)行計(jì)數(shù)都沒有關(guān)系，因?yàn)槊恳涣卸紤?yīng)該有查詢中每一行的數(shù)據(jù)。但是有時(shí)查詢可能缺少某些行的值(或“空”)。如果不確定一列是否包含空值，則可以COUNT在該id列上運(yùn)行-該id列永遠(yuǎn)不會(huì)為空，因此我們可以確保計(jì)數(shù)不會(huì)遺漏任何內(nèi)容。
 

　　我們還可以使用COUNT(1)或COUNT(*)來計(jì)數(shù)查詢中的每一行。值得注意的是，有時(shí)我們實(shí)際上可能想COUNT在具有空值的列上運(yùn)行。例如，我們可能想知道數(shù)據(jù)庫中有多少行缺少一列的值。
 

　　讓我們看一個(gè)查詢來回答我們的問題。我們可以SELECT COUNT(*)用來計(jì)算返回的總行數(shù)，并WHERE sub_type = "Registered"確保只計(jì)算注冊(cè)用戶的旅行次數(shù)。

　　該查詢有效，并且已返回我們問題的答案。但是列標(biāo)題不是特別描述性的。如果其他人看這張桌子，他們將無法理解它的含義。如果我們想使結(jié)果更具可讀性，可以使用AS別名(或昵稱)作為輸出。讓我們重新運(yùn)行上一個(gè)查詢，但給我們的列標(biāo)題加上別名Total Trips by Registered Users：

　　匯總功能
 

　　COUNT這不是SQL掌握的唯一數(shù)學(xué)技巧。我們也可以使用SUM，AVG，MIN和MAX分別返回列的求和，平均值，最小值和最大值。這些與COUNT一起被稱為集合函數(shù)。
 

　　因此，要回答我們的第三個(gè)問題，“平均旅行持續(xù)時(shí)間是多少?” ，我們可以使用列AVG上的函數(shù)duration(再次使用AS，為輸出列起一個(gè)更具描述性的名稱)：

　　事實(shí)證明，平均旅行持續(xù)時(shí)間是912秒，大約是15分鐘。這是有道理的，因?yàn)槲覀冎繦ubway會(huì)對(duì)30分鐘以上的行程收取額外費(fèi)用。該服務(wù)是為騎手短途單程旅行而設(shè)計(jì)的。
 

　　接下來的問題是，注冊(cè)用戶或臨時(shí)用戶旅行更長(zhǎng)的時(shí)間呢?我們已經(jīng)知道一種解決此問題的方法-我們可以SELECT AVG(duration) FROM trips使用WHERE子句運(yùn)行兩個(gè)查詢，這些子句將一個(gè)限制到"Registered"一個(gè)，一個(gè)限制到"Casual"用戶。
 

　　不過，讓我們以不同的方式來做。SQL還包括使用GROUP BY命令在單個(gè)查詢中回答此問題的方法。
 

　　通過...分組
 

　　GROUP BY 根據(jù)特定列的內(nèi)容將行分為幾組，并允許我們?cè)诿總€(gè)組上執(zhí)行聚合函數(shù)。
 

　　為了更好地了解其工作原理，讓我們看一下該gender專欄。每行可以有三個(gè)可能的值一個(gè)gender列，"Male"，"Female"或Null(丟失;我們沒有g(shù)ender對(duì)普通用戶的數(shù)據(jù))。
 

　　當(dāng)使用時(shí)GROUP BY，數(shù)據(jù)庫將根據(jù)gender列中的值將每一行分成不同的組，就像我們將一副紙牌分成不同的花色一樣。我們可以想象制造兩堆，所有雄性之一，所有雌性之一。
 

　　一旦我們擁有兩個(gè)獨(dú)立的堆，數(shù)據(jù)庫將依次對(duì)它們中的每一個(gè)執(zhí)行查詢中的任何聚合函數(shù)。COUNT例如，如果使用，則查詢將計(jì)算每個(gè)堆中的行數(shù)，并分別返回每個(gè)堆的值。
 

　　讓我們?cè)敿?xì)介紹如何編寫查詢來回答我們的問題，即注冊(cè)用戶或臨時(shí)用戶是否需要更長(zhǎng)的行程。
 

　　1)與到目前為止的每個(gè)查詢一樣，我們將從SELECT告訴數(shù)據(jù)庫想要查看哪些信息開始。在這種情況下，我們需要sub_type和AVG(duration)。

　　2)我們還將包括GROUP BY sub_type按訂閱類型分離數(shù)據(jù)，并分別計(jì)算注冊(cè)用戶和臨時(shí)用戶的平均值。
 

　　當(dāng)我們將它們放在一起時(shí)，代碼如下所示：

　　完全不同!平均而言，注冊(cè)用戶的出行時(shí)間約為11分鐘，而休閑用戶每次出行的時(shí)間則接近25分鐘。注冊(cè)用戶可能會(huì)進(jìn)行更短，更頻繁的旅行，這可能是他們上下班的一部分。另一方面，休閑用戶每次旅行花費(fèi)的時(shí)間大約是兩倍。
 

　　休閑用戶可能來自人口統(tǒng)計(jì)學(xué)(例如旅游者)，他們更傾向于長(zhǎng)途旅行，以確保他們四處逛逛并看到所有景點(diǎn)。一旦我們發(fā)現(xiàn)了數(shù)據(jù)的差異，公司便可以通過多種方式對(duì)其進(jìn)行調(diào)查，以更好地了解造成這種情況的原因。
 

　　但是，出于共享單車數(shù)據(jù)分析的SQL設(shè)計(jì)的目的，讓我們繼續(xù)。我們的下一個(gè)問題是旅行次數(shù)最多的是哪輛自行車?。我們可以使用非常相似的查詢來回答這個(gè)問題?？聪旅娴氖纠?，看看是否可以弄清楚每行的內(nèi)容-我們將逐步進(jìn)行操作，以便檢查是否正確：

　　從輸出中可以看到，自行車B00490出行最多。讓我們來看看如何到達(dá)那里：
 

　　1)第一行是一個(gè)SELECT子句，告訴數(shù)據(jù)庫我們要查看bike_number列和每行的計(jì)數(shù)。它還AS用于告訴數(shù)據(jù)庫以更有用的名稱顯示每一列。

　　2)第二行用于FROM指定我們要查找的數(shù)據(jù)在trips表中。

　　3)第三行是開始有些棘手的地方。我們GROUP BY用來告訴第COUNT1行的函數(shù)分別計(jì)算每個(gè)值bike_number。

　　4)在第四行，我們有一個(gè)ORDER BY子句對(duì)表格進(jìn)行降序排序，并確保最常用的自行車在頂部。

　　5)最后，我們LIMIT將輸出限制為第一行，因?yàn)槲覀內(nèi)绾螌?duì)第四行的數(shù)據(jù)進(jìn)行排序，所以我們知道這將是旅行次數(shù)最多的自行車。
 

　　算術(shù)運(yùn)算符
 

　　我們的最后一個(gè)問題比其他問題更加棘手。我們想知道30歲以上注冊(cè)會(huì)員的平均旅行時(shí)間。
 

　　我們可以算出30歲以下的人出生的那一年，然后再插入，但是一個(gè)更優(yōu)雅的解決方案是直接在查詢中使用算術(shù)運(yùn)算。SQL允許我們使用+，-，*和/在一次對(duì)整個(gè)列執(zhí)行運(yùn)算。

　　加入
 

　　到目前為止，我們一直在研究?jī)H從trips表中提取數(shù)據(jù)的查詢。但是，SQL之所以如此強(qiáng)大是因?yàn)樗刮覀兡軌驈耐徊樵冎械亩鄠€(gè)表中提取數(shù)據(jù)。
 

　　我們的自行車共享數(shù)據(jù)庫包含第二個(gè)表stations。該stations表包含有關(guān)Hubway網(wǎng)絡(luò)中每個(gè)站點(diǎn)的信息，并包括id該trips表引用的列。
 

　　不過，在開始研究該數(shù)據(jù)庫中的一些實(shí)際示例之前，讓我們回顧一下較早的假設(shè)訂單跟蹤數(shù)據(jù)庫。在該數(shù)據(jù)庫中，我們有兩個(gè)表orders和customers，它們通過customer_id列連接。
 

　　假設(shè)我們要編寫一個(gè)查詢，該查詢返回?cái)?shù)據(jù)庫中的每個(gè)訂單的order_number和name。如果它們都存儲(chǔ)在同一個(gè)表中，則可以使用以下查詢：

　　不幸的是，order_number列和name列存儲(chǔ)在兩個(gè)不同的表中，因此我們必須添加一些額外的步驟。讓我們花一點(diǎn)時(shí)間考慮一下數(shù)據(jù)庫在返回所需信息之前需要了解的其他事項(xiàng)：
 

　　1)該order_number列在哪個(gè)表中?

　　2)該name列在哪個(gè)表中?

　　3)orders表中的信息如何與表中的信息連接customers?
 

　　要回答這些問題中的前兩個(gè)，我們可以在SELECT命令中包括每列的表名。我們這樣做的方法就是簡(jiǎn)單地寫一個(gè)表名和列名，用.。分隔。例如，代替SELECT order_number, name我們會(huì)寫SELECT orders.order_number, customers.name。在此處添加表名稱可以通過告訴數(shù)據(jù)庫要查找的表來幫助數(shù)據(jù)庫查找我們要查找的列。
 

　　為了告訴數(shù)據(jù)庫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è)表的列上。因此，我們將要用來ON告訴數(shù)據(jù)庫，這兩列引用的是這樣的東西：

　　我們?cè)俅问褂?來確保數(shù)據(jù)庫知道這些列中的每一個(gè)都在哪個(gè)表中。因此，當(dāng)我們將所有這些放在一起時(shí)，我們得到的查詢?nèi)缦滤荆?/p>

　　該查詢將返回?cái)?shù)據(jù)庫中每個(gè)訂單的訂單號(hào)以及與每個(gè)訂單相關(guān)聯(lián)的客戶名稱。
 

　　回到我們的Hubway數(shù)據(jù)庫，我們現(xiàn)在可以編寫一些查詢以JOIN進(jìn)行實(shí)際操作。
 

　　在開始之前，我們應(yīng)該看一下表中的其余列stations。這是一個(gè)查詢，向我們顯示了前5行，因此我們可以看到stations表的外觀：

　　1)id—每個(gè)工作站的唯一標(biāo)識(shí)符(對(duì)應(yīng)于表中的start_station和end_station列trips)

　　2)station —站名

　　3)municipality —車站所在的城市(波士頓，布魯克林，劍橋或薩默維爾)

　　4)lat —車站的緯度

　　5)lng —車站的經(jīng)度

　　6)哪些車站最經(jīng)常往返?

　　7)在不同的城市中有多少次旅行開始和結(jié)束?
 

　　與以前一樣，我們將嘗試回答數(shù)據(jù)中的一些問題，從哪個(gè)站是最頻繁的起點(diǎn)開始?讓我們逐步進(jìn)行操作：
 

　　1)首先，我們要使用SELECT返回表中的station列stations和COUNT行數(shù)。

　　2)接下來，我們指定我們想要的表JOIN并告訴數(shù)據(jù)庫連接它們ON的start_station列trips表和id列stations的表。

　　3)然后我們進(jìn)入查詢的內(nèi)容-我們?cè)诒砀裰蠫ROUP BY的station列，stations以便我們COUNT將分別計(jì)算每個(gè)車站的行程次數(shù)

　　4)最后，我們可以O(shè)RDER BY我們COUNT并LIMIT輸出到結(jié)果的管理的數(shù)量

　　如果您熟悉波士頓，您將了解為什么這些是最受歡迎的電臺(tái)。南站是該市主要的通勤火車站之一，查爾斯街沿河延伸，靠近一些風(fēng)景優(yōu)美的路線，博伊爾斯頓和信標(biāo)街就在市中心，靠近許多辦公大樓。
 

　　我們要看的下一個(gè)問題是往返最常用的車站是?我們可以使用與以前幾乎相同的查詢。我們將以相同的方式SELECT使用相同的輸出列和JOIN表，但是這次我們將添加一個(gè)WHERE子句以限制我們COUNT的行程start_station與相同end_station。

　　我們可以看到，這些站點(diǎn)的數(shù)量與上一個(gè)問題相同，但數(shù)量要低得多。最繁忙的站點(diǎn)仍然是最繁忙的站點(diǎn)，但是總體而言，較低的站點(diǎn)表明人們通常在使用Hubway自行車從A點(diǎn)到達(dá)B點(diǎn)，而不是在返回起點(diǎn)之前先騎自行車一會(huì)兒。
 

　　這里有一個(gè)明顯的不同-Esplande并不是我們第一個(gè)查詢中總體上最繁忙的車站之一，它似乎是往返行程中最繁忙的車站。為什么?好吧，一張圖片值一千個(gè)字。當(dāng)然，這看起來像是騎自行車的好地方：
 

　　接下來的問題是：在不同的城市開始和結(jié)束多少次旅行?這個(gè)問題使事情更進(jìn)一步。我們想知道在不同的地方開始和結(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)建別名以使其更直觀地顯示名稱的方式完全相同AS。
 

　　例如，我們可以使用下面的代碼JOIN的stations表到trips使用的“開始”別名表。然后，我們可以將“開始”與我們的列名稱結(jié)合使用，.以引用來自此特定對(duì)象的數(shù)據(jù)JOIN(而不是第二個(gè)，JOIN我們將處理ON該end_station列)：
 

　　這是我們運(yùn)行最終查詢時(shí)的樣子。請(qǐng)注意，我們?cè)?jīng)<>表示“不等于”，但!=也可以使用。

　　這表明，在150萬次旅行中，大約有300,000次(或20%)在與開始的城市不同的地方結(jié)束–進(jìn)一步的證據(jù)表明，人們大多使用Hubway自行車進(jìn)行相對(duì)較短的旅行，而不是在城鎮(zhèn)之間進(jìn)行較長(zhǎng)的旅行。
 

　　如果您已經(jīng)做到了，那么恭喜!您已經(jīng)開始掌握SQL的基礎(chǔ)知識(shí)。我們已經(jīng)討論了許多重要的命令，SELECT，LIMIT，WHERE，ORDER BY，GROUP BY和JOIN，以及骨料和算術(shù)功能。這些將為您繼續(xù)SQL之旅提供堅(jiān)實(shí)的基礎(chǔ)。
 

　　下一步
 

　　在完成本入門SQL教程之后，您現(xiàn)在應(yīng)該能夠找到自己感興趣的數(shù)據(jù)庫并編寫查詢以提取信息。好的第一步可能是繼續(xù)使用Hubway數(shù)據(jù)庫，以了解您還能找到什么。以下是您可能想嘗試回答的其他一些問題：
 

　　1)有多少趟旅程產(chǎn)生了額外的費(fèi)用(持續(xù)時(shí)間超過30分鐘)?

　　2)哪輛自行車使用的時(shí)間最長(zhǎng)?

　　3)注冊(cè)用戶或臨時(shí)用戶是否往返更多?

　　4)哪個(gè)城市的平均停留時(shí)間最長(zhǎng)?
 

　　如果您想更進(jìn)一步，請(qǐng)查看我們的交互式SQL課程，該課程涵蓋了您從入門到高級(jí)SQL所需的一切知識(shí)，適用于數(shù)據(jù)分析師和數(shù)據(jù)科學(xué)家的工作。在數(shù)據(jù)科學(xué)課程頁面上的SQL菜單下查找所有交互式SQL課程產(chǎn)品的完整列表。