如何使用sql處理分析數據

❶ 如何配置使用微軟SQL Server 數據分析工具

本系列文章分兩部分，本文是第二部分(第一部分參見：你的數據有多干凈?)。我們將以測試體驗的方式了解微軟SQL Server提供的數據分析工具。

其它一些外部分析工具具有更豐富的功能，但是SQL Server授權允許你免費就可以使用這款低調的分析工具。

接下來讓我們通過分析「AdventureWorksLT 」資料庫中的一些數據來了解這款工具。

在此之前，你需要先安裝好SQL Server集成服務，以及與SQL Server捆綁的Visual Studio精簡版。在SQL Server 2012中，叫做「SQL Server Data Tools」;而在SQL Server 2008中，名稱叫做「Business Intelligence Development Studio」。

如果你缺少這些組件，你可以運行SQL Server安裝程序並勾選相關組件，操作很簡單。

配置分析工具

不管你啟動哪個Visual Studio工作環境，都要創建一個集成服務項目。然後，從SSIS工具箱中拖拽一個數據分析任務到設計面板上，然後雙擊開始配置。

配置結果信息存儲在XML文件中，所以需要指定保存文件的名稱和路徑。

點擊目標旁邊的空白框，會出現一個箭頭。

點擊箭頭，然後點擊「New File connection」。

在結果框中，指定路徑和文件名(包括.xml後綴)。

點擊「確定」按鈕，然後點擊「Quick Profile」按鈕。

點擊「ADO.NET Connection」旁邊的「新建」按鈕。

在出現的對話框中，指定SQL Server和託管待分析數據的資料庫(我使用的是「AdventureWorksLT2012 」資料庫)，然後點擊「確定」按鈕。

點擊表或視圖下拉框，選擇待分析數據(我選用的是「SalesLT.Proct」)。

保留默認勾選的復選框，然後點擊「確定」按鈕。確定後就會回到主設計界面。

最後，按「F5」就可以開始分析了。

分析成功，在SQL Server 2008中會有一個綠色小框來顯示，在SQL Server 2012中是個綠色小勾。如果你看到紅色小框或者紅叉，那麼可以在「Progress 」標簽頁找到哪裡出錯了。

查看結果

假設你看到的是綠色(在集成服務中每個人都喜歡這個顏色)，那麼接下來啟動「Data Profile Viewer」應用程序。在開始菜單中，它在「Microsoft SQL Server XXXX」文件夾的集成服務子文件夾下。

在查看器中，點擊「打開」按鈕，找到你之前配置的xml文件。你現在可以看到分析結果了。

「adventureWorks」庫中的數據本來是乾乾凈凈的，所以我做了一些修改，你自己直接做的話看到的分析結果會不一樣。

在查看器左側面板中，你可以看到我們選擇的表，它下面是數據分析的五個不同方面(也就是我們配置任務時默認選中的五項)。

點擊「Candidate Key Profiles 」，就會在右側面板中顯示結果。

該信息展示了值的唯一性，你可以看看我的數據(修改過的)，「名稱」列包含的數據不是百分之百唯一的。

我可以點擊「名稱」列來查看更多，界面中「 Key Violations 」部分下面會顯示更多信息。

錯誤的產品是「HL Road Frame – Black, 58」，問題是有兩行有相同的名稱(產品名稱通常應該是唯一的)。

在「 Key Violations 」中的問題產品上點擊，就會顯示出需要關注的行。

在左側面板中(+微信關注網路世界)，點擊「Column Length Distribution Profiles」。

功能正如其名，這個頁面會分析每個列值的長度。

我這里顯示的結果，「顏色」列包含列最小長度是2。

就像我們前面例子中所做的那樣，我們可以向下鑽取，我可以看到那些值的分布，進一步鑽取我可以看到臟數據對應的實際行。

其它類型的分析操作都是類似的。

「Column NULL Ratio Profiles」顯示了某一列中「NULL」值的百分比。

如果你有些列實際上不允許為「NULL」，例如「Proct Name」，那麼這個功能就非常有用。

「Column Statistics Profiles 」顯示了日期和數值的最小值、最大值、平均值以及標准偏差。對於識別「327歲的員工」與「價格為負數的產品」這類不合理數據非常有幫助。

「Column Value Distribution Profiles 」顯示某一列中有多少不同的值。這對於處理像「性別」這種列是很有用的，你可能預期該列只有兩個不同值，但是卻找到了15個，很容易發現問題。

還有另外兩個分析功能也是可用的，不過默認沒有啟用。

其中一個就是「Functional Dependency Profile 」，它可以找到那些依賴於其它列值得列。

這一條功能對於檢查地址信息時很有用;例如，我們希望相同城市的地址共用相同的郵政編碼。

