HQL Notes | BiliBili Internship - Z指数用户数据监控系统
前情提要：
所谓全埋点，就是将预先定义的某类事件（通常包括这四种事件类型：APP启动、APP关闭、页面访问、元素点击），通过SDK实现完全自动上报。
SPM码的全称是 super position model（超级位置模型），它通过五个字符串，将一个页面功能位置，唯一标识出来，下面是一个SPM码的格式：
SPM=SPMA.SPMB.SPMC.SPMD.SPME
SPMA 唯一标识一个站点
SPMB 唯一标识某站点的一个页面
SPMC 唯一标识某页面的一个区块
SPMD 唯一标识某区块的一个具体位置
SPME 随机生成的字串，跟时间有关系，在循环页面计算时可以区分点击的时序
Hive SQL开发：
DAG图：Z指数各模块日访问明细
埋点信息底表：b_dwd.dwd_flow_ubt_web_ad_event_l_hr
重要的字段：
event_id：埋点事件信息字段
buvid：设备号
bid：外网用户id
uid：内网用户id
url：co和com区分内外网
module：不同访问模块
bid_role：不同类型用户
能实现T+1的准确数据交付目标
第一次更新：
- 将小程序埋点信息数据加入，统一web和小程序的口径，区别：web端是通过不同埋点上报信息来区分不同模块访问行为，xcx端是通过module字段来区分；
- web埋点：rlike 999.173
- xcx埋点：999.194.0.0.pv
- 回刷历史数据
- 修改BI看板 加入小程序筛选项与数据类别
第二次更新：
- 发现原先外网未登录类型用户未计入MAU与DAU，我沟通后修改了口径，增加外网未登录，整套区分用户身份字段换新：coalesece(bid or uid, buvid)：将设备号作为未登录用户id计入MAU统计，结果显示web与小程序端总共增加了700+MAU数量，占原先的60%，口径更新后数据增幅直接完成了team里Z指数产品姐姐的okr。
- 修补id字段数据缺失bug：有过登录的小程序用户，但是部分事件数据没有id数据，因为刚点进小程序首页在未登录状态被埋点系统上报了，后续有点进个人页面进行登录，因此显示同一设备号但不同类型用户：外网未登录/个人。修补做法，建立设备号与id单一映射关系，再进行join补全。
- 修补MAU数据缺失bug：某日某种角色0人使用平台，导致DAU为0，导致该角色MAU数据在当日缺失，我修改了能跑得对。（这段补丁GPT帮我写的，反正能跑通）
- 回刷历史数据
- 修改BI看板：发现整体UV与不同角色MAU分布中的总计数字无法对齐（有个位数的出入），完成数据明细探查后给到指标解释：部分用户在时间周期内存在角色变化的情况，因此在MAU分布的总计中可能在不同角色下被重复统计。
-- HQL任务开发必读：https://info.bilibili.co/pages/viewpage.action?pageId=231965315#id-%E6%95%B0%E6%8D%AE%E5%BC%80%E5%8F%91BestPractice-%E6%9C%80%E4%BD%B3HQL%E4%BD%BF%E7%94%A8%E5%A7%BF%E5%8A%BF
-- Author: chenmingyang01
-- CreateTime: 2025-05-15 14:28:35
-- Description: 测试项目测试表
-- Copyright: bilibili.com
-- Modify: 修改时间: 2024-05-15 14:28:35
-- 修改人: chenmingyang01
-- 修改内容: 创建
--
--
-- 平台变量简介(详细请点击本页面右上角)：
--
-- 常用天变量${yyyyMMdd}，例如20210329
-- 常用小时变量${HH}，例如02
-- 常见查询： where log_date='${yyyyMMdd}' and log_hour='${HH}'
WITH
-- 小程序数据
xcx_data AS (
SELECT
buvid,
event_id,
extended_fields['user_name'] as user_name,
extended_fields['first_level_module'] as first_level_module,
extended_fields['second_level_module'] as second_level_module,
extended_fields['bid'] as outer_id,
extra['uid'] as inner_id,
extended_fields['bid_role'] as bid_role,
extended_fields['dept_name_seq'] as dept_name,
extended_fields['org_name'] as org_name,
extended_fields['report_name'] as report_name,
url
FROM
b_dwd.dwd_flow_ubt_web_ad_event_l_hr
WHERE
log_date = '${yyyyMMdd}'
AND event_id = '999.194.0.0.pv'
AND extended_fields['first_level_module'] is not null
),
-- 下面这个子表的作用也是将小程序用户中有过登录但是没有outer_id的数据行补全（可能是刚刚点进小程序没有登录，但是埋点已经上报了）
buvid2bidplus AS (
SELECT DISTINCT
buvid,
user_name,
outer_id,
inner_id,
bid_role,
dept_name,
org_name
FROM
xcx_data
WHERE
outer_id IS NOT NULL
),
-- 小程序最终子表
xcx_data_new AS (
SELECT
xcx_data.buvid,
xcx_data.event_id,
COALESCE(buvid2bidplus.user_name, xcx_data.user_name) AS user_name,
xcx_data.first_level_module,
xcx_data.second_level_module,
COALESCE(buvid2bidplus.outer_id, xcx_data.buvid) AS outer_id,
COALESCE(buvid2bidplus.inner_id, xcx_data.inner_id) AS inner_id,
COALESCE(buvid2bidplus.bid_role, xcx_data.bid_role) AS bid_role,
COALESCE(buvid2bidplus.dept_name, xcx_data.dept_name) AS dept_name,
COALESCE(buvid2bidplus.org_name, xcx_data.org_name) AS org_name,
xcx_data.report_name,
xcx_data.url
FROM
xcx_data
LEFT JOIN buvid2bidplus ON xcx_data.buvid = buvid2bidplus.buvid
),
-- web端数据
web_data AS (
SELECT
buvid,
event_id,
extended_fields['user_name'] as user_name,
extended_fields['bid'] as outer_id,
extended_fields['uid'] as inner_id,
extended_fields['bid_role'] as bid_role,
extended_fields['dept_name_seq'] as dept_name,
extended_fields['org_name'] as org_name,
extended_fields['report_name'] as report_name,
url
FROM
b_dwd.dwd_flow_ubt_web_ad_event_l_hr
WHERE
log_date = '${yyyyMMdd}'
AND event_id rlike '999.173'
AND url is not null
),
buvid2outeridplus AS (
SELECT DISTINCT
buvid,
user_name,
outer_id,
inner_id,
bid_role,
dept_name,
org_name
FROM
web_data
WHERE
outer_id IS NOT NULL
),
-- 这个表是用来将外部用户中有过登录但是没有outerid的数据行补全
web_data_middle AS (
SELECT
web_data.buvid,
web_data.event_id,
COALESCE(web_data.user_name, buvid2outeridplus.user_name) AS user_name,
COALESCE(web_data.outer_id, buvid2outeridplus.outer_id) outer_id,
web_data.inner_id,
COALESCE(web_data.bid_role, buvid2outeridplus.bid_role) AS bid_role,
COALESCE(web_data.dept_name, buvid2outeridplus.dept_name) AS dept_name,
COALESCE(web_data.org_name, buvid2outeridplus.org_name) AS org_name,
web_data.report_name,
web_data.url
FROM
web_data
LEFT JOIN buvid2outeridplus ON web_data.buvid = buvid2outeridplus.buvid
),
-- web端最终子表
web_data_new as (
SELECT
buvid,
event_id,
user_name,
CASE
when (outer_id is null)
and (inner_id is null) then buvid
else outer_id
end as outer_id, -- 将外网未登录类别中的buvid当做outer_id，外网未登录就是外部用户
inner_id,
bid_role,
dept_name,
org_name,
report_name,
url
FROM
web_data_middle
WHERE
not (
outer_id is null
AND not (url rlike 'com')
AND inner_id is null
) -- 删掉那些内网用户的数据行中没有inner_id的数据行（脏数据）
)
INSERT overwrite table
bi_sycpb.zindex_user_behavior_i_d partition (log_date = '${yyyyMMdd}') (
SELECT
case event_id
when '999.173.0.0' THEN '整体'
when '999.173.insight.detail.click' THEN '营销洞察报告下载'
when '999.173.zindex.brandall.show' THEN '品牌综合榜'
when '999.173.zindex.brandsearch.show' THEN '单品带货榜'
when '999.173.zindex.content.show' THEN '内容指数'
when '999.173.zindex.homepage.show' THEN '首页'
when '999.173.zindex.insight.show' THEN '营销洞察'
when '999.173.zindex.categorysearch.show' THEN '品牌搜索榜'
ELSE '其他'
END AS module,
user_name,
case
when outer_id is NOT null THEN outer_id
ELSE inner_id
end AS bid, -- or we can: COALESCE(outer_id, inner_id) AS bid,
case
when bid_role = -1 THEN '个人'
when bid_role = 2
or bid_role = 4 THEN '代理'
when bid_role = 1 THEN '品牌方'
ELSE if (url rlike 'com', '外网未登录', '内部用户') -- z指数内部用户登录的网址是co结尾，但是外部用户登录的网址是com结尾
end AS user_role,
dept_name,
org_name,
report_name,
if (url rlike 'com', 1, 0) as outer_user,
'web' as platform,
case
when outer_id is NOT null THEN outer_id
ELSE inner_id
end AS user_id
FROM
web_data_new
) -- 这个是web端的数据终表
union ALL
(
select
first_level_module as module,
user_name,
COALESCE(outer_id, inner_id) AS bid,
case
when bid_role = -1 THEN '个人'
when bid_role = 2
or bid_role = 4 THEN '代理'
when bid_role = 1 THEN '品牌方'
ELSE '外网未登录'
end AS user_role,
dept_name,
org_name,
report_name,
1 as outer_user,
'小程序' as platform,
COALESCE(outer_id, inner_id) AS user_id
FROM
xcx_data_new
) -- 这个是小程序端的module分明细终表
union ALL
(
select
'整体' as module,
user_name,
COALESCE(outer_id, inner_id) AS bid,
case
when bid_role = -1 THEN '个人'
when bid_role = 2
or bid_role = 4 THEN '代理'
when bid_role = 1 THEN '品牌方'
ELSE '外网未登录'
end AS user_role,
dept_name,
org_name,
report_name,
1 as outer_user,
'小程序' as platform,
COALESCE(outer_id, inner_id) AS user_id
FROM
xcx_data_new
) -- 这个是小程序端的module整体终表
with
t1 as (
SELECT
platform_type,
user_role,
outer_user,
cast(
regexp_replace(log_date, '(\\d{4})(\\d{2})(\\d{2})', '$1-$2-$3') AS DATE
) AS log_date,
COUNT(DISTINCT user_info) as user_cnt
FROM
(
select
platform_type,
user_role,
outer_user,
log_date,
concat(outer_user, '_', user_id) as user_info
from
bi_sycpb.zindex_user_behavior_i_d
WHERE
log_date BETWEEN '${yyyyMMdd,-1d}' and '${yyyyMMdd}'
)
group by
platform_type,
user_role,
outer_user,
log_date
),
t2 AS (
SELECT
c.platform_type,
c.user_role,
c.outer_user,
c.log_date,
COALESCE(c.user_cnt - d.user_cnt, 0) as user_change
FROM
t1 c
left join t1 d on DATEDIFF(c.log_date, d.log_date) = 1
AND c.user_role = d.user_role
AND c.platform_type = d.platform_type
WHERE
d.log_date is not null
),
t3 AS (
select
platform_type,
user_role,
outer_user,
cast(
regexp_replace(
'${yyyyMMdd}',
'(\\d{4})(\\d{2})(\\d{2})',
'$1-$2-$3'
) AS DATE
) AS log_date,
COUNT(DISTINCT outer_user, user_id) as mau_cnt
from
bi_sycpb.zindex_user_behavior_i_d
WHERE
log_date BETWEEN '${yyyyMMdd,-29d}' and '${yyyyMMdd}'
group by
platform_type,
user_role,
outer_user
),
date_range AS (
SELECT
'${yyyyMMdd,-1d}' AS raw_log_date
UNION ALL
SELECT
'${yyyyMMdd}' AS raw_log_date
),
distinct_users AS (
SELECT DISTINCT
user_role,
outer_user
FROM
bi_sycpb.zindex_user_behavior_i_d
WHERE
log_date BETWEEN '${yyyyMMdd,-1d}' and '${yyyyMMdd}'
),
full_combinations AS (
SELECT
du.user_role,
du.outer_user,
dr.raw_log_date
FROM
distinct_users du
CROSS JOIN date_range dr
),
t4 as (
SELECT
fc.user_role,
fc.outer_user,
CAST(
REGEXP_REPLACE(
fc.raw_log_date,
'(\\d{4})(\\d{2})(\\d{2})',
'$1-$2-$3'
) AS DATE
) AS log_date,
COUNT(
DISTINCT CASE
WHEN t.user_info IS NOT NULL THEN t.user_info
END
) AS user_cnt
FROM
full_combinations fc
LEFT JOIN (
SELECT
user_role,
outer_user,
log_date,
CONCAT(outer_user, '_', user_id) AS user_info
FROM
bi_sycpb.zindex_user_behavior_i_d
WHERE
log_date BETWEEN '${yyyyMMdd,-1d}' and '${yyyyMMdd}'
) t ON fc.raw_log_date = t.log_date
AND fc.user_role = t.user_role
AND fc.outer_user = t.outer_user
GROUP BY
fc.user_role,
fc.outer_user,
fc.raw_log_date
ORDER BY
fc.user_role,
fc.outer_user,
fc.raw_log_date
),
t5 AS (
SELECT
c.user_role,
c.outer_user,
c.log_date,
COALESCE(c.user_cnt - d.user_cnt, 0) as user_change
FROM
t4 c
left join t4 d on DATEDIFF(c.log_date, d.log_date) = 1
AND c.user_role = d.user_role
WHERE
d.log_date is not null
),
t6 AS (
select
user_role,
outer_user,
cast(
regexp_replace(
'${yyyyMMdd}',
'(\\d{4})(\\d{2})(\\d{2})',
'$1-$2-$3'
) AS DATE
) AS log_date,
COUNT(DISTINCT outer_user, user_id) as mau_cnt
from
bi_sycpb.zindex_user_behavior_i_d
WHERE
log_date BETWEEN '${yyyyMMdd,-29d}' and '${yyyyMMdd}'
group by
user_role,
outer_user
),
t7 AS (
SELECT
t4.user_role,
t4.outer_user,
t4.user_cnt,
COALESCE(t5.user_change, 0) as user_change,
COALESCE(t6.mau_cnt, 0) as user_mau
FROM
t4
LEFT JOIN t5 ON t4.log_date = t5.log_date
AND t4.user_role = t5.user_role
LEFT JOIN t6 ON t4.log_date = t6.log_date
AND t4.user_role = t6.user_role
WHERE
t6.log_date IS NOT NULL
)
INSERT overwrite table
bi_sycpb.zindex_user_behavior_change_i_d partition (log_date = '${yyyyMMdd}') (
SELECT
coalesce(t1.user_role, t2.user_role, t3.user_role) as user_role,
coalesce(t1.outer_user, t2.outer_user, t3.outer_user) as outer_user,
coalesce(t1.user_cnt, 0) as user_cnt,
COALESCE(t2.user_change, 0) as user_change,
COALESCE(t3.mau_cnt, 0) as user_mau,
coalesce(
t1.platform_type,
t2.platform_type,
t3.platform_type
) as platform_type
FROM
t1
FULL OUTER JOIN t2 ON t1.log_date = t2.log_date
AND t1.user_role = t2.user_role
AND t1.platform_type = t2.platform_type
FULL OUTER JOIN t3 ON t1.log_date = t3.log_date
AND t1.user_role = t3.user_role
AND t1.platform_type = t3.platform_type
WHERE
t3.log_date IS NOT NULL
)
UNION ALL
(
SELECT DISTINCT
user_role,
outer_user,
user_cnt,
user_change,
user_mau,
'整体' as platform_type
FROM
t7
)
