# 167 信息流

### SQL162 2021年11月每天的人均浏览文章时长

### 描述

用户行为日志表tb\_user\_log

| id | uid | artical\_id | in\_time            | out\_time           | sign\_cin |
| -- | --- | ----------- | ------------------- | ------------------- | --------- |
| 1  | 101 | 9001        | 2021-11-01 10:00:00 | 2021-11-01 10:00:31 | 0         |
| 2  | 102 | 9001        | 2021-11-01 10:00:00 | 2021-11-01 10:00:24 | 0         |
| 3  | 102 | 9002        | 2021-11-01 11:00:00 | 2021-11-01 11:00:11 | 0         |
| 4  | 101 | 9001        | 2021-11-02 10:00:00 | 2021-11-02 10:00:50 | 0         |
| 5  | 102 | 9002        | 2021-11-02 11:00:01 | 2021-11-02 11:00:24 | 0         |

（uid-用户ID, artical\_id-文章ID, in\_time-进入时间, out\_time-离开时间, sign\_in-是否签到）

**场景逻辑说明**：**artical\_id-文章ID**代表用户浏览的文章的ID，**artical\_id-文章ID**为**0**表示用户在非文章内容页（比如App内的列表页、活动页等）。

**问题**：统计2021年11月每天的人均浏览文章时长（秒数），结果保留1位小数，并按时长由短到长排序。

**输出示例**：

示例数据的输出结果如下

| dt         | avg\_viiew\_len\_sec |
| ---------- | -------------------- |
| 2021-11-01 | 33.0                 |
| 2021-11-02 | 36.5                 |

解释：

11月1日有2个人浏览文章，总共浏览时长为31+24+11=66秒，人均浏览33秒；

11月2日有2个人浏览文章，总共时长为50+23=73秒，人均时长为36.5秒。

### 示例1

输入：

```
DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (101, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:31', 0),
  (102, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:24', 0),
  (102, 9002, '2021-11-01 11:00:00', '2021-11-01 11:00:11', 0),
  (101, 9001, '2021-11-02 10:00:00', '2021-11-02 10:00:50', 0),
  (102, 9002, '2021-11-02 11:00:01', '2021-11-02 11:00:24', 0);
```

复制

输出：

```
2021-11-01|33.0
2021-11-02|36.5
```

#### 题眼：

1.2021年11月：date\_format(in\_time, "%Y-%m") = "2021-11"

2.人均浏览时长，结果保留1位小数：round(sum(timestampdiff(second, in\_time, out\_time)) / count(distinct uid), 1) avg\_lensec。人均用 distinct uid就可以

3.每天：date之后group by就可以

```sql
select
	date(in_time) dt,
	round(sum(timestampdiff(second, in_time, out_time)) / count(distinct uid), 1) avg_lensec
from
	tb_user_log
where
	date_format(in_time, "%Y-%m") = "2021-11"
	and artical_id != 0
group by
	dt
order by
	avg_lensec
```

### SQL163 每篇文章同一时刻最大在看人数

### 描述

用户行为日志表tb\_user\_log

| id | uid | artical\_id | in\_time            | out\_time           | sign\_cin |
| -- | --- | ----------- | ------------------- | ------------------- | --------- |
| 1  | 101 | 9001        | 2021-11-01 10:00:00 | 2021-11-01 10:00:11 | 0         |
| 2  | 102 | 9001        | 2021-11-01 10:00:09 | 2021-11-01 10:00:38 | 0         |
| 3  | 103 | 9001        | 2021-11-01 10:00:28 | 2021-11-01 10:00:58 | 0         |
| 4  | 104 | 9002        | 2021-11-01 11:00:45 | 2021-11-01 11:01:11 | 0         |
| 5  | 105 | 9001        | 2021-11-01 10:00:51 | 2021-11-01 10:00:59 | 0         |
| 6  | 106 | 9002        | 2021-11-01 11:00:55 | 2021-11-01 11:01:24 | 0         |
| 7  | 107 | 9001        | 2021-11-01 10:00:01 | 2021-11-01 10:01:50 | 0         |

（uid-用户ID, artical\_id-文章ID, in\_time-进入时间, out\_time-离开时间, sign\_in-是否签到）

