最真实的大数据SQL面试题（二）

13. 抽象分组–断点排序

表名：t13
表字段及内容：

问题一：断点排序

输出结果如下所示：

a    b    c 
2014  1    1
2015  1    2
2016  1    3
2017  0    1
2018  0    2
2019  -1   1
2020  -1   2
2021  -1   3
2022  1    1
2023  1    2
1
2
3
4
5
6
7
8
9
10
11

参考答案:

select  
  a,
  b,
  row_number() over( partition by b,repair_a order by a asc) as c--按照b列和[b的组首]分组，排序
from 
(
  select  
    a,
    b,
    a-b_rn as repair_a--根据b列值出现的次序,修复a列值为b首次出现的a列值,称为b的[组首]
  from 
  (
   select 
     a,
     b,
     row_number() over( partition by b order by  a  asc ) as b_rn--按b列分组,按a列排序,得到b列各值出现的次序
   from t13 
  )tmp1
)tmp2--注意，如果不同的b列值，可能出现同样的组首值，但组首值需要和a列值 一并参与分组，故并不影响排序。
order by a asc; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

14. 业务逻辑的分类与抽象–时效

日期表：d_date
表字段及内容：

date_id      is_work
2017-04-13       1
2017-04-14       1
2017-04-15       0
2017-04-16       0
2017-04-17       1
1
2
3
4
5
6

工作日：周一至周五09:30-18:30

客户申请表：t14
表字段及内容：

a      b       c
1     申请   2017-04-14 18:03:00
1     通过   2017-04-17 09:43:00
2     申请   2017-04-13 17:02:00
2     通过   2017-04-15 09:42:00
1
2
3
4
5

问题一：计算上表中从申请到通过占用的工作时长

输出结果如下所示：

a         d
1        0.67h
2       10.67h 
1
2
3

参考答案:

select 
    a,
    round(sum(diff)/3600,2) as d
from (
    select 
        a,
        apply_time,
        pass_time,
        dates,
        rn,
        ct,
        is_work,
        case when is_work=1 and rn=1 then unix_timestamp(concat(dates,' 18:30:00'),'yyyy-MM-dd HH:mm:ss')-unix_timestamp(apply_time,'yyyy-MM-dd HH:mm:ss')
            when is_work=0 then 0
            when is_work=1 and rn=ct then unix_timestamp(pass_time,'yyyy-MM-dd HH:mm:ss')-unix_timestamp(concat(dates,' 09:30:00'),'yyyy-MM-dd HH:mm:ss')
            when is_work=1 and rn!=ct then 9*3600
        end diff
    from (
        select 
            a,
            apply_time,
            pass_time,
            time_diff,
            day_diff,
            rn,
            ct,
            date_add(start,rn-1) dates
        from (
            select 
                a,
                apply_time,
                pass_time,
                time_diff,
                day_diff,
                strs,
                start,
                row_number() over(partition by a) as rn,
                count(*) over(partition by a) as ct
            from (
                select 
                    a,
                    apply_time,
                    pass_time,
                    time_diff,
                    day_diff,
                    substr(repeat(concat(substr(apply_time,1,10),','),day_diff+1),1,11*(day_diff+1)-1) strs
                from (
                    select 
                        a,
                        apply_time,
                        pass_time,
                        unix_timestamp(pass_time,'yyyy-MM-dd HH:mm:ss')-unix_timestamp(apply_time,'yyyy-MM-dd HH:mm:ss') time_diff,
                        datediff(substr(pass_time,1,10),substr(apply_time,1,10)) day_diff
                    from (
                        select 
                            a,
                            max(case when b='申请' then c end) apply_time,
                            max(case when b='通过' then c end) pass_time
                        from t14
                        group by a
                    ) tmp1
                ) tmp2
            ) tmp3 
            lateral view explode(split(strs,",")) t as start
        ) tmp4
    ) tmp5
    join d_date 
    on tmp5.dates = d_date.date_id
) tmp6
group by a;
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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

15. 时间序列–进度及剩余

表名：t15
表字段及内容：

date_id      is_work
2017-07-30      0
2017-07-31      1
2017-08-01      1
2017-08-02      1
2017-08-03      1
2017-08-04      1
2017-08-05      0
2017-08-06      0
2017-08-07      1
1
2
3
4
5
6
7
8
9
10

