第1章 高效處理千萬數據

小白進入公司已經一年有余，在自身的不斷努力下，工作表現出色，獲得領導及同事們的一致認可與贊揚，并獲得“年度優秀員工”稱號。

隨著公司的業務規模變大，業務運營數據也迅速增長。為了充分利用好這些數據，公司領導決定成立運營分析部門。Mr.林參加了公司內部競聘，通過層層篩選與選拔，眾望所歸地成為了運營分析部門的經理，負責部門運作及管理，為公司業務運營提供有效的數據分析支撐。

由于運營分析部門剛成立，急需招兵買馬，因此小白也成為Mr.林“心儀”的對象。

Mr.林找到小白：小白，愿意加入運營分析團隊嗎？

小白興奮地說：當然愿意啦！夢寐以求的事情，我就擔心牛董不放人。

Mr.林：你放心！牛董那邊我去說，只要你愿意就行。我先找牛董要人，現在領導層都很重視運營分析工作，等事情定下了就通知你過來上班。

小白美滋滋地說：好的。

半小時后，Mr.林打電話給小白：小白，牛董已經同意了，并且已經通知HR安排其他同事跟你交接工作。你今天交接完工作，明天就帶上你的家當過來吧。

小白很快就進入了狀態：遵命，領導。

第二天上班一大早小白帶著全部家當來到Mr.林辦公桌前報到。

Mr.林驚訝地問：小白，你就這點家當啊？除了一臺筆記本電腦外，就一個保溫杯、一個靠枕、一小瓶綠色植物、一本記事本，外加一支筆！跟我見到的其他女同事完全不一樣，她們還有各種五花八門的小擺設、小公仔等。

小白淡定地說：嘿嘿！我就喜歡簡單，就像做數據分析一樣，簡單而不空洞，能說明問題就好。

Mr.林面露喜悅之色：小白，我果然沒選錯你，對我胃口。

小白向Mr.林略微彎了下腰說道：Mr.林，請多多指教！

Mr.林：那我們就開始工作吧！小白，先給你介紹下我們部門的主要職責。考一下你，數據分析在我們日常經營分析工作中的作用體現在哪幾方面呢？

小白底氣十足：這個難不倒我，我剛進公司的第一天，您就給我介紹了數據分析，談到它的作用。在我們日常經營分析工作中，數據分析主要有三大作用，如圖1-1所示。

圖1-1 數據分析三大作用

正是因為您告訴我數據分析這三大作用，我才對數據分析有了更深刻的理解與認識，加上您傳授的實用數據分析方法與技巧，我對牛董交辦的工作，才能輕松自如地處理完成。

Mr.林：說得一點也不錯！我們運營分析部的工作正是基于數據分析這三大作用展開的。所以，運營分析部的主要職責如下：

① 負責完成公司運營日報、周報、月報等日常通報，告訴公司領導及運營部門現階段公司整體運營情況，這是通過各個關鍵經營指標完成情況來衡量的。

② 根據公司運營需要，開展業務專題分析。比如基于日報、周報、月報的現狀分析，我們對公司的運營情況有了基本了解，但這還不夠，還需要知道運營情況具體好在哪里，差在哪里，是什么原因引起的。這時就需要開展原因分析，以進一步確定業務變動的具體原因。

③ 根據公司運營需要，開展市場研究工作。如果現有數據無法滿足分析需求，就需要通過外部用戶調研進行補充說明，我們才能進一步了解用戶的真實想法與需求。

④ 開展預測分析，預測公司未來發展趨勢，為公司制訂運營目標及策略提供有效的決策依據，以保證公司的可持續健康發展。

⑤ 搭建公司經營分析體系，指導公司業務運營。

以上5條就是我們運營分析部門現階段的主要工作職責，我會帶領你和其他同事一起完成。

小白：好的，那我們接下來要做什么？

Mr.林：我要先對你進行一些培訓，主要是數據處理與分析的應用工具的培訓。

小白滿臉疑惑地問道：這些工作不是用Excel就可以完成么？

Mr.林：嘿嘿！Excel當然是非常實用的數據分析工具，不過那是有前提條件的，因為Excel對數據有限制，Excel 97-2003版本，能容納的行與列數都是有限制的，具體如圖1-2所示，Excel 2007-2010版本最多也只有1048576行、16384列。

圖1-2 Excel各版本對數據的限制

現在已經到了大數據時代，數據量動不動就超過百萬條，Excel已經滿足不了數據處理與分析的需求了。

沒等Mr.林說完，小白又發問了：什么是大數據呢？

Mr.林耐心地解釋道：大數據具有4大特點，可以用4個“V”來概括，如圖1-3所示。

圖1-3 大數據4大特點

舉個例子，我們公司有1000多萬的用戶，單單一個用戶信息表，Excel 2007—2010版本就無法容納得下，更別說1000多萬用戶的購買行為數據。

這時候我們就要借助于數據庫來實現數據的高效存儲、處理、分析。

1.1 最容易上手的數據庫

1.1.1 數據庫那些事兒

Mr.林：我們先來認識下什么是數據庫吧！

小白迫不及待地說：非常期待，快開始吧！

Mr.林：數據庫（Database）是按照數據結構來組織、存儲和管理數據的倉庫。它利用數據庫中的各種對象，記錄、處理和分析各種數據。

隨著現代社會進入信息時代，我們每天的工作和生活都離不開各種信息。對這樣的海量數據，這時就需要采用數據庫對其進行有效的存儲與管理，并運用數據庫進行合理的處理與分析，使其轉化為有價值的數據信息。

圖1-3 大數據4大特點

一個通用數據庫具有以下幾項基本功能。

★ 向數據庫中添加新數據記錄，例如增加用戶注冊信息。

★ 編輯數據庫中的現有數據，例如修改某個用戶信息。

★ 刪除數據庫中信息記錄，例如刪除失去時效性的數據，以釋放存儲空間。

★ 以不同方式組織和查看數據，例如對數據進行查詢、處理與分析。

常用的數據庫有Oracle、Microsoft SQL Server、MySQL、Microsoft Access等關系型數據庫，隨著大數據時代的到來，相關的數據庫技術也快速發展，如基于NoSQL技術的分布式數據庫Hbase、MongoDB、Redis等。

Access數據庫

Mr.林：我們從關系型數據庫Access入手，因為它夠友好、夠簡單，會讓我們的學習之旅更輕松。

小白：好的。

Mr.林：Access數據庫是Microsoft Office辦公軟件中一個極為重要的組成部分，是一種關系數據庫管理系統軟件，它能夠幫助用戶處理各種海量信息，不僅能存儲數據，更重要的是還能夠對數據進行處理和分析，數據處理功能比Excel更勝一籌。由于目前Access 2010為較為常用版本，所以我們將基于Access 2010來學習數據處理和分析（如圖1-5所示）。

圖1-5 Access數據庫示例

在Access 2010中，數據庫窗口中包含“表”、“查詢”、“窗體”、“報表”、“宏”、“模塊”六大對象。在數據庫中，“表”用來存儲數據；“查詢”用來查找數據；“窗體”、“報表”用于獲取數據；而“宏”和“模塊”則用來實現數據的自動化操作。

Access 2010數據庫還提供了多種向導、生成器、模板，把數據存儲、數據查詢、界面設計、報表生成等操作規范化，為建立功能完善的數據庫管理系統提供了方便，也使得普通用戶不必編寫代碼，就可以完成大部分數據管理的任務，操作簡便，容易使用和掌握。

Access數據庫作為Microsoft Office辦公軟件包中的一員，它還可以與Excel、Word、Power-Point、Outlook等軟件進行數據的交互與共享，例如分析報告的自動化，后面我會進一步介紹。

小白：好的。

Mr.林：下面我們就學習用Access 2010進行數據處理與分析，用到的主要對象是：表和查詢。

（1）表

作為一個數據庫，最基本的組成單位就是表。建立和規劃數據庫，首先要做的就是建立各種數據表。數據表是數據庫中存儲數據的唯一單位，數據庫將各種信息分門別類地存放在各種數據表中，例如用戶信息表、訂單表、采購表等。

（2）查詢

查詢是數據庫中應用最多的對象之一，可執行很多不同的功能，最常用的功能是根據指定條件從表中檢索數據。

查詢和表的區別在于，查詢中的所有數據都不是真正單獨存在的。查詢實際上是一個固定的篩選，它根據指定條件將表中的數據篩選出來，并以表的形式返回篩選結果。

在Access數據庫中，我們就是采用查詢方式進行數據處理與分析的。

優勢與不足

小白疑惑不解地問：為什么用Access數據庫，而不用Oracle、Microsoft SQL Server等數據庫呢？

Mr.林：因為Access數據庫與Oracle等其他關系型數據庫相比具有以下兩大優勢：

（1）操作界面友好，易操作