**场景逻辑说明**：**artical\_id-文章ID**代表用户浏览的文章的ID，**artical\_id-文章ID**为**0**表示用户在非文章内容页（比如App内的列表页、活动页等）。

**问题**：统计每篇文章同一时刻最大在看人数，如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，结果按最大人数降序。

**输出示例**：

示例数据的输出结果如下

| artical\_id | max\_uv |
| ----------- | ------- |
| 9001        | 3       |
| 9002        | 2       |

解释：10点0分10秒时，有3个用户正在浏览文章9001；11点01分0秒时，有2个用户正在浏览文章9002。

### 示例1

输入：

```
DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (101, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:11', 0),
  (102, 9001, '2021-11-01 10:00:09', '2021-11-01 10:00:38', 0),
  (103, 9001, '2021-11-01 10:00:28', '2021-11-01 10:00:58', 0),
  (104, 9002, '2021-11-01 11:00:45', '2021-11-01 11:01:11', 0),
  (105, 9001, '2021-11-01 10:00:51', '2021-11-01 10:00:59', 0),
  (106, 9002, '2021-11-01 11:00:55', '2021-11-01 11:01:24', 0),
  (107, 9001, '2021-11-01 10:00:01', '2021-11-01 10:01:50', 0);
```

复制

输出：

```
9001|3
9002|2
```

#### 题眼：

1.同一时刻最大在看人数：我们自然会想到常见的编码+联立。在此对原表in\_time和out\_time进行编码，in为观看人数+1， out为观看人数-1，进行两次SELECT联立，并按artical\_id升序，时间戳升序：

```sql
SELECT
  artical_id,
  MAX(instant_viewer_cnt) max_uv
FROM (
  SELECT
    artical_id,
    SUM(diff) OVER(PARTITION BY artical_id ORDER BY dt, diff DESC) instant_viewer_cnt
  FROM (
    SELECT 
      artical_id, in_time dt, 1 diff
    FROM tb_user_log
    WHERE artical_id != 0
    UNION ALL
    SELECT 
      artical_id, out_time dt, -1 diff
    FROM tb_user_log
    WHERE artical_id != 0) t1 
) t2
GROUP BY artical_id
ORDER BY max_uv DESC;
```

### SQL164 2021年11月每天新用户的次日留存率

### 描述

用户行为日志表tb\_user\_log

| id | uid | artical\_id | in\_time            | out\_time           | sign\_cin |
| -- | --- | ----------- | ------------------- | ------------------- | --------- |
| 1  | 101 | 0           | 2021-11-01 10:00:00 | 2021-11-01 10:00:42 | 1         |
| 2  | 102 | 9001        | 2021-11-01 10:00:00 | 2021-11-01 10:00:09 | 0         |
| 3  | 103 | 9001        | 2021-11-01 10:00:01 | 2021-11-01 10:01:50 | 0         |
| 4  | 101 | 9002        | 2021-11-02 10:00:09 | 2021-11-02 10:00:28 | 0         |
| 5  | 103 | 9002        | 2021-11-02 10:00:51 | 2021-11-02 10:00:59 | 0         |
| 6  | 104 | 9001        | 2021-11-02 11:00:28 | 2021-11-02 11:01:24 | 0         |
| 7  | 101 | 9003        | 2021-11-03 11:00:55 | 2021-11-03 11:01:24 | 0         |
| 8  | 104 | 9003        | 2021-11-03 11:00:45 | 2021-11-03 11:00:55 | 0         |
| 9  | 105 | 9003        | 2021-11-03 11:00:53 | 2021-11-03 11:00:59 | 0         |
| 10 | 101 | 9002        | 2021-11-04 11:00:55 | 2021-11-04 11:00:59 | 0         |

（uid-用户ID, artical\_id-文章ID, in\_time-进入时间, out\_time-离开时间, sign\_in-是否签到）

**问题**：统计2021年11月每天新用户的次日留存率（保留2位小数）

**注**：

* 次日留存率为当天新增的用户数中第二天又活跃了的用户数占比。
* 如果**in\_time-进入时间**和**out\_time-离开时间**跨天了，在两天里都记为该用户活跃过，结果按日期升序。

**输出示例**：

示例数据的输出结果如下

