报表查询

发表于 | 分类于

级联报表查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
A,2015-01,5
A,2015-01,15
B,2015-01,5
A,2015-01,8
B,2015-01,25
A,2015-01,5
C,2015-01,10
C,2015-01,20
A,2015-02,4
A,2015-02,6
C,2015-02,30
C,2015-02,10
B,2015-02,10
B,2015-02,5
A,2015-03,14
A,2015-03,6
B,2015-03,20
B,2015-03,25
C,2015-03,10
C,2015-03,20

create table t_access_times(username string,month string,counts int)
row format delimited fields terminated by ',';

upload successful

1
2
3
select uid, month, amount, 
sum (amount) over(partition by uid order by month rows between unbounded preceding and current row) as accumulate
from t_access_amount;

参考：
https://www.cnblogs.com/arjenlee/p/9692312.html#auto_id_81

留存

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
---dif = 0表示当日活跃
---dif = 1 表示次日留存
---dif = 7表示7日内留存
---dif = 30表示30日内留存留存：
SELECT * dif,
         count(DISTINCT uid)
FROM
  (SELECT uid,
          dif
   FROM
     (SELECT a.uid,
             CASE 
             WHEN diff(b.dt,a.dt) = 0 THEN '0' 
             WHEN diff(b.dt,a.dt) = 1 THEN '1,7,30' 
             WHEN diff(b.dt,a.dt) <= 7 THEN '7,30' 
             WHEN diff(b.dt,a.dt) <= 30 THEN '30' 
             END AS diffs
      FROM
        (SELECT uid,
                dt
         FROM current_table
         WHERE dt>=’2018-06-02’
           AND dt<=’2018-07-02’) a
      LEFT JOIN
        (SELECT uid,
                dt
         FROM current_table
         WHERE dt>=’2018-06-02’
           AND dt<=’2018-07-02’) b ON a.uid = b.uid
         ) 
           c 
           LATERAL VIEW explode(split(diffs,',')) diftable 
           AS dif
  ) d
GROUP BY dif

SQL 统计日环比、周同比

upload successful

1
2
3
select date,gmv,
lag(gmv,1) over(partition by type order by dt) as gmv1,
lag(gmv,7) over(partition by date order by dt) as gmv7 from tablue