问题一：求每天的累计周工作日，剩余周工作日

输出结果如下所示：

date_id      week_to_work  week_left_work
2017-07-31      1             4
2017-08-01      2             3
2017-08-02      3             2
2017-08-03      4             1
2017-08-04      5             0
2017-08-05      5             0
2017-08-06      5             0
1
2
3
4
5
6
7
8

参考答案:
此处给出两种解法，其一：

select 
 date_id
,case date_format(date_id,'u')
    when 1 then 1
    when 2 then 2 
    when 3 then 3 
    when 4 then 4
    when 5 then 5 
    when 6 then 5 
    when 7 then 5 
 end as week_to_work
,case date_format(date_id,'u')
    when 1 then 4
    when 2 then 3  
    when 3 then 2 
    when 4 then 1
    when 5 then 0 
    when 6 then 0 
    when 7 then 0 
 end as week_to_work
from t15
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

其二：

select
date_id,
week_to_work,
week_sum_work-week_to_work as week_left_work
from(
    select
    date_id,
    sum(is_work) over(partition by year,week order by date_id) as week_to_work,
    sum(is_work) over(partition by year,week) as week_sum_work
    from(
        select
        date_id,
        is_work,
        year(date_id) as year,
        weekofyear(date_id) as week
        from t15
    ) ta
) tb order by date_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

16. 时间序列–构造日期

问题一：直接使用SQL实现一张日期维度表，包含以下字段：

date                 string               日期
d_week               string               年内第几周
weeks                int                  周几
w_start              string               周开始日
w_end                string               周结束日
d_month             int                  第几月
m_start             string               月开始日
m_end               string               月结束日
d_quarter            int                    第几季
q_start             string               季开始日
q_end               string               季结束日
d_year               int                    年份
y_start             string               年开始日
y_end               string               年结束日
1
2
3
4
5
6
7
8
9
10
11
12
13
14

参考答案：

drop table if exists dim_date;
create table if not exists dim_date(
    `date` string comment '日期',
    d_week string comment '年内第几周',
    weeks string comment '周几',
    w_start string comment '周开始日',
    w_end string comment '周结束日',
    d_month string comment '第几月',
    m_start string comment '月开始日',
    m_end string comment '月结束日',
    d_quarter int comment '第几季',
    q_start string comment '季开始日',
    q_end string comment '季结束日',
    d_year int comment '年份',
    y_start string comment '年开始日',
    y_end string comment '年结束日'
);
--自然月: 指每月的1号到那个月的月底，它是按照阳历来计算的。就是从每月1号到月底，不管这个月有30天，31天，29天或者28天，都算是一个自然月。
 
insert overwrite table dim_date
select `date`
     , d_week --年内第几周
     , case weekid
           when 0 then '周日'
           when 1 then '周一'
           when 2 then '周二'
           when 3 then '周三'
           when 4 then '周四'
           when 5 then '周五'
           when 6 then '周六'
    end  as weeks -- 周
     , date_add(next_day(`date`,'MO'),-7) as w_start --周一
     , date_add(next_day(`date`,'MO'),-1) as w_end   -- 周日_end
     -- 月份日期
     , concat('第', monthid, '月')  as d_month
     , m_start
     , m_end
 
     -- 季节
     , quarterid as d_quart
     , concat(d_year, '-', substr(concat('0', (quarterid - 1) * 3 + 1), -2), '-01') as q_start --季开始日
     , date_sub(concat(d_year, '-', substr(concat('0', (quarterid) * 3 + 1), -2), '-01'), 1) as q_end   --季结束日
     -- 年
     , d_year
     , y_start
     , y_end
 
 
