文章列表： 12 篇
MySQL常用函数
live_max_online
MySQL常用30种SQL查询语句优化方法
SQL每日一题(20230814)
SQL每日一题F0215，多种方法及思路讲解
SQL每日一题F1021，while循环操作
SQL每日一题F1025，复杂逻辑处理
SQL每日一题F1028，关联子查询
top10_sql_skills
查询连续登陆3天的用户id和登陆天数
求占据前90%销售额的商品类型
给定用户登录表，求表中每一天的3天留存率
2023-09-19
MySQL常用函数
2023-09-19 ~ 2023-09-19
SQL

1、数学函数

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

ABS(x)         --返回x的绝对值
BIN(x)         --返回x的二进制（OCT返回八进制，HEX返回十六进制）
CEILING(x)     --返回大于x的最小整数值
EXP(x)         --返回值e（自然对数的底）的x次方
FLOOR(x)       --返回小于x的最大整数值
GREATEST(x1,x2,...,xn)
                --返回集合中最大的值
LEAST(x1,x2,...,xn)   
                --返回集合中最小的值
LN(x)           --返回x的自然对数
LOG(x,y)        --返回x的以y为底的对数
MOD(x,y)        --返回x/y的模（余数）
PI()            --返回pi的值（圆周率）
RAND()          --返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
ROUND(x,y)      --返回参数x的四舍五入的有y位小数的值
SIGN(x)         --返回代表数字x的符号的值
SQRT(x)         --返回一个数的平方根
TRUNCATE(x,y)   --返回数字x截短为y位小数的结果

2、聚合函数

1
2
3
4
5
6

AVG(X)            --返回指定列的平均值
COUNT(X)          --返回指定列中非NULL值的个数
MIN(X)            --返回指定列的最小值
MAX(X)            --返回指定列的最大值
SUM(X)            --返回指定列的所有值之和
GROUP_CONCAT(X)   --返回由属于一组的列值连接组合而成的结果，非常有用

3、字符串函数（20个）

····
2023-08-01
live_max_online
2023-08-01 ~ 2023-08-01
SQL
  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
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116

-- step0 建表
-- drop table cm.tb_live_logs;
create table cm.tb_live_logs
(
    live_id     int,
    user_id     int,
    ts  datetime,
    type varchar(10)
);

-- step1 插入数据
insert into cm.tb_live_logs
(live_id, user_id, ts, type) values
(901,1001,'2022-10-01 12:00:00','IN'),
(901,1002,'2022-10-01 12:01:00','IN'),
(901,1003,'2022-10-01 12:01:00','IN'),
(901,1004,'2022-10-01 12:02:00','IN'),
(901,1005,'2022-10-01 12:02:00','IN'),
(901,1006,'2022-10-01 12:03:00','IN'),
(901,1007,'2022-10-01 12:03:00','IN'),
(901,1008,'2022-10-01 12:05:00','IN'),
(901,1009,'2022-10-01 12:05:00','IN'),
(901,1010,'2022-10-01 12:06:03','IN'),
(902,1101,'2022-10-01 12:00:00','IN'),
(902,1102,'2022-10-01 12:01:00','IN'),
(902,1103,'2022-10-01 12:01:00','IN'),
(902,1104,'2022-10-01 12:02:00','IN'),
(902,1105,'2022-10-01 12:29:00','IN'),
(902,1106,'2022-10-01 12:30:00','IN'),
(902,1107,'2022-10-01 12:31:00','IN'),
(902,1108,'2022-10-01 12:32:00','IN'),
(902,1109,'2022-10-01 12:39:00','IN'),
(902,1110,'2022-10-01 12:06:03','IN'),
(901,1001,'2022-10-01 12:03:03','OUT'),
(901,1002,'2022-10-01 12:01:00','OUT'),
(901,1003,'2022-10-01 12:03:03','OUT'),
(901,1004,'2022-10-01 12:05:03','OUT'),
(901,1005,'2022-10-01 12:10:03','OUT'),
(901,1006,'2022-10-01 12:03:01','OUT'),
(901,1007,'2022-10-01 12:03:03','OUT'),
(901,1008,'2022-10-01 12:06:12','OUT'),
(901,1009,'2022-10-01 12:06:03','OUT'),
(901,1010,'2022-10-01 12:10:03','OUT'),
(902,1101,'2022-10-01 12:03:03','OUT'),
(902,1102,'2022-10-01 12:03:03','OUT'),
(902,1103,'2022-10-01 12:03:03','OUT'),
(902,1104,'2022-10-01 12:05:03','OUT'),
(902,1105,'2022-10-01 12:30:03','OUT'),
(902,1106,'2022-10-01 12:30:01','OUT'),
(902,1107,'2022-10-01 12:40:03','OUT'),
(902,1108,'2022-10-01 12:44:12','OUT'),
(902,1109,'2022-10-01 12:42:03','OUT'),
(902,1110,'2022-10-01 12:10:03','OUT');

