mysql 案例分析一(用作學(xué)習(xí))

在data下面新建兩張order和user表,將原數(shù)據(jù)導(dǎo)入mysql藏姐,csv的格式以逗號分隔。
load data local infile 'C:/Users/Administrator/Desktop/day1/user_info_utf.csv' into table data.user
fields terminated by ',';

order表部分如下:
select * from data.order


image.png

user表部分如下:


image.png

統(tǒng)計不同月份的下單人數(shù)
思路:按月進(jìn)行分組,統(tǒng)計人數(shù)清蚀,注意去重蹲嚣。
select date_format(paidTime,'%Y%m') as month,
count(distinct userId) as countid
from data.order
where isPaid='已支付'
group by date_format(paidTime,'%Y%m')

image.png

三月份的下單人數(shù)高于四月份递瑰,五月份的數(shù)據(jù)含有些臟數(shù)據(jù)不作分析祟牲。

統(tǒng)計三月份的復(fù)購率和回購率
復(fù)購率:三月份買過一次后,當(dāng)月再次購買
回購率:三月份買過一次后抖部,四月份繼續(xù)購買

1.三月份復(fù)購率:
思路:在三月份的條件下说贝,對userId進(jìn)行分組再計數(shù),篩選出購買次數(shù)大于1的人占三月份購買總?cè)藬?shù)的占比

select userId,count(userId) as paidcounts from data.order
where isPaid ='已支付'
and date_format(paidTime,'%Y%m')=201603
group by userId

先找出每個用戶的購買次數(shù):

image.png

篩選出大于1的占比:
select count(userId),count(if(paidcounts>1,1,null)) as repaidc
from(
select userId,count(userId) as paidcounts from data.order
where isPaid ='已支付'
and date_format(paidTime,'%Y%m')=201603
group by userId) t
image.png

兩者相除慎颗,得出結(jié)論
三月份的復(fù)購率(保留一位小數(shù))約為:30.9%

2.三月份的回購率:
通用方法:可求每個月的回購率
思路:先將每個用戶所有的消費時間篩選出來乡恕,用left join,產(chǎn)生笛卡爾積效應(yīng)哗总,篩選出月份相差為1的記錄几颜,求出占比。
select userId,date_format(paidTime,'%Y-%m-01') m
from data.order
where isPaid='已支付'
group by userId,date_format(paidTime,'%Y-%m-01')


image.png

將表進(jìn)行自連接讯屈,條件是id相同蛋哭,且前后消費時間相差一個月


image.png

再此基礎(chǔ)上對各個月份進(jìn)行分組,計算占比
select t1.m,count(t1.m),count(t2.m) cback from(
select userId,date_format(paidTime,'%Y-%m-01') m from data.order
where isPaid='已支付'
group by userId,date_format(paidTime,'%Y-%m-01')) t1
left join(
select userId,date_format(paidTime,'%Y-%m-01') m from data.order
where isPaid='已支付'
group by userId,date_format(paidTime,'%Y-%m-01'))t2
on t1.userId=t2.userId and t1.m=date_sub(t2.m,interval 1 month)
group by t1.m

image.png

三月份回購率約為:23.9%

統(tǒng)計男女消費頻次是否有差異
思路:將order表和user表連接涮母,條件是已支付且性別不為空谆趾。對userId分組,查詢出對應(yīng)的消費次數(shù)叛本。再次嵌套groupby對性別分組沪蓬,求平均值。
select sex,avg(c)
from(
select o.userId,u.sex,count(u.sex) as c
from data.order o
join data.user u
on o.userId=u.userId
where isPaid='已支付' and sex !=''
group by o.userId) t
group by sex

image.png

男女的消費頻次約分別為1.78次和1.80次来候,消費頻次差距不大跷叉。

統(tǒng)計多次消費的用戶,第一次和最后一次消費間隔是多少营搅?
思路:對userId分組云挟,過濾出消費大于1次的,再找出時間的最大和最小值转质、消費間隔园欣。
select userId,max(paidTime),min(paidTime),
datediff(max(paidTime),min(paidTime)) deltaday
from data.order
where isPaid='已支付'
group by userId
having count(userId)>1

image.png

統(tǒng)計不同年齡段,用戶的消費金額是否有差異
先將用戶年齡按10的梯級劃分休蟹,把大于100歲的去除沸枯。
select userId,sex,ceil(age/10) as level from(
select userId,sex,year(now())-year(user.birth) as age
from data.user) t1
where age<100

image.png

將兩表進(jìn)行連接:


image.png

再對連接表的level和userId分組,計算出每個userId的消費次數(shù)赂弓,最后算出平均值绑榴。
select level,avg(ct)
from(
select t2.level,o.userId,count(o.userId) ct
from(
select userId,sex,ceil(age/10) as level from(
select userId,sex,year(now())-year(user.birth) as age
from data.user) t1
where age<100) t2
join data.order o
on o.userId=t2.userId and o.isPaid='已支付'
group by t2.level,o.userId) t3
group by level


image.png

根據(jù)結(jié)果可知,0-30年齡段的消費頻次較低盈魁,中年段的消費頻次相對低齡段有所增高且平均消費頻次在所有年齡段中最高彭沼,到老齡段頻次又逐漸降低。

統(tǒng)計消費的二八法則备埃,消費的TOP20%用戶姓惑,貢獻(xiàn)了多少額度?
思路:先計算出前20%的用戶數(shù)量按脚,再算出前20%用戶貢獻(xiàn)額度總和于毙。