Access與Excel、PowerPoint、Word都是微軟Office產品，只要熟悉Excel、PowerPoint、Word中的任一款軟件，即使沒有數據庫經驗，對Access也能快速上手。Access風格與Windows完全一樣，用戶想要生成對象并應用，只要使用鼠標進行拖放即可，非常直觀方便。并且作為Office辦公軟件的一部分，Access可以與Office其他軟件集成，實現無縫連接。

（2）Access查詢處理可直接生成相應的SQL語句

通過Access查詢向導設置好需要的表關聯及查詢條件，單擊“SQL視圖”，即可獲取相應的SQL語句，無須重新編寫。在此基礎上，還可以進行簡單的調整、優化，即可轉化為所需的SQL語句，方便快捷。

小白心中釋然：那我就放心了，你沒說之前，我還擔心數據庫比較難學呢。

Mr.林：不用擔心，有Mr.林在嘛，包教包會，我們繼續。

有優勢，自然也有不足，Access是小型數據庫，與Oracle等其他關系型數據庫相比存在以下不足。

★ 數據庫過大時（一般Access數據庫文件百兆以上），其性能會變差。

★ 記錄數過多時（一般記錄數達到千萬條以上），其性能會變差。

★ Access數據庫中每個數據庫文件上限為2GB。

雖然Access數據庫存在以上三大不足，但并不妨礙我們使用它完成日常工作與學習任務，因為用它學習SQL處理數據真的很方便，不需要寫SQL語句。只要數據記錄不超億條，其處理速度還是可以接受的，數據記錄越少，其處理速度就越快。

1.1.2 萬能的SQL

小白：Mr.林，你剛才提到了好幾次SQL，什么是SQL呀？

Mr.林： SQL（Structured Query Language）是結構化查詢語言，它是一種通用的關系型數據庫操作語言。簡單來說，它就是讓數據庫按我們的意思來實現查詢操作的語言。

說到這里，小白靈機一動：我可不可以這樣理解：SQL就好比動畫片《葫蘆娃兄弟》里那個女妖精手中的寶貝——如意，如意、如意，按我心意，快快顯靈……

Mr.林忍不住捧腹大笑：哈哈！我看行，還是你機靈，你這個比喻既生動又形象。

由于SQL功能豐富強大，語言簡潔易學，使用方法靈活，目前所有主要的關系數據庫管理系統都支持SQL。

雖然Access數據庫大部分查詢都可通過菜單完成，不需要用到SQL語句，但是如果想真正利用Access數據庫強大的數據處理、分析能力，那么掌握SQL是非常必要的。

作為一名優秀的數據分析師，只有親自經歷在數據庫中處理與分析數據的過程，才能對分析結果有更深層次的認識，同時也會加深對業務的理解，否則看到的只是一個個數字，并不能體會其內涵。

另外，業內人士常說的數據挖掘，很多是通過對歷史數據進行建模預測，生成一定的規則，然后數據庫工程師將生成的規則編寫成相應的SQL語句，并編寫成數據庫的存儲過程，可定期執行它們得到數據模型結果。

最后，處理大數據的Hadoop，所使用的Hive語言（HQL），也是與SQL語言基本一致，只不過部分語句的編寫或功能存在差異。掌握了SQL，再學習HQL就非常容易了。

小白：那么如何編寫SQL語句呢？

基本語法

Mr.林：我們現在來了解一下SQL基本語法，常用的SQL語句如圖1-6所示。

圖1-6 Access數據庫常用的SQL語句

我們做數據分析時，在數據庫中主要的操作就是數據合并、數據分組、數據去重等，這些操作都是通過查詢來完成的。因此，數據查詢是數據庫的核心操作。而在SQL查詢語言中有一條查詢命令，即SELECT語句。

SELECT語句是SQL的核心語言，它能完成強大的查詢功能，根據指定的條件規則從數據庫中查詢出所要的數據。SELECT語句的基本語法是：

SELECT字段1,字段2,字段3,……
FROM表
WHERE條件

小白撓了撓頭：不是太明白，能否舉個例子？

Mr.林靈機一動：那好，我就給你舉生活中例子：假設你爸媽催你結婚，并且他們已經上婚姻中介所幫你物色對象相親。

小白紅著小臉，不好意思地問道：你怎么知道我爸媽在催我？

Mr.林：人之常情呀！老人家都希望自己兒女早點結婚，等著抱孫子呢！我們繼續剛才的例子。中介所工作人員從他們的會員數據庫中按你爸媽的要求篩選出目標人選，供他們進一步選擇，那么婚姻中介所的工作人員會在他的數據庫命令窗口寫下如下SQL語句：

SELECT姓名,性別,年齡,身高,婚姻狀況,教育背景,月收入,是否有房,是否有車,備注
FROM會員表
WHERE性別=男
AND年齡BETWEEN(26,30)
AND身高BETWEEN(170,180)
AND婚姻狀況=未婚
AND教育背景IN( 本科, 研究生)
AND月收入>=8000
AND是否有房=是
AND是否有車=是
AND備注IN ( 細心, 大方, 浪漫, 英俊, 紳士, 智慧 )
ORDER BY月收入DESC;

小白：您舉的這個生動例子，確實很清晰直觀。

我爸媽的要求，不對，差點被您繞暈了，應該是您假設我爸媽的要求：首先必須是男的，年齡在26至30歲之間，身高在1米7至1米8之間，婚姻狀況為未婚，教育背景為本科或研究生，月收入不低于8000元，必須有房有車，還要求細心、大方、浪漫、英俊、紳士、智慧。最后篩出來的名單再按月收入降序排序。

Oh！My God！上哪找這樣條件的未婚男士？如果有的話，我就考慮考慮。

Mr.林：哈哈！小白，你入戲還真快，這么快就進入角色了。

小白的臉瞬間又紅了：Mr.林，您又在給我下套呀！不過這樣的例子確實很生動，一看就懂，我大概知道SELECT語句怎么用了。

注意事項

Mr.林：好的，現在我們一起來了解下編寫SQL語句時的一些注意事項。

① SQL語句中，英文字母大寫或小寫均可。

② 每個SQL語句的關鍵字用空格符號分隔，例如：

SELECT字段FROM表

③ 字段或參數之間用逗號分隔，例如：

SELECT姓名,性別,年齡,身高,教育背景
FROM會員表
WHERE教育背景IN( 本科 , 研究生 )

④ SQL語句中如參數為字符型，那么需要使用單引號，數值型不使用單引號，例如：

SELECT姓名,性別,月收入
FROM會員表
WHERE性別=男AND月收入>=8000

⑤ SQL語句結束時，在語句結尾處添加分號。在Access數據庫中，用分號結束對于SQL語句不是必須的。

⑥ SQL語句中如表名、字段名中出現空格、“/”、“\”等特殊字符時，需用方括號“[]”將含有特殊字符的表名或字段名括起來，以免得到不正確的結果或SQL語句無法運行。

⑦ SQL語句中，“*”代表選擇選定表格中的所有字段，并且按照其在數據庫中的固定順序來排序，例如：

SELECT * FROM表

⑧ 在函數參數或條件查詢中，如果參數或查詢條件為日期和時間類型數據，需要在數據值兩端加上井字符號“#”，以表示數據類型為日期型。

⑨ SQL語句中使用的逗號、分號、單引號、括號等符號均為英文狀態下輸入的符號。

⑩ 應盡量避免在數據庫中進行全表掃描。首先應考慮用WHERE子句篩選出需要的數據，其次在WHERE子句中，應盡量避免使用“!=”或“< >”、“OR”等，最后應盡量避免在WHERE子句中對字段進行函數操作，否則將進行全表掃描。

其他注意事項等介紹到具體示例時再進行講解。

小白：好的，您剛才說的10條注意事項我都記下了，回去我再認真復習復習。

1.1.3 兩招導入數據

Mr.林：小白，接下來我們學習如何將數據導入到數據庫中。因為數據量大才采用Access數據庫進行數據處理與分析，而大型數據文件一般以TXT文本形式存儲，所以我們主要學習如何導入TXT文本數據。還記得我教你的如何將TXT文本數據導入Excel么？

小白：當然記得，工作中我常用呢。

Mr.林：很好，在Access數據庫中導入TXT文本數據與Excel中的操作步驟類似，都是按照一定的數據分隔符號或者數據寬度，將文本中的數據自動分配到數據表中。

在Access數據庫中主要有兩種方式：一是直接導入法，二是建立鏈接法。下面以導入“訂購明細.txt”數據為例，一起來學習這兩種數據導入方法。

直接導入法

STEP 01 啟動Access，單擊【文件】選項卡，單擊【新建】按鈕，在【可用模板】中選擇【空數據庫】，并為新建的數據庫文件命名，設置好存放路徑，最后單擊【創建】按鈕，如圖1-7所示。

圖1-7 新建數據庫文件