-- step2 IN/OUT转数字
select
    live_id,
    user_id,
    ts ,
    IF(type = 'IN', 1, -1) as contribution
from cm.tb_live_logs;

-- step3 按照时间进行累加，求每个时间点的delta
select
    live_id,
    ts,
    sum(contribution) as new_number
from (select
        live_id,
        user_id,
        ts,
        IF(type = 'IN', 1, -1) as contribution
      from cm.tb_live_logs
      ) t1
group by live_id, ts;

-- step4 按照时间顺序累加
select
    *,
    sum(new_number) over (partition by live_id order by ts) as online_number
from (select
        live_id,
        ts,
        sum(contribution) as new_number
      from (select
                live_id,
                user_id,
                ts,
                IF(type = 'IN', 1, -1) as contribution
            from cm.tb_live_logs
            ) t1
      group by live_id, ts
      ) t2;

-- step5 求累加过程中，最大的值，就是同一时刻最大在线用户
select
    live_id,
    max(online_number) as max_online_number
from (select
        *,
        sum(new_number) over (partition by live_id order by ts) as online_number
        from (select
                live_id,
                ts,
                sum(contribution) as new_number
              from (select
                        live_id,
                        user_id,
                        ts,
                        IF(type = 'IN', 1, -1) as contribution
                    from cm.tb_live_logs
                    ) t1
              group by live_id, ts
              ) t2
        ) t3
group by live_id;
2023-08-01
MySQL常用30种SQL查询语句优化方法
2023-08-01 ~ 2023-08-01
SQL

引言

在开发和维护MySQL数据库时，优化SQL查询语句是提高数据库性能和响应速度的关键。通过合理优化SQL查询，可以减少数据库的负载，提高查询效率，为用户提供更好的用户体验。本文将介绍常用的30种MySQL SQL查询优化方法，并通过实际案例演示它们的应用。

····
2023-08-01
SQL每日一题(20230814)
2023-08-01 ~ 2023-08-01
SQL

SQL每日一题(20230814)

题目

有如下两张表G0227A（客户表）

IdName
1曹操
2关于
3刘备
4张飞

G0227B（订单表）

IdCustomerId
13
21

查询G0227B表（订单表）中找出从来没有买过商品的用户。

预计结果如下：

····
2023-08-01
SQL每日一题F0215，多种方法及思路讲解
2023-08-01 ~ 2023-08-01
SQL

SQL每日一题F0215，多种方法及思路讲解

  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
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132

CREATE TABLE F0215

(

StuID INT,

CID VARCHAR(10),

Course INT

)

  

INSERT INTO F0215 VALUES

(1,'001',67),

(1,'002',89),

(1,'003',94),

(2,'001',95),

(2,'002',88),

(2,'004',78),

(3,'001',94),

(3,'002',77),

(3,'003',90)

select * from f0215

/*

查询出既学过'001'课程，也学过'003'号课程的学生ID

*/

  

--错误写法

SELECT * FROM F0215

WHERE CID='001' AND CID='003'

  

SELECT * FROM F0215

WHERE CID='001' OR CID='003'

  

--思路一，取自连接符合条件的学生

SELECT T1.STUID FROM

( SELECT STUID FROM F0215 WHERE CID='001' ) T1

INNER JOIN

(SELECT STUID FROM F0215 WHERE CID='003' )  T2  

ON T2.STUID=T1.STUID

  
  

SELECT A.STUID

FROM F0215 A,F0215 B

WHERE A.CID = '001'

AND B.CID = '003'

AND A.STUID = B.STUID

  

--思路二，使用交集取出同时满足条件的学生

SELECT SC.STUID

FROM F0215 SC

WHERE  SC.CID='001'

INTERSECT

SELECT SC.STUID

FROM F0215 SC

WHERE  SC.CID='003'

  

--思路三

  

SELECT StuID FROM F0215

WHERE CID IN ('001','003')

GROUP BY StuID

HAVING COUNT(StuID)=2

  

--思路四（思路三的变体）

