企业官网建设流程全解析

前言

在前面实验中，我们已经将 800 多万条半结构化日志解析为 behavior_events 明细表，并完成了初步的 browser_coverage 和 browser_hourly 统计。但要支撑一个完整的浏览器市场分析大屏，还需要加工更多维度的目标表。本实验就是完成这些「弹药」的准备——基于 daily_browser_detail 用户-日-浏览器-小时明细表，依次加工出周活跃趋势、使用频率分布、浏览器使用数量分布、工作日与周末对比、核心指标概览以及用户画像统计共六类目标表。

一、实验说明

1.1 实验目的

• 基于「用户-日-浏览器-小时」明细表，完成数据大屏所需的各项统计表加工
• 包括浏览器市场格局统计、周活跃趋势、使用频率分布、使用数量分布、工作日周末对比、用户画像统计

1.2 实验环境

• 实验平台：助睿在线实验平台 HTTPS://lab.guilian.cn/
• 数据处理：助睿 ETL 数据集成平台
• 数据规模：1000 用户，800 万+条行为记录，约 825MB

二、实验数据

本实验基于上个实验产出的数据以及原始 demographic 用户属性表。上个实验已输出的数据包括 daily_browser_detail（用户-日-浏览器-小时明细表，将在本实验 6.1 节中输出）、browser_coverage（浏览器市场覆盖率统计表）、browser_hourly（浏览器时段活跃统计表）。

三、整体分析框架

3.1 业务问题与分析维度

在制作数据大屏之前，需要明确大屏要展示什么。大屏需要的是聚合后的统计结果而非原始明细，多个图表可能共用同一份聚合数据。本实验围绕以下十二个业务问题展开：

3.2 需要加工的目标表

大屏一目标表（市场行为分析）：

目标表对应维度数据来源 browser_overview核心指标 daily_browser_detailbrowser_coverage市场格局 daily_browser_detail（已输出）browser_weekly_active周活跃趋势 daily_browser_detailbrowser_hourly时段偏好 daily_browser_detail（已输出）browser_frequency_stats使用频率 daily_browser_detailbrowser_multi_usage浏览器使用数量 daily_browser_detailbrowser_cooccurrence竞品重叠 daily_browser_detailbrowser_weekday_weekend工作日 vs 周末

daily_browser_detail

大屏二目标表（用户画像分析）：user_profile_stats，按性别、年龄、学历、职业、收入、省份、居住地类型分组统计，数据来源为 demographic 和 daily_browser_detail。

四、实验步骤

4.1 准备用户-日-浏览器-小时明细表

上个实验的「互联网用户行为日志数据清洗抽取」转换流已经包含了生成明细数据的完整逻辑，但只输出了 browser_coverage 和 browser_hourly 两个分支。需要将其复制一份，改为输出明细表。

4.1.1 创建明细数据表

打开上个实验创建的项目「互联网用户行为日志」，新建转换流「创建用户_日_浏览器_小时明细表」。拖入「执行一个 SQL 脚本」组件，数据库连接选「团队私有数据库」，输入以下建表 SQL：

CREATE TABLE IF NOT EXISTS `daily_browser_detail` (`user_id` VARCHAR(50) NOT NULL COMMENT 『用户 ID』,`usage_date` DATE NOT NULL COMMENT 『使用日期』,`browser_name` VARCHAR(50) NOT NULL COMMENT 『浏览器名称』,`hour` TINYINT NOT NULL COMMENT 『小时』,`total_duration_sec` INT NOT NULL COMMENT 『总使用时长(秒)』,`active_count` INT NOT NULL COMMENT 『活跃次数』) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4COMMENT=『用户_日_浏览器_小时明细表』;

点击「运行」按钮，执行转换流。

4.1.2 复制转换流

在上个实验的项目中找到「互联网用户行为日志数据清洗抽取」转换流，右键选择「复制」。右键根目录，点击「粘贴」。粘贴后右键重命名为「输出用户日浏览器小时明细表」。