STEP 02 在創建好的數據庫中，單擊【外部數據】選項卡，在【導入并鏈接】組中單擊【文本文件】按鈕，彈出如圖1-8所示的對話框，瀏覽指定數據源，并在【指定數據在當前數據庫中的存儲方式和存儲位置】項中，選中默認的【將源數據導入當前數據庫的新表中】項，單擊【確定】按鈕。

圖1-8 【獲取外部數據—文本文件】對話框

STEP 03 在彈出的第一個【導入文本向導】對話框中，選中默認的【帶分隔符】作為數據分隔方式，如圖1-9所示，單擊【下一步】按鈕。

圖1-9 【導入文本向導】對話框1

STEP 04 在彈出的第二個【導入文本向導】對話框中（如圖1-10所示），選擇【逗號】作為分隔符，并勾選【第一行包含字段名稱】復選框，單擊【下一步】按鈕。

圖1-10 【導入文本向導】對話框2

需要說明的是，分隔符及第一行是否包含字段名稱需根據數據本身的實際情況進行選擇，本例中為以逗號分隔，并且第一行包含字段名稱。

STEP 05 在彈出的第三個【導入文本向導】對話框中（如圖1-11所示），可對文本數據的各個字段名稱、數據類型、索引以及是否導入字段進行設置，用戶可根據數據本身的實際情況進行選擇與設置。本例采用默認設置，單擊【下一步】按鈕。

圖1-11 【導入文本向導】對話框3

STEP 06 在彈出的第四個【導入文本向導】對話框中（如圖1-12所示），選擇【讓Access添加主鍵】，則Access數據庫會將“ID”字段添加為目標表中的第一個字段，并且用從1開始的唯一ID自動填充它，單擊【完成】按鈕。

圖1-12 【導入文本向導】對話框4

STEP 07 在彈出的【保存導入步驟】對話框中保存詳細信息，有助于在以后重復執行該操作，而不必每次都逐步完成向導。可根據數據導入的需求，選擇是否保存導入步驟，本例選擇不保存導入步驟，單擊【關閉】按鈕。

文本數據導入Access數據庫后的結果如圖1-13所示，用鼠標雙擊左邊Access對象框里的“訂購明細”表，即可在右邊窗口顯示產品訂購明細。

圖1-13 文本數據導入結果

小白：Mr.林，剛才您說到兩個新名詞“索引”、“主鍵”，這兩個是什么呀？

Mr.林：索引相當于對指定的列進行排序，它就好比是一本書的目錄，通過它可以快速查詢到結果，不需要進行全表掃描，可以大大加快數據庫的查詢速度。

主鍵是確定數據中每一條記錄的唯一標識，其實也是一個索引，是一個特殊索引，因為主鍵所在列里的每一個記錄都是唯一的，在同一個表里只能有一個主鍵。簡單來說，主鍵就是所在列不能出現相同記錄的特殊索引，且這個索引只能在表里出現一次。

綜上所述，主鍵與索引的具體區別為以下四點。

① 主鍵用于標識數據庫記錄的唯一性，不允許記錄重復，且鍵值不能為空。主鍵也是一個特殊索引，主鍵等于索引，索引不一定等于主鍵。

② 索引可以提高查詢速度，通過它可以快速查詢到結果，不需要進行全表掃描。

③ 使用主鍵，數據庫會自動創建主索引，也可以在非主鍵上創建索引，提高查詢速度。

④ 數據表中只允許有一個主鍵，但是可以有多個索引。

在Access數據庫中，雖然主鍵不是必需的，但最好為每個表都設置一個主鍵，這樣可提高查詢速度。

小白點了點頭：明白。

建立鏈接法

Mr.林：現在我們來學習第二種導入方法：建立鏈接法。

建立鏈接法與直接導入法步驟基本類似，不同的地方就在于步驟2與步驟7。在步驟2中，對于【指定數據在當前數據庫中的存儲方式和存儲位置】項，更改為選中【通過創建鏈接表來鏈接到數據源】，如圖1-14所示。

圖1-14 【獲取外部數據—文本文件】對話框

因為這種方法是以鏈接方式建立數據庫與源數據的鏈接關系，所以只要不刪除，這個鏈接關系一直存在，也就無須【保存導入步驟】，所以采用鏈接方式就沒有步驟7。

其余數據導入操作步驟基本一致，小白，你可以事后自行練習這兩種文本數據導入方法。

小白：好的，您介紹的“直接導入法”與“建立鏈接法”這兩種方法之間有何區別呢？

Mr.林：你這個問題問得真好，不愧是做數據分析的好苗子。

★ 直接導入法：Access數據庫中的表與數據源脫離了聯系，對數據的更改不會影響源文本數據文件。

★ 建立鏈接法：鏈接表顯示源文本文件中的數據，但是它實際上并不將數據存儲在數據庫中，對源文本文件進行的任何更改都將自動反映到鏈接表中，即數據會隨數據源的變化而自動更新。

你可以根據實際需求，選擇“直接導入法”或“建立鏈接法”導入文本數據。

Mr.林：如果數據是以Excel格式存儲的，將Excel數據導入Access數據庫的步驟基本與TXT文本數據導入步驟類似，同樣你也可事后自行練習數據導入方法。

小白：好的。

1.1.4 數據合并的二三式

Mr.林：小白，接下來我們就要開始學習用Access數據庫處理數據啦！再考一下你，什么是數據處理？數據處理主要包含哪些操作？

小白：這個難不倒我。數據處理就是根據數據分析的目的，將采集到的數據，用適當的處理方法整理和加工，形成適合數據分析要求的樣式，也就是一維表。它是數據分析前必不可少的階段。數據處理包括數據合并、數據計算、數據分組、數據去重等操作。

Mr.林：說的沒錯，我們先來學習數據合并。數據合并包括橫向合并與縱向合并。

橫向合并

Mr.林：橫向合并就是從多個表中，根據各表共有的關鍵字段，把各表所需的記錄一一對應起來。這個功能也相當于Excel中的VLOOKUP精確匹配功能。

例如剛才導入的“訂購明細”表，它只記錄了用戶訂購單的相應信息，但缺乏用戶的相關背景信息，如果要統計不同性別的用戶的產品購買偏好，就必須獲得用戶的性別信息。這時就需要將“訂購明細”表與“用戶明細”表根據關鍵字段“用戶ID”進行關聯匹配查詢，如圖1-15所示。

圖1-15 “訂購明細”表與“用戶明細”表關系圖

現在就看看在Access數據庫中如何實現數據橫向合并。

小白：接下來要先做什么呢？怎么做？

Mr.林：主要有兩種方式，一種是菜單操作法，另一種就是SQL查詢法。我們先學習菜單操作法。

（1）菜單操作法

首先，建立“訂購明細”與“用戶明細”兩表的數據庫關系。

STEP 01 單擊【數據庫工具】選項卡，在【關系】組中單擊【關系】按鈕。

STEP 02 在彈出的【顯示表】對話框中，同時選中“訂購明細”與“用戶明細”兩表（可結合Shift鍵同時選中，也可結合Ctrl鍵依次選中），如圖1-16所示。單擊【添加】按鈕，再單擊【關閉】按鈕，以關閉【顯示表】對話框。

圖1-16 【顯示表】對話框

STEP 03 在【關系管理器】中（如圖1-17所示），將“訂購明細”表中的“用戶ID”字段用鼠標拖到“用戶明細”表中的“用戶ID”字段處，松開鼠標。

圖1-17 關系管理器

STEP 04 在彈出【編輯關系】對話框中，單擊【聯接類型】按鈕，默認選擇第一種關系【只包含兩個表中聯接字段相等的行】，單擊【確定】按鈕，返回【編輯關系】對話框，如圖1-18所示。

圖1-18 【編輯關系】與【聯接屬性】對話框

小白，這里需要注意：在本例中，我們確定“訂購明細”表中的“用戶ID”記錄信息都在“用戶明細”表中，所以可以用第一種關系；反之，則需要選擇第三種關系，以確保“訂購明細”表信息的完整性。

STEP 05 單擊【創建】按鈕，返回【關系管理器】，可以看到，在【關系】窗口中兩個表的“用戶ID”字段之間出現了一條關系連接線，如圖1-19所示。

圖1-19 兩表關系連接示例

STEP 06 單擊【保存】按鈕，并關閉【關系管理器】。

Mr.林：小白，這樣這兩張表的關系就建立好了。接下來就要執行查詢步驟，從“訂購明細”表中選取“產品”字段，從“用戶明細”表中選取“性別”字段，就能取出我們需要的不同用戶性別與所購買產品一一對應的明細數據，以方便統計不同性別的用戶的產品購買偏好。

可利用Access數據庫中“簡單查詢”功能來實現，具體查詢操作步驟如下。

STEP 01 單擊【創建】選項卡，在【查詢】組中單擊【查詢向導】按鈕。