from (
         select `date`
              , pmod(datediff(`date`, '2012-01-01'), 7)                  as weekid    --获取周几
              , cast(substr(`date`, 6, 2) as int)                        as monthid   --获取月份
              , case
                    when cast(substr(`date`, 6, 2) as int) <= 3 then 1
                    when cast(substr(`date`, 6, 2) as int) <= 6 then 2
                    when cast(substr(`date`, 6, 2) as int) <= 9 then 3
                    when cast(substr(`date`, 6, 2) as int) <= 12 then 4
             end                                                       as quarterid --获取季节 可以直接使用 quarter(`date`)
              , substr(`date`, 1, 4)                                     as d_year    -- 获取年份
              , trunc(`date`, 'YYYY')                                    as y_start   --年开始日
              , date_sub(trunc(add_months(`date`, 12), 'YYYY'), 1) as y_end     --年结束日
              , date_sub(`date`, dayofmonth(`date`) - 1)                 as m_start   --当月第一天
              , last_day(date_sub(`date`, dayofmonth(`date`) - 1))          m_end     --当月最后一天
              , weekofyear(`date`)                                       as d_week    --年内第几周
         from (
                    -- '2021-04-01'是开始日期, '2022-03-31'是截止日期
                  select date_add('2021-04-01', t0.pos) as `date`
                  from (
                           select posexplode(
                                          split(
                                                  repeat('o', datediff(
                                                          from_unixtime(unix_timestamp('2022-03-31', 'yyyy-mm-dd'),
                                                                        'yyyy-mm-dd'),
                                                          '2021-04-01')), 'o'
                                              )
                                      )
                       ) t0
              ) t1
     ) t2;
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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

17. 时间序列–构造累积日期

表名：t17
表字段及内容：

date_id
2017-08-01
2017-08-02
2017-08-03
1
2
3
4

问题一：每一日期，都扩展成月初至当天

输出结果如下所示：

date_id    date_to_day
2017-08-01  2017-08-01
2017-08-02  2017-08-01
2017-08-02  2017-08-02
2017-08-03  2017-08-01
2017-08-03  2017-08-02
2017-08-03  2017-08-03
1
2
3
4
5
6
7

这种累积相关的表，常做桥接表。

参考答案:

select
  date_id,
  date_add(date_start_id,pos) as date_to_day
from
(
  select
    date_id,
    date_sub(date_id,dayofmonth(date_id)-1) as date_start_id
  from t17
) m  lateral view 
posexplode(split(space(datediff(from_unixtime(unix_timestamp(date_id,'yyyy-MM-dd')),from_unixtime(unix_timestamp(date_start_id,'yyyy-MM-dd')))), '')) t as pos, val;
1
2
3
4
5
6
7
8
9
10
11

18. 时间序列–构造连续日期

表名：t18
表字段及内容：

a             b         c
101        2018-01-01     10
101        2018-01-03     20
101        2018-01-06     40
102        2018-01-02     20
102        2018-01-04     30
102        2018-01-07     60
1
2
3
4
5
6
7

问题一：构造连续日期

问题描述：将表中数据的b字段扩充至范围[2018-01-01, 2018-01-07]，并累积对c求和。
b字段的值是较稀疏的。

输出结果如下所示：

a             b          c      d
101        2018-01-01     10     10
101        2018-01-02      0     10
101        2018-01-03     20     30
101        2018-01-04      0     30
101        2018-01-05      0     30
101        2018-01-06     40     70
101        2018-01-07      0     70
102        2018-01-01      0      0
102        2018-01-02     20     20
102        2018-01-03      0     20
102        2018-01-04     30     50
102        2018-01-05      0     50
102        2018-01-06      0     50
102        2018-01-07     60    110
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

参考答案:

select
  a,
  b,
  c,
  sum(c) over(partition by a order by b) as d
from
(
  select
  t1.a,
  t1.b,
  case
    when t18.b is not null then t18.c
    else 0
  end as c
  from
  (
    select
    a,
    date_add(s,pos) as b
    from
    (
      select
        a, 
       '2018-01-01' as s, 
       '2018-01-07' as r
      from (select a from t18 group by a) ta
    ) m  lateral view 
      posexplode(split(space(datediff(from_unixtime(unix_timestamp(r,'yyyy-MM-dd')),from_unixtime(unix_timestamp(s,'yyyy-MM-dd')))), '')) t as pos, val
  ) t1
    left join t18
    on  t1.a = t18.a and t1.b = t18.b
) ts;
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

19. 时间序列–取多个字段最新的值

表名：t19
表字段及内容：

date_id   a   b    c
2014     AB  12    bc
2015         23    
2016               d
2017     BC 
1
2
3
4
5

问题一：如何一并取出最新日期

输出结果如下所示：

date_a   a    date_b    b    date_c   c
2017    BC    2015     23    2016    d
1
2