| dt         | uv\_left\_rate |
| ---------- | -------------- |
| 2021-11-01 | 0.67           |
| 2021-11-02 | 1.00           |
| 2021-11-03 | 0.00           |

解释：

11.01有3个用户活跃101、102、103，均为新用户，在11.02只有101、103两个又活跃了，因此11.01的次日留存率为0.67；

11.02有104一位新用户，在11.03又活跃了，因此11.02的次日留存率为1.00；

11.03有105一位新用户，在11.04未活跃，因此11.03的次日留存率为0.00；

11.04没有新用户，不输出。

### 示例1

输入：

```
DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (101, 0, '2021-11-01 10:00:00', '2021-11-01 10:00:42', 1),
  (102, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:09', 0),
  (103, 9001, '2021-11-01 10:00:01', '2021-11-01 10:01:50', 0),
  (101, 9002, '2021-11-02 10:00:09', '2021-11-02 10:00:28', 0),
  (103, 9002, '2021-11-02 10:00:51', '2021-11-02 10:00:59', 0),
  (104, 9001, '2021-11-02 10:00:28', '2021-11-02 10:00:50', 0),
  (101, 9003, '2021-11-03 11:00:55', '2021-11-03 11:01:24', 0),
  (104, 9003, '2021-11-03 11:00:45', '2021-11-03 11:00:55', 0),
  (105, 9003, '2021-11-03 11:00:53', '2021-11-03 11:00:59', 0),
  (101, 9002, '2021-11-04 11:00:55', '2021-11-04 11:00:59', 0);
```

复制

输出：

```
2021-11-01|0.67
2021-11-02|1.00
2021-11-03|0.00
```

解答：

```sql
select t1.dt, round(count(t2.uid) / count(t1.uid), 2) uv_rate
    from (select uid
                  ,
                 min(date(in_time)) dt
              from tb_user_log
              group by uid) as t1 -- 每天新用户表
             left join (select uid, date(in_time) dt
                            from tb_user_log
                        union
                        select uid, date(out_time)
                            from tb_user_log) as t2 -- 用户活跃表
                       on t1.uid = t2.uid
                           and t1.dt = date_sub(t2.dt, INTERVAL 1 day)
    where date_format(t1.dt, '%Y-%m') = '2021-11'
    group by t1.dt
    order by t1.dt
```

### **SQL165** **统计活跃间隔对用户分级结果**

#### 描述

用户行为日志表tb\_user\_log

| id | uid | artical\_id | in\_time            | out\_time           | sign\_cin |
| -- | --- | ----------- | ------------------- | ------------------- | --------- |
| 1  | 109 | 9001        | 2021-08-31 10:00:00 | 2021-08-31 10:00:09 | 0         |
| 2  | 109 | 9002        | 2021-11-04 11:00:55 | 2021-11-04 11:00:59 | 0         |
| 3  | 108 | 9001        | 2021-09-01 10:00:01 | 2021-09-01 10:01:50 | 0         |
| 4  | 108 | 9001        | 2021-11-03 10:00:01 | 2021-11-03 10:01:50 | 0         |
| 5  | 104 | 9001        | 2021-11-02 10:00:28 | 2021-11-02 10:00:50 | 0         |
| 6  | 104 | 9003        | 2021-09-03 11:00:45 | 2021-09-03 11:00:55 | 0         |
| 7  | 105 | 9003        | 2021-11-03 11:00:53 | 2021-11-03 11:00:59 | 0         |
| 8  | 102 | 9001        | 2021-10-30 10:00:00 | 2021-10-30 10:00:09 | 0         |
| 9  | 103 | 9001        | 2021-10-21 10:00:00 | 2021-10-21 10:00:09 | 0         |
| 10 | 101 | 0           | 2021-10-01 10:00:00 | 2021-10-01 10:00:42 | 1         |

（uid-用户ID, artical\_id-文章ID, in\_time-进入时间, out\_time-离开时间, sign\_in-是否签到）

**问题**：统计活跃间隔对用户分级后，各活跃等级用户占比，结果保留两位小数，且按占比降序排序。

**注**：

* 用户等级标准简化为：忠实用户(近7天活跃过且非新晋用户)、新晋用户(近7天新增)、沉睡用户(近7天未活跃但更早前活跃过)、流失用户(近30天未活跃但更早前活跃过)。
* 假设**今\*\*\*\*天**就是数据中所有日期的最大值。
* 近7天表示包含当天T的近7天，即闭区间\[T-6, T]。