STEP 02 在彈出的【新建查詢】對話框中，默認選擇【簡單查詢向導】，如圖1-20所示，單擊【確定】按鈕。

圖1-20 【新建查詢】對話框

STEP 03 在彈出的【簡單查詢向導】第一個對話框【表/查詢】項中，選擇“訂購明細”表，并把“用戶ID”、“產品”兩個字段移至【選定字段】框中，如圖1-21所示。

圖1-21 【簡單查詢向導】對話框1

STEP 04 在【簡單查詢向導】第二個對話框【表/查詢】項中，選擇“用戶明細”表，并把“性別”字段移至【選定字段】框中，如圖1-22所示，單擊【完成】按鈕，在彈出的對話框中，單擊【保存】按鈕。

圖1-22 【簡單查詢向導】對話框2

Mr.林：“訂購明細”和“用戶明細”兩表聯合查詢的結果，如圖1-23所示。

圖1-23 簡單查詢結果示例

還沒等Mr.林說完，小白就搶著說：哇！結果出來啦，確實比Excel方便！如果用VLOOKUP匹配這么大量的數據，至少也要好幾分鐘，要是匹配的字段較多，速度還會更慢。

Mr.林：沒錯，這就是數據庫具備的Excel所不具備的優勢。在Access數據庫左側對象欄中可以發現比查詢前新增了一個查詢對象“訂購明細 查詢”，這就是我們剛才新建的查詢。只要雙擊它，Access數據庫就會按指定的條件重新執行查詢，如果數據量非常大，雙擊需謹慎！

小白笑嘻嘻地說：好的。

Mr.林：另外還有一種菜單操作法，如果你對Access查詢功能及原理比較熟悉，還可以用“查詢設計”新建查詢，相比“查詢向導”會快捷一點，當然這要看個人習慣與偏好，仁者見仁，智者見智。后面我會通過其他例子介紹“查詢設計”功能的使用方法。

小白：好的。

（2）SQL查詢法

Mr.林：現在我們就來學習SQL查詢法。小白，記得我之前提到Access數據庫中進行查詢處理可直接生成相應的SQL語句，不需要我們親自編寫SQL語句嗎？

小白：我記得呀！我也好奇到底Access數據庫是怎么生成相應SQL語句的。

Mr.林故弄玄虛：呵呵！見證奇跡的時刻到了。在剛才的Access數據庫查詢結果窗口中（如圖1-23所示），單擊Access數據庫窗口最右下方的按鈕，效果如圖1-24所示。

圖1-24 SQL查詢視圖數據橫向合并示例

Mr.林：小白，你看SQL視圖窗口里不就是寫好的SQL語句嘛！

小白瞪大眼睛：咦！果然，很神奇。

Mr.林：Access數據庫可直接生成相應的SQL語句，我說的沒錯吧？讓我們一起來看看生成的SQL語句與剛才的查詢操作是否能對應上。

這一條SQL語句的意思是：選擇查詢“訂購明細”表的“用戶ID”、“產品”字段，以及相對應的“用戶明細”表中的“性別”字段，從“用戶明細”表內連接“訂購明細”表選擇，條件是“用戶明細”表的“用戶ID”字段與“訂購明細”表的“用戶ID”字段相等。

沒錯，對上了，Access數據庫生成的SQL語句就是我們剛才聯合查詢的操作。

只需單擊【設計】選項卡中【結果】組的【運行】按鈕，如圖1-24所示，Access數據庫就直接執行查詢操作。

小白：那么，內連接是什么意思呢？

Mr.林：這需要先了解下數據庫連接關系，主要包含內連接（INNER JOIN）、左連接（LEFT JOIN）、右連接（RIGHT JOIN）三種數據庫關系，使用時請注意區分（如圖1-25所示）。

圖1-25 三種數據庫連接關系圖

★ 內連接（INNER JOIN）：選擇兩個表中關鍵字段相匹配的記錄，對應圖1-18“聯接屬性”對話框中的第一個關系。

★ 左連接（LEFT JOIN）：選擇第一個表中的所有記錄以及第二個表中與關鍵字段相匹配的記錄，對應圖1-18“聯接屬性”對話框中的第二個關系。

★ 右連接（RIGHT JOIN）：選擇第二個表中的所有記錄以及第一個表中與關鍵字段相匹配的記錄，對應圖1-18“聯接屬性”對話框中的第三個關系。

小白：明白了。

Mr.林：這條SQL語句我們還可以進一步簡化為：

SELECT B.用戶ID, B.產品, A.性別
FROM用戶明細AS A, 訂購明細AS B
WHERE A.用戶ID = B.用戶ID;

① 這條SQL語句的條件采用WHERE子句進行編寫，相對內連接（INNER JOIN）語法更容易理解。WHERE子句由一個運算符 (WHERE) 及后面的條件表達式（A.用戶ID = B.用戶ID) 組成。Access數據庫會選擇出符合WHERE子句所列條件的記錄，如果沒有指定WHERE子句，查詢會返回表中的所有行。條件表達式的書寫規則如圖1-26所示。

圖1-26 條件表達式的書寫規則

② 這條SQL語句對“用戶明細”表和“訂購明細”表的表名分別重新命名為A、B，用關鍵字AS來命名；在編寫SQL語句時，關鍵字AS可省略，直接在原表名后加上別名，中間以空格分隔。

③ 如果需要從不同表引用字段，先寫上表名（或別名），再加上“點”（.），再加上相應字段，以區分不同表的相同的字段名，防止出錯，提高查詢效率，特別是在各表有相同字段的情況下，例如：B.用戶ID。

④ 對字段名同樣可以重新命名，其方法與為表命名別名的方式一致。采用別名的好處在于可簡化表名，使SQL語句清晰易懂、易編寫。

小白：明白，這些注意事項我都記下了。

縱向合并

Mr.林：現在我們來學習如何進行數據的縱向合并，也就是數據記錄的合并。合并的表必須具有相同的字段結構，它們字段的數目必須相同，并且字段的數據類型也必須相同。

假設剛才的“訂購明細”表是以每天一個表的方式存儲的，即每天的數據保存為一個表，如“訂購明細20110901”、“訂購明細20110902”、“訂購明細20110903”、“訂購明細20110904”等，現在需要把它們合并到一張表中。

（1）菜單操作法

我們可采用Access數據庫中“追加查詢”功能來實現。先看看如何把“訂購明細20110902”表追加到“訂購明細20110901”中，具體查詢操作步驟如下。

STEP 01 單擊【創建】選項卡，在【查詢】組中單擊【查詢設計】按鈕。

STEP 02 在彈出的【設計視圖】和【顯示表】對話框中，選擇“訂購明細20110902”表，單擊【添加】按鈕將表添加進查詢的【設計視圖】，如圖1-27所示。

圖1-27 查詢設計視圖——【顯示表】對話框

STEP 03 單擊【設計】選項卡【查詢類型】組的【追加】按鈕，彈出【追加】對話框，在【表名稱】下拉列表框中選擇“訂購明細20110901”作為目標表，如圖1-28所示。

圖1-28 追加查詢設計視圖—【追加】對話框

STEP 04 單擊【確定】按鈕，返回查詢的【設計視圖】，依次雙擊選擇“訂購明細20110901”表中所有字段，被選擇的字段將在下面的查詢設計網格中顯示，如圖1-29所示。

圖1-29 追加查詢設計視圖—字段選擇

STEP 05 單擊【設計】選項卡中【結果】組的【運行】按鈕（如圖1-30所示），Access數據庫將彈出如圖1-31所示的提示框，單擊【是】按鈕，Access數據庫直接執行追加查詢操作。

圖1-30 【設計】選項卡

圖1-31 追加查詢操作提示框

雙擊“訂購明細20110901”表可以查看追加查詢的結果。重復上述步驟，將“訂購明細20110903”、“訂購明細20110904”表中記錄，通過“追加查詢”功能追加至“訂購明細20110901”表中，以完成數據縱向合并的目的。

如果不希望把各表數據記錄都追加至“訂購明細20110901”表中，而希望追加至表名為“訂購明細201109”的空白數據表，可新建一張數據結構與“訂購明細20110901”表一致，并且表名為“訂購明細201109”的空白數據表，通過上述的“追加查詢”功能，將“訂購明細20110901”等表追加至“訂購明細201109”表中。

小白：原來如此。可是我有個問題，如果需要合并的表較少，通過菜單操作還可接受，如果需要合并的表較多時，這樣操作就比較麻煩了，該怎么辦？

（2）SQL查詢法

Mr.林：小白，你這個問題很不錯，當需要合并的表較多時，這樣操作確實效率低下，我們可以轉變下思路，想一想能否采用SQL語句呢？

小白：對啊！我們可以從追加查詢的SQL視圖中取出相應的SQL語句，只要更改要追加數據的表名，分別運行，就比剛才重復菜單操作來得便捷。Mr.林，是這樣的嗎？