總結

該分析工具的局限性在於，源數據必須存儲在SQL Server資料庫中。例如，如果你希望分析存儲在平面文件中的數據，那麼你必須先把數據導入到SQL Server資料庫中。

此外，該分析工具不支持創建額外的定製分析功能實現擴展。你看到的所有功能就是它能提供的所有功能。

局限性且放一旁，SQL Server附帶提供的這款工具為那些希望得到並能快速運行數據分析的人提供了很好的起點。

❷ 資料庫作業分析過程中如何使用sql語句進行統計 用語言來表達

使用sql統計大多數都是利用函數，比如count求總數，avg求平均值，sum求和等，如果資料庫自帶函數不符合需求，就需要自定義函數，根據具體情況來計算統計。

❸ sql數據分析需要學什麼

作為數據分析師，你首先需要從正在查詢的資料庫中讀取數據。我們一般會採用這種方法：
了解SELECT語句的工作方式。這其中包括研究邏輯查詢處理的工作方式。邏輯查詢處理描述了資料庫引擎執行SELECT語句的子句的順序。了解它會引導你編寫更好，更准確的查詢。
研究聯接的工作方式
內部聯接是最有效的。左，右和完全外部聯接的效率較低，但有時必須使用它們。內部聯接可以導致行被過濾掉，外部聯接不能導致過濾。研究加入，因此你知道何時使用每種類型。只有最簡單的資料庫查詢才不涉及聯接。

了解如何使用GROUP BY子句進行聚合
了解窗口函數/有序分析函數。這些是在SQL中進行分析的縮影。最初很難將你的頭纏繞在它們周圍，但是這是值得的。一旦了解了它們，便會一直使用它們。
了解資料庫規范化。如果你了解規范化，你將了解為什麼架構師以這種方式構造資料庫的理論。這有助於編寫查詢並確定要聯接的表。
了解實體關系圖（ERD）的工作方式。大多數ERD是使用魚尾紋符號構建的。確保知道基數和可選性約束是如何工作的，這將幫助你解密連接表時要使用的連接類型。

一旦掌握了這些知識，就應該擴展到學習SQL的DML和DDL子類別。DML代表數據操作語言，SELECT語句是該語言的一部分。DDL代表數據定義語言，這是資料庫架構師用來創建表的語言。
本文禁止轉載或摘編
本文為我原創
大數據
數據分析
數據分析師
1
收藏
分享
推薦文章
35歲轉行數據分析師可以嗎？
學習 · 56閱讀
Excel之PowerQuery 提取單元格指定文字
學習 · 175閱讀
[簡述]答題驗證系統部署說明
學習 · 865閱讀

❹ 用SQL做簡單數據分析（入門級）

例：統計注冊次數大於10，而且ID中包含ming
select id ,count(id) from _tianyaid where patindex('%ming%',id)>0 group by id having count(id)>10

❺ 如何在EXCEL中使用SQL進行數據處理與分析

方法一：利用Microsoft Query使用SQL語句

❻ sql數據分析是啥意思

sql數據分析是結構化查詢語言。

結構化查詢語言（Structured Query Language）簡稱SQL，是一種特殊目的的編程語言，是一種資料庫查詢和程序設計語言，用於存取數據以及查詢、更新和管理關系資料庫系統。

結構化查詢語言是高級的非過程化編程語言，允許用戶在高層數據結構上工作。它不要求用戶指定對數據的存放方法，也不需要用戶了解具體的數據存放方式。

所以具有完全不同底層結構的不同資料庫系統, 可以使用相同的結構化查詢語言作為數據輸入與管理的介面。結構化查詢語言語句可以嵌套，這使它具有極大的靈活性和強大的功能。

SQL具有數據定義、數據操縱、數據查詢和數據控制的功能。

1、SQL數據定義功能：能夠定義資料庫的三級模式結構，即外模式、全局模式和內模式結構。在SQL中，外模式又叫做視圖（View），全局模式簡稱模式（Schema），內模式由系統根據資料庫模式自動實現，一般無需用戶過問。

2、SQL數據操縱功能：包括對基本表和視圖的數據插入、刪除和修改，特別是具有很強的數據查詢功能。

3、SQL的數據控制功能：主要是對用戶的訪問許可權加以控制，以保證系統的安全性。

❼ 如何用SQL分析電商用戶行為數據（案例）

        

本文以「淘寶用戶行為數據集」的分析全過程為例，展示數據分析的全過程

——使用工具：MySQL，Excel，Navicat，PowerBI

——分析類型：描述分析，診斷分析

——分析方法：漏斗分析，用戶路徑分析，RFM用戶價值分析，活躍/存留分析，帕累托分析，假設驗證分析。