**输出示例**：

示例数据的输出结果如下

| user\_grade | ratio |
| ----------- | ----- |
| 忠实用户        | 0.43  |
| 新晋用户        | 0.29  |
| 沉睡用户        | 0.14  |
| 流失用户        | 0.14  |

解释：

今天日期为2021.11.04，根据用户分级标准，**用户行为日志表tb\_user\_log**中忠实用户有：109、108、104；新晋用户有105、102；沉睡用户有103；流失用户有101；共7个用户，因此他们的比例分别为0.43、0.29、0.14、0.14。

### 示例1

输入：

```
DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (109, 9001, '2021-08-31 10:00:00', '2021-08-31 10:00:09', 0),
  (109, 9002, '2021-11-04 11:00:55', '2021-11-04 11:00:59', 0),
  (108, 9001, '2021-09-01 10:00:01', '2021-09-01 10:01:50', 0),
  (108, 9001, '2021-11-03 10:00:01', '2021-11-03 10:01:50', 0),
  (104, 9001, '2021-11-02 10:00:28', '2021-11-02 10:00:50', 0),
  (104, 9003, '2021-09-03 11:00:45', '2021-09-03 11:00:55', 0),
  (105, 9003, '2021-11-03 11:00:53', '2021-11-03 11:00:59', 0),
  (102, 9001, '2021-10-30 10:00:00', '2021-10-30 10:00:09', 0),
  (103, 9001, '2021-10-21 10:00:00', '2021-10-21 10:00:09', 0),
  (101, 0, '2021-10-01 10:00:00', '2021-10-01 10:00:42', 1);
```

复制

输出：

```
忠实用户|0.43
新晋用户|0.29
沉睡用户|0.14
流失用户|0.14
```

#### 解答

#### 明确题意：

统计活跃间隔对用户分级后，各活跃等级用户占比，结果保留两位小数，且按占比降序排序

***

#### 问题分解：

* 计算每个用户最早最晚活跃日期（作为子表t\_uid\_first\_last）：
  * 按用户ID分组：GROUP BY uid
  * 统计最早活跃：MIN(DATE(in\_time)) as first\_dt
  * 统计最晚活跃：MAX(DATE(out\_time)) as last\_dt
* 计算当前日期和总用户数（作为子表t\_overall\_info）：
  * 获取当前日期：MAX(DATE(out\_time)) as cur\_dt
  * 统计总用户数：COUNT(DISTINCT uid) as user\_cnt
* 左连接两表，即将全表统计信息追加到每一行上：t\_uid\_first\_last LEFT JOIN t\_overall\_info ON 1
* 计算最早最晚活跃离当前天数差（作为子表t\_user\_info）：
  * 最早活跃距今天数：TIMESTAMPDIFF(DAY,first\_dt,cur\_dt) as first\_dt\_diff
  * 最晚（最近）活跃距今天数：TIMESTAMPDIFF(DAY,last\_dt,cur\_dt) as last\_dt\_diff
* 计算每个用户的活跃等级：

  `CASE`` ``WHEN last_dt_diff >= ``30` `THEN ``"流失用户"`` ``WHEN last_dt_diff >= ``7` `THEN ``"沉睡用户"`` ``WHEN first_dt_diff < ``7` `THEN ``"新晋用户"`` ``ELSE ``"忠实用户"``END as user_grade`
* 统计每个等级的占比：
  * 按用户等级分组：GROUP BY user\_grade
  * 计算占比，总人数从子表得到，非聚合列避免语法错误加了MAX：COUNT(uid) / MAX(user\_cnt) as ratio
  * 保留2位小数：ROUND(x, 2)

***

#### 细节问题：

* 表头重命名：as
* 按占比降序排序：ORDER BY ratio DESC;;