Mr.林：小白，不錯啊！剛學的就馬上用上了，你說的是一種思路，不過這樣也要分開運行多次，有幾個表就要運行幾次，還是有點慢。我教你一個只要運行一次的妙招。

小白張大嘴巴：還有這樣的妙招？Mr.林，快快教我。

Mr.林：就是用UNION ALL或UNION指令進行兩表或多表合并，但是所有查詢中的列數和列的順序必須相同，數據類型必須兼容。

小白疑惑不解地問：這兩個指令有何不同呢？什么情況下該用UNION ALL？什么情況下該用UNION？

Mr.林：UNION ALL就是將各表的記錄合并到一起，并且對這些記錄不做任何更改。而UNION會刪除各表存在的重復記錄，并對記錄進行自動排序，這樣UNION比UNION ALL需要更多的計算資源，所以盡可能使用UNION ALL指令進行各表合并。

一般需要使用UNION ALL指令的情況如下：

★ 知道有重復記錄且想保留這些記錄。

★ 知道不可能存在任何的重復記錄。

★ 不在乎是否存在任何的重復記錄。

現在我們就來看看如何使用UNION ALL來合并9月1日—9月4日的4張當日訂購明細表，合并至“訂購明細201109”的空白數據表，因為我們知道這4張當日訂購明細表之間不可能存在重復記錄，所以這里使用UNION ALL指令，具體步驟如下。

STEP 01 新建表名為“訂購明細201109”的空白數據表，要求其表結構、各字段數據類型與“訂購明細20110901”表一致，相應的SQL語句如下：

SELECT*INTO訂購明細201109
FROM訂購明細20110901
WHERE 1=2;

小白疑惑不解地問：為什么條件是“1=2”呢？

Mr.林：我們的目的是得到“訂購明細20110901”表的結構，而不需要表里面的記錄，因而需要設置一個不可能存在的條件，因為1是不可能等于2的，所以在建立的“訂購明細201109”表里將插入0條記錄，這樣就巧妙地新建一張表結構、各字段數據類型與“訂購明細20110901”表一致的空白數據表“訂購明細201109”。

STEP 02 在剛才的Access數據庫追加查詢結果窗口中，單擊Access數據庫窗口最右下方的按鈕，得到如下SQL語句：

INSERT INTO訂購明細20110901(訂單編號,訂購日期,用戶ID,產品,[單價(元)],數量,訂購金額)
SELECT訂單編號,訂購日期,用戶ID,產品,[單價(元)],數量,訂購金額
FROM訂購明細20110902;

STEP 03 修改、添加相應的SQL語句，結果如下（紅色加粗部分為修改、添加之處）：

INSERT INTO訂購明細201109(訂單編號,訂購日期,用戶ID,產品,[單價(元)],數量,訂購金額)
SELECT訂單編號,訂購日期,用戶ID,產品,[單價(元)],數量,訂購金額
FROM
(SELECT A.訂單編號,A.訂購日期,A.用戶ID,A.產品,A.[單價(元)],A.數量,A.訂購金額
FROM訂購明細20110901 AS A
UNION ALL
SELECT B.訂單編號,B.訂購日期,B.用戶ID,B.產品,B.[單價(元)],B.數量,B.訂購金額
FROM訂購明細20110902 AS B
UNION ALL
SELECT C.訂單編號,C.訂購日期,C.用戶ID,C.產品,C.[單價(元)],C.數量,C.訂購金額
FROM訂購明細20110903 AS C
UNION ALL
SELECT D.訂單編號,D.訂購日期,D.用戶ID,D.產品,D.[單價(元)],D.數量,D.訂購金額
FROM訂購明細20110904 AS D);

Mr.林看到小白疑惑不解的樣子，就繼續解釋：下面圓括號里的語句是子查詢語句，子查詢的結果將生成一張數據表，它將作為插入語句（INSERT INTO）的源表，這也叫嵌套查詢，原理就與IF函數的嵌套原理基本類似。

小白豁然開朗：原來是這樣。

Mr.林：小白，你有沒有發現，因為各個表的表結構、數據類型都是一樣的，所以對于這段SQL語句我們還可以簡化：

INSERT INTO訂購明細201109
SELECT * FROM
(SELECT * FROM訂購明細20110901
UNION ALL
SELECT * FROM訂購明細20110902
UNION ALL
SELECT * FROM訂購明細20110903
UNION ALL
SELECT * FROM訂購明細20110904);

小白：果然簡化了很多。這么說，當多張表的表結構、數據類型一樣時，可以用“SELECT *”的方式查詢所有字段與記錄；當多張表的表結構、數據類型不一樣時，就要單獨提出需要的字段，有時候甚至還要進行字段類型的轉換，將各表對應的各個字段類型統一，是這樣的嗎？

Mr.林：沒錯，孺子可教也！

接下來我們就要將修改好的SQL語句，復制至SQL視圖窗口運行。

STEP 04 單擊【創建】選項卡，在【查詢】組中單擊【查詢設計】按鈕，關閉彈出查詢的【顯示表】對話框，單擊Access數據庫窗口右下方的按鈕，進入SQL視圖窗口，將修改好的SQL語句復制至SQL視圖窗口，如圖1-32所示。

圖1-32 SQL查詢視圖數據縱向合并示例

STEP 05 單擊【設計】選項卡中【結果】組的【運行】按鈕，如圖1-32所示，Access數據庫將彈出類似如圖1-31所示的提示框，單擊【是】按鈕，Access數據庫直接執行追加查詢操作。

小白：Mr.林，我有個問題，剛才建空白數據表“訂購明細201109”的時候，我們只用了“訂購明細20110901”的表結構，那為什么不把9月1日—9月4日4張當日訂購明細表的記錄也一起加進去呢？

Mr.林：小白，不錯，有想法。剛才我介紹的是追加查詢的方法，你的提議是直接查詢并把數據添加至新表中，這是可行的。這樣好了，你來寫相應的SQL語句。

小白不客氣地說：那我就班門弄斧啦！我這就寫來：

SELECT*INTO訂購明細201109
FROM
(SELECT*FROM訂購明細20110901
UNION ALL
SELECT*FROM訂購明細20110902
UNION ALL
SELECT*FROM訂購明細20110903
UNION ALL
SELECT*FROM訂購明細20110904);

小白寫完問道：Mr.林，您看，是這樣的么？

Mr.林：咱們運行一下，不就知道結果了嘛！

小白：好的，那我單擊“運行”啦！哇，果真！這樣比較方便快捷，一步到位！

1.1.5 快速實現數據計算

Mr.林：小白，接下來學習在Access數據庫中進行數據計算，這里的數據計算是指對原有的字段進行相應的計算得到新的字段，以滿足我們的分析需求。你還記得數據計算有哪幾種方式么？

小白：當然記得，工作中也常用到嘛！數據計算包括簡單計算與函數計算。

★ 簡單計算就是加、減、乘、除等簡單算術運算。

★ 函數計算就是通過軟件內置的函數進行計算，比如求和，求平均值、最大值、最小值等。

Mr.林：還不錯，都沒忘，那我們就先來學習Access數據庫中的簡單計算。

簡單計算

Mr.林：以“訂購明細”表為例，這個表里面有每張訂單的詳細信息，如訂購的“產品”、“單價”、“數量”、“訂購金額”，這里的“訂購金額”就是通過“單價”x“數量”計算而來。現在假設沒有這個“訂購金額”字段，而需要通過簡單計算方式來新增“訂購金額”字段。

（1）菜單操作法

STEP 01 單擊【創建】選項卡，在【查詢】組中單擊【查詢設計】按鈕。

STEP 02 在彈出查詢的【設計視圖】和【顯示表】對話框中，選擇“訂購明細”表，單擊【添加】按鈕將表添加進查詢的【設計視圖】。

STEP 03 依次雙擊選擇“訂購明細”表中所有字段，被選擇的字段會在下面的查詢設計網格中顯示，如圖1-33所示。

圖1-33 查詢設計視圖—字段選擇

STEP 04 在第8個字段表中輸入“訂單金額： [單價(元)]*[數量]”，表示“訂單金額=單價(元)×數量”，如圖1-33所示。

STEP 05 單擊【設計】選項卡中【結果】組的【運行】按鈕，運行結果如圖1-34所示。

圖1-34 簡單計算的運行結果

Mr.林：小白，你看，我們計算出來的“訂單金額”與原表的“訂購金額”數據一致。

小白：沒錯，完全相等。

（2）SQL查詢法

Mr.林：我們再來看SQL查詢法。同理，在剛才的Access數據庫查詢結果窗口中，單擊Access數據庫窗口右下方的按鈕，得到如下簡化的SQL語句：

SELECT訂單編號,訂購日期,用戶ID,產品,[單價(元)],數量,訂購金額,[單價(元)]*[數量]AS訂單金額
FROM訂購明細;

