SQL練習2：用戶消費行為分析(回購率和復購率)

目錄：
一、將數(shù)據(jù)導入數(shù)據(jù)庫
二、SQL--用戶消費行為分析
1-統(tǒng)計不同月份的下單人數(shù)
2-統(tǒng)計用戶三月份的回購率和復購率
3-統(tǒng)計男女的消費頻次是否有差異
4-統(tǒng)計多次消費的用戶，第一次和最后一次消費時間的間隔
5-統(tǒng)計不同年齡段的用戶消費金額是否有差異
6-統(tǒng)計消費的二八法則，消費的top20%用戶，貢獻了多少額度

一、將數(shù)據(jù)導入數(shù)據(jù)庫

目的：將兩份csv文件導入數(shù)據(jù)庫
步驟：建表、導入數(shù)據(jù)
前面給出的數(shù)據(jù)較大，分別是10萬和50萬條數(shù)據(jù)，如果用數(shù)據(jù)庫自帶的導數(shù)工具比較緩慢，像WORKBENCH導入幾千行數(shù)據(jù)就需要3分鐘時間，這里可以用cmd命令行導入、或者用KETTLE進行抽取

建表

1、訂單明細表

CREATE TABLE ORDERINFO  ( 
    ORDERID     varchar(10) NULL,--訂單ID，主鍵
    USERID      varchar(10) NULL,--用戶ID，可以和用戶表進行關(guān)聯(lián)
    ISPAID      varchar(10) NULL,--是否支付
    PRINCE      varchar(10) NULL,--訂單價格
    PAIDTIME    varchar(10) NULL --訂單支付時間
    )

2、用戶表

CREATE TABLE YWUSER.USERINFO  ( 
    USERID  varchar(10) NULL,--用戶ID，主鍵
    SEX     varchar(10) NULL,--性別
    BIRTH   varchar(10) NULL --出生日期
    )

導入數(shù)據(jù)

這里用KETTLE進行數(shù)據(jù)抽取，沒用過KETTLE的同學可以用CMD命令行導入，可以參考秦路老師七周七成為數(shù)據(jù)分析師視頻。

0、kettle轉(zhuǎn)換過程

抽取用戶表

如果在導入csv文件的時候亂碼，可以添加字段選擇節(jié)點。
1、csv文件輸入

2、字段選擇


在csv文件輸入的時候可能出現(xiàn)亂碼的情況，可以通過字段選擇修改數(shù)據(jù)類型，將encoding修改為UTF-8
3、表輸出

上面是用戶表的數(shù)據(jù)導入，導入訂單明細表方法類似，更換文件路徑、目標表即可。

二、SQL--用戶消費行為分析

使用的是SQLSERVER數(shù)據(jù)庫

分析問題：
1-統(tǒng)計不同月份的下單人數(shù)
2-統(tǒng)計用戶三月份的回購率和復購率
3-統(tǒng)計男女的消費頻次是否有差異
4-統(tǒng)計多次消費的用戶，第一次和最后一次消費時間的間隔
5-統(tǒng)計不同年齡段的用戶消費金額是否有差異
6-統(tǒng)計消費的二八法則，消費的top20%用戶，貢獻了多少額度
1-統(tǒng)計不同月份的下單人數(shù)
select substring(paidtime,1,4)+'-'+ substring(paidtime,6,2) MONTH_,count(distinct USERid) XDRS from YWUSER.ORDERINFO
where ISPAID = '已支付'
group by substring(paidtime,1,4),substring(paidtime,6,2)
1
2-統(tǒng)計用戶三月份的回購率和復購率

復購率

--復購率是在本月消費中多少人消費一次以上的占比

select count(tt.USERID) '三月購買的總用戶數(shù)',count(tt.USERID_count) '多次購買用戶數(shù)'from
(select 
t.USERID USERID,
 case when t.USERID_count>1 then 1 else null end USERID_count
from 
(select  USERID  ,count(USERID)  USERID_count from YWUSER.ORDERINFO
where ISPAID = '已支付' and substring(paidtime,6,2) = '03'
group by USERID ) T
)tt

2

回購率

--回購率是三月份購買的人數(shù)四月份依舊購買

select substring(a.paidtime,1,7) '年月',count(distinct a.USERID) '本月消費的用戶數(shù)量',count(distinct b.USERID)'本月回購的用戶數(shù)' from
(select  userid,paidtime from YWUSER.ORDERINFO
where ISPAID = '已支付'
group by userid,paidtime) a
left join (select  userid,paidtime from YWUSER.ORDERINFO
where ISPAID = '已支付'
group by userid,paidtime) b on a.userid = b.userid and substring(a.paidtime,6,2) =  substring(b.paidtime,6,2)-1
group by substring(a.paidtime,1,7)
2
3-統(tǒng)計男女的消費頻次是否有差異
--先統(tǒng)計每個用戶的消費頻次在統(tǒng)計，在統(tǒng)計男女的平均消費頻次