SELECT STUID FROM

(

SELECT STUID FROM F0215 WHERE CID = '001'

UNION ALL

SELECT STUID FROM F0215 WHERE CID = '003'

) A

GROUP BY STUID HAVING COUNT(STUID) = 2
2023-08-01
SQL每日一题F1021，while循环操作
2023-08-01 ~ 2023-08-01
SQL

SQL每日一题F1021，while循环操作

/* 写一个查询语句要求 求出整数1到100之间排除55后的和 预计结果是4995 该如何写这个查询？

要求：使用while循环 */

DECLARE @i INT DECLARE @sum INT SET @i=0 SET @sum=0 WHILE @i<100 BEGIN SET @i=@i+1 IF @i=55 CONTINUE ELSE SET @sum=@sum+@i END PRINT @sum

····
2023-08-01
SQL每日一题F1025，复杂逻辑处理
2023-08-01 ~ 2023-08-01
SQL

SQL每日一题F1025，复杂逻辑处理

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

create table F1025
(
id int,
num int
)

insert into F1025 values(1,5);
insert into F1025 values(2,11);
insert into F1025 values(3,0);
insert into F1025 values(4,-2);
insert into F1025 values(5,2);
insert into F1025 values(6,9);
insert into F1025 values(7,1);
insert into F1025 values(8,-4);
insert into F1025 values(9,-7);

– Q：要求当Num中的数据同时大于上下两行数据，返回值为“是”， – 当Num中的数据小于上下两行数据中的任何一行，返回值为“否” – 例如：11大于5,11大于0，所以11那行返回值为“是”；5小于11，所以5那行返回值为“否”

····
2023-08-01
SQL每日一题F1028，关联子查询
2023-08-01 ~ 2023-08-01
SQL

SQL每日一题F1028，关联子查询

create table F1028A (aid varchar(20), bid varchar(20) ) insert into F1028A values (‘跑步’,‘张三’); insert into F1028A values (‘游泳’,‘张三’); insert into F1028A values (‘跳远’,‘李四’); insert into F1028A values (‘跳高’,‘王五’);

····
2023-08-01
top10_sql_skills
2023-08-01 ~ 2023-08-01
SQL
  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
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137

-- 01
-- bad
create table cm.tb_user(
    id int,
    name varchar(255),
    age int,
    gender varchar(16)
);
-- good
create table cm.tb_user(
    id int primary key,
    name varchar(255),
    age int,
    gender varchar(16),
    store_time datetime default now()
);

-- 02
-- bad
create table cm.tb_user(
    id int primary key ,
    name varchar(255) not null default 'NULL',
    age int,
    gender varchar(16),
    create_time datetime default now(),
    update_time datetime default now()
);
-- good
create table cm.tb_user(
    id int primary key ,
    name varchar(255) not null default '-',
    age int,
    gender varchar(16),
    store_time datetime default now(),
    update_time datetime default now()
);

-- 03
-- bad
select min(age), max(age) from cm.tb_user where age < 40 group by gender;
-- good
select
    min(age),
    max(age)
from cm.tb_user
where age < 40
group by gender;

-- 04
-- bad
select id,
       name
from cm.tb_user
where age > 10
  and id<=10010;
-- good
select id,
       name
from cm.tb_user
where 1=1
  and age > 10
  and id<=10010;

-- 05
-- bad
insert into cm.tb_user
values
    (1001, 'Mike', 20, 'M');
-- good
insert into cm.tb_user
(id, name, age, gender)
values
    (1001, 'Mike', 20, 'M');

-- 06
-- good
explain
select *
from cm.tb_user
where id=1001 and age = 20;

-- 07
-- bad
select *
from cm.tb_user;
-- good
select id,
       name
from cm.tb_user;

-- 08
-- 分批次+limit进行delete或update
delete
from cm.tb_user
where age > 35
limit 5;

-- 09
-- bad
delete from cm.tb_user
where age <= 20;
update cm.tb_user
set age = age + 1;
-- good
begin;
delete from cm.tb_user
where age <= 20;
update cm.tb_user
set age = age + 1;
commit;

-- 10
-- data sample
insert into cm.data_count
(dt, count)
values
    ('2023-06-20', 101),
    ('2023-06-21', 231),
    ('2023-06-22', 170),
    ('2023-06-23', 146),
    ('2023-06-24', 187),
    ('2023-06-25', 123),
    ('2023-06-26', 221),
    ('2023-06-27', 101),
    ('2023-06-28', 103),
    ('2023-06-29', 122),
    ('2023-06-30', 144);