```sql
SELECT user_grade, ROUND(COUNT(uid) / MAX(user_cnt), 2) as ratio
    FROM (SELECT uid,
                 user_cnt,
                 CASE
                     WHEN last_dt_diff >= 30 THEN 流失用户
                     WHEN last_dt_diff >= 7  THEN 沉睡用户
                     WHEN first_dt_diff < 7  THEN 新晋用户
                                             ELSE 忠实用户
                     END as user_grade
              FROM (SELECT uid,
                           user_cnt,
                           TIMESTAMPDIFF(DAY, first_dt, cur_dt) as first_dt_diff,
                           TIMESTAMPDIFF(DAY, last_dt, cur_dt) as last_dt_diff
                        FROM (SELECT uid,
                                     MIN(DATE(in_time)) as first_dt,
                                     MAX(DATE(out_time)) as last_dt
                                  FROM tb_user_log
                                  GROUP BY uid) as t_uid_first_last
                                 LEFT JOIN (SELECT MAX(DATE(out_time)) as cur_dt,
                                                   COUNT(DISTINCT uid) as user_cnt
                                                FROM tb_user_log) as t_overall_info ON 1) as t_user_info) as t_user_grade
    GROUP BY user_grade
    ORDER BY ratio DESC;
```

### SQL166 每天的日活数及新用户占比

### 描述

用户行为日志表tb\_user\_log

| id | uid | artical\_id | in\_time            | out\_time           | sign\_cin |
| -- | --- | ----------- | ------------------- | ------------------- | --------- |
| 1  | 101 | 9001        | 2021-10-31 10:00:00 | 2021-10-31 10:00:09 | 0         |
| 2  | 102 | 9001        | 2021-10-31 10:00:00 | 2021-10-31 10:00:09 | 0         |
| 3  | 101 | 0           | 2021-11-01 10:00:00 | 2021-11-01 10:00:42 | 1         |
| 4  | 102 | 9001        | 2021-11-01 10:00:00 | 2021-11-01 10:00:09 | 0         |
| 5  | 108 | 9001        | 2021-11-01 10:00:01 | 2021-11-01 10:00:50 | 0         |
| 6  | 108 | 9001        | 2021-11-02 10:00:01 | 2021-11-02 10:00:50 | 0         |
| 7  | 104 | 9001        | 2021-11-02 10:00:28 | 2021-11-02 10:00:50 | 0         |
| 8  | 106 | 9001        | 2021-11-02 10:00:28 | 2021-11-02 10:00:50 | 0         |
| 9  | 108 | 9001        | 2021-11-03 10:00:01 | 2021-11-03 10:00:50 | 0         |
| 10 | 109 | 9002        | 2021-11-03 11:00:55 | 2021-11-03 11:00:59 | 0         |
| 11 | 104 | 9003        | 2021-11-03 11:00:45 | 2021-11-03 11:00:55 | 0         |
| 12 | 105 | 9003        | 2021-11-03 11:00:53 | 2021-11-03 11:00:59 | 0         |
| 13 | 106 | 9003        | 2021-11-03 11:00:45 | 2021-11-03 11:00:55 | 0         |

（uid-用户ID, artical\_id-文章ID, in\_time-进入时间, out\_time-离开时间, sign\_in-是否签到）

**问题**：统计每天的日活数及新用户占比

**注**：

* 新用户占比=当天的新用户数÷当天活跃用户数（日活数）。
* 如果**in\_time-进入时间**和**out\_time-离开时间**跨天了，在两天里都记为该用户活跃过。
* 新用户占比保留2位小数，结果按日期升序排序。

**输出示例**：

示例数据的输出结果如下

| dt         | dau | uv\_new\_ratio |
| ---------- | --- | -------------- |
| 2021-10-30 | 2   | 1.00           |
| 2021-11-01 | 3   | 0.33           |
| 2021-11-02 | 3   | 0.67           |
| 2021-11-03 | 5   | 0.40           |

解释：

2021年10月31日有2个用户活跃，都为新用户，新用户占比1.00；

2021年11月1日有3个用户活跃，其中1个新用户，新用户占比0.33；

### 示例1

输入：

