mysql 本月老客户次月留存率_SQL 实现月度留存率/复购率

DROP TABLE IF EXISTSbigdata2c.temp_shoe_daidai_1118_04 ;CREATE TABLE IF NOT EXISTS bigdata2c.temp_shoe_daidai_1118_04 AS

SELECT distincta.order_month

,count(distinct a.user_id_month_0)

,count(distinct b.client_str),count(distinct c.client_str),count(distinct d.client_str),count(distinct f.client_str),count(distinct g.client_str),count(distinct h.client_str),count(distinct i.client_str),count(distinct j.client_str),count(distinct k.client_str),count(distinct l.client_str),count(distinct m.client_str),count(distinct n.client_str)FROM(SELECT distinct SUBSTR(create_time,1,6) asorder_month

,client_strASuser_id_month_0FROMbigdata2c.temp_shoe_daidai_1118_01where client_str is not NULL

and create_time is not NULL

and SUBSTR(create_time,1,6) >=201910

and SUBSTR(create_time,1,6) <=202010) aLEFT JOINbigdata2c.temp_shoe_daidai_1118_02 bON DATEDIFF( to_date(b.order_month,'yyyymm'),to_date(a.order_month,'yyyymm'),'mm') = 1

AND a.user_id_month_0 =b.client_strLEFT JOINbigdata2c.temp_shoe_daidai_1118_02 cON DATEDIFF( to_date(c.order_month,'yyyymm'),to_date(a.order_month,'yyyymm'),'mm') = 2

AND a.user_id_month_0 =c.client_strLEFT JOINbigdata2c.temp_shoe_daidai_1118_02 dON DATEDIFF( to_date(d.order_month,'yyyymm'),to_date(a.order_month,'yyyymm'),'mm') = 3

AND a.user_id_month_0 = d.client_str LEFT

JOINbigdata2c.temp_shoe_daidai_1118_02 fON DATEDIFF( to_date(f.order_month,'yyyymm'),to_date(a.order_month,'yyyymm'),'mm') = 4

AND a.user_id_month_0 =f.client_strLEFT JOINbigdata2c.temp_shoe_daidai_1118_02 gON DATEDIFF( to_date(g.order_month,'yyyymm'),to_date(a.order_month,'yyyymm'),'mm') = 5

AND a.user_id_month_0 = g.client_str LEFT

JOINbigdata2c.temp_shoe_daidai_1118_02 hON DATEDIFF( to_date(h.order_month,'yyyymm'),to_date(a.order_month,'yyyymm'),'mm') = 6

AND a.user_id_month_0 =h.client_strLEFT JOINbigdata2c.temp_shoe_daidai_1118_02 iON DATEDIFF( to_date(i.order_month,'yyyymm'),to_date(a.order_month,'yyyymm'),'mm') = 7

AND a.user_id_month_0 = i.client_str LEFT

JOINbigdata2c.temp_shoe_daidai_1118_02 jON DATEDIFF( to_date(j.order_month,'yyyymm'),to_date(a.order_month,'yyyymm'),'mm') = 8

AND a.user_id_month_0 =j.client_strLEFT JOINbigdata2c.temp_shoe_daidai_1118_02 kON DATEDIFF( to_date(k.order_month,'yyyymm'),to_date(a.order_month,'yyyymm'),'mm') = 9

AND a.user_id_month_0 = k.client_str LEFT

JOINbigdata2c.temp_shoe_daidai_1118_02 lON DATEDIFF( to_date(l.order_month,'yyyymm'),to_date(a.order_month,'yyyymm'),'mm') = 10

AND a.user_id_month_0 =l.client_strLEFT JOINbigdata2c.temp_shoe_daidai_1118_02 mON DATEDIFF( to_date(m.order_month,'yyyymm'),to_date(a.order_month,'yyyymm'),'mm') = 11

AND a.user_id_month_0 = m.client_str LEFT

JOINbigdata2c.temp_shoe_daidai_1118_02 nON DATEDIFF( to_date(n.order_month,'yyyymm'),to_date(a.order_month,'yyyymm'),'mm') = 12

AND a.user_id_month_0 =n.client_strgroup by a.order_month

;