跳到主要内容

设备型号活跃数据分析操作手册 (SQL指南)

核心概念：研发文档 | 设备型号活跃数据分析操作手册 (SQL指南) | 概览

适用对象: 运营人员、数据分析师
文档用途: 指导用户直接从数据库查询设备活跃、留存、流失等核心指标，包含基础查询与高级透视分析。

核心概念：研发文档 | 设备型号活跃数据分析操作手册 (SQL指南) | 1. 写在前面 (快速入门)

1. 写在前面 (快速入门)​

本手册提供了现成的 SQL 查询语句，你只需要：

  1. 复制 你需要的 SQL 语句。
  2. 粘贴 到你的数据库查询工具 (如 Navicat, DBeaver) 中。
  3. 运行 即可得到结果。

数据表说明​

  • 表名: device_model_activity_detail
  • 数据粒度: 每一行代表 "某一天" + "某一个设备型号" + "某个来源" 的数据汇总。
核心概念：研发文档 | 设备型号活跃数据分析操作手册 (SQL指南) | 2. 字段字典 (查表必备)

2. 字段字典 (查表必备)​

如果你想修改查询结果，请参考下表找到对应的字段名。

字段名 (英文)含义 (中文)说明
base_day统计日期格式如 20250701
device_model设备型号如 TV_BOX, PAD_PRO
source_type数据来源0=全部, 1=仅巴伦, 2=非巴伦
device_count日活跃数当日有多少设备活跃
new_active_count新增活跃当日新出现的活跃设备
dead_count流失设备数当日活跃但之后再也没出现过的设备
revive_N_ratioN天复活率比如 revive_7_ratio 代表7天后回流的比例
interval_N_firstN天后仍活跃比如 interval_30_first 代表30天后还在活跃
核心概念：研发文档 | 设备型号活跃数据分析操作手册 (SQL指南) | 3. 基础分析场景 (常用指标)

3. 基础分析场景 (常用指标)​

场景一：查看大盘走势 (总活跃数)​

目的: 了解每天整体有多少设备在活跃，趋势是涨是跌。

SELECT 
    base_day as 统计日期,
    SUM(device_count) as 总活跃设备数,
    SUM(new_active_count) as 总新增设备数,
    SUM(dead_count) as 总流失设备数
FROM device_model_activity_detail
WHERE source_type = 0  -- 0代表统计"全部"来源的数据
GROUP BY base_day
ORDER BY base_day DESC   -- 按日期倒序排列（最近的在最上面）
LIMIT 30;              -- 只看最近30天
核心概念：研发文档 | 设备型号活跃数据分析操作手册 (SQL指南) | 场景二：热门型号排行榜

场景二：热门型号排行榜​

目的: 找出平均日活最高的 20 个设备型号。

SELECT 
    device_model as 设备型号,
    ROUND(AVG(device_count), 0) as 平均日活, -- ROUND(..., 0) 表示不保留小数
    MAX(device_count) as 单日最高日活,
    SUM(new_active_count) as 累计新增活跃
FROM device_model_activity_detail
WHERE source_type = 0
GROUP BY device_model
ORDER BY 平均日活 DESC  -- 按平均日活从大到小排序
LIMIT 20;
核心概念：研发文档 | 设备型号活跃数据分析操作手册 (SQL指南) | 场景三：稳定性分析 (谁最稳？)

场景三：稳定性分析 (谁最稳？)​

目的: 找出哪些型号的日活波动最小（最稳定），哪些波动最大（可能异常）。 核心指标: 变异系数。数值越小，说明日活越稳定。

SELECT 
    device_model as 设备型号,
    COUNT(*) as 统计天数,
    ROUND(AVG(device_count), 0) as 平均日活,
    -- 变异系数 = 标准差 / 平均值。
    ROUND(STDDEV(device_count) / AVG(device_count), 4) as 波动率_变异系数
FROM device_model_activity_detail
WHERE source_type = 0
GROUP BY device_model
HAVING 平均日活 > 100 -- 过滤掉日活太小的型号（小样本波动大是正常的）
ORDER BY 波动率_变异系数 ASC -- ASC表示从小到大排，越靠前越稳定
LIMIT 20;
核心概念：研发文档 | 设备型号活跃数据分析操作手册 (SQL指南) | 场景四：流失预警 (哪天掉量最严重？)

场景四：流失预警 (哪天掉量最严重？)​

目的: 监控哪一天的流失率异常高，可能对应了系统故障或版本更新。

SELECT 
    base_day as 统计日期,
    SUM(device_count) as 当日活跃数,
    SUM(dead_count) as 确认为流失数, -- 指该日活跃后，后续所有统计周期内都未再活跃
    CONCAT(ROUND(SUM(dead_count) / SUM(device_count) * 100, 2), '%') as 流失率