（考慮到閱讀體驗文章中只放了SQL截圖，如需PDF版本，再公眾號後台回復「用戶行為分析」領取）

（目錄如下）

       

1.分析流程和方法

當沒有清晰的數據看板時我們需要先清洗雜亂的數據，基於分析模型做可視化，搭建描述性的數據看板。

然後基於描述性的數據挖掘問題，提出假設做優化，或者基於用戶特徵數據進行預測分析找規律，基於規律設計策略。簡單來說：

——描述性分析就是：「畫地圖」

——診斷性分析就是：「找問題」

——預測性分析就是 ：「找規律」

在數據分析中有兩個典型的場景：

一種是有數據，沒有問題：需要先整體分析數據，然後再根據初步的描述分析，挖掘問題做診斷性分析，提出假設，設計策略解決問題。

 

另一種是已經發現了問題，或者已經有了假設，這種做數據分析更偏向於驗證假設。

 

2.淘寶用戶行為分析

本次是對「淘寶用戶行為數據集」進行分析，在分析之前我們並不知道有什麼問題，所以需要先進行描述性分析，分析數據挖掘問題。

我們首先來看下這個數據集的元數據：

       

根據以上數據欄位我們可以拿用戶行為為主軸從縱深方向提出一些問題，然後再從數據中找答案

       

縱向：

——這個數據集中用戶的日活躍和周活躍時間有什麼規律嗎？

——在當日活躍的用戶次日，三日，四日……還有多少活躍？

深向：

——用戶從瀏覽到購買的整體轉化率怎麼樣？

——用戶從瀏覽到購買的路徑是怎麼樣子的？ 

——平台主要會給用戶推送什麼商品？

——用戶喜歡什麼類目？喜歡什麼商品？ 

——怎麼判斷哪些是高價值用戶 ？ 

 

 

下面是叮當整理的常用分析方法：      

我們可以給前面的問題匹配一下分析方法，便於後面的分析：

為了便於後面的數據分析，在分析之前我們需要先對做一下清洗

看元數據（欄位解釋，數據來源，數據類型，數據量……）初步發現問題為之後的處理做准備。

       

確定缺失值范圍，去除不需要欄位，填充缺失內容    

根據元數據格式和後續分析需要的格式對數據進行處理

  

去除重復值，異常值

——去除重復值：並把用戶ID,商品ID，時間戳設置為主鍵

——異常值處理：查詢並刪除2017年11月25日至2017年12月3日之外的數據

     

查詢並刪除小於2017-11-25的

——驗證數據：      

——分析思路：

——SQL提數：

       

       

——Excel可視化：

       

活躍曲線整體為上升狀態，同為周六日，12月2號，3號相比11月25日，26日活躍度更高。

用戶在周六周日相比其他時間更活躍（周六周日為休息日，用戶有更多時間）

      

一天內用戶活躍的最高峰期為21點（用戶在這個時間段空閑較多）

 

——分析思路：

——SQL提數：

列出每用戶每天及當天後面又活躍的日期，並創建「活躍時間間隔表」用於後面求次日存留，三日存留……

       

對「活躍時間間隔表視圖」引用進行分組統計，計算每日存留人數並創建視圖

對存留人數表進行計算，統計活躍用戶留存率

——Excel可視化：

       

——分析思路：

——SQL提數：

-把各種用戶行為分離出來並創建視圖方便後續查詢用戶行為數據

查詢整體數據漏斗

——Excel可視化：

       

用戶從瀏覽到購買整體轉化率2.3%，具體主要在哪個環節流失還需要再細分用戶路徑分析

 

——分析思路：

       

——SQL提數：

——PowerBI可視化：

       

用戶從瀏覽到購買的路徑主要有4條，路徑越長轉化率越底

路徑1：瀏覽→購買：轉化率1.45%

路徑2：瀏覽→加購物車→購買：轉化率0.33

路徑3：瀏覽→收藏→購買：轉化率0.11%

路徑4：瀏覽→收藏→加購物車→購買：轉化率0.03%

——分析思路：

——SQL提數：

——Excel可視化：

       

——描述性分析：

瀏覽量top100的商品瀏覽量呈階梯分布，越靠前的階梯之間的落差相對越大在這個階梯中的商品越少，越靠後商品瀏覽量階梯之間的落差相對越小，同階梯內的商品越多。

瀏覽量TOP100的商品所屬類目中，4756105，3607361，4357323三個類目瀏覽量遠超其他類目。

——分析思路：

——SQL提數：

查詢計算商品轉化率，升序排列，取前100個

       

——Excel可視化：

       

——描述性分析：

從商品看：有17款商品轉化率超過了1。

從類目看：這些商品所屬類目分布均勻，除965809，4801426，2735466，2640118，5063620，4789432，2945933這7個類目之外，其他類目都只有一個商品在轉化率TOP100的商品中。

