sql自关联-下单用户随着月份流逝的用户粘性

解释说明

第一行数据代表的含义：首次下单在2016-05月的用户数总共有2943人，这2943个用户在2016-06月还有766人下单，这2943个用户在2016-07月还有526人下单，以此类推
第二行数据代表的含义：首次下单在2016-06月的用户数总共有5091人，这5091个用户在2016-07月还有1267人下单，这5091个用户在2016-08月还有999人下单，以此类推

非真实数据

上面的数据可以通过sql自关联实现

准备数据

准备如下表结构的数据

随机抽取某用户A看下数据表现

	memberid	crt_date	is_fst_ord	orderno
1	A	2017/4/30	1	daaae69f071
2	A	2017/5/1	0	5dec7096b02
3	A	2017/5/4	0	b13be6e2dd3
4	A	2017/10/7	0	f1029437794
5	A	2017/10/7	0	20440f144c5
6	A	2017/10/24	0	5a45f183836
7	A	2017/10/24	0	ddc5a008947
8	A	2017/11/17	0	8ce7aa2f658
9	A	2017/11/17	0	026661cdac9
10	A	2017/12/6	0	9277abcbab10
11	A	2017/12/6	0	e5b4ab425611
12	A	2017/12/8	0	4a2aa7cc5f12
13	A	2017/12/8	0	11450fc58413
14	A	2017/12/20	0	5d0c36c56814
15	A	2017/12/20	0	be17780f0d15
16	A	2017/12/21	0	be2ddece8e16
17	A	2017/12/25	0	c2bb51839f17
18	A	2018/1/5	0	300457ac6818
19	A	2018/1/5	0	a107ff5fbf19
20	A	2018/1/10	0	7c9b0be26d20
21	A	2018/1/10	0	e5f4966d4721
22	A	2018/1/28	0	36d7a56bb722
23	A	2018/1/31	0	532fe5206123
24	A	2018/2/5	0	d4c111181c24
25	A	2018/2/27	0	1c2677374925
26	A	2018/2/27	0	371a8a6d8826
27	A	2018/3/15	0	503b999fef27
28	A	2018/5/14	0	0d867e44f928
29	A	2018/5/14	0	7b0401224629
30	A	2018/8/11	0	32744e3b0630
31	A	2018/9/1	0	4f62f99ea731
32	A	2018/9/2	0	e6545af0ae32
33	A	2018/11/5	0	e6a0c8d85033
34	A	2018/11/12	0	561a9635ad34
35	A	2018/11/24	0	5292e1b11e35

数据解释：用户A首次下单时间在2017-04月，is_fst_ord标记1，其他下单数据都是0，该用户总共有35条下单数据，近一年的下单时间分别在2017-04、2017-05、2017-10、2017-11、2017-12、2018-01、2018-02、2018-03

即数据表现如下：

自关联

1、首次下单记录做主表

2、所有下单记录做次表

3、将1和2通过会员进行关联后，明细数据如下

dif_mth:每笔订单下单时间与首次下单时间的月份差值

	memberid	fst_mth	mem_id	mth	orderno	dif_mth
1	A	2017-04	A	2017-04	daaae69f071	0
2	A	2017-04	A	2017-05	5dec7096b02	1
3	A	2017-04	A	2017-05	b13be6e2dd3	1
4	A	2017-04	A	2017-10	f1029437794	6
5	A	2017-04	A	2017-10	20440f144c5	6
6	A	2017-04	A	2017-10	5a45f183836	6
7	A	2017-04	A	2017-10	ddc5a008947	6
8	A	2017-04	A	2017-11	8ce7aa2f658	7
9	A	2017-04	A	2017-11	026661cdac9	7
10	A	2017-04	A	2017-12	9277abcbab10	8
11	A	2017-04	A	2017-12	e5b4ab425611	8
12	A	2017-04	A	2017-12	4a2aa7cc5f12	8
13	A	2017-04	A	2017-12	11450fc58413	8
14	A	2017-04	A	2017-12	5d0c36c56814	8
15	A	2017-04	A	2017-12	be17780f0d15	8
16	A	2017-04	A	2017-12	be2ddece8e16	8
17	A	2017-04	A	2017-12	c2bb51839f17	8
18	A	2017-04	A	2018-01	300457ac6818	9
19	A	2017-04	A	2018-01	a107ff5fbf19	9
20	A	2017-04	A	2018-01	7c9b0be26d20	9
21	A	2017-04	A	2018-01	e5f4966d4721	9
22	A	2017-04	A	2018-01	36d7a56bb722	9
23	A	2017-04	A	2018-01	532fe5206123	9
24	A	2017-04	A	2018-02	d4c111181c24	10
25	A	2017-04	A	2018-02	1c2677374925	10
26	A	2017-04	A	2018-02	371a8a6d8826	10
27	A	2017-04	A	2018-03	503b999fef27	11
28	A	2017-04	A	2018-05	0d867e44f928	13
29	A	2017-04	A	2018-05	7b0401224629	13
30	A	2017-04	A	2018-08	32744e3b0630	16
31	A	2017-04	A	2018-09	4f62f99ea731	17
32	A	2017-04	A	2018-09	e6545af0ae32	17
33	A	2017-04	A	2018-11	e6a0c8d85033	19
34	A	2017-04	A	2018-11	561a9635ad34	19
35	A	2017-04	A	2018-11	5292e1b11e35	19

4、基于3的明细数据，限制 dif_mth <=12 ，对 fst_mth,dif_mth 进行分组，统计用户数

5、将纵表打横展示，即得到想要的数据啦

附件

SELECT
    fst_mth
    ,max(case when dif_mth=0  then num end) as mob0
    ,max(case when dif_mth=1  then num end) as mob1
    ,max(case when dif_mth=2  then num end) as mob2
    ,max(case when dif_mth=3  then num end) as mob3
    ,max(case when dif_mth=4  then num end) as mob4
    ,max(case when dif_mth=5  then num end) as mob5
    ,max(case when dif_mth=6  then num end) as mob6
    ,max(case when dif_mth=7  then num end) as mob7
    ,max(case when dif_mth=8  then num end) as mob8
    ,max(case when dif_mth=9  then num end) as mob9
    ,max(case when dif_mth=10 then num end) as mob10
    ,max(case when dif_mth=11 then num end) as mob11
    ,max(case when dif_mth=12 then num end) as mob12
from 
    (SELECT 
        fst_mth ,dif_mth,count(DISTINCT memberid) as num 
    from 
        (SELECT 
            *
            ,(cast(substr(mth,1,4) as int)-cast(substr(fst_mth,1,4) as int))*12+cast(substr(mth,6,2) as int)-cast(substr(fst_mth,6,2) as int) as dif_mth
        from 
            (SELECT
                memberid,substr(crt_date,1,7) as fst_mth 
            from dc_dev.spl_0926_t2 
            where is_fst_ord=1 
            ) a  -- 首次下单
            left join 
            (SELECT 
                memberid as mem_id,substr(crt_date,1,7) as mth,orderno 
            from dc_dev.spl_0926_t2 
            ) b on a.memberid=b.mem_id 
        ) x0
    where dif_mth <=12
    group by fst_mth,dif_mth
    ) x1 
group by fst_mth
;