sql環比

A. 如何用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版本，再公眾號後台回復「用戶行為分析」領取）

B. sql欄位設計中如何實現同比環比功能

select '上期' as '期間' ,sum(sl) sl,sum(je) je from subfhd where kdrq between '2016-06-01' and '2016-06-30'union select '本期' as '期間',sum(sl) sl,sum(je) je from subfhd where kdrq between '2016-07-01' and '2016-07-31'

C. 這是一個環比增長率的SQL但是運行結果卻有問題，求大神解決一下

可能是你在分區里漏了銷售員了，光是按年度肯定是不夠的，加上銷售員然後把lag單獨拿出來做一列看一下確保正確，再作計算。

D. qlikview 怎麼做環比

qlikview需要用到sql計算，可以用excel直接添加公式進去就好啦，或者直接使用DataFocus ，中文搜索輸入「按xx計算的yy的X增長率」就可得到環比啦~

E. SQL里怎麼查詢銷售同比與環比

與上月比即為環比，與上年同月比即為同比
select sum(case when to_char(fdbs,'yyyy')-1 || to_char(fdbs,'MM『) =年月參數 then sshje else 0 end ) 上年同期,sum(case when to_char(fdbs,'yyyy') || to_char(fdbs,'MM『)-1 = 年月參數 then sshje else 0 end ) 上月銷售額,sum(case when to_char(fdbs,'yyyy') || to_char(fdbs,'MM') = 年月參數 then sshje else 0 end ) 本月銷售額
from retmls

F. sqlServer 實現環比

實施鼎捷軟體易飛ERP，輕松實現企業商業智能化管理!

實施的易飛ERP系統，包括了基本信息模塊、采購管理、存貨管理、銷售管理、BOM、工單及委外管理、批次需求計劃、應收應付管理、會計總帳管理、自動分錄管理、成本管理共11個模塊。

易飛ERP是由製造、質量、財務、銷售和人力資源等30多個模塊組成，其功能覆蓋了企業管理的方方面面。易飛ERP強調系統集成性，並可根據各企業的不同需求加以調整組合。易飛ERP集易操作性、高技術水準和強大功能於一身。經過15000家客戶的經驗積累，沉澱了厚重的製造、流通行業知識。主要功能有：信息實時掌握 全球經營監控；內控內審循環；製造管理；進銷存管理；財務管理；商業智能；人力資源管理；智能的管理應用工具等。
另外,虛機團上產品團購,超級便宜

G. 如何用mysql實現環比的計算

SELECT
t.bill_fee,
y.bill_fee,
CASE

WHEN y.bill_fee IS NULL
OR y.bill_fee = 0 THEN
0.00 ELSE round(( t.bill_fee - y.bill_fee )/ y.bill_fee * 100, 2 )
END ratio
FROM
( SELECT DATE_FORMAT( t.attr_cycle, '%Y%m%d' ) AS attr_cycle, T.BILL_FEE FROM dd_result t ) t
LEFT JOIN (
SELECT
DATE_FORMAT( t.attr_cycle, '%Y%m%d' ) AS attr_cycle,
DATE_FORMAT( DATE_ADD( t.attr_cycle, INTERVAL 1 DAY ), '%Y%m%d' ) AS tomorrow,
T.BILL_FEE
FROM
dd_result t
) y ON t.attr_cycle = y.tomorrow

H. 請教Oracle計算同比和環比sql語句

首先明確概念：

環比增長率＝（本期數－上期數）/上期數*100％反映本期比上期增長了多少。

同比增長率＝（本期數－同期數）/同期數*100％指和去年同期相比較的增長率。

數據表名：d_temp_data
查詢數據如下：

查詢的sql語句如下：

selecta.*,

nvl(round(money/lag(money)over(orderbyid)*100,2),'0')||'%'"同比",

nvl(round(money/lag(money)over(partitionbymonorderbyid)*100,2),'0')||'%'"環比"

fromd_temp_dataa

idinameyearmonmoney同比環比

----------------------------------------------------------------------

1 1 飛馬股 2013 01 300 0% 0%

2 2 飛馬股 2013 02 270 90% 0%

3 3 飛馬股 2013 03 350 129.63% 0%

4 4 飛馬股 2013 04 180 51.43% 0%

5 5 飛馬股 2013 05 500 277.78% 0%

6 6 飛馬股 2013 06 400 80% 0%

7 7 飛馬股 2014 01 210 52.5% 70%

8 8 飛馬股 2014 02 240 114.29% 88.89%

9 9 飛馬股 2014 03 320 133.33% 91.43%

10 10 飛馬股 2014 04 480 150% 266.67%

11 11 飛馬股 2014 05 400 83.33% 80%

I. 求問Mysql同比環比SQL語句怎麼寫

首先明確概念：
環比增長率＝（本期數－上期數）/上期數*100％ 反映本期比上期增長了多少。
同比增長率＝（本期數－同期數）/同期數*100％ 指和去年同期相比較的增長率。

J. sql問題，關於sql時間上處理上的問題

環境sqlserver 2008下

declare @user0 as long --當期
declare @user1 as long --當期1
declare @user2 as long --當期2

--為天時

select @user0 = count(*) from 會員表 where 日期 = @ap_data
select @user1 = count(*) from 會員表 where 日期 = dateadd(day,-1,=@ap_data)
select @user2 = count(*) from 會員表 where 日期 = dateadd(day,-2,=@ap_data)

--為周

select @user0 = count(*) from 會員表 where 日期 <= @ap_data and 日期 >=dateadd(day,-7,=@ap_data)
select @user1 = count(*) from 會員表 where 日期 <= dateadd(day,-8,=@ap_data) and 日期 >=dateadd(day,-14,=@ap_data)
select @user2 = count(*) from 會員表 where 日期 <= dateadd(day,-15,=@ap_data) and 日期 >=dateadd(day,-21,=@ap_data)

--為月時，修改以上幾個數字，不粘貼了