select aa.sex '性別',round(avg(aa.PC_ORDERID),2) '消費頻次'from 
(select a.USERID,b.sex,count(a.ORDERID) PC_ORDERID from 
YWUSER.ORDERINFO a
inner join (select * from YWUSER.USERINFO where SEX is not null)b on a.USERID = B.USERID
group by a.USERID,b.sex)aa
group by aa.sex
思路正確，結(jié)果有一點問題
4、統(tǒng)計多次消費的用戶，第一次和最后一次消費時間的間隔
--（可以大概理解為生命周期、多次消費的用戶：消費頻次超過2次的用戶；先求每個用戶的第一次和最后一次的消費時間間隔，在求平均消費間隔）
select 
USERID,
ORDERID_CS,
convert(date,replace(paidtime_max,'/','-'),120) paidtime_max,
convert(date,replace(paidtime_min,'/','-'),120) paidtime_min,
datediff(dd,convert(date,replace(paidtime_min,'/','-'),120),convert(date,replace(paidtime_max,'/','-'),120)) '第一次最后一次消費時間差' from
(select  USERID,count(ORDERID) ORDERID_CS,max(paidtime) paidtime_max,min(paidtime) paidtime_min from YWUSER.ORDERINFO
where ISPAID = '已支付'
group by USERID having count(ORDERID)>1)aa
4
5-統(tǒng)計不同年齡段的用戶消費金額是否有差異
select aa.NLD,round(avg(aa.PRINCE),2) avg_prince from
(select  a.ORDERID,A.USERID,cast(A.PRINCE as float) PRINCE,
case  when B.AGE between 10 and 19 then '10-19歲'  when B.AGE between 20 and 29 then '20-29歲' 
when B.AGE between 30 and 39 then '30-39歲' when B.AGE between 40 and 49 then '40-49歲' when B.AGE between 50 and 59 then '50-59歲' 
when B.AGE between 60 and 69 then '60-69歲'when B.AGE between 70 and 79 then '70-79歲'
else null end NLD,
B.AGE from 
(select * from  YWUSER.ORDERINFO where ISPAID = '已支付')a
inner join (select USERID,year(getdate())-left(BIRTH,4) age from YWUSER.USERINFO where BIRTH is not null) b on a.USERID = b.USERID
)aa group by aa.NLD having aa.NLD is not null
5
6-統(tǒng)計消費的二八法則，消費的top20%用戶，貢獻了多少額度
select sum(sum_prince) from --top20%用戶貢獻的總額度
(select *,row_number()over(order by sum_prince desc)as '排序' from 
(select  userid,round(sum(cast(prince as float)),2) sum_prince from  YWUSER.ORDERINFO where ISPAID = '已支付' group by USERID
) aa) tt
where 排序< (select  count(distinct USERID)* 0.2 from  YWUSER.ORDERINFO where ISPAID = '已支付')
6

如果有其他的分析方法歡迎大家留言，有不會的地方可以寫到下面，看到一定回復大家。

關(guān)于SQL的練習可以參考我以前寫的2篇文章：
1、導入數(shù)據(jù)時出現(xiàn)的3個問題：【SQL練習】經(jīng)典SQL練習題
2、總結(jié)：SQL練習【SQL經(jīng)典練習題】

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關(guān)閱讀更多精彩內(nèi)容

  • Kettle 1.Kettle的介紹： Kettle是一款國外開源...
    有你就行閱讀 796評論 0贊 1
  • 今天看到一位朋友寫的mysql筆記總結(jié)，覺得寫的很詳細很用心，這里轉(zhuǎn)載一下，供大家參考下，也希望大家能關(guān)注他原文地...
    信仰與初衷閱讀 4,818評論 0贊 30
  • ORA-00001: 違反唯一約束條件 (.) 錯誤說明：當在唯一索引所對應的列上鍵入重復值時，會觸發(fā)此異常。 O...
    我想起個好名字閱讀 5,916評論 0贊 9
  • http://192.168.136.131/sqlmap/mysql/get_int.php?id=1 當給sq...
    xuningbo閱讀 10,559評論 2贊 22
  • 漫長的夜 總是不期而遇 鐘表的滴答聲 讓人煩躁不安 點上一支煙 遐想青春與現(xiàn)實 肉搏后的美好時光 對未來的憧憬 也...
    等等老少年閱讀 366評論 1贊 3

友情鏈接更多精彩內(nèi)容