——分析思路：

用戶價值分析常用的分析方式是RFM模型

       

本次分析中的R,F,M具體定義（僅用於演示分析方法，無實際業務參考價值）：

 

——SQL取數與分析：

1）建立打分標准：先計算R,F的值，並排序，根據R,F值最大值和最小值得區間設計本次得打分標准

-查詢並計算R，F值創建視圖

       

-引用RF數值表，分別查詢R,F的最大值和最小值

       

       

-結合人工瀏覽的建立打分標准      

2）給R，F按價值打分

3）計算價值的平均值

       

4）用平均值和用戶分類規則表比較得出用戶分類   

     

——Excel可視化      

 

通過描述性分析得到可視化的數據後我們一般會先看一下是否符合業務常識

如果符合常識接下來我們會通過與行業平均數據和本產品的同比環比對比看是否正常，如果不正常就要找原因，設計解決方案，如果正常那就看是否有可以優化的地方。

       

我們首先來看一下這些描述性分析是否符合業務常識和指標是否正常：

       

1.活躍曲線整體為上升狀態，同為周六日，12月2號，3號相比11月25日，26日活躍度更高。

2.用戶在周六周日相比其他時間更活躍

3.一天內用戶活躍的最高峰期為21點

4.從2017年11月15日致2017年12月3日，活躍用戶新增38%

5.從2017年11月15日致2017年12月3日，活躍用戶次日留存增長18.67%，當日的活躍用戶留存也在快速增長，第七日留存比次日留存高18.56%。

6.用戶從瀏覽到購買整體轉化率2.3%

7.用戶從瀏覽到購買的路徑主要有4條，路徑越長轉化率越低。

8.瀏覽量top100的商品瀏覽量呈階梯分布，越靠前的階梯之間的落差相對越大在這個階梯中的商品越少，越靠後商品瀏覽量階梯之間的落差相對越小，同階梯內的商品越多。

9.瀏覽量TOP100的商品所屬類目中，4756105，3607361，4357323三個類目瀏覽量遠超其他類目。

10.從商品看：有17款商品轉化率超過了1。

11.從類目看：這些商品所屬類目分布均勻，除965809，4801426，2735466，2640118，5063620，4789432，2945933這7個類目之外，其他類目都只有一個商品在轉化率TOP100的商品中。

根據以上診斷分析我們梳理出了以下假設，做假設驗證。

       

 

假設1：這些商品中有高轉化率的爆款商品

       

 

對比瀏覽量TOP5的商品，發現這些商品轉化率在同一類目下並不高，假設不成立

 

假設2：4756105，3607361，4357323三個類目屬於高頻剛需類目

-創建類目購買頻次表

       

-計算類目購買頻次平均值

       

-查詢4756105，3607361，4357323三個類目的購買頻次       

4756105，3607361，4357323三個類目的用戶購買頻次明顯高於平均值，假設成立

 

假設3：有部分用戶是未點擊商詳直接從收藏和購物車購買的。

       

用戶不是直接從收藏和購物車購買的，只是後續復購未點擊商詳，假設不成立

 

假設4：淘寶推薦的商品主要是「同一類目下的高轉化商品」

       

用Excel對瀏覽量TOP100的商品ID和轉化率TOP100的商品ID進行去重，結果無重復值，假設不成立

3.結論：

1）用戶活躍：用戶活躍曲線整體呈上升趨勢，在一周中周六，周日活躍度比平時更高，在一天中用戶活躍曲線從凌晨4點開始往上升，在中午12點和下午5~6點有兩個小低谷（吃飯），到晚上9點時活躍度達到頂峰。

 

2）用戶留存：從2017年11月15日致2017年12月3日的用戶留存數據來看，淘寶的用戶留存數據較好，活躍用戶次日留存增長18.67%，當日的活躍用戶留存也在快速增長，第七日留存比次日留存高18.56%。

 

3）用戶轉化：整體轉化2.3%，用戶從瀏覽到購買的路徑主要有4條，路徑越長轉化率越低。

4）平台推薦與用戶偏好：從數據集中的數據來看，排除用戶興趣偏好標簽，淘寶給用戶用戶推送的商品主要是高頻剛需的類目，促使用戶復購，流量迴流平台。

 

以上結論受數據量和數據類型的影響，並不一定準確，僅用來練習數據分析方法。

（考慮到閱讀體驗文章中只放了SQL截圖，如需PDF版本，再公眾號後台回復「用戶行為分析」領取）

❽ 數據分析過程如果用SQL語句進行統計如何實現

方法和詳細的操作步驟如下：

1、第一步，創建一個測試表，詳細代碼見下圖，轉到下面的步驟。