参考答案:
此处给出三种解法，
其一：

SELECT  max(CASE WHEN rn_a = 1 THEN date_id else 0 END) AS date_a
        ,max(CASE WHEN rn_a = 1 THEN a else null END) AS a
        ,max(CASE WHEN rn_b = 1 THEN date_id else 0 END) AS date_b
        ,max(CASE WHEN rn_b = 1 THEN b else NULL  END) AS b
        ,max(CASE WHEN rn_c = 1 THEN date_id  else 0 END) AS date_c
        ,max(CASE WHEN rn_c = 1 THEN c else null END) AS c
FROM    (
            SELECT  date_id
                    ,a
                    ,b
                    ,c
                    --对每列上不为null的值  的 日期 进行排序
                    ,row_number()OVER( PARTITION BY 1 ORDER BY CASE WHEN a IS NULL THEN 0 ELSE date_id END DESC) AS rn_a
                    ,row_number()OVER(PARTITION BY 1 ORDER BY CASE WHEN b IS NULL THEN 0 ELSE date_id END DESC) AS rn_b
                    ,row_number()OVER(PARTITION BY 1 ORDER BY CASE WHEN c IS NULL THEN 0 ELSE date_id END DESC) AS rn_c
            FROM    t19
        ) t
WHERE   t.rn_a = 1
OR      t.rn_b = 1
OR      t.rn_c = 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

其二：

SELECT  
   a.date_id
  ,a.a
  ,b.date_id
  ,b.b
  ,c.date_id
  ,c.c
FROM
(
   SELECT  
      t.date_id,
      t.a
   FROM  
   (
     SELECT  
       t.date_id
       ,t.a
       ,t.b
       ,t.c
     FROM t19 t INNER JOIN    t19 t1 ON t.date_id = t1.date_id AND t.a IS NOT NULL
   ) t
   ORDER BY t.date_id DESC
   LIMIT 1
) a
LEFT JOIN 
(
  SELECT  
    t.date_id
    ,t.b
  FROM    
  (
    SELECT  
      t.date_id
      ,t.b
    FROM t19 t INNER JOIN t19 t1 ON t.date_id = t1.date_id AND t.b IS NOT NULL
  ) t
  ORDER BY t.date_id DESC
  LIMIT 1
) b ON 1 = 1 
LEFT JOIN
(
  SELECT  
    t.date_id
    ,t.c
  FROM    
  (
    SELECT  
      t.date_id
      ,t.c
    FROM t19 t INNER JOIN t19 t1 ON t.date_id = t1.date_id AND t.c IS NOT NULL
  ) t
  ORDER BY t.date_id DESC
  LIMIT   1
) c
ON 1 = 1;
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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

其三：

select 
  * 
from  
(
  select t1.date_id as date_a,t1.a from (select t1.date_id,t1.a  from t19 t1 where t1.a is not null) t1
  inner join (select max(t1.date_id) as date_id   from t19 t1 where t1.a is not null) t2
  on t1.date_id=t2.date_id
) t1
cross join
(
  select t1.date_b,t1.b from (select t1.date_id as date_b,t1.b  from t19 t1 where t1.b is not null) t1
  inner join (select max(t1.date_id) as date_id   from t19 t1 where t1.b is not null)t2
  on t1.date_b=t2.date_id
) t2
cross join 
(
  select t1.date_c,t1.c from (select t1.date_id as date_c,t1.c  from t19 t1 where t1.c is not null) t1
  inner join (select max(t1.date_id) as date_id   from t19 t1 where t1.c is not null)t2
  on t1.date_c=t2.date_id
) t3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

20. 时间序列–补全数据

表名：t20
表字段及内容：

date_id   a   b    c
2014     AB  12    bc
2015         23    
2016               d
2017     BC 
1
2
3
4
5

问题一：如何使用最新数据补全表格

输出结果如下所示：

date_id   a   b    c
2014     AB  12    bc
2015     AB  23    bc
2016     AB  23    d
2017     BC  23    d
1
2
3
4
5

参考答案:

select 
  date_id, 
  first_value(a) over(partition by aa order by date_id) as a,
  first_value(b) over(partition by bb order by date_id) as b,
  first_value(c) over(partition by cc order by date_id) as c