这里需要注意：上个实验中「排序记录 1」组件仅按照 process_name 升序排序，而分组组件的分组字段是 user_id、usage_date、process_name、hour，所以需要更正「排序记录 1」组件的排序字段与分组组件的分组字段一致，否则会出现多条重复数据。双击「排序记录 1」组件，将排序字段改为 user_id、usage_date、process_name、hour 四个字段全部升序。

4.1.3 浏览器名称映射

在分组组件后拖入「值映射」组件，值映射组件的输出端连接到原分支 A 的分组 1 组件，复制一份也发送到原分支 B 的排序记录 2 组件。双击值映射组件，按照以下映射表将进程名映射为浏览器中文名：

进程名映射为中文字段值 iexplore.exeIE 浏览器360chrome.exe360 极速360se.exe360sechrome.exeGooglesogouexplorer.exe搜狗QQBrowser.exeQQ

浏览器

注意：需要检查上个实验「4.5.3 过滤记录」中的匹配条件，如果匹配值包含了 EXCEL.EXE、WINWORD.EXE、AlilM.exe，需要删除，因为这三个不是浏览器。另外，转换流中分组组件的聚合类型如果是「个数」，需要改成「统计不同值的数量(N)」，并在分支 A 的「分组 1」组件前添加排序记录组件按 process_name 升序排序。

4.1.4 添加表输出并执行

拖拽「表输出」组件到画布中，值映射组件连接到表输出组件。双击配置：数据库连接选「团队私有数据库」，目标表选 daily_browser_detail，勾选「裁剪表」清空原有数据，勾选「指定数据库字段」建立字段映射。完成后点击「运行」按钮，执行转换流。

4.2 创建目标数据表

新建转换流「创建浏览器大屏分析目标数据表」，拖入「执行一个 SQL 脚本」组件。数据库连接选「团队私有数据库」，输入以下 SQL，使用 DROP TABLE 避免重复建表时报错，一次性创建 6 张目标表：

-- 1.核心指标概览表 DROP TABLE IF EXISTS `browser_overview`;CREATE TABLE `browser_overview` (`metric_name` VARCHAR(50) NOT NULL,`metric_value` DECIMAL(12,2) NOT NULL) COMMENT=『核心指标概览表』;-- 2.各浏览器周活跃趋势表 DROP TABLE IF EXISTS `browser_weekly_active`;CREATE TABLE `browser_weekly_active` (`browser_name` VARCHAR(50) NOT NULL,`week_range` VARCHAR(20) NOT NULL,`active_user_count` INT NOT NULL) COMMENT=『各浏览器周活跃趋势表』;-- 3.浏览器使用频率分布表 DROP TABLE IF EXISTS `browser_frequency_stats`;CREATE TABLE `browser_frequency_stats` (`browser_name` VARCHAR(50) NOT NULL,`usage_level` VARCHAR(10) NOT NULL,`user_count` INT NOT NULL) COMMENT=『浏览器使用频率分布表』;-- 4.用户使用浏览器数量分布表 DROP TABLE IF EXISTS `browser_multi_usage`;CREATE TABLE `browser_multi_usage` (`browser_count` VARCHAR(10) NOT NULL,`user_count` DECIMAL(5,2) NOT NULL) COMMENT=『用户使用浏览器数量分布表』;-- 5.浏览器工作日周末对比表 DROP TABLE IF EXISTS `browser_weekday_weekend`;CREATE TABLE `browser_weekday_weekend` (`browser_name` VARCHAR(50) NOT NULL,`day_type` VARCHAR(10) NOT NULL,`avg_duration_sec` INT NOT NULL,`total_duration_hour` BIGINT NOT NULL,`user_count` INT NOT NULL) COMMENT=『浏览器工作日周末对比表』;-- 6.用户画像统计表 DROP TABLE IF EXISTS `user_profile_stats`;CREATE TABLE `user_profile_stats` (`browser_name` VARCHAR(50) NOT NULL,`gender` VARCHAR(10), `age_group` VARCHAR(10),`edu` VARCHAR(50), `job` VARCHAR(50),`income` VARCHAR(50), `city_type` VARCHAR(10),`province` VARCHAR(50), `user_count` INT NOT NULL) COMMENT=『用户画像统计表』;