-- bad
select dt, count
from cm.data_count t1;
-- good
select
    t1.dt,
    t1.count * 1.0 / t2.count as ratio
from cm.data_count t1
    join cm.data_count t2
on datediff(t1.dt, t2.dt)=7;
2023-08-01
查询连续登陆3天的用户id和登陆天数
2023-08-01 ~ 2023-08-01
SQL
 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

-- 查询连续登陆3天的用户id和登陆天数
-- step1:用户登录日期去重
select distinct
    user_id,
    date(visit_time) as dt
from cm.tb_user_logs;

-- step2:用row_number()计数
select *,
       row_number() over (PARTITION by user_id order by dt) as xrank
from (select distinct
        user_id,
        date(visit_time) as dt
    from cm.tb_user_logs) a;

-- step3: 日期减去计数值得到差值delta
select *, date_sub(dt, INTERVAL xrank DAY) as delta
from (select *,
             row_number() over (PARTITION by user_id order by dt) as xrank
      from (select distinct
                user_id,
                date(visit_time) as dt
            from cm.tb_user_logs) a
      ) b;

-- step4:根据id和结果分组并计算总和,大于等于3的即为连续登录3天的用户
select user_id, min(dt) as start_date, count(*) as days
from (select *, date_sub(dt, INTERVAL xrank DAY) as delta
      from (select *,
                 row_number() over (PARTITION by user_id order by dt) as xrank
            from (select distinct
                      user_id,
                        date(visit_time) as dt
                  from cm.tb_user_logs) a
            ) b
      ) c
GROUP BY user_id, delta
having count(*) >= 3;
2023-08-01
求占据前90%销售额的商品类型
2023-08-01 ~ 2023-08-01
SQL
  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
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144

-- 求占据前90%销售额的商品类型
-- step0. 准备数据
create table cm.tb_sale_amount(
    good_category int comment '商品类型ID',
    sale_date date comment '销售日期',
    amount int comment '销售额',
    primary key(good_category, sale_date)
);


truncate cm.tb_sale_amount;

insert into cm.tb_sale_amount
(good_category, sale_date, amount) values
(1003, '2022-01-10', 264),
(1001, '2022-06-01', 21),
(1005, '2022-06-01', 73),
(1002, '2022-06-27', 44),
(1006, '2022-06-27', 405),
(1003, '2022-09-10', 16),
(1005, '2022-09-13', 72),
(1004, '2022-10-01', 29),
(1005, '2022-10-03', 332),
(1001, '2022-10-29', 10),
(1006, '2022-10-29', 137),
(1002, '2022-12-02', 23),
(1007, '2022-12-02', 19),
(1003, '2022-12-02', 30),
(1008, '2022-12-03', 3),
(1009, '2022-12-04', 1),
(1010, '2022-12-05', 9),
(1003, '2022-12-30', 121);

-- step1. 计算每种商品的总销售额，并降序排序
select
    good_category,
    sum(amount) as good_amount
from cm.tb_sale_amount
group by good_category
order by good_amount desc;

-- step2. 求全部商品的总销售额，为了step3求各种商品的占比，需要先求和。注意：求总和时，窗口值既不排序也不进行分组
select
    *,
    sum(good_amount) over () as all_amount
from
    (select
        good_category,
        sum(amount) as good_amount
    from cm.tb_sale_amount
    group by good_category
    order by good_amount desc
    ) t1;

-- step3. 求占比
select
    *,
    good_amount * 1.0 / all_amount as ratio
from (select
          *,
          sum(good_amount) over () as all_amount
      from (select
                good_category,
                sum(amount) as good_amount
            from cm.tb_sale_amount
            group by good_category
            order by good_amount desc
            ) as t1
      ) t2;

-- step4. 求累计占比，注意：求累计值时，一定要进行排序
select
    *,
    sum(ratio) over (order by ratio desc) as acc_ratio
from(
    select
        *,
        good_amount * 1.0 / all_amount as ratio
    from (select
              *,
              sum(good_amount) over () as all_amount
            from (select
                    good_category,
                    sum(amount) as good_amount
                from cm.tb_sale_amount
                group by good_category
                order by good_amount desc
            ) as t1
        ) t2
    ) t3;

-- step5. 求前一行的累计占比
select
    *,
    lag(acc_ratio) over() as pre_acc_ratio