from
(
  select 
    date_id,
    a,
    b,
    c,
    count(a) over(order by date_id) as aa,
    count(b) over(order by date_id) as bb,
    count(c) over(order by date_id) as cc
  from t20
)tmp1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

21. 时间序列–取最新完成状态的前一个状态

表名：t21
表字段及内容：

date_id   a    b
2014     1    A
2015     1    B
2016     1    A
2017     1    B
2013     2    A
2014     2    B
2015     2    A
2014     3    A
2015     3    A
2016     3    B
2017     3    A
1
2
3
4
5
6
7
8
9
10
11
12

上表中B为完成状态。

问题一：取最新完成状态的前一个状态

输出结果如下所示：

date_id  a    b
2016     1    A
2013     2    A
2015     3    A
1
2
3
4

参考答案:
此处给出两种解法，其一：

select
    t21.date_id,
    t21.a,
    t21.b
from
    (
        select
            max(date_id) date_id,
            a
        from
            t21
        where
            b = 'B'
        group by
            a
    ) t1
    inner join t21 on t1.date_id -1 = t21.date_id
and t1.a = t21.a;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

其二：

select
  next_date_id as date_id
  ,a
  ,next_b as b
from(
  select
    *,min(nk) over(partition by a,b) as minb
  from(
    select
      *,row_number() over(partition by a order by date_id desc) nk
      ,lead(date_id) over(partition by a order by date_id desc) next_date_id
      ,lead(b) over(partition by a order by date_id desc) next_b
    from(
      select * from t21
    ) t
  ) t
) t
where minb = nk and b = 'B';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

问题二：如何将完成状态的过程合并

输出结果如下所示:

a   b_merge
1   A、B、A、B
2   A、B
3   A、A、B
1
2
3
4

参考答案：

select
  a
  ,collect_list(b) as b
from(
  select
    *
    ,min(if(b = 'B',nk,null)) over(partition by a) as minb
  from(
    select
      *,row_number() over(partition by a order by date_id desc) nk
    from(
      select * from t21
    ) t
  ) t
) t
where nk >= minb
group by a;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

22. 非等值连接–范围匹配

表f是事实表，表d是匹配表，在hive中如何将匹配表中的值关联到事实表中？

表d相当于拉链过的变化维，但日期范围可能是不全的。

表f：

date_id  p_id
 2017    C
 2018    B
 2019    A
 2013    C
1
2
3
4
5

表d：

d_start    d_end    p_id   p_value
 2016     2018     A       1
 2016     2018     B       2
 2008     2009     C       4
 2010     2015     C       3
1
2
3
4
5

问题一：范围匹配

输出结果如下所示：

date_id  p_id   p_value
 2017    C      null
 2018    B      2
 2019    A      null
 2013    C      3
1
2
3
4
5

**参考答案：
此处给出两种解法，其一：

select 
  f.date_id,
  f.p_id,
  A.p_value
from f 
left join 
(
  select 
    date_id,
    p_id,
    p_value
  from 
  (
    select 
      f.date_id,
      f.p_id,
      d.p_value
    from f 
    left join d on f.p_id = d.p_id
    where f.date_id >= d.d_start and f.date_id <= d.d_end
  )A
)A
ON f.date_id = A.date_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

其二：

select 
    date_id,
    p_id,
    flag as p_value
from (
    select 
        f.date_id,
        f.p_id,
        d.d_start,
        d.d_end,
        d.p_value,
        if(f.date_id between d.d_start and d.d_end,d.p_value,null) flag,
        max(d.d_end) over(partition by date_id) max_end
    from f
    left join d
    on f.p_id = d.p_id
) tmp
where d_end = max_end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

23. 非等值连接–最近匹配

表t23_1和表t23_2通过a和b关联时，有相等的取相等的值匹配，不相等时每一个a的值在b中找差值最小的来匹配。

t23_1和t23_2为两个班的成绩单，t23_1班的每个学生成绩在t23_2班中找出成绩最接近的成绩。

表t23_1：a中无重复值

表t23_2：b中无重复值

问题一：单向最近匹配

输出结果如下所示：
注意：b的值可能会被丢弃

参考答案：

select 
  * 