image.png

排名前20%的用戶的消費總額度:
select count(userId) as top20,sum(sp)
from(
select userId,sum(price) as sp
from data.order
where isPaid='已支付'
group by userId
order by sp desc
limit 17130) t


image.png

總?cè)藬?shù)的消費總額度是:


image.png

前20%的用戶貢獻(xiàn)了約85%的額度。

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末辅搬,一起剝皮案震驚了整個濱河市唯沮,隨后出現(xiàn)的幾起案子,更是在濱河造成了極大的恐慌堪遂,老刑警劉巖介蛉,帶你破解...
    沈念sama閱讀 222,807評論 6 518
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件,死亡現(xiàn)場離奇詭異溶褪,居然都是意外死亡币旧,警方通過查閱死者的電腦和手機(jī),發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,284評論 3 399
  • 文/潘曉璐 我一進(jìn)店門猿妈,熙熙樓的掌柜王于貴愁眉苦臉地迎上來吹菱,“玉大人,你說我怎么就攤上這事彭则△⑺ⅲ” “怎么了?”我有些...
    開封第一講書人閱讀 169,589評論 0 363
  • 文/不壞的土叔 我叫張陵俯抖,是天一觀的道長输瓜。 經(jīng)常有香客問我,道長芬萍,這世上最難降的妖魔是什么尤揣? 我笑而不...
    開封第一講書人閱讀 60,188評論 1 300
  • 正文 為了忘掉前任,我火速辦了婚禮担忧,結(jié)果婚禮上芹缔,老公的妹妹穿的比我還像新娘。我一直安慰自己瓶盛,他們只是感情好最欠,可當(dāng)我...
    茶點故事閱讀 69,185評論 6 398
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著惩猫,像睡著了一般芝硬。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上轧房,一...
    開封第一講書人閱讀 52,785評論 1 314
  • 那天拌阴,我揣著相機(jī)與錄音,去河邊找鬼奶镶。 笑死迟赃,一個胖子當(dāng)著我的面吹牛陪拘,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播纤壁,決...
    沈念sama閱讀 41,220評論 3 423
  • 文/蒼蘭香墨 我猛地睜開眼左刽,長吁一口氣:“原來是場噩夢啊……” “哼!你這毒婦竟也來了酌媒?” 一聲冷哼從身側(cè)響起欠痴,我...
    開封第一講書人閱讀 40,167評論 0 277
  • 序言:老撾萬榮一對情侶失蹤,失蹤者是張志新(化名)和其女友劉穎秒咨,沒想到半個月后喇辽,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 46,698評論 1 320
  • 正文 獨居荒郊野嶺守林人離奇死亡雨席,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 38,767評論 3 343
  • 正文 我和宋清朗相戀三年菩咨,在試婚紗的時候發(fā)現(xiàn)自己被綠了。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片舅世。...
    茶點故事閱讀 40,912評論 1 353
  • 序言:一個原本活蹦亂跳的男人離奇死亡旦委,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出雏亚,到底是詐尸還是另有隱情缨硝,我是刑警寧澤,帶...
    沈念sama閱讀 36,572評論 5 351
  • 正文 年R本政府宣布罢低,位于F島的核電站查辩,受9級特大地震影響,放射性物質(zhì)發(fā)生泄漏网持。R本人自食惡果不足惜宜岛,卻給世界環(huán)境...
    茶點故事閱讀 42,254評論 3 336
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望功舀。 院中可真熱鬧萍倡,春花似錦、人聲如沸辟汰。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,746評論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽帖汞。三九已至戴而,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間翩蘸,已是汗流浹背所意。 一陣腳步聲響...
    開封第一講書人閱讀 33,859評論 1 274
  • 我被黑心中介騙來泰國打工, 沒想到剛下飛機(jī)就差點兒被人妖公主榨干…… 1. 我叫王不留,地道東北人扶踊。 一個月前我還...
    沈念sama閱讀 49,359評論 3 379
  • 正文 我出身青樓泄鹏,卻偏偏與公主長得像,于是被迫代替她去往敵國和親姻檀。 傳聞我的和親對象是個殘疾皇子命满,可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 45,922評論 2 361

推薦閱讀更多精彩內(nèi)容

  • MYSQL 基礎(chǔ)知識 1 MySQL數(shù)據(jù)庫概要 2 簡單MySQL環(huán)境 3 數(shù)據(jù)的存儲和獲取 4 MySQL基本操...
    Kingtester閱讀 7,820評論 5 116
  • pyspark.sql模塊 模塊上下文 Spark SQL和DataFrames的重要類: pyspark.sql...
    mpro閱讀 9,465評論 0 13
  • ORACLE自學(xué)教程 --create tabletestone ( id number, --序號usernam...
    落葉寂聊閱讀 1,086評論 0 0
  • 日精進(jìn)第50天 【體驗入】晚上下班剛剛到家,客人一個電話打過來貓急診剖腹產(chǎn)绣版。醫(yī)院里同事都是下班時間,腦子想的是怎么...
    Alina_qi閱讀 176評論 0 4
  • 關(guān)于無條件的接納 第一個赡矢,在我最初的時候就是感覺杭朱,無條件就是沒有條件的接納,是那個被接納的感覺吹散,我現(xiàn)在想弧械,我似乎很...
    OH咨詢師斯慶閱讀 228評論 0 0