本文共 1541 字，大约阅读时间需要 5 分钟。

数据库优化方案

CREATE DATABASE camera DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

2. 创建用户信息表在使用数据库前，需要创建存储用户信息的表，并指定字段的存储类型和字符集。

USE camera; CREATE TABLE act_use_userinfo ( uid VARCHAR(10) NOT NULL, app_name VARCHAR(20) NOT NULL, duration INT(10) NOT NULL, times INT(10) NOT NULL, dayno DATE NOT NULL );

3. 插入用户数据将用户相关数据插入数据库表中。

INSERT INTO act_use_userinfo (uid, app_name, duration, times, dayno) VALUES ('12345', '相机', 2, 3, '2021-05-18');

4. 数据分析与报表功能开发

4.1 某日活跃用户数：某日的去重用户数

SELECT dayno, COUNT(DISTINCT uid) AS active_users FROM act_use_userinfo WHERE app_name = '相机' GROUP BY dayno;

4.2 次日留存：使用两表自交，利用case when找到符合相差日期为1天的id，计数，得出次日留存人数，最后用distinct去重

实现方式一：通过增加临时字段

可以通过在表中增加一个临时字段 is_next_day，表示是否为次日用户，用于统计留存率。以下是实现方式：

ALTER TABLE act_use_userinfo ADD COLUMN is_next_day BOOLEAN After duration;

UPDATE act_use_userinfo SET is_next_day = 1 WHERE dayno = DATE('2021-05-18') + interval '1 day' AND uid IN (SELECT uid FROM act_use_userinfo WHERE app_name = '相机' AND dayno = DATE('2021-05-18'));

SELECT dayno, COUNT(DISTINCT uid) AS next_day_users FROM act_use_userinfo WHERE app_name = '相机' AND is_next_day = 1 GROUP BY dayno;

实现方式二：不增加临时字段

这种方法使用CTE（通用子查询）和ROW_NUMBER函数来计算次日留存人数。

WITH cte AS ( SELECT ROWNUMBER() OVER (ORDER BY dayno, times DESC) AS row_num, uid, dayno, app_name FROM act_use_userinfo WHERE app_name = '相机' ORDER BY dayno, times )SELECT dayno, COUNT(DISTINCT uid) AS next_day_users FROM cte WHERE row_num > 1 GROUP BY dayno;

4.3 其他需求（未详细说明）

根据实际需求继续扩展表结构和查询逻辑，确保所有分析指标能够准确反映业务需求。

转载地址：http://zhdgz.baihongyu.com/