1.項(xiàng)目需求（計(jì)算復(fù)購(gòu)率）
訂單表中有用戶(hù)ID、訂單金額、每筆訂單的下單時(shí)間等信息，需要統(tǒng)計(jì)每個(gè)月在接下來(lái)幾個(gè)月用戶(hù)復(fù)購(gòu)情況

// 創(chuàng)建數(shù)據(jù)表 create table orders( order_id int primary key, user_id int, amount int, create_time datetime ); // 插入數(shù)據(jù) insert into orders values (101,11211,749.00,'2021-05-06 00:04:00'), (102,75205,939.00,'2021-05-08 09:13:00'), (103,81384,349.00,'2021-05-09 22:18:00'), (104,50437,687.00,'2021-05-19 22:20:00'), (105,30321,658.00,'2021-05-19 21:20:00'), (106,49811,355.00,'2021-05-20 21:20:00'), (107,30352,363.00,'2021-05-19 21:56:00'), (108,30362,435.00,'2021-05-26 23:45:00'), (109,30363,270.00,'2021-05-27 10:16:00'), (110,30324,552.00,'2021-05-30 15:28:00'), (111,11211,692.00,'2021-05-08 16:34:00'), (112,75205,536.00,'2021-06-09 17:38:00'), (113,81384,478.00,'2021-06-13 21:36:00'), (114,30362,675.00,'2021-06-16 23:29:00'), (115,30363,723.00,'2021-06-20 11:42:00'), (116,30324,914.00,'2021-06-21 11:42:00'), (117,49262,1110.00,'2021-06-24 20:32:00'), (118,12074,617.00,'2021-06-29 21:12:00'), (119,50437,911.00,'2021-07-02 16:36:00'), (120,30321,695.00,'2021-07-14 22:56:00'), (121,12074,275.00,'2021-07-15 18:37:00'), (122,81384,1066.00,'2021-07-18 17:39:00'), (123,30362,958.00,'2021-07-23 11:30:00'), (124,27727,687.00,'2021-07-25 16:38:00'), (125,27727,858.00,'2021-07-28 15:20:00'), (126,75205,1120.00,'2021-07-28 15:20:00'), (127,30324,618.00,'2021-08-13 15:20:00'), (128,30362,958.00,'2021-08-16 11:30:00'), (129,27727,323.00,'2021-08-22 16:38:00'), (130,30362,558.00,'2021-08-26 17:20:00'), (131,75205,1120.00,'2021-08-28 19:26:00'), (132,27727,858.00,'2021-08-28 15:20:00');

目標(biāo)分解
1.首先統(tǒng)計(jì)每個(gè)月下單的用戶(hù)數(shù)

SELECT MONTH( create_time ) as 月份,user_id FROMorders GROUP BYyuefen,user_id;

2.將每個(gè)月下單的用戶(hù)數(shù)表自連接判斷是否復(fù)購(gòu),同時(shí)計(jì)算復(fù)購(gòu)人數(shù)

SELECT a.月份 as 購(gòu)買(mǎi)月份, b.月份 as 復(fù)購(gòu)月, COUNT(distinct(b.user_id)) as 復(fù)購(gòu)人數(shù) from (SELECT MONTH( create_time ) as 月份,user_id FROMorders GROUP BY月份,user_id) a join (SELECT MONTH( create_time ) as 月份,user_id FROMorders GROUP BY月份,user_id ) b on a.user_id = b.user_id and a.月份< b.月份 GROUP BY a.月份,b.月份

3.統(tǒng)計(jì)每個(gè)月總用戶(hù)下單數(shù)

SELECT MONTH( create_time ) as 月份,COUNT( distinct ( user_id ) ) 總用戶(hù)數(shù) fromorders GROUP BY月份;

4.計(jì)算復(fù)購(gòu)率，復(fù)購(gòu)人數(shù)/總用戶(hù)下單數(shù)

select 購(gòu)買(mǎi)月份,復(fù)購(gòu)月,復(fù)購(gòu)人數(shù),總用戶(hù)數(shù),CONCAT(ROUND(復(fù)購(gòu)人數(shù)/總用戶(hù)數(shù),2) *100,'%') as 復(fù)購(gòu)率 FROM (SELECT a.月份 as 購(gòu)買(mǎi)月份,b.月份 as 復(fù)購(gòu)月,COUNT(distinct(b.user_id)) as 復(fù)購(gòu)人數(shù) from (SELECT MONTH( create_time ) as 月份,user_id FROMorders GROUP BY月份,user_id) a join (SELECT MONTH( create_time ) as 月份,user_id FROMorders GROUP BY月份,user_id ) b on a.user_id = b.user_id and a.月份< b.月份 GROUP BY a.月份,b.月份 ) fg join (SELECT MONTH( create_time ) as 月份,COUNT( distinct ( user_id ) ) 總用戶(hù)數(shù) fromorders GROUP BY月份 ) zr on fg.購(gòu)買(mǎi)月份 = zr.月份

總結(jié)

以上是生活随笔為你收集整理的MySQL中用户订单复购率的计算的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。