可以看出，在SQL語句中進行簡單計算的方式，就是直接寫出運算表達式，然后對新增的字段采用AS命令命名即可。

同樣，我們只需單擊【設計】選項卡中【結果】組的【運行】按鈕，Access數據庫直接按照編寫好的SQL語句執行相應的查詢操作。

小白：確實簡單、方便。

函數計算

Mr.林：我們繼續來學習Access數據庫中的函數計算。

假如，現在我們需要了解截至2011年年底用戶注冊天數的分布，以了解現有存量用戶的構成情況，為后續用戶細分做準備。數據庫中現有的“用戶明細”表只有用戶注冊日期，我們需要通過相應的日期函數計算得到用戶注冊的天數。

我們可用DATEDIFF函數，它的作用與Excel中的DATEDIF函數一致，但用法略有不同，不同之處在于日期間隔參數移至表達式前部，其參數設置及說明詳見圖1-35，其語法如下：

圖1-35 DAtEDlff函數參數設置及說明表

DATEDIFF（"參數",起始日期,結束日期）

再次提醒：在函數參數或條件查詢中，若參數或查詢條件為日期和時間類型，需要在數據值兩端加上井字符號（#），以表示數據類型為日期型。

我們可以在如圖1-33所示的查詢設計網格字段中輸入函數進行計算，也可以直接在SQL語句中進行計算。在此以SQL語句應用為例，編寫的SQL語句如下：

