mysql口径,分析跑数口径与表内在关系逻辑

#控奶新客：T-14至T-7内购买大于等于1，且T-14至T-194(最远到7月)未购控奶

#T为1月13日;DELETE

FROM`0114-0120控奶新客`;

#写入会员基本信息INSERT INTO `0114-0120控奶新客` (

门店ID,

门店,

会员姓名,

手机号码,

控奶首购时间

)SELECT

*

FROM(SELECTa.salesdepart_idAS门店ID,

b.`name`AS门店,

c.`name`AS会员姓名,

c.mobileAS手机号码,MIN(d.create_date) AS`控奶首购时间`FROMarm_changsha.goods_salesASaLEFT JOIN arm_changsha.sys_office AS b ON b.id =a.salesdepart_idLEFT JOIN arm_changsha.ms_member_def AS c ON c.id =a.member_idLEFT JOIN arm_changsha.goods_sales_detail AS d ON d.sales_id =a.idLEFT JOIN arm_changsha.goods_base AS e ON e.id =d.goods_idWHEREa.sales_type= '1'

AND d.barcode IN(SELECT条码FROM`tmp_通货`WHERE商品标签= '非通货'

AND 大类 LIKE '01%')AND c.mobile NOT IN(SELECTmobileFROM`7~9月英克系统消费客户统计`

)AND c.mobile IS NOT NULL

GROUP BYc.mobile

)ASLWHERE`控奶首购时间`>= '2018-12-31 00:00:00'

AND `控奶首购时间` <= '2019-01-06 23:59:59';

#写入首购购买数量UPDATE `0114-0120控奶新客` ASaSET a.`控奶首购数量` =(SELECT

sum(b.sales_number) AS购买数量FROMarm_changsha.goods_sales_detailASbLEFT JOIN arm_changsha.goods_sales AS d ON d.id =b.sales_idLEFT JOIN arm_changsha.ms_member_def AS c ON c.id =d.member_idLEFT JOIN arm_changsha.goods_base AS e ON e.id =b.goods_idWHEREc.mobile=a.手机号码AND b.create_date =a.控奶首购时间AND e.kind_id LIKE '01%'

AND d.sales_type = '1'

AND b.barcode IN(SELECT条码FROM`tmp_通货`WHERE商品标签= '非通货'

AND 大类 LIKE '01%')

);

#写入T+7控货奶粉购买数量UPDATE `0114-0120控奶新客` ASaSET a.`T+7控奶购买数量` =(SELECT

sum(b.sales_number) AS购买数量FROMarm_changsha.goods_sales_detailASbLEFT JOIN arm_changsha.goods_sales AS d ON d.id =b.sales_idLEFT JOIN arm_changsha.ms_member_def AS c ON c.id =d.member_idWHEREc.mobile=a.手机号码AND b.create_date >= '2019-01-21 00:00:00'

AND b.create_date <= '2019-01-27 23:59:59'

AND d.sales_type = '1'

AND b.barcode IN(SELECT条码FROM`tmp_通货`WHERE商品标签= '非通货'

AND 大类 LIKE '01%')

);

#写入T+7复购金额UPDATE `0114-0120控奶新客` ASaSET a.`T+7复购金额` =(SELECT

ROUND(SUM(b.real_pay) / 10000, 2) AS累计消费金额FROMarm_changsha.goods_salesASbLEFT JOIN arm_changsha.ms_member_def AS c ON c.id =b.member_idWHEREc.mobile=a.手机号码AND b.create_date >= '2019-01-21 00:00:00'

AND b.create_date <= '2019-01-27 23:59:59'

AND b.sales_type = '1'

GROUP BYb.member_id

);

#写入T+7购买次数UPDATE `0114-0120控奶新客` ASaSET a.`T+7小票数` =(SELECT

COUNT(DISTINCTb.sales_id)FROMarm_changsha.goods_sales_detailASbLEFT JOIN arm_changsha.goods_sales AS d ON d.id =b.sales_idLEFT JOIN arm_changsha.ms_member_def AS c ON c.id =d.member_idWHEREc.mobile=a.手机号码AND b.create_date >= '2019-01-21 00:00:00'

AND b.create_date <= '2019-01-27 23:59:59'

AND d.sales_type = '1');

#写入近90日充值金额UPDATE `0114-0120控奶新客` ASaSET a.`近90日充值金额` =(SELECT

ROUND(sum(b.use_recharge_balance) / 10000,2)AS充值金额FROMarm_changsha.ms_member_card_logASbLEFT JOIN arm_changsha.ms_member_def AS c ON c.id =b.member_idLEFT JOIN arm_changsha.ms_acct_book_def AS d ON d.member_id =b.member_idWHEREc.mobile=a.手机号码AND d.acct_id IS NOT NULL

AND b.oper_type IN ('4', '7', '8')AND b.create_date >= '2018-10-16 00:00:00'

AND b.create_date <= '2019-01-13 23:59:59');

#写入近90天控货奶粉购买数量UPDATE `0114-0120控奶新客` ASaSET a.`近90日控奶购买数量` =(SELECT

sum(b.sales_number) AS购买数量FROMarm_changsha.goods_sales_detailASbLEFT JOIN arm_changsha.goods_sales AS d ON d.id =b.sales_idLEFT JOIN arm_changsha.ms_member_def AS c ON c.id =d.member_idWHEREc.mobile=a.手机号码AND b.create_date >= '2018-10-16 00:00:00'

AND b.create_date <= '2019-01-13 23:59:59'

AND d.sales_type = '1'

AND b.barcode IN(SELECT条码FROM`tmp_通货`WHERE商品标签= '非通货'

AND 大类 LIKE '01%')

);

#判断是否运营门店UPDATE `0114-0120控奶新客` ASaSET a.`是否运营门店` = CASE

WHEN a.门店ID IN('1008','1016','1025','1026')THEN

'是'

ELSE

'否'

END;

#判断是否稳定UPDATE `0114-0120控奶新客` ASaSET a.`是否稳定` = CASE

WHEN a.近90日控奶购买数量 >= '4'

OR a.近90日充值金额 >= '500' THEN

'是'

ELSE

'否'

END;