```
DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (101, 9001, '2021-10-31 10:00:00', '2021-10-31 10:00:09', 0),
  (102, 9001, '2021-10-31 10:00:00', '2021-10-31 10:00:09', 0),
  (101, 0, '2021-11-01 10:00:00', '2021-11-01 10:00:42', 1),
  (102, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:09', 0),
  (108, 9001, '2021-11-01 10:00:01', '2021-11-01 10:01:50', 0),
  (108, 9001, '2021-11-02 10:00:01', '2021-11-02 10:01:50', 0),
  (104, 9001, '2021-11-02 10:00:28', '2021-11-02 10:00:50', 0),
  (106, 9001, '2021-11-02 10:00:28', '2021-11-02 10:00:50', 0),
  (108, 9001, '2021-11-03 10:00:01', '2021-11-03 10:01:50', 0),
  (109, 9002, '2021-11-03 11:00:55', '2021-11-03 11:00:59', 0),
  (104, 9003, '2021-11-03 11:00:45', '2021-11-03 11:00:55', 0),
  (105, 9003, '2021-11-03 11:00:53', '2021-11-03 11:00:59', 0),
  (106, 9003, '2021-11-03 11:00:45', '2021-11-03 11:00:55', 0);
```

复制

输出：

```
2021-10-31|2|1.00
2021-11-01|3|0.33
2021-11-02|3|0.67
2021-11-03|5|0.40
```

#### 解答

和之前一样的逻辑，理顺了之后越做越快，6行代码搞定。

### 一、题目理解

* 统计每天的日活数及新用户占比
* 新用户占比=当天的新用户数÷当天活跃用户数（日活数）。
* 如果in\_time-进入时间和out\_time-离开时间跨天了，在两天里都记为该用户活跃过。
* 新用户占比保留2位小数，结果按日期升序排序。

**这几句话给的核心信息是，in\_time和out\_time都算作是活跃日，同时要把用户首次登录的日期找出来，最后再计算新用户占比。**

### 二、解题步骤

**1）老方法，先建立一张拥有基本信息的用户活跃基础表**

* 这张表要包含用户id，活跃日，成为新用户的日期。因为用户可能1天活跃N次，所以要做去重处理。
* 活跃日直接并联in\_time和out\_time
* 成为新用户日期，用窗口函数来取：MIN(DATE(in\_time))OVER(PARTITION BY uid) AS new\_dt

代码如下：

```
SELECT DISTINCT uid,DATE(in_time) dt,MIN(DATE(in_time))OVER(PARTITION BY uid) new_dt FROM tb_user_log``UNION``SELECT DISTINCT uid,DATE(out_time) dt,MIN(DATE(in_time))OVER(PARTITION BY uid) new_dt FROM tb_user_log
```