SELECT用戶ID,注冊日期,DATEDIFF("D",注冊日期,#2011-12-31#) AS注冊天數
FROM用戶明細;

字段可根據實際需求選擇，盡量減少不必要的字段，字段越少越好，可大大提升系統運行效率。

將編寫好的SQL語句直接復制至一個新建的查詢SQL視圖窗口中（操作步驟可參見前文），單擊【運行】按鈕，得到如圖1-36所示的結果。

圖1-36 DAtEDlff函數計算結果示例

其他Access數據庫常用函數如圖1-37所示，可根據實際計算需求采用。

圖1-37 Access數據庫常用函數

小白：好的，我先記下，留著備用。

1.1.6 數據分組小妙招

Mr.林：小白，還記得數據分析三大基本方法么？

小白：當然記得：對比、細分與預測。

Mr.林：是的，現在我們就要學習其中的細分方法，也就是數據分組。

進行數據分析時不僅要對總體的數量特征和數量關系進行分析，還要深入總體的內部進行分組分析。數據分組是一種重要的數據分析方法，這種方法根據數據分析對象的特征，按照一定的標志（指標），如業務、用戶屬性、時間等維度，把數據分析對象劃分為不同的部分和類型來進行研究，以揭示其內在的聯系和規律性。

常用的數據分組方式主要包括數值分組、日期/時間分組兩種。

數值分組

（1）IIF函數法

Mr.林：小白，還記得Excel中的IF函數吧？

小白：當然，這個函數可好用了，IF與VLOOKUP函數相當于萬金油，是工作中用到最多的兩個函數。

Mr.林：現在我們要學的第一個用于數值分組的函數是IIF，它與Excel中的IF函數用法及功能一樣。在Access數據庫中，IIF函數最多可進行13層嵌套，如果嵌套超過13層，運行時Access數據庫將提示“表達式過于復雜”。

IIF函數語法如下：

IIF (表達式，表達式成立時返回的值，表達式不成立時返回的值)

仍以“用戶明細”表為例。表中有個“年齡”字段，我們需要了解用戶年齡結構，這時就需要對用戶年齡進行分組。下面就采用IIF函數進行分組，編寫的SQL語句如下：

SELECT用戶ID,年齡,
IIF(年齡<=20,"20歲及其以下",
IIF(年齡<=30,"21-30歲",
IIF(年齡<=40,"31-40歲",
"40歲以上"))) AS年齡分組
FROM用戶明細;

將編寫好的SQL語句直接復制至一個新建的查詢SQL視圖窗口中，單擊【運行】按鈕，得到如圖1-38所示的結果。

小白：果然，IIF函數的用法及效果與Excel中的IF函數一樣。

圖1-38 llf函數分組結果示例

（2）CHOOSE函數法

Mr.林：第二個用于數值分組的函數CHOOSE，它與Excel中的CHOOSE函數用法及功能一樣。CHOOSE函數語法如下：

CHOOSE (參數，結果1，結果2，……，結果N)

說明：

① 參數可為數值或表達式，如果參數或表達式返回的值為1，則函數CHOOSE返回結果1；如果參數或表達式返回的值為2，函數CHOOSE返回結果2，以此類推。

② 參數或表達式返回的值必須為1～254的數字，如果小于1或者大于254，則Access數據庫將返回錯誤值“#VALUE!”。

③ 如果參數為小數，則在使用前將被截尾取整，即相當于Excel中的lNt函數效果。

仍以“用戶明細”表為例，對用戶年齡進行分組，下面就采用CHOOSE函數進行分組，編寫的SQL語句如下：

SELECT用戶ID,年齡,
CHOOSE((年齡-1)/10+1,"10歲及其以下","11-20歲","21-30歲","31-40歲","40歲以上") AS年齡分組
FROM用戶明細;

將編寫好的SQL語句直接復制至一個新建的查詢SQL視圖窗口中，單擊【運行】按鈕，得到如圖1-39所示的結果。

圖1-39 CHOOSE函數分組結果示例

小白：Mr.林，請教一個問題，為何表達式為“(年齡-1)/10+1”？

Mr.林：我們可以分步來看。首先假設年齡范圍是1～10歲，那么“(年齡-1)/10”返回的值就落入[0,1)區間，而“(年齡-1)/10+1”返回的值就落入[1,2)區間，根據剛才介紹的函數說明的第3點“如果參數為小數，則在使用前將被截尾取整”原則，那么參數最終返回值為1，也就對應第1個結果“10歲及其以下”，以此類推，就可把用戶年齡劃分為不同的范圍，從而保證各個用戶年齡都能落入正確的區間。

小白：原來如此，我明白了。

（3）SWITCH函數法

Mr.林：第三個用于數值分組的函數是SWITCH。SWITCH函數語法如下：

SWITCH (條件1，結果1，條件2，結果2，……，條件N，結果N)

說明：

① 如果條件1為true，SWltCH將返回結果1，如果條件2為true，SWltCH將返回結果2，以此類推。

② 參數由成對的條件表達式和結果值組成，條件表達式按照從左到右的順序求值，將返回與第一個求值結果為true的表達式相對應的結果值。

③ SWltCH函數在SQL語句中的條件表達式最多可以達到14個，如果多于14個表達式，將提示錯誤。

④ 如果所有表達式的結果值都不為true，SWltCH將返回Null。

我們仍以“用戶明細”表為例，對用戶年齡進行分組。下面就采用SWITCH函數進行分組，編寫的SQL語句如下：

SELECT用戶ID,年齡,
SWITCH(年齡<=20,"20歲及其以下",
   年齡<=30,"21-30歲",
   年齡<=40,"31-40歲",
   年齡>40,"40歲以上") AS年齡分組
FROM用戶明細;

將編寫好的SQL語句直接復制至一個新建的查詢SQL視圖窗口中，單擊【運行】按鈕，得到的結果與IIF函數分組結果一樣，如圖1-38所示。

小白：果然，SWITCH函數分組與IIF函數分組效果一樣，但SWITCH函數會讓人感覺分組更清晰些。

（4）PARTITION函數法

Mr.林：第四個用于數值分組的函數是PARTITION。PARTITION函數語法如下：

PARTITION (數值參數，開始值，結束值，組距)

說明：

① 數值參數為要根據范圍進行計算的整數。

② 開始值必須為整數，并且不能小于0。

③ 結束值也必須為整數，該數值不能等于或小于開始值。

④ 組距也必須為整數，指定在整個數值范圍內（在開始值與結束值之間）的分區大小。

⑤ PARtltlON返回的內容為每組的“下限：上限”。

我們仍以“用戶明細”表為例，對用戶年齡進行分組。下面就采用PARTITION函數進行分組，開始值設置為1，結束值設置為100，組距設置為20，編寫的SQL語句如下：

SELECT用戶ID,年齡,
PARTITION(年齡,1,100,20) AS年齡分組
FROM用戶明細;

將編寫好的SQL語句直接復制至一個新建的查詢SQL視圖窗口中，單擊【運行】按鈕，得到的結果如圖1-40所示。

圖1-40 PARtltlON函數分組結果示例

小白：哇，用PARTITION函數分組更加簡單、清晰、明了。

（5）四個分組函數的比較

小白：Mr.林，還有個問題：這四個數值分組函數，分別在什么情況下使用呢？

Mr.林：好的。我就來總結下這四個數值分組函數的優缺點，如圖1-41所示，這樣你可以根據實際情況選擇相應的函數進行數據分組。

圖1-41 四個分組函數的優缺點比較

如果要進行數值等距分組，可考慮使用PARTITION或CHOOSE函數；如果要進行數值不等距分組，可考慮使用SWITCH或IIF函數。

小白：經過您這么歸納、總結和對比，四個數值分組函數的優缺點很清晰直觀，我知道該如何選擇使用了。

日期/時間分組

Mr.林：介紹完數值分組，接下來學習對日期/時間的分組，這一類分組也是我們數據處理與分析工作中常用到的。

在Access數據庫中，除了可以采用YEAR、MONTH、DAY等常用日期函數（如圖1-37所示）進行日期分組外，我們還可以采用FORMAT函數進行日期/時間分組。FORMAT函數可對文本、數值、日期/時間等類型數據按指定要求進行格式化，這里我們主要介紹FORMAT函數的日期/時間格式化功能。FORMAT函數的語法如下：

FORMAT（日期/時間，日期/時間格式參數）

FORMAT函數中日期/時間相關的格式參數說明如圖1-42所示。

圖1-42 fORMAt函數日期/時間參數說明表

我們以“訂購明細”表為例，對用戶訂購日期依次按年、季、月、日、星期、小時、分、秒等8個日期/時間單位進行格式化分組。下面就采用FORMAT函數進行分組，編寫的SQL語句如下：

SELECT訂單編號,訂購日期,
FORMAT(訂購日期,"yyyy") AS年,
FORMAT(訂購日期,"q") AS季,
FORMAT(訂購日期,"m") AS月,
FORMAT(訂購日期,"d") AS日,
FORMAT(訂購日期,"dddd") AS星期,
FORMAT(訂購日期,"h") AS小時,
FORMAT(訂購日期,"n") AS分,
FORMAT(訂購日期,"s") AS秒
FROM訂購明細;

將編寫好的SQL語句直接復制至一個新建的查詢SQL視圖窗口中，單擊【運行】按鈕，得到的結果如圖1-43所示。

圖1-43 fORMAt函數日期/時間分組結果示例

小白：FORMAT函數真方便，我想要什么樣的日期/時間分組，就有什么樣的日期/時間分組。

1.1.7 重復數據巧處理

Mr.林：小白，還記得在Excel中處理重復數據的幾種方式嗎？

小白：當然記得，有函數、條件格式標識、高級篩選、數據透視表等方法。

Mr.林：沒錯，接下來學習Access數據庫中處理重復數據的方法，主要包含重復項查詢、不重復項查詢以及數據去重查詢，同樣也可以通過菜單操作、SQL語句兩種方式完成。

重復項查詢

（1）菜單操作法

Mr.林：小白，之前學習新建簡單查詢時，對話框里面就有一項“查找重復項查詢向導”功能（如圖1-20所示），下面就要用它來查找數據重復項。我們以查找“訂購明細”表中重復的“用戶ID”為例，具體查詢操作如下。

STEP 01 單擊【創建】選項卡，在【查詢】組中單擊【查詢向導】按鈕。

STEP 02 在彈出的【新建查詢】對話框中，選擇【查找重復項查詢向導】，如圖1-20所示，單擊【確定】按鈕。

STEP 03 在彈出的【查找重復項查詢向導】第一個對話框中，選擇【表】視圖，并在列表框中選擇“訂購明細”表作為查詢對象，單擊【下一步】按鈕，如圖1-44所示。

圖1-44 【查找重復項查詢向導】對話框1

STEP 04 在【查找重復項查詢向導】第二個對話框【可用字段】列表框中，選擇“用戶ID”作為要進行查找重復項查詢的字段，單擊【完成】按鈕，如圖1-45所示。

圖1-45 【查找重復項查詢向導】對話框2

Mr.林：查找“訂購明細”表中“用戶ID”重復的結果，如圖1-46所示。從重復項查詢結果中可獲知兩個信息：重復訂購的用戶ID，每個重復訂購用戶所重復訂購的次數。

圖1-46 重復項查詢結果示例

小白驚訝地說道：哇！比Excel方便很多！如果用Excel數據透視表進行重復用戶統計，還需要把統計結果復制出來，再篩選出訂購次數大于或等于2次的結果。

Mr.林：沒錯，Access數據庫還能處理比Excel大得多的數據，而且速度一點也不慢。

（2）SQL查詢法

小白：Mr.林，快單擊Access數據庫窗口右下方的按鈕，我想看看這個查找重復項算法是怎樣的，SQL語句是怎樣寫的。

Mr.林：好的。單擊Access數據庫窗口右下方的按鈕，其簡化的SQL語句如下：

SELECT First(用戶ID),Count(用戶ID) AS NumberOfDups
FROM訂購明細
GROUP BY用戶ID
HAVING Count(用戶ID)>1;

將編寫好的SQL語句直接復制至一個新建的查詢SQL視圖窗口中，單擊【運行】按鈕，同樣可以得到如圖1-46所示的結果。

小白：咦！多了幾條陌生的語句，First、Count、GROUP BY、HAVING分別代表什么意思？Count，我知道，在Excel中是計數的意思，它在Access數據庫中也是計數的意思吧！

Mr.林：沒錯，Count就是計數的意思。

First，很簡單，就是第一，在這里的意思就是第一條記錄。有第一就有最后，其函數為Last。如果有用戶重復訂購，那么我們也可不使用First函數，直接用字段表示即可，“用戶ID”取哪個值都是一樣的。

GROUP BY子句就是實現對數據按指定的分組字段進行分組，本例中按用戶進行分組，這與數據透視表分組統計的道理是一致的。

HAVING子句在SELECT語句中與GROUP BY子句聯合使用，用于表示GROUP BY子句輸出結果的條件，其作用相當于WHERE子句之于SELECT語句。即WHERE子句設定被選擇字段的條件，而HAVING子句設置GROUP BY子句形成的分組條件。

另外它們都需要使用關系比較運算符：“=”、“<”、“>”、“<=”、“>=”或“<>”。

在本例中，“HAVING Count(用戶ID)>1”的意思就是對用戶ID出現2次及以上的數據進行分組。

小白：明白了。

不重復項查詢

Mr.林：找出了重復項，那么不重復項如何找呢，小白？

小白：我想想……有了，既然重復項是用戶訂購次數大于或等于2次的結果，那么不重復項不就是用戶訂購次數等于1的結果么？也就是說，我們只需在剛才查找重復項的SQL語句中，把“HAVING Count(用戶ID)>1”更改為“HAVING Count(用戶ID)=1”即可，是這樣的么，Mr.林？

Mr.林：真聰明！非常正確，加10分，查找不重復項的SQL語句如下：

SELECT用戶ID,Count(用戶ID) AS NumberOfDups
FROM訂購明細
GROUP BY用戶ID
HAVING Count(用戶ID)=1;

將編寫好的SQL語句直接復制至一個新建的查詢SQL視圖窗口中，單擊【運行】按鈕，同樣可以得到如圖1-47所示的結果。

圖1-47 不重復項查詢結果示例

小白：學會SQL語句確實很方便，數據處理起來杠杠的，一條SQL語句就搞定了。

數據去重查詢

Mr.林：小白，如果我要進行數據去重——就是找出所有購買行為的“用戶ID”，但只保留其中一條，在Excel 2007-2010版本有“刪除重復項”功能，那么在Access數據庫中如何處理呢？

（1）GROUP BY子句

小白：非常簡單啊！剛才對重復項和不重復項的查找都是在HAVING子句設置GROUP BY子句形成的分組條件下進行的，如果要去重，就不需要設置條件，顯示出所有唯一的“用戶ID”，直接用GROUP BY“用戶ID”字段即可，這個同樣與數據透視表分組統計的道理是一致的。

Mr.林：非常正確，再加10分，數據去重的SQL語句如下：

SELECT用戶ID
FROM訂購明細
GROUP BY用戶ID;

將編寫好的SQL語句直接復制至一個新建的查詢SQL視圖窗口中，單擊【運行】按鈕，可以得到如圖1-48所示的結果。

圖1-48 數據去重查詢結果示例

（2）DISTINCT

Mr.林：在Access數據庫中，我們還可以使用DISTINCT關鍵字，它的作用就是忽略所選字段中包含重復數據的記錄，簡單來說，就是數據去重。對于剛才的例子：找出所有有購買行為的“用戶ID”，但只保留其中一條，其SQL語句如下：

SELECT DISTINCT用戶ID
FROM訂購明細;

將編寫好的SQL語句直接復制至一個新建的查詢SQL視圖窗口中，單擊【運行】按鈕，同樣可以得到如圖1-48所示的結果。

需要注意：如果SELECT子句中包含多個字段，則對于結果中包含的特定記錄，其所有字段的值組合必須是唯一的。

小白：好的。

1.1.8 數據分析一步到位

Mr.林：前面我們學習了Access數據庫中的數據合并、數據計算、數據分組、重復數據處理四大常用的數據處理方式。

而數據處理的目的就是將采集到的數據，用適當的處理方法整理加工，形成適合數據分析要求的樣式，也就是一維表，為數據分析做好準備工作。

小白：我知道您的意思了，接下來是要進行數據分析了吧？

Mr.林：沒錯。我們日常工作中所做的數據分析，主要指通過對比與細分進行現狀分析及原因分析，通過數據分組了解其數據構成，甚至通過不同時間維度的對比，查找數據變化的原因，最后通過制作相關圖表等對現狀進行呈現及描述。

接下來學習在Access數據庫中進行數據分析的方法，主要包含簡單統計、分組統計、交叉表統計三種方法，我主要介紹SQL語句實現方式。

小白：Mr.林，打斷一下，我發現Access數據庫中有數據透視表功能，為什么不用數據透視表進行數據分析呢？

Mr.林：這個問題問得好。不用的原因是因為在Access數據庫中使用數據透視表時，拖動一個字段，Access數據庫就會計算一次，數據越多，其計算速度就越慢，也不知何時結束計算。如果每拖動一次字段就這樣計算一次，你會瘋掉的！你可以事后自行測試一下。

而用SQL語句，你只需寫一次，直接單擊【運行】按鈕即可。并且有運行進度條顯示運行進度，大大提高數據分析效率，所以我推薦用SQL語句進行數據分析。

小白：明白，您繼續。

簡單統計

Mr.林：在重復數據處理時我們已經用到一個計數函數Count，這是最常用的統計函數之一。Access數據庫中常見的統計函數如圖1-49所示。

圖1-49 Access數據庫常用的統計函數

我們以“訂購明細”表為例，統計“訂單總數”、“訂購金額總額”、“平均訂單金額”三個數據，SQL語句如下：

SELECT
Count(訂單編號) AS訂單總數,
Sum(訂購金額) AS訂購金額總額,
Avg(訂購金額) AS平均訂單金額
FROM訂購明細;

將編寫好的SQL語句直接復制至一個新建的查詢SQL視圖窗口中，單擊【運行】按鈕，可以得到如圖1-50所示的結果。

圖1-50 簡單統計結果示例

分組統計

Mr.林：小白，在學習重復數據處理時我們還用過一個GROUP BY子句，實現對數據按指定的分組字段進行分組功能。

小白：對，您說這個與數據透視表分組統計的道理是一致的。

Mr.林：沒錯。我們同樣以“訂購明細”表為例，統計各個產品的“訂單總數”、“訂購金額總額”、“平均訂單金額”的數據，SQL語句如下：

SELECT產品,
Count(訂單編號) AS訂單總數,
Sum(訂購金額) AS訂購金額總額,
Avg(訂購金額) AS平均訂單金額
FROM訂購明細
GROUP BY產品;

將編寫好的SQL語句直接復制至一個新建的查詢SQL視圖窗口中，單擊【運行】按鈕，可以得到如圖1-51所示的結果。

圖1-51 產品分組統計結果示例

Mr.林：小白，你發現這個分組統計與剛才的簡單統計SQL語句有什么區別嗎？

小白：發現了，就是在最后加上“GROUP BY產品”，并且在SELECT中增加“產品”字段。

Mr.林：是的，分組統計就是這么簡單。再來看個復雜一點的案例。還是以“訂購明細”表為例，我們需要了解用戶訂購時段分布信息。原有“訂購明細”表只有“訂購日期”字段，需要取出時段信息，這時可采用FORMAT函數，SQL語句如下：

SELECT FORMAT(訂購日期,"h") AS時段,
Count(訂單編號) AS訂單數
FROM訂購明細
GROUP BY FORMAT(訂購日期,"h");

將編寫好的SQL語句直接復制至一個新建的查詢SQL視圖窗口中，單擊【運行】按鈕，可以得到如圖1-52所示的結果。我們只需復制出統計結果，保存至Excel中，調整一下時段順序，即可繪制用戶訂購時段分布圖。

圖1-52 時段分組統計結果示例

小白：哦，只要在原來的基礎上，把分組表達式當作一個字段，放在GROUP BY后面，同時在SELECT中增加分組表達式，并重新命名。

Mr.林：是的，我們再來看個更復雜一點的案例。還是以“訂購明細”表為例，我們需要了解不同年齡段的用戶訂購分布情況，原有“訂購明細”表中沒有用戶年齡信息，并且訂購用戶存在重復情況，需要去重。

小白：確實又復雜了一些，怎么做呢？

Mr.林：先將“訂購明細”表與“用戶明細”表按關鍵字段“用戶ID”進行關聯查詢，并且可同時進行去重處理，采用PARTITION函數法對用戶年齡分組，然后把查詢結果作為子查詢嵌套在分組統計查詢中。SQL語句如下：

SELECT年齡分組,Count(用戶ID) AS用戶數
FROM
(SELECT DISTINCT A.用戶ID, PARTITION(B.年齡,1,100,5) AS年齡分組
FROM訂購明細A, 用戶明細B
WHERE A.用戶ID = B.用戶ID)
GROUP BY年齡分組;

將編寫好的SQL語句直接復制至一個新建的查詢SQL視圖窗口中，單擊【運行】按鈕，可以得到如圖1-53所示的結果。

圖1-53 年齡分組統計結果示例

Mr.林：這里需要說明的一點是：在Access數據庫中進行數據去重處理時，需要使用嵌套查詢，把數據去重結果作為子查詢。如果本例需要了解的是各省的訂單數分布，無須先進行數據去重處理，那么就無須使用嵌套查詢，SQL語句如下：

SELECT B.省份, Count(A.訂單編號) AS訂單數
FROM訂購明細A, 用戶明細B
WHERE A.用戶ID = B.用戶ID
GROUP BY B.省份;

小白：明白！

交叉表統計

（1）菜單操作法

Mr.林：最后介紹交叉表統計，這個需要借助“簡單查詢”功能來實現。我們以“用戶明細”表為例，統計不同省份、性別的用戶分布情況，具體查詢操作步驟如下。

STEP 01 單擊【創建】選項卡，在【查詢】組中單擊【查詢向導】按鈕。

STEP 02 在彈出的【新建查詢】對話框中，選擇【交叉表查詢向導】，如圖1-20所示，單擊【確定】按鈕。

STEP 03 在彈出的【交叉表查詢向導】第一個對話框中，選擇【表】視圖，并在列表框中選擇“用戶明細”表作為查詢對象，單擊【下一步】按鈕，如圖1-54所示。

圖1-54 【交叉表查詢向導】對話框1

STEP 04 在彈出的【交叉表查詢向導】第二個對話框【可用字段】列表框中，選擇“省份”作為行標題，單擊【下一步】按鈕，如圖1-55所示。

圖1-55 【交叉表查詢向導】對話框2

STEP 05 在彈出的【交叉表查詢向導】第三個對話框字段列表框中，選擇“性別”作為列標題，單擊【下一步】按鈕，如圖1-56所示。

圖1-56 【交叉表查詢向導】對話框3

STEP 06 在彈出的【交叉表查詢向導】第四個對話框【字段】列表框中，選擇“用戶ID”作為每個行和列的交叉點統計項，并在【函數】列表框中，選擇“Count”函數，并保持默認勾選的【是，包括各行小計】項，單擊【下一步】按鈕，如圖1-57所示。

圖1-57 【交叉表查詢向導】對話框4

STEP 07 在彈出的【交叉表查詢向導】第五個對話框中，輸入該查詢的名稱，單擊【完成】按鈕，結果如圖1-58所示。

圖1-58 交叉表查詢結果

（2）SQL語句法

小白：Mr.林，單擊Access數據庫窗口右下方的按鈕，讓我瞧瞧SQL語句是怎樣寫的。

Mr.林：好的。我們單擊Access數據庫窗口右下方的按鈕，其簡化的SQL語句如下：

TRANSFORM Count(用戶ID) AS用戶ID計數
SELECT省份,Count(用戶ID) AS總計用戶ID
FROM用戶明細
GROUP BY省份
PIVOT性別;

Mr.林：小白，你看出交叉表的SQL語句與我們剛才學的分組統計SQL語句的區別沒有？

小白：交叉表的SQL語句在分組統計SQL語句的基礎上，前后增加了TRANSFORM與PIVOT語句。

Mr.林：沒錯，只要在分組統計SQL語句的基礎上，前后增加TRANSFORM與PIVOT語句，并且在TRANSFORM后面增加每個行和列的交叉點統計函數及字段，在PIVOT后面增加要作為列標題的分組字段即可。

其運行結果與剛才的菜單操作法結果一樣，我就不再重復運行了。小白，用Access數據庫進行數據處理與分析的內容就先介紹到這兒，現在你能真正體會用SQL語句處理與分析數據的強大與實用了吧！除了掌握基本原理，還要做到結合實際情況，融會貫通。

小白：確實不是一般的強大與實用。