复购买人数/销售金额/销售订单数/销售件数/复购率/客件数/客单价/连带率

复购率

    SELECT
        a.NAME,
        下单年月,
        COUNT( c ) AS 下单人数,
        COUNT( IF ( c > 1, 1, NULL ) ) AS 复购人数,
        concat(round( COUNT( IF ( c > 1, 1, NULL ) ) / COUNT( c ) * 100, 2 ),'%') AS '复购率' 
    FROM
    (
        SELECT
            c_channel.NAME AS NAME,
            CONCAT( date_format( c_orders.pay_time, '%Y-%m-%d' ) ) AS 下单年月,
            COUNT( c_orders.member_id ) AS c 
        FROM
        c_orders
        INNER JOIN c_member ON c_orders.member_id = c_member.id
        INNER JOIN c_channel ON c_channel.id = c_orders.channel_id
        INNER JOIN c_goods ON c_orders.tid = c_goods.tid 
        WHERE
            c_channel.id = 2 
            AND c_goods.after_refund_payment > 0 
            AND date_format( c_orders.pay_time, '%Y-%m-%d' ) BETWEEN '2021-06-12' 
            AND '2021-07-11' 
        GROUP BY
            date_format( c_orders.pay_time, '%Y-%m-%d' ),
            c_orders.member_id 
    ) a 
        GROUP BY
        下单年月;

　　按日

-- 按日
    SELECT
        c_channel.name,
        count( distinct c_orders.member_id) as 购买人数,
        sum(c_goods.after_refund_payment) as 销售金额,
        count(c_goods.tid) as 销售订单数,
        sum(c_goods.after_refund_good_count) as 销售件数,
        sum(c_goods.after_refund_good_count)/count(distinct c_orders.member_id) as 客件数,
        sum(c_goods.after_refund_payment)/count(distinct c_orders.tid) as 客单价,
        sum(c_orders.after_refund_good_count)/ count(DISTINCT c_orders.tid) as 连带率,
        c_orders.pay_date
    FROM
        c_member
        INNER JOIN c_orders ON c_orders.member_id = c_member.id
        INNER JOIN c_channel ON c_channel.id = c_orders.channel_id 
        INNER JOIN c_goods  ON c_orders.tid = c_goods.tid
        WHERE  1   
        and c_member.identity = 2  -- 会员
        and c_channel.STATUS = 1 
        and  c_orders.after_refund_payment >0
        and  c_orders.pay_date  BETWEEN '2021-06-07'  AND '2021-07-06'
    GROUP BY
        c_channel.id,
        c_orders.pay_date;

　　按店铺

-- 按店铺
    SELECT
        c_orders.store_name,
        count( distinct c_orders.member_id) as 购买人数,
        sum(c_goods.after_refund_payment) as 销售金额,
        count(c_goods.tid) as 销售订单数,
        sum(c_goods.after_refund_good_count) as 销售件数,
        sum(c_goods.after_refund_good_count)/count(distinct c_orders.member_id) as 客件数,
        sum(c_goods.after_refund_payment)/count(distinct c_orders.tid) as 客单价,
        sum(c_orders.after_refund_good_count)/ count(DISTINCT c_orders.tid) as 连带率,
        c_orders.pay_date
    FROM
        c_member
        INNER JOIN c_orders ON c_orders.member_id = c_member.id
        INNER JOIN c_goods  ON c_orders.tid = c_goods.tid
        WHERE  1   --      
        and  c_member.identity = 2  -- 会员
        and  c_orders.after_refund_payment >0
        and  c_orders.store_id in ('14')
        and  c_orders.pay_date  BETWEEN '2021-06-06'  AND '2021-07-05'
    GROUP BY
        c_orders.pay_date