![img](https://uploadfiles.nowcoder.com/images/20220325/235454159_1648195152751/102060A826F4B48D309DFA54980818E5)

**2）定义新用户**

如果dt=new\_dt那这天就是用户首次登录成为新用户的日子啦\~

```
WITH t1 AS(``SELECT DISTINCT uid,DATE(in_time) dt,MIN(DATE(in_time))OVER(PARTITION BY uid) new_dt FROM tb_user_log``UNION``SELECT DISTINCT uid,DATE(out_time) dt,MIN(DATE(in_time))OVER(PARTITION BY uid) new_dt FROM tb_user_log``)``SELECT uid,dt,IF(dt=new_dt,``1``,``0``) ``'是否为新用户（是为1，不是为0）'``FROM t1;
```

![img](https://uploadfiles.nowcoder.com/images/20220325/235454159_1648195340690/74678B2ED37789EBF7EE220405F8E530)

**3）计算新用户占比，结果按照日期升序，输出结果。**

* 日活：COUNT(1)
* 新用户数：SUM（是否为新用户）
* 新用户占比：ROUND(SUM(新用户)/COUNT(1),2)

```
WITH t1 AS(``SELECT DISTINCT uid,DATE(in_time) dt,MIN(DATE(in_time))OVER(PARTITION BY uid) new_dt FROM tb_user_log``UNION``SELECT DISTINCT uid,DATE(out_time) dt,MIN(DATE(in_time))OVER(PARTITION BY uid) new_dt FROM tb_user_log``)``SELECT dt,COUNT(``1``) dau,ROUND(SUM(IF(dt=new_dt,``1``,``0``))/COUNT(``1``),``2``) uv_new_ratio``FROM t1 GROUP BY dt ORDER BY dt ASC;
```

![img](https://raw.githubusercontent.com/qkd90/figureBed/main/202407111746951.png)

完成啦~~是不是很好理解~~

```sql
WITH t1 AS (SELECT DISTINCT uid,
                            DATE(in_time) dt,
                            MIN(DATE(in_time)) OVER (
                                PARTITION BY
                                    uid
                                ) new_dt
                FROM tb_user_log
            UNION
            SELECT DISTINCT uid,
                            DATE(out_time) dt,
                            MIN(DATE(in_time)) OVER (
                                PARTITION BY
                                    uid
                                ) new_dt
                FROM tb_user_log)
SELECT dt,
       COUNT(1) dau,
       ROUND(SUM(IF(dt = new_dt, 1, 0)) / COUNT(1), 2) uv_new_ratio
    FROM t1
    GROUP BY dt
    ORDER BY dt ASC;

```

### **SQL167** **连续签到领金币**

### 描述

用户行为日志表tb\_user\_log

| id | uid | artical\_id | in\_time            | out\_time           | sign\_in |
| -- | --- | ----------- | ------------------- | ------------------- | -------- |
| 1  | 101 | 0           | 2021-07-07 10:00:00 | 2021-07-07 10:00:09 | 1        |
| 2  | 101 | 0           | 2021-07-08 10:00:00 | 2021-07-08 10:00:09 | 1        |
| 3  | 101 | 0           | 2021-07-09 10:00:00 | 2021-07-09 10:00:42 | 1        |
| 4  | 101 | 0           | 2021-07-10 10:00:00 | 2021-07-10 10:00:09 | 1        |
| 5  | 101 | 0           | 2021-07-11 23:59:55 | 2021-07-11 23:59:59 | 1        |
| 6  | 101 | 0           | 2021-07-12 10:00:28 | 2021-07-12 10:00:50 | 1        |
| 7  | 101 | 0           | 2021-07-13 10:00:28 | 2021-07-13 10:00:50 | 1        |
| 8  | 102 | 0           | 2021-10-01 10:00:28 | 2021-10-01 10:00:50 | 1        |
| 9  | 102 | 0           | 2021-10-02 10:00:01 | 2021-10-02 10:01:50 | 1        |
| 10 | 102 | 0           | 2021-10-03 10:00:55 | 2021-10-03 11:00:59 | 1        |
| 11 | 102 | 0           | 2021-10-04 10:00:45 | 2021-10-04 11:00:55 | 0        |
| 12 | 102 | 0           | 2021-10-05 10:00:53 | 2021-10-05 11:00:59 | 1        |
| 13 | 102 | 0           | 2021-10-06 10:00:45 | 2021-10-06 11:00:55 | 1        |

（uid-用户ID, artical\_id-文章ID, in\_time-进入时间, out\_time-离开时间, sign\_in-是否签到）

**场景逻辑说明**：

* **artical\_id-文章ID**代表用户浏览的文章的ID，特殊情况**artical\_id-文章ID**为**0**表示用户在非文章内容页（比如App内的列表页、活动页等）。注意：只有artical\_id为0时sign\_in值才有效。
* 从2021年7月7日0点开始，用户每天签到可以领1金币，并可以开始累积签到天数，连续签到的第3、7天分别可额外领2、6金币。
* 每连续签到7天后重新累积签到天数（即重置签到天数：连续第8天签到时记为新的一轮签到的第一天，领1金币）

**问题**：计算每个用户2021年7月以来每月获得的金币数（该活动到10月底结束，11月1日开始的签到不再获得金币）。结果按月份、ID升序排序。

**注**：如果签到记录的in\_time-进入时间和out\_time-离开时间跨天了，也只记作in\_time对应的日期签到了。

**输出\*\*\*\*示例：**

示例数据的输出结果如下：

| uid | month  | coin |
| --- | ------ | ---- |
| 101 | 202107 | 15   |
| 102 | 202110 | 7    |

解释：

101在活动期内连续签到了7天，因此获得1\*7+2+6=15金币；

102在10.01\~10.03连续签到3天获得5金币

10.04断签了，10.05\~10.06连续签到2天获得2金币，共得到7金币。

### 示例1

输入：

```
DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (101, 0, '2021-07-07 10:00:00', '2021-07-07 10:00:09', 1),
  (101, 0, '2021-07-08 10:00:00', '2021-07-08 10:00:09', 1),
  (101, 0, '2021-07-09 10:00:00', '2021-07-09 10:00:42', 1),
  (101, 0, '2021-07-10 10:00:00', '2021-07-10 10:00:09', 1),
  (101, 0, '2021-07-11 23:59:55', '2021-07-11 23:59:59', 1),
  (101, 0, '2021-07-12 10:00:28', '2021-07-12 10:00:50', 1),
  (101, 0, '2021-07-13 10:00:28', '2021-07-13 10:00:50', 1),
  (102, 0, '2021-10-01 10:00:28', '2021-10-01 10:00:50', 1),
  (102, 0, '2021-10-02 10:00:01', '2021-10-02 10:01:50', 1),
  (102, 0, '2021-10-03 11:00:55', '2021-10-03 11:00:59', 1),
  (102, 0, '2021-10-04 11:00:45', '2021-10-04 11:00:55', 0),
  (102, 0, '2021-10-05 11:00:53', '2021-10-05 11:00:59', 1),
  (102, 0, '2021-10-06 11:00:45', '2021-10-06 11:00:55', 1);
```

复制

输出：

```
101|202107|15
102|202110|7
```

#### 解答

比较好理解的思考方式是**根据需要的结果，一步一步反推自己需要什么的格式的数据**

1. 要求活动期间的签到获得的金币总数，那我最希望的是能够获得**每一天用户签到时获得的金币数**，然后只需要按照ID和month分组，**sum**一下就可以，如图

![alt](https://uploadfiles.nowcoder.com/images/20220130/920350351_1643512306066/4A47A0DB6E60853DEDFCFDF08A5CA249)

1. 再反推，想要获得**每一天用户签到时获得的金币数**，那么我必须知道，用户当天签到是**连续签到的第几天**，得到天数以后很简单了，用case when 将天数 % 7 ，看余数。 余数是3 ，当天获得 3枚。余数是 0 ，当天获得7枚 。其他为 1 枚 。如图

![alt](https://raw.githubusercontent.com/qkd90/figureBed/main/202407111747157.png)

1. 推到这里那其实思路已经清晰了，求**连续签到的天数**，那无非就是**连续问题**了
   1. 连续问题核心就是利用**排序编号与签到日期的差值是相等的**。因为如果是连续的话，编号也是自增1，日期也是自增1。
   2. 如图，***dt***&#x662F;签到日期，***dt\_tmp***&#x662F;编号和签到日期的差值。可以发现 编号 8 是断了连续签到的，所&#x4EE5;***dt\_tmp***&#x4E0E;前面的不相同

![alt](https://uploadfiles.nowcoder.com/images/20220130/920350351_1643512326887/09DD8C2662B96CE14928333F055C5580)

1. 那么再以dt\_tmp和 uid 来分组，&#x518D;***dense\_rank*** 一次，就可以获得连续签到的天数了。那么问题就解决了。

![alt](https://uploadfiles.nowcoder.com/images/20220130/920350351_1643512334153/8266E4BFEDA1BD42D8F9794EB4EA0A13)

```sql
WITH t1 AS ( -- t1表筛选出活动期间内的数据，并且为了防止一天有多次签到活动，distinct 去重
    SELECT DISTINCT uid,
                    DATE(in_time) dt,
                    DENSE_RANK() over (PARTITION BY uid ORDER BY DATE(in_time)) rn -- 编号
        FROM tb_user_log
        WHERE DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31'
          AND artical_id = 0
          AND sign_in = 1),
     t2 AS (SELECT *,
                   DATE_SUB(dt, INTERVAL rn day) dt_tmp,
                   case DENSE_RANK() over (PARTITION BY DATE_SUB(dt, INTERVAL rn day),uid ORDER BY dt ) % 7 -- 再次编号
                       WHEN 3 THEN 3
                       WHEN 0 THEN 7
                              ELSE 1
                       END as day_coin -- 用户当天签到时应该获得的金币数
                FROM t1)
SELECT uid,
       DATE_FORMAT(dt, '%Y%m') month,
       sum(day_coin) coin -- 总金币数
    FROM t2
    GROUP BY uid, DATE_FORMAT(dt, '%Y%m')
    ORDER BY DATE_FORMAT(dt, '%Y%m'), uid;

```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://qiangrens-organization.gitbook.io/qkd90/shu-ju-ku-he-zhong-jian-jian/sql-yu-ju/162167-xin-xi-liu.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.