点击「运行」按钮，执行转换流。

4.3 各浏览器周活跃趋势表数据抽取

目标：统计每个浏览器在第 1-4 周的每周活跃用户数。

新建转换流「各浏览器周活跃趋势表数据抽取」。拖入「表输入」组件，数据库连接选「团队私有数据库」，获取 daily_browser_detail 的 SQL 查询语句。

拖入「字段选择」组件，创建连线。双击组件，点击「元数据」标签，右键插入一行，字段名输入 usage_date，类型选 Date，格式为「yyyy-MM-dd」。这一步将 usage_date 的字符串格式转为日期格式，便于后续值映射。

拖入「值映射」组件，创建连线。双击组件，使用的字段名选「usage_date」，目标字段名（空=覆盖）输入「week_range」，表示创建新字段 week_range 存储映射结果。插入行，将每个日期映射为对应的周区间：5/7-5/13 为第 1 周、6/4-6/10 为第 2 周、7/2-7/8 为第 3 周、8/6-8/12 为第 4 周。

拖入「排序记录」组件，创建连线，按 browser_name、week_range 升序排序。拖入「分组」组件，创建连线，分组字段为 browser_name 和 week_range，聚合时对 user_id 进行去重计数得到 active_user_count（聚合类型选「统计不同值的数量(N)」）。

拖入「表输出」组件，创建连线。数据库连接选「团队私有数据库」，目标表选 browser_weekly_active，勾选「裁剪表」和「指定数据库字段」，映射字段后执行转换流。

4.4 各浏览器使用频率分布表数据抽取

目标：按轻/中/重度划分用户使用频率。

新建转换流「使用频率分布数据抽取」。拖入「表输入」组件，数据库连接选「团队私有数据库」，获取 daily_browser_detail 的 SQL 查询。

拖入「排序记录」组件，创建连线，按 user_id、browser_name 升序排序。拖入「分组」组件，创建连线，分组字段为 user_id 和 browser_name，聚合：总使用时长 total_seconds = SUM(total_duration_sec)。

拖入「增加常量」组件，创建连线。新增字段 hour_m_s，类型设置为 Integer，值固定为 3600（一小时=3600 秒）。

拖入「计算器」组件，创建连线。新增字段 total_hours，计算公式为 A/B，字段 A 为 total_seconds，字段 B 为 hour_m_s，保留 2 位小数。这样就将秒转换成了小时。

拖入「JavaScript 代码」组件，创建连线。输入以下 JS 代码划分使用频率等级：

var total_hours = total_hours;var usage_level = 『』;if (total_hours < 3) {usage_level = 『轻度』;} else if (total_hours >= 3 && total_hours < 10) {usage_level = 『中度』;} else {usage_level = 『重度』;}

usage_level 字段需要在之前的「增加常量」组件中提前新增（类型 String，值留空）。点击 JS 组件的「获取变量」按钮自动获取变量。

拖入「排序记录」组件，创建连线，按 browser_name、usage_level 升序排序。拖入「分组」组件，按 browser_name 和 usage_level 分组，聚合 user_count 为 user_id 去重计数。拖入「表输出」组件，目标表选 browser_frequency_stats，裁剪表并映射字段，执行转换流。

4.5 各浏览器使用数量分布表数据抽取

目标：统计用户使用 1 种、2 种、3 种及以上浏览器的用户数。

新建转换流「浏览器使用数量分布数据抽取」。拖入「表输入」组件获取 daily_browser_detail 数据。拖入「排序记录」组件，按 user_id 升序排序。拖入「分组」组件，分组字段为 user_id，聚合：browser_cnt = 浏览器名称去重计数（统计不同值的数量(N)）。