from
(
  select 
    ttt1.a,
    ttt1.b 
  from
  (
    select 
      tt1.a,
      t23_2.b,
      dense_rank() over(partition by tt1.a order by abs(tt1.a-t23_2.b)) as dr 
    from 
    (
      select 
        t23_1.a 
      from t23_1 
      left join t23_2 on t23_1.a=t23_2.b 
      where t23_2.b is null
    ) tt1 
    cross join t23_2
  ) ttt1 
  where ttt1.dr=1 
  union all
  select 
    t23_1.a,
    t23_2.b 
  from t23_1 
  inner join t23_2 on t23_1.a=t23_2.b
) result_t 
order by result_t.a;
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

24. N指标–累计去重

假设表A为事件流水表，客户当天有一条记录则视为当天活跃。

表A：

   time_id          user_id
2018-01-01 10:00:00    001
2018-01-01 11:03:00    002
2018-01-01 13:18:00    001
2018-01-02 08:34:00    004
2018-01-02 10:08:00    002
2018-01-02 10:40:00    003
2018-01-02 14:21:00    002
2018-01-02 15:39:00    004
2018-01-03 08:34:00    005
2018-01-03 10:08:00    003
2018-01-03 10:40:00    001
2018-01-03 14:21:00    005
1
2
3
4
5
6
7
8
9
10
11
12
13

假设客户活跃非常，一天产生的事件记录平均达千条。

问题一：累计去重

输出结果如下所示：

日期       当日活跃人数     月累计活跃人数_截至当日
date_id   user_cnt_act    user_cnt_act_month
2018-01-01      2                2
2018-01-02      3                4
2018-01-03      3                5
1
2
3
4
5

参考答案：

SELECT  tt1.date_id
       ,tt2.user_cnt_act
       ,tt1.user_cnt_act_month
FROM
(   -- ④ 按照t.date_id分组求出user_cnt_act_month，得到tt1
 SELECT  t.date_id
        ,COUNT(user_id) AS user_cnt_act_month
 FROM
 (   -- ③ 表a和表b进行笛卡尔积，按照a.date_id,b.user_id分组，保证截止到当日的用户唯一，得出表t。
  SELECT  a.date_id
         ,b.user_id
  FROM
  (   -- ① 按照日期分组，取出date_id字段当主表的维度字段 得出表a
   SELECT  from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd') AS date_id
   FROM test.temp_tanhaidi_20211213_1
   GROUP BY  from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd')
  ) a
  INNER JOIN
  (   -- ② 按照date_id、user_id分组，保证每天每个用户只有一条记录，得出表b
   SELECT  from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd') AS date_id
          ,user_id
   FROM test.temp_tanhaidi_20211213_1
   GROUP BY  from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd')
            ,user_id
  ) b
  ON 1 = 1
  WHERE a.date_id >= b.date_id
  GROUP BY  a.date_id
           ,b.user_id
 ) t
 GROUP BY  t.date_id
) tt1
LEFT JOIN
(   -- ⑥ 按照date_id分组求出user_cnt_act，得到tt2
 SELECT  date_id
        ,COUNT(user_id) AS user_cnt_act
 FROM
 (   -- ⑤ 按照日期分组，取出date_id字段当主表的维度字段 得出表a
  SELECT  from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd') AS date_id
         ,user_id
  FROM test.temp_tanhaidi_20211213_1
  GROUP BY  from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd')
           ,user_id
 ) a
 GROUP BY date_id
) tt2
ON tt2.date_id = tt1.date_id
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
36
37
38
39
40
41
42
43
44
45
46
47

相关阅读:
MySQL数据库入门到精通1--基础篇(MySQL概述，SQL)
（十一）React Ant Design Pro + .Net5 WebApi：后端环境搭建-IdentityServer4（三）持久化
 【Git】git分支的三种常见应用场景
 Spark性能调优案例-多表join优化，减少shuffle
【接口加密】接口加密的未来发展与应用场景
 OpenCV(四十):图像分割—漫水填充
 Springboot简单功能示例-6 使用加密数据源并配置日志
 Effective C++ 阅读笔记 02：构造/析构/赋值运算
 你最少用几行代码实现深拷贝？
课程目录《C语言程序设计：一个小球的编程之旅》
原文地址：https://blog.csdn.net/CLKTOY/article/details/126041694