临时表添加以及优化

-- ALTER TABLE  `大润发数据测试对比` ADD `消费日期` datetime DEFAULT NULL

DELETE
FROM
    `大润发数据测试对比`;

DROP TABLE base ;

CREATE TEMPORARY TABLE base SELECT
    a.salesdepart_id AS 门店ID,
    b.`name` AS 会员姓名,
    b.mobile AS 手机号码,
    b.create_date AS 注册日期,
    ROUND(a.real_pay / 10000, 2) AS 消费金额,
        a.sales_date AS 消费日期 ,
    COUNT(a.id) AS 消费次数
FROM
    arm_changsha.goods_sales AS a
LEFT JOIN arm_changsha.ms_member_def AS b ON b.id = a.member_id
WHERE
    b.create_date >= ‘2019-01-17 00:00:00‘
AND b.create_date <= ‘2019-01-30 23:59:59‘
AND a.salesdepart_id = ‘1008‘
AND a.sales_type = ‘1‘
AND   ROUND(a.real_pay / 10000, 2) > 10
GROUP BY
    b.mobile;

SELECT * FROM base;


-- 选取临时表时,直接选取字段.不用写前缀那些东西. 这个最基础的都不知道.也是醉了.服了你.
--  业务存在会员在创建的时候没有购买东西,而后在后续的时间内购买的东西 选取的应该是固定时间段内的会员购买量. 一月注册的二月再买东西不应该算在内.
#写入会员基本信息
INSERT INTO `大润发数据测试对比` (
    门店ID,
    会员姓名,
    手机号码,
    注册日期,
    消费金额,
        消费日期,
    消费次数
) SELECT
    门店ID,
     会员姓名,
     手机号码,
     注册日期,
     消费金额,
        消费日期 ,
     消费次数
FROM
    base AS a
WHERE
    消费日期  >= ‘2019-01-17 00:00:00‘
AND  消费日期 <= ‘2019-01-30 23:59:59‘;


/*随机一半人发券
UPDATE `大润发数据测试对比` AS a
INNER JOIN(
SELECT *
FROM
`大润发2月第二周_copy`
ORDER BY
RAND(手机号码)
LIMIT 0,49)b
SET a.`是否发券` = ‘是‘
WHERE
b.手机号码 = a.手机号码;*/



#补充发券日期
-- UPDATE `大润发数据测试对比` AS a
-- SET a.`发券日期` = ‘2019-01-14‘
-- WHERE
--     a.`是否发券` = ‘是‘;



#写入T+7复购金额
UPDATE `大润发数据测试对比` AS a
SET a.`T+7消费金额` = (
    SELECT
        ROUND(SUM(b.real_pay) / 10000, 2) AS 累计消费金额
    FROM
        arm_changsha.goods_sales AS b
    LEFT JOIN arm_changsha.ms_member_def AS c ON c.id = b.member_id
    WHERE
        c.mobile = a.手机号码
    AND b.create_date >= ‘2019-01-31 00:00:00‘
    AND b.create_date <= ‘2019-02-13 23:59:59‘
    AND b.sales_type = ‘1‘
    GROUP BY
        b.member_id
);

#写入T+7购买次数
UPDATE `大润发数据测试对比` AS a
SET a.`T+7消费次数` = (
    SELECT
        COUNT(DISTINCT b.sales_id)
    FROM
        arm_changsha.goods_sales_detail AS b
    LEFT JOIN arm_changsha.goods_sales AS d ON d.id = b.sales_id
    LEFT JOIN arm_changsha.ms_member_def AS c ON c.id = d.member_id
    WHERE
        c.mobile = a.手机号码
    AND b.create_date >= ‘2019-02-14 00:00:00‘
    AND b.create_date <= ‘2019-02-20 23:59:59‘
    AND d.sales_type = ‘1‘
);

#写入T+14复购金额
UPDATE `大润发数据测试对比` AS a
SET a.`T+14消费金额` = (
    SELECT
        ROUND(SUM(b.real_pay) / 10000, 2) AS 累计消费金额
    FROM
        arm_changsha.goods_sales AS b
    LEFT JOIN arm_changsha.ms_member_def AS c ON c.id = b.member_id
    WHERE
        c.mobile = a.手机号码
    AND b.create_date >= ‘2019-02-14 00:00:00‘
    AND b.create_date <= ‘2019-02-27 23:59:59‘
    AND b.sales_type = ‘1‘
    GROUP BY
        b.member_id
);

#写入T+14购买次数
UPDATE `大润发数据测试对比` AS a
SET a.`T+14消费次数` = (
    SELECT
        COUNT(DISTINCT b.sales_id)
    FROM
        arm_changsha.goods_sales_detail AS b
    LEFT JOIN arm_changsha.goods_sales AS d ON d.id = b.sales_id
    LEFT JOIN arm_changsha.ms_member_def AS c ON c.id = d.member_id
    WHERE
        c.mobile = a.手机号码
    AND b.create_date >= ‘2019-02-14 00:00:00‘
    AND b.create_date <= ‘2019-02-27 23:59:59‘
    AND d.sales_type = ‘1‘
);

#写入T+30复购金额
UPDATE `大润发数据测试对比` AS a
SET a.`T+30消费金额` = (
    SELECT
        ROUND(SUM(b.real_pay) / 10000, 2) AS 累计消费金额
    FROM
        arm_changsha.goods_sales AS b
    LEFT JOIN arm_changsha.ms_member_def AS c ON c.id = b.member_id
    WHERE
        c.mobile = a.手机号码
    AND b.create_date >= ‘2019-02-14 00:00:00‘
    AND b.create_date <= ‘2019-03-15 23:59:59‘
    AND b.sales_type = ‘1‘
    GROUP BY
        b.member_id
);

#写入T+30购买次数
UPDATE `大润发数据测试对比` AS a
SET a.`T+30消费次数` = (
    SELECT
        COUNT(DISTINCT b.sales_id)
    FROM
        arm_changsha.goods_sales_detail AS b
    LEFT JOIN arm_changsha.goods_sales AS d ON d.id = b.sales_id
    LEFT JOIN arm_changsha.ms_member_def AS c ON c.id = d.member_id
    WHERE
        c.mobile = a.手机号码
    AND b.create_date >= ‘2019-02-14 00:00:00‘
    AND b.create_date <= ‘2019-03-15 23:59:59‘
    AND d.sales_type = ‘1‘
);