拖入「JavaScript 代码」组件，创建连线。输入以下 JS 代码划分浏览器数量等级：

var browser_cnt = browser_cnt;var browser_count = 『』;if (browser_cnt == 1) {browser_count = 『1 种』;} else if (browser_cnt == 2) {browser_count = 『2 种』;} else {browser_count = 『3 种及以上』;}

点击「获取变量」，然后拖入「排序记录」按 browser_count 升序排序，拖入「分组」按 browser_count 分组统计 user_count（user_id 去重计数），拖入「表输出」到 browser_multi_usage 表，裁剪表并映射字段后执行。

4.6 各浏览器工作日周末对比表数据抽取

目标：统计各浏览器工作日与周末的使用时长对比。

新建转换流「浏览器工作日周末对比数据抽取」。拖入「表输入」组件获取 daily_browser_detail 数据。拖入「JavaScript 代码」组件，创建连线，输入以下 JS 代码判断日期类型：

var date = usage_date;var dayOfWeek = date.getDay();var day_type = 「」;if (dayOfWeek >= 1 && dayOfWeek <= 5) {day_type = 「工作日」;} else {day_type = 「周末」;}

点击「获取变量」。拖入「排序记录」组件，按 browser_name、day_type 升序排序。拖入「分组」组件，按 browser_name 和 day_type 分组，聚合：avg_seconds=平均使用时长、total_seconds=总使用时长、user_count=user_id 去重计数。

总使用时长单位是秒数值很大，不够直观。参考 4.4 节的方法，使用「增加常量」组件（常量 3600）和「计算器」组件（total_seconds/3600）将总使用时长转为小时。然后使用「字段选择」组件删除计算过程中产生的中间字段。

拖入「表输出」组件，目标表选 browser_weekday_weekend，裁剪表并映射字段后执行转换流。

4.7 核心指标数据抽取

目标：将大屏顶部四个指标卡的数据存入一张通用的键值对表中。

新建转换流，拖入「表输入」组件，数据库连接选「团队私有数据库」，在 SQL 语句框中输入以下 SQL 一次性算出四个指标：

SELECT

ROUND(SUM(total_duration_sec)/3600,2) AS total_hours,

ROUND(SUM(total_duration_sec)/3600/COUNT(DISTINCT user_id),2) AS avg_hours,

ROUND((SELECT COUNT(DISTINCT user_id) FROM daily_browser_detail

WHERE usage_date BETWEEN 『2012-08-06』 AND 『2012-08-12』

)*100.0/COUNT(DISTINCT user_id),2) AS active_ratio,

ROUND((SELECT COUNT(*) FROM (

SELECT user_id FROM daily_browser_detail

WHERE usage_date BETWEEN 『2012-05-07』 AND 『2012-07-08』

GROUP BY user_id HAVING SUM(total_duration_sec)/3600>30

) t)*100.0/COUNT(DISTINCT user_id),2) AS heavy_ratio

FROM daily_browser_detail

接下来使用「行转列」组件将字段名称转为指标名称、字段值转为指标值：total_hours→metric_name 为 total_hours、metric_value 为对应值，avg_hours、active_ratio、heavy_ratio 同理。然后使用「值映射」组件将指标名称映射为中文（total_hours→总使用时长、avg_hours→人均使用时长、active_ratio→活跃用户占比、heavy_ratio→重度用户占比）。

最后拖入「表输出」组件，目标表选 browser_overview，裁剪表并映射字段后执行。

4.8用户画像表加工

目标：统计每个浏览器按性别、年龄、学历、职业、收入、居住地类型、省份的用户分布。

4.8.1 获取人口属性数据

点击「公共空间」→「数据资源」，找到 demographic.csv 数据卡片。点击右上角「更多」→「导出」，选择导出到根目录，点击「确定」。刷新文件库即可看到 demographic.csv。