from(select
         *,
       sum(ratio) over (order by ratio desc) as acc_ratio
    from(
        select
            *,
            good_amount * 1.0 / all_amount as ratio
        from (select
                  *,
                  sum(good_amount) over () as all_amount
              from (select
                        good_category,
                        sum(amount) as good_amount
                    from cm.tb_sale_amount
                    group by good_category
                    order by good_amount desc
                ) t1
            ) t2
        ) t3
    ) t4;

-- step6. 过滤
select *
from (
    select
    *,
    lag(acc_ratio) over() as pre_acc_ratio
from(select
         *,
       sum(ratio) over (order by ratio desc) as acc_ratio
     from(
        select
            *,
            good_amount * 1.0 / all_amount as ratio
        from (select
                  *,
                  sum(good_amount) over () as all_amount
              from (select
                        good_category,
                        sum(amount) as good_amount
                    from cm.tb_sale_amount
                    group by good_category
                    order by good_amount desc
                )  t1
              ) t2
        ) t3
    ) t4
) t5
where pre_acc_ratio IS NULL or pre_acc_ratio < 0.90;
2023-08-01
给定用户登录表，求表中每一天的3天留存率
2023-08-01 ~ 2023-08-01
SQL
 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
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

-- 给定用户登录表，求表中每一天的"3天留存率"

-- step 0 表准备，用户ID，登录时间
create table cm.tb_user_logs(
    user_id int,
    visit_time datetime
);

insert into cm.tb_user_logs
(user_id, visit_time)
values
(1001, '2022-10-01 08:01:23'),
(1001, '2022-10-01 08:11:15'),
(1002, '2022-10-01 08:22:19'),
(1003, '2022-10-01 09:00:53'),
(1002, '2022-10-02 18:00:13'),
(1004, '2022-10-02 13:30:43'),
(1004, '2022-10-02 15:06:22'),
(1005, '2022-10-02 08:00:39'),
(1002, '2022-10-03 08:00:13'),
(1003, '2022-10-03 18:00:13'),
(1004, '2022-10-03 21:00:13'),
(1006, '2022-10-03 22:00:13'),
(1001, '2022-10-04 11:10:13'),
(1002, '2022-10-04 12:00:13'),
(1002, '2022-10-04 09:00:13'),
(1004, '2022-10-04 08:00:13'),
(1006, '2022-10-04 08:00:13'),
(1004, '2022-10-05 08:00:13'),
(1005, '2022-10-05 08:00:13'),
(1002, '2022-10-05 08:00:43'),
(1003, '2022-10-05 12:00:13'),
(1004, '2022-10-05 10:00:43'),
(1006, '2022-10-05 08:00:11'),
(1001, '2022-10-06 09:00:47'),
(1001, '2022-10-06 07:00:15'),
(1002, '2022-10-06 18:00:43'),
(1003, '2022-10-07 20:00:19'),
(1002, '2022-10-07 21:00:23'),
(1004, '2022-10-07 22:00:43');

-- step 1 创建视图，进行用户ID和时间去重
create view A as
select distinct
       user_id,
       date(visit_time) as dt
from cm.tb_user_logs;

-- step2. 计算每天的"3天活跃用户数"，即对于每一天而言，在当天的活跃用户中，3天后还活跃的那些用户---分子
select
    t1.dt      as dt,
    count(t1.user_id) as 3day_active_cnt
from A as t1
         join A t2 on t1.dt = t2.dt - 3
where 1 = 1
  and t1.user_id = t2.user_id
group by t1.dt;

-- step3. 计算每天的活跃用户数---分母
select
    dt,
    count(user_id) as active_cnt
from A
group by dt;

-- step4. 将上述两个步骤结果，按照同一天的日期，进行关联，求比率即可
select t3.dt,
       t3.3days_active_cnt,
       t4.active_cnt,
       t3.3days_active_cnt * 1.0 / t4.active_cnt as 3day_alive_ratio
from (select t1.dt,
             count(t1.user_id) as 3days_active_cnt
            from A as t1
                     join A t2 on t1.dt = t2.dt - 3
            where 1=1
                and t1.user_id = t2.user_id
            group by dt) as t3
         join
         (select
              dt,
              count(user_id) as active_cnt
          from A
          group by dt
          ) as t4
         on t3.dt = t4.dt
order by t3.dt;

select distinct (user_id)
from cm.tb_user_logs
where date(visit_time)='2022-10-01';

select distinct (user_id)
from cm.tb_user_logs
where date(visit_time)='2022-10-04';