FROM device_model_activity_detail
WHERE source_type = 0
GROUP BY base_day
ORDER BY 流失率 DESC -- 流失率最高的排在前面
LIMIT 10;
核心概念：研发文档 | 设备型号活跃数据分析操作手册 (SQL指南) | 场景五：回流能力分析 (复活率)

场景五：回流能力分析 (复活率)​

目的: 分析哪些型号的用户在断连后更容易回来。 如何修改天数:

  • SQL中的 revive_7_ratio 代表 7天复活率。
  • 如果你想看 30天复活率，请将代码中的 revive_7_ratio 改为 revive_30_ratio。
SELECT 
    device_model as 设备型号,
    -- 下面这行可以修改：revive_7_ratio -> revive_30_ratio
    ROUND(AVG(revive_7_ratio), 4) as 平均7天复活率, 
    ROUND(AVG(device_count), 0) as 平均日活
FROM device_model_activity_detail
WHERE source_type = 0
GROUP BY device_model
HAVING 平均日活 > 100
ORDER BY 平均7天复活率 DESC -- 复活率最高的排前面
LIMIT 20;
核心概念：研发文档 | 设备型号活跃数据分析操作手册 (SQL指南) | 场景六：巴伦 vs 非巴伦 对比分析

场景六：巴伦 vs 非巴伦 对比分析​

目的: 对比同一个型号在“巴伦”环境和“非巴伦”环境下的表现差异。

SELECT 
    device_model as 设备型号,
    -- 将数字代码转换为中文显示
    CASE source_type 
        WHEN 1 THEN '仅巴伦' 
        WHEN 2 THEN '非巴伦' 
    END as 来源类型,
    ROUND(AVG(device_count), 0) as 平均日活,
    ROUND(AVG(revive_7_ratio), 4) as 平均7天复活率
FROM device_model_activity_detail
WHERE source_type IN (1, 2) -- 只筛选巴伦(1)和非巴伦(2)的数据
  AND device_model = 'TV_BOX' -- 【注意】这里替换为你关心的具体型号！
GROUP BY device_model, source_type
ORDER BY source_type;
核心概念：研发文档 | 设备型号活跃数据分析操作手册 (SQL指南) | 4. 高级多维分析 (行转列与复合指标)

4. 高级多维分析 (行转列与复合指标)​

本部分包含更复杂的分析，特别是行转列 (Pivot) 功能，可以将重点关注的型号横向展开对比。

场景七：全量型号自动透视 (存储过程版)​

目的: 针对**1000+**个设备型号，自动生成一张超宽的大表。 特点:

  1. 全量: 包含所有型号，不只是 Top N。
  2. 动态: 型号再多也不怕，自动生成列。
  3. 智能排序: 活跃度越高的型号，自动排在列的最前面（左侧），方便查看。
  4. 灵活: 想看“日活”就传 device_count，想看“流失”就传 dead_count。

第一步：创建存储过程 (只需执行一次)​

请将下面的代码块完整复制到 SQL 编辑器中执行。

DELIMITER $$

DROP PROCEDURE IF EXISTS `pivot_all_models`$$

