sql复购率计算多种方法

注：这个复购率算的更加细致，比其他的算法更加精妙，能看懂的不多，好好体会！

说明：这是一个订单的复购率，计算的时候单独提取了n天复购的用户明细

关键sql说明：

D:取得用户下的第一单订单的时间，以及把第一单取出来，方便后面把第一单排除，排除后的单就是复购单

od:订单表，关联的时候限制订单时间范围以及排除用户下的第一单    

这里取得是一段时间内，用户的复购情况， adddate用来获取多少天之后，限制了订单表的范围，并且取得是每个用户下的第一单后的多少天的复购情况

 

 

 

以下是sql明细

 SELECT A.day,
IFNULL(cast((base.`3days复购用户数`*1.0/base.`完成用户数`)*100 as decimal(18,2)),'0.00') AS rebuyRate3,
IFNULL(cast((base.`5days复购用户数`*1.0/base.`完成用户数`)*100 as decimal(18,2)),'0.00') AS rebuyRate5,
IFNULL(cast((base.`7days复购用户数`*1.0/base.`完成用户数`)*100 as decimal(18,2)),'0.00') AS rebuyRate7,
IFNULL(cast((base.`15days复购用户数`*1.0/base.`完成用户数`)*100 as decimal(18,2)),'0.00') AS rebuyRate15,
IFNULL(cast((base.`30days复购用户数`*1.0/base.`完成用户数`)*100 as decimal(18,2)),'0.00') AS rebuyRate30,
IFNULL(cast((base.`3days复购单量`/base.`3days复购用户数`) as decimal(18,2)),'0.00') AS avgRebuyNum3,
IFNULL(cast((base.`5days复购单量`/base.`5days复购用户数`) as decimal(18,2)),'0.00') AS avgRebuyNum5,
IFNULL(cast((base.`7days复购单量`/base.`7days复购用户数`) as decimal(18,2)),'0.00') AS avgRebuyNum7,
IFNULL(cast((base.`15days复购单量`/base.`15days复购用户数`) as decimal(18,2)),'0.00') AS avgRebuyNum15,
IFNULL(cast((base.`30days复购单量`/base.`30days复购用户数`) as decimal(18,2)),'0.00') AS avgRebuyNum30,
IFNULL(cast((base.`3days复购单量`/base.`完成用户数`) as decimal(18,2))+1,'0.00') AS avgRebuyFinishNum3,
IFNULL(cast((base.`5days复购单量`/base.`完成用户数`) as decimal(18,2))+1,'0.00') AS avgRebuyFinishNum5,
IFNULL(cast((base.`7days复购单量`/base.`完成用户数`) as decimal(18,2))+1,'0.00') AS avgRebuyFinishNum7,
IFNULL(cast((base.`15days复购单量`/base.`完成用户数`) as decimal(18,2))+1,'0.00') AS avgRebuyFinishNum15,
IFNULL(cast((base.`30days复购单量`/base.`完成用户数`) as decimal(18,2))+1,'0.00') AS avgRebuyFinishNum30
FROM
(
    SELECT DISTINCT order_day AS day
    FROM wireless_statistic_offline.order_detail 
    WHERE order_day>='"+sdate+"' AND order_day<='"+edate+"'
) A
LEFT JOIN
(
    SELECT A.service_stop_day,A.`完成用户数`,
    B.`复购用户数` AS '3days复购用户数',B.`复购单量` AS '3days复购单量',
    C.`复购用户数` AS '5days复购用户数',C.`复购单量` AS '5days复购单量',
    D.`复购用户数` AS '7days复购用户数',D.`复购单量` AS '7days复购单量',
    E.`复购用户数` AS '15days复购用户数',E.`复购单量` AS '15days复购单量',
    F.`复购用户数` AS '30days复购用户数',F.`复购单量` AS '30days复购单量'
    FROM
    (
        SELECT service_stop_day,COUNT(DISTINCT order_phone) AS '完成用户数'
        FROM wireless_statistic_offline.order_detail od
        WHERE  source_type=5 AND promotion_type=8 -- 电销
        AND service_stop_day>='"+sdate+"' AND service_stop_day<='"+edate+"'
        GROUP BY service_stop_day
    ) A
    LEFT JOIN
    (
        SELECT service_stop_day,COUNT(DISTINCT IF(r.rebuyOrderNums!=0,r.order_phone,NULL)) AS '复购用户数',SUM(r.rebuyOrderNums) AS '复购单量'
        FROM
        (
            SELECT D.service_stop_day,D.order_phone,COUNT(DISTINCT od.order_id) AS rebuyOrderNums
            FROM wireless_statistic_offline.order_detail od
            RIGHT JOIN 
            (
            SELECT order_phone,MIN(CONCAT(service_stop_time, '\\t', order_id)) AS tag,date(MIN(service_stop_day)) AS service_stop_day
            FROM wireless_statistic_offline.order_detail od
            WHERE  source_type=5 AND promotion_type=8 -- 电销
            AND service_stop_day>='"+sdate+"' AND service_stop_day<='"+edate+"'
            GROUP BY order_phone
            ) D ON (od.order_phone = D.order_phone
            AND od.service_stop_day >= D.service_stop_day AND od.service_stop_day <= ADDDATE(D.service_stop_day, 2)
            AND od.order_id != substring_index(D.tag, '\\t', - 1))
            GROUP BY D.order_phone
        ) r
        GROUP BY r.service_stop_day
    ) B ON A.service_stop_day=B.service_stop_day
    LEFT JOIN
    (
        SELECT service_stop_day,COUNT(DISTINCT IF(r.rebuyOrderNums!=0,r.order_phone,NULL)) AS '复购用户数',SUM(r.rebuyOrderNums) AS '复购单量'
        FROM
        (
            SELECT D.service_stop_day,D.order_phone,COUNT(DISTINCT od.order_id) AS rebuyOrderNums
            FROM wireless_statistic_offline.order_detail od
            RIGHT JOIN 
            (
            SELECT order_phone,MIN(CONCAT(service_stop_time, '\\t', order_id)) AS tag,date(MIN(service_stop_day)) AS service_stop_day
            FROM wireless_statistic_offline.order_detail od
            WHERE  source_type=5 AND promotion_type=8 -- 电销
            AND service_stop_day>='"+sdate+"' AND service_stop_day<='"+edate+"'
            GROUP BY order_phone
            ) D ON (od.order_phone = D.order_phone
            AND od.service_stop_day >= D.service_stop_day AND od.service_stop_day <= ADDDATE(D.service_stop_day, 4)
            AND od.order_id != substring_index(D.tag, '\\t', - 1))
            GROUP BY D.order_phone
        ) r
        GROUP BY r.service_stop_day
    ) C ON A.service_stop_day=C.service_stop_day
    LEFT JOIN
    (
        SELECT service_stop_day,COUNT(DISTINCT IF(r.rebuyOrderNums!=0,r.order_phone,NULL)) AS '复购用户数',SUM(r.rebuyOrderNums) AS '复购单量'
        FROM
        (
            SELECT D.service_stop_day,D.order_phone,COUNT(DISTINCT od.order_id) AS rebuyOrderNums
            FROM wireless_statistic_offline.order_detail od
            RIGHT JOIN 
            (
            SELECT order_phone,MIN(CONCAT(service_stop_time, '\\t', order_id)) AS tag,date(MIN(service_stop_day)) AS service_stop_day
            FROM wireless_statistic_offline.order_detail od
            WHERE  source_type=5 AND promotion_type=8 -- 电销
            AND service_stop_day>='"+sdate+"' AND service_stop_day<='"+edate+"'
            GROUP BY order_phone
            ) D ON (od.order_phone = D.order_phone
            AND od.service_stop_day >= D.service_stop_day AND od.service_stop_day <= ADDDATE(D.service_stop_day, 6)
            AND od.order_id != substring_index(D.tag, '\\t', - 1))
            GROUP BY D.order_phone
        ) r
        GROUP BY r.service_stop_day
    ) D ON A.service_stop_day=D.service_stop_day
    LEFT JOIN
    (
        SELECT service_stop_day,COUNT(DISTINCT IF(r.rebuyOrderNums!=0,r.order_phone,NULL)) AS '复购用户数',SUM(r.rebuyOrderNums) AS '复购单量'
        FROM
        (
            SELECT D.service_stop_day,D.order_phone,COUNT(DISTINCT od.order_id) AS rebuyOrderNums
            FROM wireless_statistic_offline.order_detail od
            RIGHT JOIN 
            (
            SELECT order_phone,MIN(CONCAT(service_stop_time, '\\t', order_id)) AS tag,date(MIN(service_stop_day)) AS service_stop_day
            FROM wireless_statistic_offline.order_detail od
            WHERE  source_type=5 AND promotion_type=8 -- 电销
            AND service_stop_day>='"+sdate+"' AND service_stop_day<='"+edate+"'
            GROUP BY order_phone
            ) D ON (od.order_phone = D.order_phone
            AND od.service_stop_day >= D.service_stop_day AND od.service_stop_day <= ADDDATE(D.service_stop_day, 14)
            AND od.order_id != substring_index(D.tag, '\\t', - 1))
            GROUP BY D.order_phone
        ) r
        GROUP BY r.service_stop_day
    ) E ON A.service_stop_day=E.service_stop_day
    LEFT JOIN
    (
        SELECT service_stop_day,COUNT(DISTINCT IF(r.rebuyOrderNums!=0,r.order_phone,NULL)) AS '复购用户数',SUM(r.rebuyOrderNums) AS '复购单量'
        FROM
        (
            SELECT D.service_stop_day,D.order_phone,COUNT(DISTINCT od.order_id) AS rebuyOrderNums
            FROM wireless_statistic_offline.order_detail od
            RIGHT JOIN 
            (
            SELECT order_phone,MIN(CONCAT(service_stop_time, '\\t', order_id)) AS tag,date(MIN(service_stop_day)) AS service_stop_day
            FROM wireless_statistic_offline.order_detail od
            WHERE  source_type=5 AND promotion_type=8 -- 电销
            AND service_stop_day>='"+sdate+"' AND service_stop_day<='"+edate+"'
            GROUP BY order_phone
            ) D ON (od.order_phone = D.order_phone
            AND od.service_stop_day >= D.service_stop_day AND od.service_stop_day <= ADDDATE(D.service_stop_day, 29)
            AND od.order_id != substring_index(D.tag, '\\t', - 1))
            GROUP BY D.order_phone
        ) r
        GROUP BY r.service_stop_day
    ) F ON A.service_stop_day=F.service_stop_day
) base ON A.day = base.service_stop_day ";