4.8.2 CSV 文件输入

新建转换流「用户画像表加工」。拖入「CSV 文件输入」组件，双击打开配置，点击「浏览文件」选择 demographic.csv，点击「确定」。列分隔符和封闭符保持不变，编码选「UTF-8」。在空白表格处右键「获取字段」，获取成功后点击「确认」。

4.8.3 年龄分段

原人口属性数据中有出生年份（BIRTHDAY 字段）但没有年龄字段。拖入「增加常量」组件，新增字段 year，值设为 2012（数据采集年份）。拖入「计算器」组件，计算 age = year - BIRTHDAY。拖入「JavaScript 代码」组件，将年龄划分为四段：

var age_group = 『』;

if (age < 18) {

age_group = 『<18』;

} else if (age <= 25) {

age_group = 『18-25』;

} else if (age <= 35) {

age_group = 『26-35』;

} else {

age_group = 『>35』;

}

4.8.4 读取明细数据并关联

拖入「表输入」组件，数据库连接选「团队私有数据库」，获取 daily_browser_detail 的 SQL 查询。现在有两个数据流：CSV 输入的属性数据流和表输入的行为数据流。

使用「记录集连接」组件将两个数据源通过 user_id 关联。注意：记录集连接之前必须对两个数据源分别排序。先拖入两个「排序记录」组件：排序记录 1 从表输入连线，按 user_id 升序（命名「明细数据按用户 ID 排序」）；排序记录 2 从 CSV 输入连线（连线类型选「主输出步骤」），按 user_id 升序。然后拖入「记录集连接」组件，第一步选排序记录 1，第二步选排序记录 2，连接类型选 INNER JOIN，连接字段都设为 user_id。

4.8.5 分组聚合与表输出

拖入「排序记录」组件，按 browser_name、GENDER、EDU、JOB、INCOME、PROVINCE、ISCITY、age_group 升序排序。拖入「分组」组件，按以上八个维度分组，聚合 user_count 为 user_id 去重计数（统计不同值的数量(N)）。

拖入「表输出」组件，目标表选 user_profile_stats，勾选「裁剪表」和「指定数据库字段」，映射字段后执行转换流。

4.8.6 验证结果

点击「元数据」标签，右键「团队私有数据库」选择「加载元数据」。进入「数据探查」页面，依次双击 browser_coverage、browser_hourly、browser_weekly_active、browser_frequency_stats、browser_multi_usage、browser_weekday_weekend、browser_overview、user_profile_stats 等目标表，切换到「查询」标签页，确认数据条数和数值范围符合预期。

五、实验总结

本次实验以 daily_browser_detail 用户-日-浏览器-小时明细表为核心数据源，通过助睿 ETL 平台依次完成了浏览器周活跃趋势、使用频率分布、浏览器使用数量分布、工作日与周末使用对比、核心指标概览以及用户画像统计共六类目标表的加工全流程。整个实验过程涵盖了表输入、字段选择、过滤记录、排序分组、值映射、JavaScript 代码、增加常量、计算器、记录集连接、行转列、CSV 文件输入等十余种 ETL 组件的组合使用，同时涉及 SQL 聚合查询、年龄计算与分段、使用频率分级、星期判断等数据处理技巧。关键操作经验总结如下：第一，分组聚合前务必确认排序字段与分组字段一致，否则会产生重复数据；第二，记录集连接之前必须对两个数据源分别按关联字段排序，这是 JOIN 操作正确执行的前提；第三，聚合类型中「统计不同值的数量(N)」等价于 SQL 的 COUNT(DISTINCT)，应优先使用而非简单「个数」；第四，浏览器名称映射使用值映射组件可在源头统一命名规范，避免后续分析时名称不一致；第五，涉及大量中间字段时及时用字段选择组件清理冗余，保持数据流清晰可控。至此，数据大屏所需的全部统计表加工完毕，下一实验将基于这些目标表在助睿 BI 中完成可视化大屏的搭建与发布。