CREATE PROCEDURE `pivot_all_models`(IN metric_col VARCHAR(50), IN target_source_type INT)
BEGIN
    -- 1. 增加 group_concat 长度限制，防止型号太多导致 SQL 被截断
    SET SESSION group_concat_max_len = 1000000;

    -- 2. 动态构建列名：查询所有型号，并按"总活跃度"从大到小排序
    -- 生成格式：SUM(CASE WHEN device_model = 'TV_BOX' THEN metric ELSE 0 END) AS `TV_BOX`
    -- 特殊处理：将 NULL 或空字符串型号统一合并为 "未知"
    SELECT GROUP_CONCAT(
        CONCAT(
            'SUM(CASE WHEN ',
            CASE 
                WHEN safe_model = '未知' THEN 'IFNULL(device_model, '''') = ''''' 
                ELSE CONCAT('device_model = ''', safe_model, '''') 
            END,
            ' THEN ', metric_col, ' ELSE 0 END) AS `', safe_model, '`'
        )
        ORDER BY total_active DESC
        SEPARATOR ','
    ) INTO @sql_columns
    FROM (
        SELECT 
            IF(IFNULL(device_model, '') = '', '未知', device_model) as safe_model, 
            SUM(device_count) as total_active 
        FROM device_model_activity_detail 
        WHERE source_type = target_source_type 
        GROUP BY safe_model 
    ) t;

    -- 3. 拼接最终 SQL 语句
    -- 结果包含：统计日期 + 当日总量 + 各个型号的指标数据
    SET @sql_query = CONCAT('SELECT base_day AS 统计日期, SUM(', metric_col, ') AS 当日总数, ', @sql_columns, ' FROM device_model_activity_detail WHERE source_type = ', target_source_type, ' GROUP BY base_day ORDER BY base_day ');

    -- 4. 准备并执行动态 SQL
    PREPARE stmt FROM @sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

第二步：调用存储过程 (开始分析)​

参数说明:

  • 第一个参数: 指标列名 (如 'device_count')
  • 第二个参数: 数据来源 (0=全部, 1=仅巴伦, 2=非巴伦)

1. 查看全量【日活】 (按活跃度排序)

-- 查全部来源
CALL pivot_all_models('device_count', 0);

-- 查仅巴伦来源
CALL pivot_all_models('device_count', 1);

-- 查非巴伦来源
CALL pivot_all_models('device_count', 2);

2. 查看全量【新增】 (按活跃度排序)

-- 查全部来源
CALL pivot_all_models('new_active_count', 0);

-- 查仅巴伦来源
CALL pivot_all_models('new_active_count', 1);

-- 查非巴伦来源
CALL pivot_all_models('new_active_count', 2);

3. 查看全量【流失】 (按活跃度排序)

-- 查全部来源
CALL pivot_all_models('dead_count', 0);

-- 查仅巴伦来源
CALL pivot_all_models('dead_count', 1);

-- 查非巴伦来源
CALL pivot_all_models('dead_count', 2);

注意: 如果型号超过 2000 个，生成的表格可能会非常宽，建议导出为 Excel 查看。

核心概念：研发文档 | 设备型号活跃数据分析操作手册 (SQL指南) | 场景八：生命周期健康度分析 (复合指标)

场景八：生命周期健康度分析 (复合指标)​

目的: 通过计算指标判断设备生态是“正向增长”还是“由于流失而在萎缩”。 核心指标:

  1. 净增数: 新增 - 流失。正数代表增长。
  2. 更替比: 新增 / 流失。大于1代表入大于出（健康）。
  3. 新增率: 新增 / 日活。反映拉新能力。
SELECT 
    device_model as 设备型号,
    SUM(device_count) as 累计活跃人次,
    SUM(new_active_count) as 累计新增,
    SUM(dead_count) as 累计流失,
    
    -- 核心计算指标
    (SUM(new_active_count) - SUM(dead_count)) as 期间净增数,
    
    -- 更替比：如果大于1，说明新增超过流失；如果小于1，说明正在萎缩
    ROUND(SUM(new_active_count) / NULLIF(SUM(dead_count), 0), 2) as 更替比_健康度,
    
    -- 新增占比：越高说明该型号越"新"
    CONCAT(ROUND(SUM(new_active_count) / SUM(device_count) * 100, 2), '%') as 新增活跃占比
FROM device_model_activity_detail
WHERE source_type = 0
GROUP BY device_model
HAVING 累计活跃人次 > 1000 -- 只看有一定规模的型号
ORDER BY 期间净增数 DESC -- 净增最多的排前面
LIMIT 20;
核心概念：研发文档 | 设备型号活跃数据分析操作手册 (SQL指南) | 场景九：分来源的质量对比

场景九：分来源的质量对比​

目的: 查看到底是“巴伦”还是“非巴伦”渠道带来的用户质量更高（流失更少，更替比更高）。

SELECT 
    CASE source_type 
        WHEN 0 THEN '全部渠道'
        WHEN 1 THEN '仅巴伦' 
        WHEN 2 THEN '非巴伦' 
    END as 渠道来源,
    
    ROUND(AVG(device_count), 0) as 平均日活,
    
    -- 质量指标：更替比 (新增/流失)
    ROUND(SUM(new_active_count) / NULLIF(SUM(dead_count), 0), 2) as 更替比_健康度,
    
    -- 质量指标：平均7天复活率
    ROUND(AVG(revive_7_ratio), 4) as 平均7天复活率
FROM device_model_activity_detail
WHERE source_type IN (1, 2) -- 对比两个分渠道
GROUP BY source_type;
核心概念：研发文档 | 设备型号活跃数据分析操作手册 (SQL指南) | 5. 常见问题 (FAQ)

5. 常见问题 (FAQ)​

Q: 为什么有的复活率是 0？ A: 可能是因为数据采集时间还不够长。例如要计算“30天复活率”，必须要有至少30天之后的数据才能算出结果。如果是最近几天的数据，这个字段自然是0或空。

Q: 我想查特定日期的数据怎么办？ A: 在 SQL 的 WHERE 语句后面加上日期筛选。例如：

WHERE source_type = 0 
  AND base_day >= 20250701 
  AND base_day <= 20250731
AI 问答