1. 每日 UV(独立用户数)
业务意义
每日UV是衡量平台用户规模与吸引力的核心指标,直观反映每日不同用户访问量。核心价值:1. 追踪用户增长趋势,判断平台用户基数变化;2. 评估营销活动效果,如促销活动带来的新用户访问量;3. 为电商等平台的商品推广、运营策略调整提供数据支撑。
实现思路
1. 按访问日期(access_date)分组,确定“每日”统计维度;2. 用COUNT(DISTINCT user_id)对用户标识去重统计,避免同一用户单日多次访问重复计数;3. 基于Hive SQL实现分组与去重统计。
输入示例
表名:user_access_log(用户访问日志表)
user_id(用户ID) | access_date(访问日期) |
---|---|
1001 | 2025-10-18 |
1001 | 2025-10-18 |
1002 | 2025-10-18 |
1003 | 2025-10-19 |
1004 | 2025-10-19 |
SQL示例
SELECT
access_date,
COUNT(DISTINCT user_id) AS daily_uv -- 统计每日去重用户数
FROM
user_access_log
GROUP BY
access_date -- 按访问日期分组
ORDER BY
access_date;
输出示例
access_date(访问日期) | daily_uv(每日UV) |
---|---|
2025-10-18 | 2 |
2025-10-19 | 2 |
2. 用户最长连续签到天数
业务意义
评估用户粘性与活跃度的关键指标。运营可据此制定分层策略:对连续签到天数长的用户发专属奖励(积分、优惠券)强化留存;对签到中断用户推召回提醒,提升整体活跃率。
实现思路
1. 按用户ID分区,对签到日期升序排序生成序号(rn);2. 用DATE_SUB(sign_in_date, INTERVAL rn DAY)生成分组标识(grp),连续签到的日期与序号差值固定,grp相同;3. 按用户ID和grp分组统计每组签到天数,取最大值即为最长连续签到天数。
输入示例
表名:sign_in_log(用户签到日志表)
user_id(用户ID) | sign_in_date(签到日期) |
---|---|
1001 | 2025-10-16 |
1001 | 2025-10-17 |
1001 | 2025-10-19 |
1002 | 2025-10-17 |
1002 | 2025-10-18 |
1002 | 2025-10-19 |
SQL示例
WITH RankedData AS (
-- 步骤1:按用户分区,签到日期排序生成序号
SELECT
user_id,
sign_in_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY sign_in_date) AS rn
FROM
sign_in_log
),
GroupedData AS (
-- 步骤2:生成连续签到分组标识
SELECT
user_id,
sign_in_date,
DATE_SUB(sign_in_date, INTERVAL rn DAY) AS grp
FROM
RankedData
)
-- 步骤3:统计每个用户的最长连续签到天数
SELECT
user_id,
MAX(continuous_days) AS max_continuous_sign_in_days
FROM (
SELECT
user_id,
grp,
COUNT(*) AS continuous_days -- 统计每组连续签到天数
FROM
GroupedData
GROUP BY
user_id, grp
) t
GROUP BY
user_id;
输出示例
user_id(用户ID) | max_continuous_sign_in_days(最长连续签到天数) |
---|---|
1001 | 2 |
1002 | 3 |
3. 拆分并统计标签字段
业务意义
适用于多标签精细化分析:电商可统计细分标签(如“女装-连衣裙-夏季新款”)商品数量,优化采购与推广;社交平台拆分用户兴趣标签(如“音乐-流行音乐-周杰伦”),实现个性化推荐。
实现思路
1. 用split函数按指定分隔符(如“-”)将标签字符串拆为数组;2. 用explode函数将数组展开为独立行,便于统计;3. 按拆分后的单个标签分组,用COUNT(*)统计出现次数。
输入示例
表名:product_tags(商品标签表)
product_id(商品ID) | tags(标签,“-”分隔) |
---|---|
P001 | 服装类-女装-连衣裙-夏季新款 |
P002 | 服装类-男装-T恤-基础款 |
P003 | 鞋包类-女鞋-凉鞋-百搭 |
P004 | 服装类-女装-连衣裙-碎花款 |
SQL示例
-- 步骤1:拆分标签并展开,步骤2:按标签分组统计数量
SELECT
tag,
COUNT(*) AS tag_count -- 统计每个标签的出现次数
FROM (
SELECT
product_id,
explode(split(tags, '-')) AS tag -- split拆分标签数组,explode展开为行
FROM
product_tags
) t
GROUP BY
tag
ORDER BY
tag_count DESC;
输出示例
tag(标签) | tag_count(标签计数) |
---|---|
服装类 | 3 |
女装 | 2 |
连衣裙 | 2 |
夏季新款 | 1 |
男装 | 1 |
4. 取每个用户最后一次登录记录
业务意义
用户行为分析与系统安全核心指标:电商通过最后登录时间判断用户活跃周期,对长期未登录用户推召回营销;银行系统监控该信息,防范账户异常登录风险。
实现思路
1. 按用户ID分区,对登录时间降序排序生成序号(rn);2. 降序排序后,最新登录记录的序号为1,筛选rn=1的记录即可获取每个用户最后一次登录信息。
输入示例
表名:user_login_log(用户登录日志表)
user_id(用户ID) | login_time(登录时间) |
---|---|
1001 | 2025-10-18 09:30:00 |
1001 | 2025-10-19 14:15:00 |
1002 | 2025-10-17 20:45:00 |
1002 | 2025-10-18 11:20:00 |
SQL示例
WITH RankedData AS (
-- 按用户分区,登录时间降序排序生成序号
SELECT
user_id,
login_time,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_time DESC) AS rn
FROM
user_login_log
)
-- 筛选最新登录记录(序号=1)
SELECT
user_id,
login_time AS last_login_time
FROM
RankedData
WHERE
rn = 1;
输出示例
user_id(用户ID) | last_login_time(最后登录时间) |
---|---|
1001 | 2025-10-19 14:15:00 |
1002 | 2025-10-18 11:20:00 |
5. 订单与用户基础信息关联
业务意义
交易与用户属性联动分析核心手段:电商通过关联订单与用户信息,结合年龄、地域等属性分析不同群体消费偏好,制定精准营销与个性化推荐策略;金融领域关联借贷订单与用户信息,可有效评估信用风险、分析借贷行为模式。
实现思路
1. 确定关联表与关联字段:订单表(orders)存交易数据、用户表(users)存基础属性,核心关联字段为user_id;2. 选择关联方式:用INNER JOIN获取有订单记录的用户数据(需包含无订单用户时用LEFT JOIN);3. 筛选目标字段:按需提取订单与用户关键信息,避免数据冗余。
输入示例
表1:orders(订单表)
order_id(订单ID) | user_id(用户ID) | order_amount(订单金额,元) |
---|---|---|
O001 | 1001 | 299 |
O002 | 1002 | 159 |
O003 | 1001 | 499 |
表2:users(用户基础信息表)
user_id(用户ID) | user_name(用户名) | user_age(用户年龄) |
---|---|---|
1001 | 张三 | 28 |
1002 | 李四 | 35 |
1003 | 王五 | 22 |
SQL示例
-- 内连接订单表与用户表,获取关联数据
SELECT
o.order_id,
o.user_id,
u.user_name,
u.user_age,
o.order_amount
FROM
orders o -- 订单表别名o
INNER JOIN
users u -- 用户表别名u
ON
o.user_id = u.user_id -- 关联条件:用户ID匹配
ORDER BY
o.order_id;
输出示例
order_id(订单ID) | user_id(用户ID) | user_name(用户名) | user_age(用户年龄) | order_amount(订单金额,元) |
---|---|---|---|---|
O001 | 1001 | 张三 | 28 | 299 |
O002 | 1002 | 李四 | 35 | 159 |
O003 | 1001 | 张三 | 28 | 499 |
6. 每天浏览量 Top3 的商品
业务意义
热门商品趋势洞察核心指标:电商平台通过该数据掌握热门商品动态,调整库存与展示位置(如首页推荐)提升转化;资讯/内容平台可类比应用,依据文章浏览Top3确定热门话题,指导内容创作与推送策略。
实现思路
1. 确定统计维度:以“天”为单位,按浏览日期(view_date)分区;2. 排序排名:对每个日期内的商品按浏览量(view_count)降序排序,用窗口函数生成排名序号(rn);3. 筛选结果:提取每个日期内序号≤3的记录,即当天浏览量Top3商品。
输入示例
表名:product_view_log(商品浏览日志表)
product_id(商品ID) | view_date(浏览日期) | view_count(浏览量) |
---|---|---|
P001 | 2025-10-18 | 520 |
P002 | 2025-10-18 | 380 |
P003 | 2025-10-18 | 250 |
P004 | 2025-10-18 | 180 |
P001 | 2025-10-19 | 610 |
P003 | 2025-10-19 | 450 |
P005 | 2025-10-19 | 320 |
SQL示例
WITH RankedData AS (
-- 按日期分区,浏览量降序排序生成排名序号
SELECT
product_id,
view_date,
view_count,
ROW_NUMBER() OVER (
PARTITION BY view_date
ORDER BY view_count DESC
) AS rn -- 排名序号,1为当日浏览量最高
FROM
product_view_log
)
-- 筛选每天浏览量Top3的商品
SELECT
product_id,
view_date,
view_count,
rn AS view_rank -- 浏览量排名
FROM
RankedData
WHERE
rn <= 3 -- 取排名前3的记录
ORDER BY
view_date, rn;
输出示例
product_id(商品ID) | view_date(浏览日期) | view_count(浏览量) | view_rank(浏览量排名) |
---|---|---|---|
P001 | 2025-10-18 | 520 | 1 |
P002 | 2025-10-18 | 380 | 2 |
P003 | 2025-10-18 | 250 | 3 |
P001 | 2025-10-19 | 610 | 1 |
P003 | 2025-10-19 | 450 | 2 |
P005 | 2025-10-19 | 320 | 3 |
7. 每日累计充值金额
业务意义
商业化效果与资金流动分析核心指标:游戏行业通过该数据评估玩家付费意愿与商业化效益,及时推出限时充值活动提升收入;电商金融领域可洞察用户资金流动规律与消费能力,为理财产品设计、风险评估提供数据支撑。
实现思路
1. 确定统计表与核心字段:充值日志表(recharge_log)存储数据,核心字段为user_id(用户ID)、recharge_date(充值日期)、recharge_time(充值时间)、recharge_amount(充值金额);2. 窗口函数逻辑:按充值日期分区,按充值时间升序排序,通过聚合函数计算分区内截至当前记录的累计金额;3. 结果输出:关联原始充值信息与累计金额,清晰呈现每日充值累计过程。
输入示例
表名:recharge_log(用户充值日志表)
user_id(用户ID) | recharge_date(充值日期) | recharge_time(充值时间) | recharge_amount(充值金额,元) |
---|---|---|---|
1001 | 2025-10-19 | 2025-10-19 10:05:00 | 100 |
1002 | 2025-10-19 | 2025-10-19 11:20:00 | 200 |
1001 | 2025-10-19 | 2025-10-19 15:30:00 | 300 |
1003 | 2025-10-20 | 2025-10-20 09:45:00 | 150 |
SQL示例
-- 按日期分区计算每日累计充值金额
SELECT
recharge_date,
user_id,
recharge_time,
recharge_amount,
-- 按日期分区,按充值时间排序,计算累计金额
SUM(recharge_amount) OVER (
PARTITION BY recharge_date
ORDER BY recharge_time
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS daily_cumulative_recharge
FROM
recharge_log
ORDER BY
recharge_date, recharge_time;
输出示例
recharge_date(充值日期) | user_id(用户ID) | recharge_time(充值时间) | recharge_amount(充值金额,元) | daily_cumulative_recharge(每日累计充值金额,元) |
---|---|---|---|---|
2025-10-19 | 1001 | 2025-10-19 10:05:00 | 100 | 100 |
2025-10-19 | 1002 | 2025-10-19 11:20:00 | 200 | 300 |
2025-10-19 | 1001 | 2025-10-19 15:30:00 | 300 | 600 |
2025-10-20 | 1003 | 2025-10-20 09:45:00 | 150 | 150 |
8. 用户首购信息
业务意义
用户生命周期与精准营销核心指标:营销端可通过首购数据(年龄、商品偏好等)优化获客策略;运营端以首购为起点,制定个性化复购激励方案,提升用户留存率与忠诚度。
实现思路
1. 确定统计表与核心字段:订单表(orders)存储数据,核心字段为user_id(用户ID)、order_date(订单日期)、order_amount(订单金额);2. 窗口函数逻辑:按用户ID分区,按订单日期升序排序,用first_value函数提取首购日期与金额;3. 去重处理:对用户ID去重,保留每条用户唯一首购记录。
输入示例
表名:orders(订单表)
user_id(用户ID) | order_date(订单日期) | order_amount(订单金额,元) |
---|---|---|
1001 | 2025-10-15 | 199 |
1001 | 2025-10-19 | 359 |
1002 | 2025-10-17 | 299 |
1002 | 2025-10-18 | 129 |
1003 | 2025-10-20 | 499 |
SQL示例
WITH FirstPurchaseData AS (
-- 按用户分区,提取首购日期与金额
SELECT
user_id,
order_date,
order_amount,
-- 提取首购日期
FIRST_VALUE(order_date) OVER (
PARTITION BY user_id
ORDER BY order_date ASC
) AS first_purchase_date,
-- 提取首购金额
FIRST_VALUE(order_amount) OVER (
PARTITION BY user_id
ORDER BY order_date ASC
) AS first_purchase_amount
FROM
orders
)
-- 按用户去重,保留唯一首购记录
SELECT
DISTINCT user_id,
first_purchase_date,
first_purchase_amount
FROM
FirstPurchaseData
ORDER BY
user_id;
输出示例
user_id(用户ID) | first_purchase_date(首购日期) | first_purchase_amount(首购金额,元) |
---|---|---|
1001 | 2025-10-15 | 199 |
1002 | 2025-10-17 | 299 |
1003 | 2025-10-20 | 499 |
9. 每省独立用户数
业务意义
区域市场运营与潜力评估核心指标:电商平台可通过该数据掌握各省份用户规模分布,对用户基数大、增长快的省份加大推广与仓储资源投入;零售或本地生活服务行业能依据省份用户数制定区域化营销方案,如针对高用户量省份推出专属满减活动,提升区域转化效率。
实现思路
1. 确定统计表与核心字段:用户信息表(user_info)存储数据,核心字段为user_id(用户唯一标识,用于去重)、province(省份,作为统计维度);2. 分组聚合:按省份(province)字段分组,确保同一省份的用户数据归为一组;3. 去重统计:使用COUNT(DISTINCT user_id)函数统计每组内的独立用户数,避免同一用户因多条记录被重复计数。
输入示例
表名:user_info(用户信息表)
user_id(用户ID) | province(省份) |
1001 | 广东省 |
1002 | 浙江省 |
1003 | 广东省 |
1004 | 江苏省 |
1005 | 浙江省 |
1006 | 广东省 |
SQL示例
-- 按省份分组,统计每省独立用户数
SELECT
province,
COUNT(DISTINCT user_id) AS unique_user_count -- 去重统计每个省份的独立用户数
FROM
user_info
GROUP BY
province -- 按省份字段分组聚合
ORDER BY
unique_user_count DESC; -- 按独立用户数降序排列,便于查看高潜力省份
输出示例
province(省份) | unique_user_count(每省独立用户数) |
广东省 | 3 |
浙江省 | 2 |
江苏省 | 1 |
10. 分页查询
业务意义
大数据量查询性能与用户体验优化核心手段:电商平台商品列表、资讯网站文章列表等场景中,数据量常达数万甚至百万级,一次性返回全部数据会导致网络传输负担重、页面加载缓慢;分页查询可按“每页固定条数”返回数据,提升页面响应速度,让用户流畅浏览。
实现思路
1. 确定统计表与核心字段:以商品信息表(product_info)为例,核心字段为product_id(商品ID,用于排序保证稳定性)、product_name(商品名称);2. 稳定排序:按唯一字段(如product_id)升序/降序排序,避免分页时数据顺序混乱;3. 控制返回范围:用OFFSET指定查询起始位置(从0开始),LIMIT指定每页返回记录数,两者结合实现分页。
输入示例
表名:product_info(商品信息表,部分数据)
product_id(商品ID) | product_name(商品名称) |
1 | 纯棉T恤 |
2 | 牛仔裤 |
3 | 运动鞋 |
... | ... |
21 | 连衣裙 |
22 | 帆布鞋 |
... | ... |
SQL示例
-- 分页查询:获取第3页数据(每页10条,起始偏移20,即从第21条开始)
SELECT
product_id,
product_name
FROM
product_info
ORDER BY
product_id ASC -- 按商品ID升序排序,保证分页数据顺序稳定
LIMIT 10 OFFSET 20; -- LIMIT 10:每页10条;OFFSET 20:从第21条记录开始
输出示例
product_id(商品ID) | product_name(商品名称) |
21 | 连衣裙 |
22 | 帆布鞋 |
23 | 棒球帽 |
24 | 太阳镜 |
25 | 双肩包 |
26 | 羊毛衫 |
27 | 休闲裤 |
28 | 马丁靴 |
29 | 围巾 |
30 | 手套 |
11. 每月活跃用户 (MAU)
业务意义
平台用户规模与粘性核心衡量指标:社交APP通过MAU判断用户基数与留存能力,评估产品市场竞争力;电商平台结合MAU与消费转化数据,分析用户活跃质量,为月度大促制定流量储备策略;游戏行业用MAU监测产品生命周期,及时调整运营活动。
实现思路
1. 确定统计表与核心字段:用户访问日志表(user_access_log),核心字段为user_id(用户唯一标识)、access_time(访问时间);2. 提取统计维度:从访问时间中提取“年月”作为MAU统计周期;3. 分组去重统计:按“年月”分组,用COUNT(DISTINCT user_id)统计当月独立用户数。
输入示例
表名:user_access_log(用户访问日志表)
user_id(用户ID) | access_time(访问时间) |
1001 | 2025-10-05 09:20:00 |
1001 | 2025-10-18 14:30:00 |
1002 | 2025-10-12 11:15:00 |
1003 | 2025-09-28 08:40:00 |
1004 | 2025-10-25 16:50:00 |
SQL示例
-- 统计每月活跃用户数(MAU)
SELECT
DATE_FORMAT(access_time, 'yyyy-MM') AS month -- 提取“年月”作为统计周期
, COUNT(DISTINCT user_id) AS mau -- 去重统计当月活跃用户数
FROM
user_access_log
GROUP BY
DATE_FORMAT(access_time, 'yyyy-MM') -- 按“年月”分组聚合
ORDER BY
month DESC; -- 按月份降序排列
输出示例
month(月份) | mau(每月活跃用户数) |
2025-10 | 3 |
2025-09 | 1 |
12. 会话切割
业务意义
用户行为路径分析核心前提:电商平台通过会话切割还原用户一次访问的浏览、加购、下单流程,识别高转化路径;资讯APP分析单会话内容浏览序列,精准推送同类话题;支撑停留时长、访问深度等指标计算。
实现思路
1. 确定统计表与核心字段:用户访问日志表(user_access_log),核心字段为user_id、access_time;2. 排序与时间差计算:按用户分区、访问时间排序,用LAG函数取上一次访问时间,计算间隔;3. 会话标识生成:设定30分钟阈值(超阈值为新会话),CASE WHEN标记新会话,累加得唯一会话ID。
输入示例
表名:user_access_log(用户访问日志表)
user_id(用户ID) | access_time(访问时间) |
1001 | 2025-10-19 10:00:00 |
1001 | 2025-10-19 10:15:00 |
1001 | 2025-10-19 11:00:00 |
1002 | 2025-10-19 09:30:00 |
1001 | 2025-10-19 14:20:00 |
SQL示例
-- 基于30分钟阈值进行会话切割
WITH UserAccessRank AS (
-- 按用户分区排序,取上一次访问时间
SELECT
user_id,
access_time,
LAG(access_time, 1) OVER (PARTITION BY user_id ORDER BY access_time) AS last_access_time
FROM user_access_log
),
SessionFlag AS (
-- 标记新会话(首次访问或间隔超30分钟为1)
SELECT
user_id,
access_time,
CASE
WHEN last_access_time IS NULL THEN 1
WHEN TIMESTAMPDIFF(MINUTE, last_access_time, access_time) > 30 THEN 1
ELSE 0
END AS is_new_session
FROM UserAccessRank
)
-- 累加生成会话ID
SELECT
user_id,
access_time,
SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY access_time) AS session_id
FROM SessionFlag
ORDER BY user_id, access_time;
输出示例
user_id(用户ID) | access_time(访问时间) | session_id(会话ID) |
1001 | 2025-10-19 10:00:00 | 1 |
1001 | 2025-10-19 10:15:00 | 1 |
1001 | 2025-10-19 11:00:00 | 2 |
1001 | 2025-10-19 14:20:00 | 3 |
1002 | 2025-10-19 09:30:00 | 1 |
13. JSON 字段解析
业务意义
非结构化数据结构化处理核心手段:当前业务中大量数据(如商品属性、用户画像、日志详情)以JSON格式存储,直接无法用于统计分析;解析JSON字段可提取其中关键信息(如商品分类、用户兴趣标签),支撑精准营销、品类分析等场景。例如电商平台解析商品表中JSON格式的“属性字段”,提取“品牌”“材质”等信息,实现按品牌统计销量。
实现思路
1. 确定统计表与核心字段:以存储JSON数据的表(如product_json)为例,核心字段为product_id(商品ID)、product_attr(JSON格式属性字段);2. 选择解析函数:Hive中常用get_json_object(单字段解析)或json_tuple(多字段解析,效率更高);3. 提取目标字段:指定JSON路径提取所需信息,处理可能的空值场景,确保解析后数据可用性。
输入示例
表名:product_json(含JSON字段的商品表)
product_id(商品ID) | product_attr(JSON格式属性字段) |
---|---|
P001 | {"name":"纯棉T恤","category":"服饰","brand":"XX服饰","price":99} |
P002 | {"name":"运动鞋","category":"鞋靴","brand":"YY运动","price":399} |
P003 | {"name":"玻璃杯","category":"家居","brand":"ZZ家居","price":29} |
SQL示例
-- 方式1:用json_tuple解析多字段(推荐,效率高于get_json_object)
SELECT
product_id,
json_tuple(product_attr, 'name', 'category', 'brand', 'price') AS (product_name, product_category, product_brand, product_price)
FROM
product_json;
-- 方式2:用get_json_object解析单字段(适用于少量字段场景)
/*
SELECT
product_id,
get_json_object(product_attr, '$.name') AS product_name,
get_json_object(product_attr, '$.price') AS product_price
FROM
product_json;
*/
输出示例
product_id(商品ID) | product_name(商品名称) | product_category(商品分类) | product_brand(商品品牌) | product_price(商品价格,元) |
---|---|---|---|---|
P001 | 纯棉T恤 | 服饰 | XX服饰 | 99 |
P002 | 运动鞋 | 鞋靴 | YY运动 | 399 |
P003 | 玻璃杯 | 家居 | ZZ家居 | 29 |
14. 半连接查询(Semi Join)
业务意义
左表数据筛选与关联效率优化核心方式:仅返回左表中与右表匹配的记录,且左表数据无重复(区别于INNER JOIN可能因右表多条匹配导致左表重复)。例如电商场景中“查询有下单记录的用户基础信息”,无需返回订单详情,半连接可高效筛选目标用户,减少数据传输与处理成本。
实现思路
1. 确定左右表与关联字段:左表为用户基础信息表(users),右表为订单表(orders),核心关联字段为user_id;2. 选择半连接实现方式:Hive中常用IN子查询或EXISTS子查询实现半连接,避免使用INNER JOIN导致的左表重复;3. 筛选目标字段:仅提取左表所需字段,聚焦筛选逻辑,提升查询效率。
输入示例
表1:users(用户基础信息表)
user_id(用户ID) | user_name(用户名) | user_age(用户年龄) |
---|---|---|
1001 | 张三 | 28 |
1002 | 李四 | 35 |
1003 | 王五 | 22 |
表2:orders(订单表)
order_id(订单ID) | user_id(用户ID) | order_amount(订单金额,元) |
---|---|---|
O001 | 1001 | 299 |
O002 | 1002 | 159 |
O003 | 1001 | 499 |
SQL示例
-- 方式1:用IN子查询实现半连接(查询有下单记录的用户信息)
SELECT
user_id,
user_name,
user_age
FROM
users
WHERE
user_id IN (SELECT DISTINCT user_id FROM orders) -- 右表去重后匹配左表
ORDER BY
user_id;
-- 方式2:用EXISTS子查询实现半连接(效率更高,尤其右表数据量大时)
/*
SELECT
u.user_id,
u.user_name,
u.user_age
FROM
users u
WHERE
EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.user_id -- 关联条件,无需右表去重
)
ORDER BY
u.user_id;
*/
输出示例
user_id(用户ID) | user_name(用户名) | user_age(用户年龄) |
---|---|---|
1001 | 张三 | 28 |
1002 | 李四 | 35 |
注:输出仅含左表(users)中与右表(orders)匹配的记录,且无重复(即使张三有2笔订单,仅返回1条用户信息)。
15. 金额区间分桶
业务意义
交易金额分层分析与精准运营核心手段:电商领域可通过金额分桶识别高、中、低客单价用户群体,针对高客单价群体推送高端商品,为低客单价群体设计满减活动提升客单价;金融领域对借贷金额分桶,评估不同金额区间的风险等级,制定差异化风控策略;零售行业通过客单价分桶优化商品组合,匹配不同消费能力用户需求。
实现思路
1. 确定统计表与核心字段:以订单表(orders)为分析对象,核心字段为order_id(订单唯一标识)、order_amount(订单金额,分桶依据);2. 定义分桶规则:根据业务需求设定合理区间(如0-100元、101-300元等),用CASE WHEN语句将金额映射到对应区间;3. 分组统计:按金额区间分组,统计每个区间的订单数量或用户数量,明确各区间分布情况。
输入示例
表名:orders(订单表)
order_id(订单ID) | order_amount(订单金额,元) |
---|---|
O001 | 89 |
O002 | 256 |
O003 | 159 |
O004 | 499 |
O005 | 68 |
SQL示例
-- 订单金额区间分桶,统计各区间订单数量
SELECT
-- 定义金额区间分桶规则
CASE
WHEN order_amount <= 100 THEN '0-100元'
WHEN order_amount BETWEEN 101 AND 300 THEN '101-300元'
WHEN order_amount BETWEEN 301 AND 500 THEN '301-500元'
ELSE '500元以上'
END AS amount_bucket,
COUNT(order_id) AS order_count -- 统计各区间订单数量
FROM
orders
GROUP BY
-- 分组字段与分桶逻辑一致
CASE
WHEN order_amount <= 100 THEN '0-100元'
WHEN order_amount BETWEEN 101 AND 300 THEN '101-300元'
WHEN order_amount BETWEEN 301 AND 500 THEN '301-500元'
ELSE '500元以上'
END
ORDER BY
-- 按金额区间升序排列
CASE amount_bucket
WHEN '0-100元' THEN 1
WHEN '101-300元' THEN 2
WHEN '301-500元' THEN 3
ELSE 4
END;
输出示例
amount_bucket(金额区间) | order_count(订单数量) |
---|---|
0-100元 | 2 |
101-300元 | 2 |
301-500元 | 1 |
500元以上 | 0 |
16. 用户留存率计算
业务意义
产品粘性与用户价值核心评估指标:互联网产品(如APP、游戏)通过留存率判断用户对产品的认可程度,次日留存率反映新用户初始体验质量,7日留存率体现产品核心功能吸引力;运营端可针对低留存环节优化(如新手引导),营销端结合留存率评估获客质量,避免无效获客成本。
实现思路
1. 确定核心表与字段:用户新增表(user_new)存储用户首次注册时间,核心字段user_id、register_date;用户访问日志表(user_access_log)存储访问记录,核心字段user_id、access_date;2. 标记新增用户与访问关系:关联两表,匹配新增用户在后续日期的访问情况,计算留存天数;3. 分组计算留存率:按新增日期分组,统计当日新增用户数及后续指定日期(如次日、7日)的留存用户数,留存率=留存用户数/新增用户数。
输入示例
表1:user_new(用户新增表)
user_id(用户ID) | register_date(注册日期) |
---|---|
1001 | 2025-10-18 |
1002 | 2025-10-18 |
1003 | 2025-10-18 |
1004 | 2025-10-19 |
表2:user_access_log(用户访问日志表)
user_id(用户ID) | access_date(访问日期) |
---|---|
1001 | 2025-10-18 |
1001 | 2025-10-19 |
1001 | 2025-10-25 |
1002 | 2025-10-18 |
1003 | 2025-10-20 |
1004 | 2025-10-19 |
SQL示例
-- 计算新增用户次日留存率与7日留存率
WITH NewUser AS (
-- 去重获取每日新增用户(避免同一用户多次注册)
SELECT DISTINCT user_id, register_date
FROM user_new
),
UserAccess AS (
-- 去重获取用户每日访问记录
SELECT DISTINCT user_id, access_date
FROM user_access_log
),
NewUserAccess AS (
-- 关联新增用户与访问记录,计算留存天数
SELECT
nu.register_date,
nu.user_id,
DATEDIFF(ua.access_date, nu.register_date) AS retention_days
FROM NewUser nu
LEFT JOIN UserAccess ua ON nu.user_id = ua.user_id
)
-- 按新增日期分组计算留存率
SELECT
register_date AS new_date,
COUNT(DISTINCT user_id) AS new_user_count, -- 当日新增用户数
-- 次日留存用户数(留存天数=1)
COUNT(DISTINCT CASE WHEN retention_days = 1 THEN user_id END) AS next_day_retention_count,
-- 次日留存率(保留2位小数)
ROUND(
COUNT(DISTINCT CASE WHEN retention_days = 1 THEN user_id END) /
COUNT(DISTINCT user_id),
2
) AS next_day_retention_rate,
-- 7日留存用户数(留存天数=7)
COUNT(DISTINCT CASE WHEN retention_days = 7 THEN user_id END) AS seven_day_retention_count,
-- 7日留存率(保留2位小数)
ROUND(
COUNT(DISTINCT CASE WHEN retention_days = 7 THEN user_id END) /
COUNT(DISTINCT user_id),
2
) AS seven_day_retention_rate
FROM NewUserAccess
GROUP BY
register_date
ORDER BY
register_date;
输出示例
new_date(新增日期) | new_user_count(新增用户数) | next_day_retention_count(次日留存用户数) | next_day_retention_rate(次日留存率) | seven_day_retention_count(7日留存用户数) | seven_day_retention_rate(7日留存率) |
---|---|---|---|---|---|
2025-10-18 | 3 | 1 | 0.33 | 1 | 0.33 |
2025-10-19 | 1 | 1 | 1.00 | 0 | 0.00 |
注:2025-10-19新增用户的7日留存需等待2025-10-26后才有数据,故当前显示为0。
17. 日活跃用户增长率
业务意义
用户增长趋势与运营效果核心评估指标:电商平台可通过该指标判断推广活动、新品上线等运营动作的效果,若增长率突增则说明活动引流有效;社交APP通过增长率监测用户规模扩张节奏,识别用户增长瓶颈期;工具类产品可结合增长率与留存率,判断产品是否处于成长期或稳定期,为资源投入决策提供依据。
实现思路
1. 确定统计表与核心字段:以用户访问日志表(user_access_log)为数据源,核心字段为user_id(用户唯一标识)、access_date(访问日期,统计维度);2. 计算每日日活(DAU):按访问日期分组,用COUNT(DISTINCT user_id)统计每日独立活跃用户数;3. 计算增长率:通过LAG窗口函数获取前一日DAU,按公式“(当日DAU-前一日DAU)/前一日DAU”计算增长率,首日无前置数据标注为“无”。
输入示例
表名:user_access_log(用户访问日志表)
user_id(用户ID) | access_date(访问日期) |
---|---|
1001 | 2025-10-18 |
1002 | 2025-10-18 |
1003 | 2025-10-18 |
1001 | 2025-10-19 |
1002 | 2025-10-19 |
1003 | 2025-10-19 |
1004 | 2025-10-19 |
1001 | 2025-10-20 |
1003 | 2025-10-20 |
1004 | 2025-10-20 |
SQL示例
-- 计算日活跃用户(DAU)及增长率
WITH DailyDAU AS (
-- 第一步:计算每日日活跃用户数(去重统计)
SELECT
DATE_FORMAT(access_date, 'yyyy-MM-dd') AS active_date,
COUNT(DISTINCT user_id) AS dau
FROM
user_access_log
GROUP BY
DATE_FORMAT(access_date, 'yyyy-MM-dd')
)
-- 第二步:关联前一日DAU,计算增长率
SELECT
active_date,
dau,
-- 获取前一日DAU数据
LAG(dau, 1) OVER (ORDER BY active_date) AS last_day_dau,
-- 计算增长率(保留2位小数),首日无增长率标注为“无”
CASE
WHEN LAG(dau, 1) OVER (ORDER BY active_date) IS NULL THEN '无'
ELSE ROUND(
(dau - LAG(dau, 1) OVER (ORDER BY active_date)) /
LAG(dau, 1) OVER (ORDER BY active_date) * 100,
2
) || '%'
END AS dau_growth_rate
FROM
DailyDAU
ORDER BY
active_date;
输出示例
active_date(活跃日期) | dau(日活跃用户数) | last_day_dau(前一日日活跃用户数) | dau_growth_rate(日活跃用户增长率) |
---|---|---|---|
2025-10-18 | 3 | 无 | 无 |
2025-10-19 | 4 | 3 | 33.33% |
2025-10-20 | 3 | 4 | -25.00% |
18. 交叉标签共现
业务意义
用户多维度属性关联分析核心手段:电商平台通过商品标签共现(如“服饰”与“鞋靴”共现)识别用户搭配偏好,设计组合促销活动;内容平台分析兴趣标签共现(如“科技”与“数码”共现),优化个性化推荐策略;用户运营场景中,通过标签共现划分细分群体(如“25-30岁”与“高客单价”共现群体),实现精准触达。
实现思路
1. 确定统计表与核心字段:以用户标签表(user_tag)为数据源,核心字段为user_id(用户唯一标识)、tag(用户标签,如商品品类、兴趣领域);2. 标签自连接匹配:对用户标签表进行自连接,关联条件为“同一用户且标签1<标签2”,避免“标签A-标签B”与“标签B-标签A”的重复统计;3. 共现次数统计:按两个标签的组合分组,统计每组的用户数量,即标签共现次数。
输入示例
表名:user_tag(用户标签表)
user_id(用户ID) | tag(用户标签) |
---|---|
1001 | 服饰 |
1001 | 鞋靴 |
1001 | 配饰 |
1002 | 家居 |
1002 | 服饰 |
1003 | 鞋靴 |
1003 | 配饰 |
SQL示例
-- 计算用户标签交叉共现次数
SELECT
t1.tag AS tag1,
t2.tag AS tag2,
COUNT(DISTINCT t1.user_id) AS co_occurrence_count -- 统计同时拥有两个标签的用户数(共现次数)
FROM
user_tag t1
INNER JOIN
user_tag t2
ON
t1.user_id = t2.user_id -- 同一用户的标签关联
AND t1.tag < t2.tag -- 避免标签组合重复(如“服饰-鞋靴”与“鞋靴-服饰”视为同一组合)
GROUP BY
t1.tag,
t2.tag
ORDER BY
co_occurrence_count DESC; -- 按共现次数降序排列,优先展示高频组合
输出示例
tag1(标签1) | tag2(标签2) | co_occurrence_count(标签共现次数) |
---|---|---|
服饰 | 鞋靴 | 1 |
服饰 | 配饰 | 1 |
鞋靴 | 配饰 | 2 |
服饰 | 家居 | 1 |
注:共现次数代表同时拥有两个标签的用户数量,“鞋靴-配饰”共现2次,说明有2个用户同时拥有这两个标签。
19. 用户分群(K-means前处理)
业务意义
用户精细化运营与聚类分析核心前置环节:K-means聚类需基于高质量特征数据,前处理可剔除噪声、统一量纲,确保分群结果贴合业务实际。电商场景中,通过前处理得到“消费频次、客单价、浏览时长”等特征,聚类后可划分高价值、潜力、流失等用户群;金融场景处理“借贷金额、还款周期、信用评分”等特征,为风险分群提供可靠数据支撑。
实现思路
1. 确定数据源与核心特征:关联用户表(users)与订单表(orders),选取消费频次、总消费金额、客单价、最近消费时间间隔(RFM模型核心指标)作为聚类特征;2. 数据预处理:去重(剔除重复订单)、缺失值填充(如无消费用户客单价填0)、异常值处理(用3σ原则剔除极端消费数据);3. 特征标准化:通过Z-score标准化消除量纲影响(如消费金额与频次量级差异),输出聚类用特征矩阵。
输入示例
表1:users(用户基础信息表)
user_id(用户ID) | register_date(注册日期) |
1001 | 2025-08-10 |
1002 | 2025-09-05 |
1003 | 2025-10-01 |
1004 | 2025-08-15 |
表2:orders(订单表,统计截止日2025-10-20)
order_id(订单ID) | user_id(用户ID) | order_amount(订单金额,元) | order_date(下单日期) |
O001 | 1001 | 299 | 2025-10-05 |
O002 | 1001 | 499 | 2025-10-18 |
O003 | 1002 | 159 | 2025-10-10 |
O004 | 1004 | 899 | 2025-09-20 |
SQL示例
-- K-means聚类前处理:提取用户特征并标准化
WITH UserConsume AS (
-- 第一步:计算用户核心消费特征
SELECT
u.user_id,
COUNT(o.order_id) AS consume_freq, -- 消费频次
COALESCE(SUM(o.order_amount), 0) AS total_consume, -- 总消费金额(无消费填0)
COALESCE(AVG(o.order_amount), 0) AS avg_consume, -- 客单价(无消费填0)
DATEDIFF('2025-10-20', MAX(COALESCE(o.order_date, u.register_date))) AS recency -- 最近消费间隔
FROM
users u
LEFT JOIN
orders o ON u.user_id = o.user_id
GROUP BY
u.user_id
),
FeatureStats AS (
-- 第二步:计算特征均值和标准差(用于Z-score标准化)
SELECT
AVG(consume_freq) AS freq_mean,
STDDEV(consume_freq) AS freq_std,
AVG(total_consume) AS total_mean,
STDDEV(total_consume) AS total_std,
AVG(avg_consume) AS avg_mean,
STDDEV(avg_consume) AS avg_std,
AVG(recency) AS recency_mean,
STDDEV(recency) AS recency_std
FROM
UserConsume
)
-- 第三步:特征标准化(Z-score = (x - 均值)/标准差,避免标准差为0报错)
SELECT
uc.user_id,
-- 消费频次标准化
CASE WHEN fs.freq_std = 0 THEN 0 ELSE (uc.consume_freq - fs.freq_mean)/fs.freq_std END AS std_freq,
-- 总消费金额标准化
CASE WHEN fs.total_std = 0 THEN 0 ELSE (uc.total_consume - fs.total_mean)/fs.total_std END AS std_total,
-- 客单价标准化
CASE WHEN fs.avg_std = 0 THEN 0 ELSE (uc.avg_consume - fs.avg_mean)/fs.avg_std END AS std_avg,
-- 最近消费间隔标准化(间隔越小价值越高,取负使方向一致)
CASE WHEN fs.recency_std = 0 THEN 0 ELSE -(uc.recency - fs.recency_mean)/fs.recency_std END AS std_recency
FROM
UserConsume uc,
FeatureStats fs
ORDER BY
uc.user_id;
输出示例
user_id(用户ID) | std_freq(标准化消费频次) | std_total(标准化总消费) | std_avg(标准化客单价) | std_recency(标准化最近消费间隔) |
1001 | 1.22 | 0.58 | -0.15 | 1.34 |
1002 | -0.41 | -0.72 | -0.89 | 0.45 |
1003 | -0.41 | -0.85 | -0.89 | -1.79 |
1004 | -0.41 | 0.99 | 1.93 | -0.00 |
注:输出为标准化后的特征矩阵,可直接作为K-means聚类算法的输入数据。
20. 多级分类销量汇总
业务意义
商品品类结构与销售表现全景分析工具:零售与电商场景中,多级分类(如“服饰-上衣-T恤”)汇总可清晰呈现各层级品类的销量分布,定位核心盈利品类(如“上衣”品类销量占比超50%);供应链端可依据汇总结果优化库存分配,优先保障高频品类备货;营销端可针对低效品类设计促销活动,提升整体销售效率。
实现思路
1. 确定关联表与分类字段:商品表(product)含多级分类字段(如一级分类category1、二级category2、三级category3),订单明细表(order_detail)含商品销量与金额;2. 表关联获取全量数据:通过商品ID关联商品表与订单明细表,确保每个销量记录匹配对应的多级分类;3. 多级分组汇总:按一级→二级→三级分类依次分组,统计各层级的销量与销售额,满足不同粒度分析需求。
输入示例
表1:product(商品表)
product_id(商品ID) | category1(一级分类) | category2(二级分类) | category3(三级分类) |
P001 | 服饰 | 上衣 | T恤 |
P002 | 服饰 | 上衣 | 衬衫 |
P003 | 服饰 | 下装 | 牛仔裤 |
P004 | 家居 | 厨具 | 炒锅 |
表2:order_detail(订单明细表)
order_detail_id(明细ID) | product_id(商品ID) | sale_quantity(销售数量) | sale_amount(销售金额,元) |
D001 | P001 | 5 | 495 |
D002 | P001 | 3 | 297 |
D003 | P003 | 2 | 398 |
D004 | P004 | 1 | 299 |
SQL示例
-- 多级分类销量与销售额汇总(支持一级、二级、三级分类下钻分析)
SELECT
p.category1 AS first_level_category, -- 一级分类
p.category2 AS second_level_category, -- 二级分类
p.category3 AS third_level_category, -- 三级分类
SUM(od.sale_quantity) AS total_sale_quantity, -- 总销量
SUM(od.sale_amount) AS total_sale_amount -- 总销售额
FROM
product p
INNER JOIN
order_detail od ON p.product_id = od.product_id
GROUP BY
-- 按多级分类分组(可通过注释调整分组粒度,如仅分一级分类)
p.category1,
p.category2,
p.category3
ORDER BY
total_sale_amount DESC; -- 按销售额降序排列,突出高销品类
输出示例
first_level_category(一级分类) | second_level_category(二级分类) | third_level_category(三级分类) | total_sale_quantity(总销量) | total_sale_amount(总销售额,元) |
服饰 | 上衣 | T恤 | 8 | 792 |
服饰 | 下装 | 牛仔裤 | 2 | 398 |
家居 | 厨具 | 炒锅 | 1 | 299 |
服饰 | 上衣 | 衬衫 | 0 | 0 |
注:若需仅展示一级分类汇总,可删除GROUP BY中的二级、三级分类字段,自动聚合该层级数据。
21. 事件类型分布占比
业务意义
用户行为结构与核心场景识别核心指标:电商平台通过点击、加购、下单等事件类型占比,判断用户转化漏斗健康度,若“加购→下单”占比低则优化结算流程;内容平台分析浏览、点赞、评论等事件占比,评估内容吸引力,聚焦高互动类型内容创作;APP运营通过事件占比定位核心功能使用情况,避免资源浪费在低使用率功能上。
实现思路
1. 确定统计表与核心字段:以用户事件日志表(event_log)为数据源,核心字段为event_id(事件唯一标识)、event_type(事件类型)、user_id(用户ID);2. 统计各类型事件数量:按event_type分组,用COUNT(DISTINCT event_id)统计各类型事件总数,避免重复事件干扰;3. 计算占比:用窗口函数SUM() OVER()获取事件总数量,通过“各类型数量/总数量”计算占比,保留百分比格式。
输入示例
表名:event_log(用户事件日志表)
event_id(事件ID) | user_id(用户ID) | event_type(事件类型) | event_time(事件时间) |
---|---|---|---|
E001 | 1001 | 点击 | 2025-10-20 10:00:00 |
E002 | 1001 | 加购 | 2025-10-20 10:05:00 |
E003 | 1002 | 点击 | 2025-10-20 10:10:00 |
E004 | 1003 | 下单 | 2025-10-20 10:15:00 |
E005 | 1002 | 加购 | 2025-10-20 10:20:00 |
E006 | 1001 | 点击 | 2025-10-20 10:25:00 |
SQL示例
-- 统计用户事件类型分布及占比
WITH EventTypeCount AS (
-- 第一步:统计各事件类型的独立事件数量
SELECT
event_type,
COUNT(DISTINCT event_id) AS event_count
FROM
event_log
GROUP BY
event_type
)
-- 第二步:计算各类型占比
SELECT
event_type,
event_count,
-- 计算事件总数量,用于占比计算
SUM(event_count) OVER () AS total_event_count,
-- 计算占比并保留2位小数,转换为百分比格式
CONCAT(
ROUND(
event_count / SUM(event_count) OVER () * 100,
2
),
'%'
) AS event_type_ratio
FROM
EventTypeCount
ORDER BY
event_count DESC; -- 按事件数量降序排列,突出核心事件
输出示例
event_type(事件类型) | event_count(事件数量) | total_event_count(总事件数量) | event_type_ratio(事件类型占比) |
---|---|---|---|
点击 | 3 | 6 | 50.00% |
加购 | 2 | 6 | 33.33% |
下单 | 1 | 6 | 16.67% |
22. 环比和同比
业务意义
数据趋势与周期波动分析核心工具:环比(与上一周期比)用于监测短期变化,如电商月度环比销量增长可评估当月促销活动效果;同比(与同期周期比)用于排除季节等周期性因素干扰,如零售行业12月销量同比增长可真实反映年度业务增长态势;企业通过两者结合,既把握短期运营节奏,又判断长期发展趋势。
实现思路
1. 确定统计表与核心字段:以销售表(sales)为数据源,核心字段为sale_date(销售日期)、sale_amount(销售额,元);2. 按周期聚合数据:按“年月”分组统计月度销售额,作为同比和环比的基础数据;3. 计算环比与同比:用LAG()窗口函数获取上一月销售额(环比基数),用DATE_SUB()函数关联去年同月销售额(同比基数),按公式“(当期-基数)/基数”计算增长率。
输入示例
表名:sales(销售表)
sale_id(销售ID) | sale_date(销售日期) | sale_amount(销售额,元) |
---|---|---|
S001 | 2024-09-05 | 15000 |
S002 | 2024-10-12 | 18000 |
S003 | 2025-09-20 | 16500 |
S004 | 2025-10-08 | 22000 |
SQL示例
-- 计算月度销售额的环比和同比增长率
WITH MonthlySales AS (
-- 第一步:按年月聚合月度销售额
SELECT
DATE_FORMAT(sale_date, 'yyyy-MM') AS sale_month,
SUM(sale_amount) AS monthly_sales
FROM
sales
GROUP BY
DATE_FORMAT(sale_date, 'yyyy-MM')
),
SalesWithBase AS (
-- 第二步:获取环比(上一月)和同比(去年同月)基数
SELECT
sale_month,
monthly_sales,
-- 环比基数:上一月销售额
LAG(monthly_sales, 1) OVER (ORDER BY sale_month) AS last_month_sales,
-- 同比基数:去年同月销售额(通过日期关联)
(SELECT monthly_sales
FROM MonthlySales ms2
WHERE ms2.sale_month = DATE_FORMAT(DATE_SUB(STR_TO_DATE(ms1.sale_month, '%Y-%m'), INTERVAL 1 YEAR), '%Y-%m')) AS last_year_same_month_sales
FROM
MonthlySales ms1
)
-- 第三步:计算增长率并格式化
SELECT
sale_month,
monthly_sales,
-- 环比增长率(保留2位小数,无基数时显示“无”)
CASE
WHEN last_month_sales IS NULL THEN '无'
ELSE CONCAT(ROUND((monthly_sales - last_month_sales)/last_month_sales * 100, 2), '%')
END AS mom_growth_rate,
-- 同比增长率(保留2位小数,无基数时显示“无”)
CASE
WHEN last_year_same_month_sales IS NULL THEN '无'
ELSE CONCAT(ROUND((monthly_sales - last_year_same_month_sales)/last_year_same_month_sales * 100, 2), '%')
END AS yoy_growth_rate
FROM
SalesWithBase
ORDER BY
sale_month;
输出示例
sale_month(销售月份) | monthly_sales(月度销售额,元) | mom_growth_rate(环比增长率) | yoy_growth_rate(同比增长率) |
---|---|---|---|
2024-09 | 15000 | 无 | 无 |
2024-10 | 18000 | 20.00% | 无 |
2025-09 | 16500 | 无 | 10.00% |
2025-10 | 22000 | 33.33% | 22.22% |
注:“无”表示无对应周期的基数数据(如首次统计月份无环比基数,无去年同期数据时无同比基数)。
25. 设备多维度统计
业务意义
设备适配与渠道推广优化核心依据:APP运营通过设备类型(手机、平板)、系统版本(iOS16、Android13)、设备品牌分布,定位适配漏洞(如某品牌Android设备闪退率高);电商平台分析设备与转化率关联,发现平板用户客单价更高则优化平板端购物流程;推广端依据设备分布选择投放渠道(如华为用户占比高则加大华为应用市场推广)。
实现思路
1. 确定数据源与核心维度:以设备访问日志表(device_access_log)为数据源,核心维度含设备标识(device_id)、用户ID(user_id)、设备类型(device_type)、系统类型(os_type)、系统版本(os_version)、设备品牌(device_brand);2. 多维度分组统计:按“设备类型-系统类型”“设备品牌-系统版本”等组合维度分组,统计各维度用户数或访问次数;3. 计算占比:用窗口函数获取总数据量,计算各维度占比,明确核心设备群体。
输入示例
表名:device_access_log(设备访问日志表)
log_id(日志ID) | user_id(用户ID) | device_id(设备ID) | device_type(设备类型) | os_type(系统类型) | os_version(系统版本) | device_brand(设备品牌) |
---|---|---|---|---|---|---|
L001 | 1001 | D001 | 手机 | iOS | 16.5 | 苹果 |
L002 | 1002 | D002 | 手机 | Android | 13 | 华为 |
L003 | 1003 | D003 | 平板 | iOS | 16.4 | 苹果 |
L004 | 1004 | D004 | 手机 | Android | 12 | 小米 |
L005 | 1005 | D005 | 手机 | Android | 13 | 华为 |
SQL示例
-- 设备多维度统计(设备类型-系统类型分布、设备品牌占比)
WITH DeviceStats AS (
-- 第一步:去重统计各维度核心数据(按用户-设备去重,避免重复日志干扰)
SELECT DISTINCT
user_id,
device_id,
device_type,
os_type,
device_brand
FROM
device_access_log
),
TotalCount AS (
-- 第二步:获取总用户设备数(去重后)
SELECT COUNT(DISTINCT CONCAT(user_id, device_id)) AS total_device_user
FROM DeviceStats
)
-- 第三步:多维度统计及占比计算
SELECT
-- 维度1:设备类型-系统类型分布
device_type,
os_type,
COUNT(DISTINCT CONCAT(user_id, device_id)) AS device_user_count,
-- 计算该维度占比
CONCAT(ROUND(COUNT(DISTINCT CONCAT(user_id, device_id))/tc.total_device_user*100,2),'%') AS ratio,
-- 维度2:设备品牌(子查询形式)
(SELECT CONCAT(device_brand, ':', COUNT(DISTINCT CONCAT(user_id, device_id)))
FROM DeviceStats ds2
WHERE ds2.device_brand = ds1.device_brand
GROUP BY ds2.device_brand) AS brand_stats
FROM
DeviceStats ds1,
TotalCount tc
GROUP BY
ds1.device_type,
ds1.os_type,
ds1.device_brand,
tc.total_device_user
ORDER BY
device_user_count DESC;
输出示例
device_type(设备类型) | os_type(系统类型) | device_user_count(设备用户数) | ratio(占比) | brand_stats(品牌统计) |
---|---|---|---|---|
手机 | Android | 3 | 60.00% | 华为:2,小米:1 |
手机 | iOS | 1 | 20.00% | 苹果:1 |
平板 | iOS | 1 | 20.00% | 苹果:1 |
26. 用户标签画像并更新
业务意义
精准运营与个性化服务核心支撑:电商通过动态更新用户标签(如“高频消费-母婴品类”),推送专属优惠券与商品;内容平台依据标签(如“科技爱好者-短视频偏好”)实现千人千面推荐;金融平台通过标签更新(如“还款及时-信用升级”)调整服务权限,提升用户体验与运营效率。
实现思路
1. 确定数据源与标签规则:用户行为表(user_behavior)存消费、浏览等行为,标签字典表(tag_dict)定义标签含义与规则;2. 初始标签生成:按规则匹配行为数据(如“年消费≥5000元”打“高消费”标签);3. 标签更新:新增“标签生效时间”“标签失效时间”字段,通过行为变化触发更新(如连续6个月低消费则失效“高消费”标签),保留标签历史记录。
输入示例
表1:user_behavior(用户行为表)
behavior_id(行为ID) | user_id(用户ID) | behavior_type(行为类型) | behavior_amount(消费金额,元) | behavior_time(行为时间) |
---|---|---|---|---|
B001 | 1001 | 消费 | 6000 | 2025-06-10 |
B002 | 1001 | 消费 | 800 | 2025-12-05 |
B003 | 1002 | 消费 | 1200 | 2025-11-20 |
表2:tag_dict(标签字典表)
tag_id(标签ID) | tag_name(标签名称) | rule(标签规则) |
---|---|---|
T001 | 高消费用户 | 年消费≥5000元 |
T002 | 中消费用户 | 年消费1000-4999元 |
表3:user_tag(用户标签表,初始为空)
user_tag_id(标签记录ID) | user_id(用户ID) | tag_id(标签ID) | tag_name(标签名称) | effective_time(生效时间) | expire_time(失效时间) | status(状态:有效/失效) |
---|
SQL示例
-- 1. 生成用户年度消费统计
WITH UserYearConsume AS (
SELECT
user_id,
YEAR(behavior_time) AS stat_year,
SUM(behavior_amount) AS year_consume
FROM
user_behavior
GROUP BY
user_id, YEAR(behavior_time)
),
-- 2. 匹配标签规则生成待更新标签
TagToUpdate AS (
SELECT
uyc.user_id,
td.tag_id,
td.tag_name,
CONCAT(uyc.stat_year, '-01-01') AS effective_time,
CONCAT(uyc.stat_year, '-12-31') AS expire_time,
'有效' AS status
FROM
UserYearConsume uyc
JOIN
tag_dict td ON (td.tag_name = '高消费用户' AND uyc.year_consume ≥ 5000)
OR (td.tag_name = '中消费用户' AND uyc.year_consume BETWEEN 1000 AND 4999)
),
-- 3. 失效历史同类标签
UpdateExpireTag AS (
UPDATE user_tag ut
JOIN TagToUpdate ttu ON ut.user_id = ttu.user_id AND ut.tag_id = ttu.tag_id
SET ut.expire_time = DATE_SUB(ttu.effective_time, INTERVAL 1 DAY),
ut.status = '失效'
WHERE ut.status = '有效'
)
-- 4. 插入新标签记录
INSERT INTO user_tag (user_id, tag_id, tag_name, effective_time, expire_time, status)
SELECT user_id, tag_id, tag_name, effective_time, expire_time, status
FROM TagToUpdate;
输出示例
表3:user_tag(用户标签表,更新后)
user_tag_id(标签记录ID) | user_id(用户ID) | tag_id(标签ID) | tag_name(标签名称) | effective_time(生效时间) | expire_time(失效时间) | status(状态:有效/失效) |
---|---|---|---|---|---|---|
UT001 | 1001 | T001 | 高消费用户 | 2025-01-01 | 2025-12-31 | 有效 |
UT002 | 1002 | T002 | 中消费用户 | 2025-01-01 | 2025-12-31 | 有效 |
注:若用户1001在2026年年消费降至4000元,将自动失效“高消费用户”标签,新增“中消费用户”标签并记录时间节点。
27. 分区表优化查询
业务意义
大数据场景下查询性能与运维效率核心优化手段:当业务数据量达到千万级甚至亿级时(如电商年度订单表、日志表),全表扫描会导致查询延迟激增,分区表通过按指定维度(时间、地域)拆分数据,使查询仅扫描目标分区,大幅减少数据扫描量;同时分区表支持单独对历史分区归档、删除,降低运维成本,避免全表锁表风险。
实现思路
1. 确定分区字段:选择查询高频且区分度高的字段(如时间类“order_date”、地域类“region_id”),此处以常用的时间分区为例;2. 创建分区表:定义表结构时指定分区类型(如RANGE分区)及分区字段,预设或动态添加分区;3. 数据插入与查询:插入数据时自动匹配分区,查询时通过分区条件触发“分区修剪”,仅扫描目标分区;4. 运维管理:定期归档历史分区、清理无效分区。
输入示例
场景:电商订单表(orders),数据量千万级,高频查询近3个月订单,按“order_date”(订单日期)做RANGE分区,分区粒度为月。初始分区规划:2025年10月、2025年11月、2025年12月三个分区
字段名 | 字段类型 | 说明 |
---|---|---|
order_id | VARCHAR(50) | 订单ID(主键) |
user_id | VARCHAR(50) | 用户ID |
order_amount | DECIMAL(10,2) | 订单金额(元) |
order_date | DATE | 订单日期(分区字段) |
待插入数据:
order_id | user_id | order_amount | order_date |
---|---|---|---|
O20251001 | 1001 | 299.00 | 2025-10-05 |
O20251101 | 1002 | 159.00 | 2025-11-10 |
O20251201 | 1003 | 499.00 | 2025-12-15 |
SQL示例
-- 1. 创建RANGE分区表(按订单日期月分区)
CREATE TABLE orders_partitioned (
order_id VARCHAR(50) PRIMARY KEY,
user_id VARCHAR(50),
order_amount DECIMAL(10,2),
order_date DATE
)
PARTITION BY RANGE (TO_DAYS(order_date)) (
PARTITION p202510 VALUES LESS THAN (TO_DAYS('2025-11-01')), -- 2025年10月分区
PARTITION p202511 VALUES LESS THAN (TO_DAYS('2025-12-01')), -- 2025年11月分区
PARTITION p202512 VALUES LESS THAN (TO_DAYS('2026-01-01')) -- 2025年12月分区
);
-- 2. 插入数据(自动匹配分区,无需手动指定)
INSERT INTO orders_partitioned
VALUES
('O20251001', '1001', 299.00, '2025-10-05'),
('O20251101', '1002', 159.00, '2025-11-10'),
('O20251201', '1003', 499.00, '2025-12-15');
-- 3. 分区优化查询(查询2025年11月订单,仅扫描p202511分区)
SELECT
order_id,
user_id,
order_amount,
order_date
FROM orders_partitioned
WHERE order_date BETWEEN '2025-11-01' AND '2025-11-30';
-- 4. 查看执行计划,验证分区修剪(仅扫描p202511)
EXPLAIN PARTITIONS
SELECT
order_id,
user_id,
order_amount,
order_date
FROM orders_partitioned
WHERE order_date BETWEEN '2025-11-01' AND '2025-11-30';
输出示例
1. 查询结果:
order_id | user_id | order_amount(元) | order_date |
---|---|---|---|
O20251101 | 1002 | 159.00 | 2025-11-10 |
2. 执行计划关键信息(验证分区修剪):
id | select_type | table | partitions | type | rows |
---|---|---|---|---|---|
1 | SIMPLE | orders_partitioned | p202511 | ALL | 1 |
注:partitions列显示仅扫描p202511分区,避免全表扫描,千万级数据场景下查询效率可提升10倍以上。
28. 动态列和 Map 合并
业务意义
灵活适配多变属性与多源数据整合的核心方案:面对属性动态变化的场景(如电商商品的自定义属性、用户画像的扩展标签),动态列可避免频繁修改表结构;Map合并则能高效整合多源异构数据(如用户基础属性Map与行为属性Map),减少表关联次数,提升数据查询与分析的灵活性,尤其适用于用户画像构建、商品多维度描述等场景。
实现思路
1. 定义动态列与Map字段:使用支持动态列的数据库(如Hive、MySQL 8.0+)定义动态列,或用Map类型存储键值对属性;2. 插入多维度数据:分别插入基础属性与动态属性,或不同来源的Map数据;3. Map合并:通过数据库内置函数(如Hive的map_concat、MySQL的JSON_MERGE_PATCH)合并多组Map;4. 提取与查询:按需提取动态列或Map中的键值对,实现灵活查询。
输入示例
场景:电商商品表,基础属性固定,扩展属性用Map存储,需合并“基础属性Map”与“营销属性Map”。表1:product_base(商品基础信息表,含Map字段)
product_id | product_name | base_attr_map(基础属性Map) |
---|---|---|
P001 | 纯棉T恤 | {"材质":"纯棉","颜色":"白色","尺码":"M"} |
P002 | 运动鞋 | {"材质":"网面","颜色":"黑色","尺码":"42"} |
表2:product_marketing(商品营销信息表,含Map字段)
product_id | marketing_attr_map(营销属性Map) |
---|---|
P001 | {"折扣":"8折","满减":"满200减50","标签":"热销"} |
P002 | {"折扣":"9折","满减":"满300减80","标签":"新品"} |
SQL示例
-- 以Hive SQL为例,实现动态列提取与Map合并
-- 1. 合并基础属性Map与营销属性Map,提取指定属性
SELECT
pb.product_id,
pb.product_name,
-- Map合并:合并两个Map,键重复时后者覆盖前者(可按需调整)
map_concat(pb.base_attr_map, pm.marketing_attr_map) AS full_attr_map,
-- 提取动态属性:从合并后的Map中提取指定键值
pb.base_attr_map['材质'] AS product_material,
pm.marketing_attr_map['折扣'] AS product_discount,
-- 提取动态列(若使用Hive动态列语法)
pb.`颜色` AS product_color -- 假设动态列名“颜色”与Map键一致
FROM product_base pb
INNER JOIN product_marketing pm
ON pb.product_id = pm.product_id;
-- 以MySQL 8.0+为例(用JSON类型模拟Map)
SELECT
pb.product_id,
pb.product_name,
-- JSON合并(Map合并等价操作)
JSON_MERGE_PATCH(pb.base_attr_json, pm.marketing_attr_json) AS full_attr_json,
-- 提取JSON中指定属性
JSON_EXTRACT(pb.base_attr_json, '$.材质') AS product_material,
JSON_UNQUOTE(JSON_EXTRACT(pm.marketing_attr_json, '$.折扣')) AS product_discount
FROM product_base pb
INNER JOIN product_marketing pm
ON pb.product_id = pm.product_id;
输出示例
1. Hive SQL输出结果:
product_id | product_name | full_attr_map(合并后Map) | product_material(材质) | product_discount(折扣) | product_color(颜色) |
---|---|---|---|---|---|
P001 | 纯棉T恤 | {"材质":"纯棉","颜色":"白色","尺码":"M","折扣":"8折","满减":"满200减50","标签":"热销"} | 纯棉 | 8折 | 白色 |
P002 | 运动鞋 | {"材质":"网面","颜色":"黑色","尺码":"42","折扣":"9折","满减":"满300减80","标签":"新品"} | 网面 | 9折 | 黑色 |
注:Map合并时若存在相同键,map_concat会以第二个Map的值为准,可通过调整参数或自定义函数实现不同覆盖规则。
29. 数据倾斜检测
业务意义
大数据处理效率与分析准确性核心保障手段:在Spark、Hive等分布式计算场景中,数据倾斜(如某分组数据量占比超50%)会导致单个任务节点负载过高,其余节点闲置,任务执行时间激增;同时倾斜数据可能掩盖真实分布特征,如电商订单按用户分组时,某测试用户的海量模拟订单会导致“高消费用户”占比统计失真,影响运营策略制定。检测并定位倾斜数据是分布式任务优化与数据质量管控的关键前置步骤。
实现思路
1. 确定检测维度:选取分布式计算中高频分组字段(如user_id、product_id、region_id)或数据分片字段,聚焦“记录数分布”“数据量大小分布”两个核心维度;2. 计算分布指标:按检测字段分组统计记录数/数据量,计算变异系数(标准差/均值)、最大分组占比(最大分组值/总数据量)、最大最小比等指标;3. 设定倾斜阈值:结合业务场景设定阈值(如变异系数>3、最大分组占比>30%判定为倾斜);4. 定位倾斜键:输出超出阈值的分组字段值(倾斜键)及具体分布数据。
输入示例
场景:电商订单表(orders),分布式计算时按user_id分组统计订单量,存在明显数据倾斜(用户1001为测试账号,订单量远超其他用户)。
order_id(订单ID) | user_id(用户ID) | order_amount(订单金额,元) | order_date(订单日期) |
---|---|---|---|
O001 | 1001 | 299 | 2025-10-01 |
O002 | 1001 | 159 | 2025-10-01 |
O003 | 1002 | 499 | 2025-10-01 |
O004 | 1003 | 399 | 2025-10-01 |
O005 | 1001 | 599 | 2025-10-01 |
SQL示例
-- 数据倾斜检测:按user_id分组检测订单量分布倾斜
WITH GroupStats AS (
-- 第一步:按检测字段分组,统计核心指标
SELECT
user_id,
COUNT(order_id) AS group_record_count, -- 分组记录数
SUM(LENGTH(TO_JSON(orders))) AS group_data_size -- 分组数据量(模拟,单位:字节)
FROM orders
GROUP BY user_id
),
TotalStats AS (
-- 第二步:计算总数据量与分布统计指标
SELECT
SUM(group_record_count) AS total_record_count,
AVG(group_record_count) AS avg_group_record_count,
STDDEV(group_record_count) AS std_group_record_count,
MAX(group_record_count) AS max_group_record_count,
MIN(group_record_count) AS min_group_record_count
FROM GroupStats
)
-- 第三步:计算倾斜判定指标,定位倾斜键
SELECT
gs.user_id AS skew_key, -- 倾斜键(分组字段值)
gs.group_record_count,
-- 计算关键倾斜指标
ROUND(gs.group_record_count / ts.total_record_count * 100, 2) AS group_ratio, -- 分组占比
ROUND(ts.std_group_record_count / ts.avg_group_record_count, 2) AS cv, -- 变异系数(>3判定为倾斜)
ts.max_group_record_count / ts.min_group_record_count AS max_min_ratio, -- 最大最小比(>10判定为倾斜)
-- 倾斜判定结果
CASE
WHEN ROUND(gs.group_record_count / ts.total_record_count * 100, 2) > 30
OR ROUND(ts.std_group_record_count / ts.avg_group_record_count, 2) > 3 THEN '是'
ELSE '否'
END AS is_skew
FROM GroupStats gs, TotalStats ts
ORDER BY gs.group_record_count DESC;
输出示例
skew_key(倾斜键) | group_record_count(分组记录数) | group_ratio(分组占比%) | cv(变异系数) | max_min_ratio(最大最小比) | is_skew(是否倾斜) |
---|---|---|---|---|---|
1001 | 3 | 60.00 | 1.29 | 3.00 | 是 |
1002 | 1 | 20.00 | 1.29 | 3.00 | 否 |
1003 | 1 | 20.00 | 1.29 | 3.00 | 否 |
注:用户1001分组占比达60%,远超30%阈值,判定为倾斜键,需通过“拆分倾斜键+单独处理”等方式优化分布式任务。
30. 异常值检测(IQR 法)
业务意义
数据质量管控与业务异常监控核心方法:异常值(如远超常规的订单金额、异常低的用户活跃度)会严重干扰统计分析结果,如某笔10万元的测试订单会使电商客单价计算偏差10倍;同时异常值可能隐含业务问题,如异常高的退款金额可能是欺诈行为,异常低的设备在线时长可能是硬件故障。IQR法(四分位距法)对极端值不敏感,较均值±3标准差法更适用于非正态分布数据的异常检测。
实现思路
1. 确定检测字段:选取需监控的数值型字段(如order_amount、user_active_time);2. 计算四分位数:通过排序获取字段的上四分位数Q3(75%分位数)、下四分位数Q1(25%分位数);3. 确定异常边界:计算四分位距IQR=Q3-Q1,上边界=Q3+1.5*IQR,下边界=Q1-1.5*IQR(1.5为经验系数,可按需调整);4. 筛选异常值:提取字段值超出上下边界的数据,标注异常类型(上异常/下异常)。
输入示例
场景:电商订单金额表(order_amount_detail),含正常订单与测试/异常订单(如1元、10000元)。
order_id(订单ID) | user_id(用户ID) | order_amount(订单金额,元) | order_date(订单日期) |
---|---|---|---|
O001 | 1001 | 299 | 2025-10-01 |
O002 | 1002 | 159 | 2025-10-01 |
O003 | 1003 | 10000 | 2025-10-01 |
O004 | 1004 | 399 | 2025-10-01 |
O005 | 1005 | 1 | 2025-10-01 |
SQL示例
-- 异常值检测(IQR法):检测订单金额异常值
WITH SortedData AS (
-- 第一步:对检测字段排序,添加排序序号
SELECT
order_id,
user_id,
order_amount,
ROW_NUMBER() OVER (ORDER BY order_amount) AS rn,
COUNT(*) OVER () AS total_rows
FROM order_amount_detail
),
QuartileCalc AS (
-- 第二步:计算Q1(25%分位数)、Q3(75%分位数)
SELECT
-- Q1:n*25%位置的数值(向上取整)
MAX(CASE WHEN rn = CEIL(total_rows * 0.25) THEN order_amount END) AS Q1,
-- Q3:n*75%位置的数值(向上取整)
MAX(CASE WHEN rn = CEIL(total_rows * 0.75) THEN order_amount END) AS Q3
FROM SortedData
),
BoundCalc AS (
-- 第三步:计算异常值边界
SELECT
Q1,
Q3,
Q3 - Q1 AS IQR,
Q3 + 1.5 * (Q3 - Q1) AS upper_bound, -- 上边界
Q1 - 1.5 * (Q3 - Q1) AS lower_bound -- 下边界
FROM QuartileCalc
)
-- 第四步:筛选异常值并标注类型
SELECT
sd.order_id,
sd.user_id,
sd.order_amount,
bc.upper_bound,
bc.lower_bound,
CASE
WHEN sd.order_amount > bc.upper_bound THEN '上异常(远超常规金额)'
WHEN sd.order_amount < bc.lower_bound THEN '下异常(远低于常规金额)'
ELSE '正常'
END AS anomaly_type
FROM SortedData sd, BoundCalc bc
ORDER BY sd.order_amount DESC;
输出示例
order_id(订单ID) | user_id(用户ID) | order_amount(订单金额,元) | upper_bound(上边界) | lower_bound(下边界) | anomaly_type(异常类型) |
---|---|---|---|---|---|
O003 | 1003 | 10000 | 699.0 | -141.0 | 上异常(远超常规金额) |
O004 | 1004 | 399 | 699.0 | -141.0 | 正常 |
O001 | 1001 | 299 | 699.0 | -141.0 | 正常 |
O002 | 1002 | 159 | 699.0 | -141.0 | 正常 |
O005 | 1005 | 1 | 699.0 | -141.0 | 正常 |
注:下边界为负值时,因订单金额不可能为负,可将实际下边界调整为0,此时1元订单需结合业务场景判断是否为异常(如秒杀订单可能为正常)。
31. TopK 高频用户
业务意义
核心用户识别与精细化运营核心依据:电商平台通过定位高频下单/访问用户,将其划分为核心客户群体,提供专属权益(如VIP服务、定制优惠券)提升留存;内容平台识别高频互动用户(点赞、评论),推送专属内容或邀请参与创作者计划;金融平台监控高频交易用户,既可为高价值用户提供快速通道,也可防范异常交易风险,实现差异化服务。
实现思路
1. 确定数据源与核心字段:以用户行为表(如订单表、访问日志表)为数据源,核心字段为user_id(用户ID)、behavior_time(行为时间)、behavior_type(行为类型,可选);2. 限定统计范围:按业务需求筛选时间周期(如近30天)、行为类型(如下单行为);3. 统计高频用户:按user_id分组,统计行为次数(如订单数、访问次数);4. 排序取TopK:按行为次数降序排序,通过LIMIT关键字取前K名用户,支持添加排名标识。
输入示例
场景:电商平台统计近30天(2025-09-20至2025-10-19)下单高频用户Top3,数据源为订单表(orders)。
order_id(订单ID) | user_id(用户ID) | order_amount(订单金额,元) | order_time(下单时间) |
O001 | 1001 | 299 | 2025-09-25 |
O002 | 1001 | 159 | 2025-10-08 |
O003 | 1002 | 499 | 2025-10-05 |
O004 | 1003 | 399 | 2025-10-10 |
O005 | 1001 | 599 | 2025-10-15 |
O006 | 1002 | 299 | 2025-10-12 |
SQL示例
-- 统计近30天下单高频用户Top3(按下单次数排序)
WITH UserOrderStats AS (
-- 第一步:筛选时间范围,按用户分组统计下单次数与消费总额
SELECT
user_id,
COUNT(order_id) AS order_count, -- 下单次数(高频核心指标)
SUM(order_amount) AS total_consume -- 消费总额(辅助评估用户价值)
FROM orders
WHERE order_time BETWEEN '2025-09-20' AND '2025-10-19' -- 限定近30天
GROUP BY user_id
)
-- 第二步:排序取Top3,添加排名
SELECT
ROW_NUMBER() OVER (ORDER BY order_count DESC, total_consume DESC) AS rank, -- 下单次数优先,总额为辅
user_id,
order_count,
total_consume
FROM UserOrderStats
LIMIT 3; -- 取Top3,调整数字可获取TopK(如Top5改LIMIT 5)
输出示例
rank(排名) | user_id(用户ID) | order_count(下单次数) | total_consume(消费总额,元) |
1 | 1001 | 3 | 1057 |
2 | 1002 | 2 | 798 |
3 | 1003 | 1 | 399 |
注:排名规则可按需调整,如“访问高频”可将order_count替换为访问次数,统计数据源换为访问日志表。
32. 访问量峰值时间段
业务意义
资源调度与运营活动优化核心参考:技术端依据峰值时间段提前扩容服务器,避免系统卡顿(如电商大促峰值前扩容);运营端在峰值时段安排客服人力、推送运营活动(如直播、秒杀)提升参与度;营销端选择峰值时段投放广告,提高曝光转化率,同时低谷时段可进行系统维护,减少对用户的影响。
实现思路
1. 确定数据源与核心字段:以访问日志表(access_log)为数据源,核心字段为access_time(访问时间)、user_id(用户ID);2. 定义时间粒度:按业务需求选择粒度(如小时、半小时、15分钟),常用“小时”粒度适配多数场景;3. 统计时段访问量:提取访问时间的粒度信息(如小时),按粒度分组统计访问次数(去重用户ID避免重复计数);4. 定位峰值:按访问量降序排序,取访问量最高的时段为峰值时间段。
输入示例
场景:统计某电商平台2025-10-19当天各小时访问量,定位峰值时间段,数据源为访问日志表(access_log)。
log_id(日志ID) | user_id(用户ID) | access_time(访问时间) | page_id(访问页面ID) |
L001 | 1001 | 2025-10-19 09:15:00 | P01 |
L002 | 1002 | 2025-10-19 10:30:00 | P02 |
L003 | 1003 | 2025-10-19 10:45:00 | P01 |
L004 | 1001 | 2025-10-19 10:20:00 | P03 |
L005 | 1004 | 2025-10-19 14:20:00 | P02 |
L006 | 1005 | 2025-10-19 10:50:00 | P01 |
SQL示例
-- 统计2025-10-19当天各小时访问量,定位峰值时间段(小时粒度)
WITH HourlyAccessStats AS (
-- 第一步:提取小时粒度,按小时分组统计去重访问量
SELECT
DATE_FORMAT(access_time, '%Y-%m-%d %H:00:00') AS access_hour, -- 提取小时时间段
COUNT(DISTINCT user_id) AS unique_access_count, -- 去重访问量(核心指标)
COUNT(log_id) AS total_access_count -- 总访问量(含重复访问,辅助参考)
FROM access_log
WHERE DATE(access_time) = '2025-10-19' -- 限定当天
GROUP BY access_hour
)
-- 第二步:排序定位峰值,添加访问量占比
SELECT
access_hour AS time_period,
unique_access_count,
total_access_count,
-- 计算各时段访问量占比(总去重访问量)
CONCAT(ROUND(unique_access_count / SUM(unique_access_count) OVER () * 100, 2), '%') AS access_ratio,
-- 标记峰值时间段
CASE WHEN unique_access_count = MAX(unique_access_count) OVER () THEN '是' ELSE '否' END AS is_peak
FROM HourlyAccessStats
ORDER BY unique_access_count DESC;
输出示例
time_period(时间段) | unique_access_count(去重访问量) | total_access_count(总访问量) | access_ratio(访问量占比) | is_peak(是否峰值) |
2025-10-19 10:00:00 | 4 | 4 | 66.67% | 是 |
2025-10-19 09:00:00 | 1 | 1 | 16.67% | 否 |
2025-10-19 14:00:00 | 1 | 1 | 16.67% | 否 |
注:时间粒度可按需调整,如统计半小时峰值可将DATE_FORMAT格式改为'%Y-%m-%d %H:%i:00'并按30分钟对齐。
32. 访问量峰值时间段
业务意义
资源调度与运营活动优化核心参考:技术端依据峰值时间段提前扩容服务器,避免系统卡顿(如电商大促峰值前扩容);运营端在峰值时段安排客服人力、推送运营活动(如直播、秒杀)提升参与度;营销端选择峰值时段投放广告,提高曝光转化率,同时低谷时段可进行系统维护,减少对用户的影响。
实现思路
1. 确定数据源与核心字段:以访问日志表(access_log)为数据源,核心字段为access_time(访问时间)、user_id(用户ID);2. 定义时间粒度:按业务需求选择粒度(如小时、半小时、15分钟),常用“小时”粒度适配多数场景;3. 统计时段访问量:提取访问时间的粒度信息(如小时),按粒度分组统计访问次数(去重用户ID避免重复计数);4. 定位峰值:按访问量降序排序,取访问量最高的时段为峰值时间段。
输入示例
场景:统计某电商平台2025-10-19当天各小时访问量,定位峰值时间段,数据源为访问日志表(access_log)。
log_id(日志ID) | user_id(用户ID) | access_time(访问时间) | page_id(访问页面ID) |
L001 | 1001 | 2025-10-19 09:15:00 | P01 |
L002 | 1002 | 2025-10-19 10:30:00 | P02 |
L003 | 1003 | 2025-10-19 10:45:00 | P01 |
L004 | 1001 | 2025-10-19 10:20:00 | P03 |
L005 | 1004 | 2025-10-19 14:20:00 | P02 |
L006 | 1005 | 2025-10-19 10:50:00 | P01 |
SQL示例
-- 统计2025-10-19当天各小时访问量,定位峰值时间段(小时粒度)
WITH HourlyAccessStats AS (
-- 第一步:提取小时粒度,按小时分组统计去重访问量
SELECT
DATE_FORMAT(access_time, '%Y-%m-%d %H:00:00') AS access_hour, -- 提取小时时间段
COUNT(DISTINCT user_id) AS unique_access_count, -- 去重访问量(核心指标)
COUNT(log_id) AS total_access_count -- 总访问量(含重复访问,辅助参考)
FROM access_log
WHERE DATE(access_time) = '2025-10-19' -- 限定当天
GROUP BY access_hour
)
-- 第二步:排序定位峰值,添加访问量占比
SELECT
access_hour AS time_period,
unique_access_count,
total_access_count,
-- 计算各时段访问量占比(总去重访问量)
CONCAT(ROUND(unique_access_count / SUM(unique_access_count) OVER () * 100, 2), '%') AS access_ratio,
-- 标记峰值时间段
CASE WHEN unique_access_count = MAX(unique_access_count) OVER () THEN '是' ELSE '否' END AS is_peak
FROM HourlyAccessStats
ORDER BY unique_access_count DESC;
输出示例
time_period(时间段) | unique_access_count(去重访问量) | total_access_count(总访问量) | access_ratio(访问量占比) | is_peak(是否峰值) |
2025-10-19 10:00:00 | 4 | 4 | 66.67% | 是 |
2025-10-19 09:00:00 | 1 | 1 | 16.67% | 否 |
2025-10-19 14:00:00 | 1 | 1 | 16.67% | 否 |
注:时间粒度可按需调整,如统计半小时峰值可将DATE_FORMAT格式改为'%Y-%m-%d %H:%i:00'并按30分钟对齐。
33. 邮件发送成功率
业务意义
邮件营销效果与系统稳定性核心评估指标:电商通过邮件发送成功率判断营销触达效果(如新品推送、优惠券发放),成功率低则优化邮件内容(避免垃圾邮件标记)或清洗用户邮箱;金融机构通过成功率保障关键通知触达(如账单提醒、风控预警),降低业务风险;技术端可通过成功率波动监控邮件发送系统稳定性,及时排查故障。
实现思路
1. 确定数据源与核心字段:以邮件发送日志表(email_send_log)为数据源,核心字段为send_id(发送ID)、user_id(用户ID)、email(用户邮箱)、send_status(发送状态:成功/失败)、send_time(发送时间)、fail_reason(失败原因,可选);2. 限定统计范围:按业务需求筛选时间周期(如近7天)、邮件类型(如营销邮件/通知邮件);3. 分组统计:按user_id、发送日期或邮件类型分组,统计总发送数与成功发送数;4. 计算成功率:成功数/总发送数*100%,标注失败原因占比(可选)。
输入示例
场景:统计某电商近7天(2025-10-13至2025-10-19)各用户邮件发送成功率,数据源为邮件发送日志表(email_send_log)。
send_id(发送ID) | user_id(用户ID) | email(邮箱) | send_status(发送状态) | send_time(发送时间) | fail_reason(失败原因) |
E001 | 1001 | 1001@xxx.com | 成功 | 2025-10-15 | 无 |
E002 | 1001 | 1001@xxx.com | 失败 | 2025-10-17 | 邮箱不存在 |
E003 | 1002 | 1002@xxx.com | 成功 | 2025-10-14 | 无 |
E004 | 1003 | 1003@xxx.com | 失败 | 2025-10-16 | 垃圾邮件拦截 |
E005 | 1002 | 1002@xxx.com | 成功 | 2025-10-18 | 无 |
SQL示例
-- 统计近7天各用户邮件发送成功率
WITH EmailSendStats AS (
-- 第一步:筛选时间范围,按用户分组统计核心指标
SELECT
user_id,
COUNT(send_id) AS total_send_count, -- 总发送数
SUM(CASE WHEN send_status = '成功' THEN 1 ELSE 0 END) AS success_send_count, -- 成功发送数
-- 统计主要失败原因占比(可选)
SUM(CASE WHEN fail_reason = '邮箱不存在' THEN 1 ELSE 0 END) AS invalid_email_count,
SUM(CASE WHEN fail_reason = '垃圾邮件拦截' THEN 1 ELSE 0 END) AS spam_intercept_count
FROM email_send_log
WHERE send_time BETWEEN '2025-10-13' AND '2025-10-19' -- 限定近7天
GROUP BY user_id
)
-- 第二步:计算成功率及辅助指标
SELECT
user_id,
total_send_count,
success_send_count,
-- 计算成功率(保留2位小数)
CONCAT(ROUND(success_send_count / total_send_count * 100, 2), '%') AS send_success_rate,
-- 失败原因占比(相对于总发送数)
CONCAT(ROUND(invalid_email_count / total_send_count * 100, 2), '%') AS invalid_email_ratio,
CONCAT(ROUND(spam_intercept_count / total_send_count * 100, 2), '%') AS spam_intercept_ratio
FROM EmailSendStats
ORDER BY send_success_rate DESC;
输出示例
user_id(用户ID) | total_send_count(总发送数) | success_send_count(成功发送数) | send_success_rate(发送成功率) | invalid_email_ratio(无效邮箱占比) | spam_intercept_ratio(垃圾邮件拦截占比) |
1002 | 2 | 2 | 100.00% | 0.00% | 0.00% |
1001 | 2 | 1 | 50.00% | 50.00% | 0.00% |
1003 | 1 | 0 | 0.00% | 0.00% | 100.00% |
注:可按邮件类型(如营销邮件/通知邮件)分组统计,针对性优化不同类型邮件的发送策略。
34. 用户生命周期价值(LTV)
业务意义
用户价值评估与精细化运营核心依据:电商通过LTV区分高价值用户(长期高复购)与低价值用户,对高价值用户提供专属权益提升留存,对低价值用户设计激活策略;金融机构通过LTV评估客户长期贡献度,优化理财产品推荐;内容平台通过LTV识别高粘性用户,优先提供创作扶持。LTV是资源分配、用户分层的核心指标。
实现思路
1. 确定数据源与核心字段:以用户表(users)和订单表(orders)为数据源,核心字段为user_id(用户ID)、register_time(注册时间)、order_amount(订单金额)、order_time(下单时间);2. 计算生命周期时长:按用户分组,取首单时间与末单时间的差值(或注册时间与末单时间的差值)作为生命周期时长;3. 统计生命周期消费:按用户分组,求和订单金额得到生命周期总消费;4. 计算LTV:常用“生命周期总消费”或“生命周期总消费/生命周期时长(如月均消费)”作为LTV指标。
输入示例
场景:统计某电商平台用户的生命周期价值(按“总消费”及“月均消费”双指标评估),数据源为用户表(users)和订单表(orders)。表1:users(用户表)
user_id(用户ID) | user_name(用户名) | register_time(注册时间) |
1001 | 张三 | 2025-01-15 |
1002 | 李四 | 2025-03-20 |
1003 | 王五 | 2025-05-10 |
表2:orders(订单表)
order_id(订单ID) | user_id(用户ID) | order_amount(订单金额,元) | order_time(下单时间) |
O001 | 1001 | 299 | 2025-01-20 |
O002 | 1001 | 499 | 2025-04-15 |
O003 | 1001 | 599 | 2025-10-10 |
O004 | 1002 | 159 | 2025-03-25 |
O005 | 1003 | 399 | 2025-05-15 |
SQL示例
-- 计算用户生命周期价值(LTV):总消费+月均消费
WITH UserOrderStats AS (
-- 第一步:按用户分组,统计订单相关指标
SELECT
user_id,
MIN(order_time) AS first_order_time, -- 首单时间
MAX(order_time) AS last_order_time, -- 末单时间
SUM(order_amount) AS total_consume, -- 生命周期总消费
COUNT(order_id) AS order_count -- 订单数(辅助指标)
FROM orders
GROUP BY user_id
)
-- 第二步:关联用户表,计算生命周期时长及LTV
SELECT
u.user_id,
u.user_name,
u.register_time,
uos.first_order_time,
uos.last_order_time,
-- 计算生命周期时长(月,保留1位小数)
ROUND(DATEDIFF(uos.last_order_time, u.register_time) / 30, 1) AS lifecycle_months,
uos.total_consume AS ltv_total, -- LTV指标1:生命周期总消费
-- LTV指标2:月均消费(总消费/生命周期时长,保留2位小数)
ROUND(uos.total_consume / (DATEDIFF(uos.last_order_time, u.register_time) / 30), 2) AS ltv_monthly,
uos.order_count
FROM users u
LEFT JOIN UserOrderStats uos
ON u.user_id = uos.user_id -- 关联用户与订单数据
ORDER BY ltv_total DESC;
输出示例
user_id(用户ID) | user_name(用户名) | lifecycle_months(生命周期时长,月) | ltv_total(总消费LTV,元) | ltv_monthly(月均消费LTV,元) | order_count(订单数) |
1001 | 张三 | 8.8 | 1397 | 158.75 | 3 |
1003 | 王五 | 5.2 | 399 | 76.73 | 1 |
1002 | 李四 | 6.8 | 159 | 23.38 | 1 |
注:若需更精准的LTV计算,可引入“折现率”(未来消费折算为当前价值)或“留存率”预测,适配长期业务规划。
35. 分桶后多表关联
业务意义
大数据场景下关联查询性能优化核心方案:当两张大表(如千万级订单表与亿级用户行为表)进行关联时,普通JOIN会产生大量数据shuffle,导致任务执行缓慢。分桶通过将关联字段相同的数据分配到同一分桶,关联时仅需桶内数据匹配,大幅减少shuffle数据量;例如电商平台关联订单表与用户浏览表时,分桶后关联效率可提升5-10倍,支撑实时报表生成与高频分析需求。
实现思路
1. 确定分桶核心要素:选择高频关联字段(如user_id)作为分桶字段,根据数据量与集群节点数设定分桶数(如10个桶,需为节点数整数倍);2. 创建分桶表:定义两张待关联表时指定分桶字段与分桶数,确保分桶规则一致;3. 加载数据:将源数据插入分桶表,数据自动按分桶规则分配到对应桶中;4. 分桶关联查询:使用CLUSTER BY或分桶表直接关联,实现桶内高效匹配。
输入示例
场景:电商订单表(orders)与用户浏览表(user_browse)均按user_id分桶(分桶数2),需关联查询用户下单与浏览行为关联数据。表1:orders(订单分桶表,分桶字段user_id,分桶数2)
order_id(订单ID) | user_id(用户ID) | order_amount(订单金额,元) | order_time(下单时间) |
---|---|---|---|
O001 | 1001 | 299 | 2025-10-01 |
O002 | 1002 | 159 | 2025-10-01 |
O003 | 1003 | 499 | 2025-10-01 |
表2:user_browse(用户浏览分桶表,分桶字段user_id,分桶数2)
browse_id(浏览ID) | user_id(用户ID) | page_id(浏览页面ID) | browse_time(浏览时间) |
---|---|---|---|
B001 | 1001 | P001 | 2025-10-01 09:00 |
B002 | 1002 | P002 | 2025-10-01 10:00 |
B003 | 1003 | P001 | 2025-10-01 11:00 |
SQL示例
-- 1. 创建订单分桶表(分桶字段user_id,分桶数2)
CREATE TABLE orders_bucket (
order_id VARCHAR(50),
user_id VARCHAR(50),
order_amount DECIMAL(10,2),
order_time DATE
)
CLUSTERED BY (user_id) INTO 2 BUCKETS -- 分桶配置:字段+数量
STORED AS ORC;
-- 2. 创建用户浏览分桶表(分桶规则与订单表一致)
CREATE TABLE user_browse_bucket (
browse_id VARCHAR(50),
user_id VARCHAR(50),
page_id VARCHAR(50),
browse_time DATETIME
)
CLUSTERED BY (user_id) INTO 2 BUCKETS
STORED AS ORC;
-- 3. 插入数据(自动按分桶规则分配到对应桶)
INSERT INTO orders_bucket VALUES
('O001','1001',299.00,'2025-10-01'),
('O002','1002',159.00,'2025-10-01'),
('O003','1003',499.00,'2025-10-01');
INSERT INTO user_browse_bucket VALUES
('B001','1001','P001','2025-10-01 09:00:00'),
('B002','1002','P002','2025-10-01 10:00:00'),
('B003','1003','P001','2025-10-01 11:00:00');
-- 4. 分桶关联查询(仅桶内数据匹配,减少shuffle)
SELECT
o.order_id,
o.user_id,
u.browse_id,
u.page_id,
o.order_amount,
o.order_time,
u.browse_time
FROM orders_bucket o
INNER JOIN user_browse_bucket u
ON o.user_id = u.user_id -- 分桶字段关联,自动触发桶内关联
WHERE o.order_time = '2025-10-01'
ORDER BY o.user_id;
输出示例
order_id(订单ID) | user_id(用户ID) | browse_id(浏览ID) | page_id(浏览页面ID) | order_amount(元) | order_time(下单时间) | browse_time(浏览时间) |
---|---|---|---|---|---|---|
O001 | 1001 | B001 | P001 | 299.00 | 2025-10-01 | 2025-10-01 09:00:00 |
O002 | 1002 | B002 | P002 | 159.00 | 2025-10-01 | 2025-10-01 10:00:00 |
O003 | 1003 | B003 | P001 | 499.00 | 2025-10-01 | 2025-10-01 11:00:00 |
注:分桶数需结合集群节点数配置(如2节点集群设2/4个桶),避免单桶数据量过大;分桶字段必须为关联字段,否则无法发挥优化效果。
36. 实时日志批量合并
业务意义
实时数据处理性能与数据一致性平衡的核心手段:实时日志(如访问日志、行为日志)高频产生(每秒万条级),单条写入会导致存储IO瓶颈;批量合并可减少写入次数,降低IO压力,同时通过按时间/用户维度合并,避免数据重复(如同一用户同一行为的重复日志);例如直播平台合并实时观看日志,既保障秒级数据更新,又避免重复统计观看时长,支撑实时在线人数监控。
实现思路
1. 确定合并规则:按核心维度(如user_id+behavior_type)去重,按时间窗口(如5分钟)或数据量(如1000条)触发批量合并;2. 日志暂存:将实时日志写入临时表(如Kafka消息队列或Hive临时表);3. 批量合并:达到触发条件后,读取临时表数据,按规则去重合并(如聚合观看时长);4. 写入目标表:将合并后的数据写入正式表,支撑后续分析。
输入示例
场景:直播平台实时观看日志,按“user_id+live_id”维度去重,5分钟批量合并,计算用户观看时长,数据源为实时日志临时表(live_watch_log_temp)。
log_id(日志ID) | user_id(用户ID) | live_id(直播ID) | watch_duration(单次观看时长,秒) | log_time(日志产生时间) |
---|---|---|---|---|
L001 | 1001 | LV001 | 30 | 2025-10-01 19:01:00 |
L002 | 1001 | LV001 | 40 | 2025-10-01 19:03:00 |
L003 | 1002 | LV001 | 50 | 2025-10-01 19:02:00 |
L004 | 1001 | LV001 | 20 | 2025-10-01 19:04:00 |
SQL示例
-- 以Hive SQL为例,5分钟批量合并实时观看日志
-- 1. 创建实时日志临时表(存储原始日志,支持高频写入)
CREATE TABLE live_watch_log_temp (
log_id VARCHAR(50),
user_id VARCHAR(50),
live_id VARCHAR(50),
watch_duration INT,
log_time DATETIME
)
STORED AS ORC
TBLPROPERTIES ('transactional'='true'); -- 支持事务,避免写入冲突
-- 2. 创建正式表(存储合并后的数据)
CREATE TABLE live_watch_log_merge (
user_id VARCHAR(50),
live_id VARCHAR(50),
total_watch_duration INT, -- 合并后总观看时长
merge_time DATETIME, -- 合并时间
log_count INT -- 合并的日志条数(辅助指标)
)
PARTITIONED BY (merge_hour STRING) -- 按小时分区,便于查询
STORED AS ORC;
-- 3. 批量合并SQL(5分钟执行一次,可通过调度工具触发)
INSERT INTO live_watch_log_merge (user_id, live_id, total_watch_duration, merge_time, log_count, merge_hour)
SELECT
user_id,
live_id,
SUM(watch_duration) AS total_watch_duration, -- 合并观看时长
CURRENT_TIMESTAMP() AS merge_time,
COUNT(log_id) AS log_count,
DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y-%m-%d %H') AS merge_hour -- 分区字段
FROM live_watch_log_temp
-- 筛选近5分钟未合并的日志(需记录已合并日志ID,此处简化用时间筛选)
WHERE log_time BETWEEN DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 5 MINUTE) AND CURRENT_TIMESTAMP()
GROUP BY user_id, live_id -- 按去重维度分组
-- 去重逻辑:同一用户同一直播的日志合并
ON DUPLICATE KEY UPDATE
total_watch_duration = total_watch_duration + VALUES(total_watch_duration),
log_count = log_count + VALUES(log_count); -- 重复合并时累加
-- 4. 清空临时表已合并数据(避免重复合并)
DELETE FROM live_watch_log_temp
WHERE log_time BETWEEN DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 5 MINUTE) AND CURRENT_TIMESTAMP();
输出示例
user_id(用户ID) | live_id(直播ID) | total_watch_duration(总观看时长,秒) | merge_time(合并时间) | log_count(合并日志条数) | merge_hour(分区字段) |
---|---|---|---|---|---|
1001 | LV001 | 90 | 2025-10-01 19:05:00 | 3 | 2025-10-01 19 |
1002 | LV001 | 50 | 2025-10-01 19:05:00 | 1 | 2025-10-01 19 |
注:批量触发条件可按需调整,高频场景用1分钟窗口,低频次场景用10分钟窗口;去重维度需结合业务场景设计,如“用户-设备-行为”三维度去重。
37. 按小时分布统计
业务意义
时间维度行为规律挖掘的核心手段:电商通过统计各小时访问量/下单量分布,定位流量高峰时段(如20-22点),集中投放营销活动提升转化;客服行业依据小时咨询量分布,合理排班避免人力浪费;直播平台通过观看人数小时分布,优化直播场次安排(如选择19-21点开播提升观看量)。同时为技术端峰值扩容、低谷维护提供数据支撑。
实现思路
1. 确定数据源与核心指标:以行为日志表(如访问日志、订单日志)为数据源,核心字段为时间字段(如access_time、order_time)、统计指标字段(如user_id、order_id);2. 提取小时维度:使用时间函数提取时间字段的“小时”部分(如00-23时);3. 分组统计:按“小时”维度分组,统计核心指标(如去重用户数、订单数);4. 补充分析:计算各小时指标占比,明确高峰时段贡献度。
输入示例
场景:统计某电商2025-10-01当天各小时访问量分布,数据源为访问日志表(access_log)。
log_id(日志ID) | user_id(用户ID) | access_time(访问时间) | behavior(行为类型) |
---|---|---|---|
L001 | 1001 | 2025-10-01 09:15:00 | 浏览 |
L002 | 1002 | 2025-10-01 10:30:00 | 下单 |
L003 | 1001 | 2025-10-01 10:45:00 | 浏览 |
L004 | 1003 | 2025-10-01 20:10:00 | 下单 |
L005 | 1004 | 2025-10-01 20:30:00 | 浏览 |
L006 | 1005 | 2025-10-01 20:45:00 | 下单 |
SQL示例
-- 统计2025-10-01当天各小时访问量与下单量分布
SELECT
-- 提取小时维度(00-23)
DATE_FORMAT(access_time, '%H') AS hour_of_day,
-- 统计核心指标:去重访问用户数、总访问次数、下单次数
COUNT(DISTINCT user_id) AS unique_user_count,
COUNT(log_id) AS total_access_count,
SUM(CASE WHEN behavior = '下单' THEN 1 ELSE 0 END) AS order_count,
-- 计算下单转化率(下单次数/总访问次数)
CONCAT(ROUND(SUM(CASE WHEN behavior = '下单' THEN 1 ELSE 0 END) / COUNT(log_id) * 100, 2), '%') AS conversion_rate,
-- 计算各小时访问量占比
CONCAT(ROUND(COUNT(log_id) / SUM(COUNT(log_id)) OVER (), 4) * 100, '%') AS access_ratio
FROM access_log
-- 限定统计日期
WHERE DATE(access_time) = '2025-10-01'
-- 按小时分组
GROUP BY hour_of_day
-- 按小时升序排序
ORDER BY hour_of_day ASC;
输出示例
hour_of_day(小时) | unique_user_count(去重访问用户数) | total_access_count(总访问次数) | order_count(下单次数) | conversion_rate(转化率) | access_ratio(访问量占比) |
---|---|---|---|---|---|
09 | 1 | 1 | 0 | 0.00% | 16.67% |
10 | 2 | 2 | 1 | 50.00% | 33.33% |
20 | 3 | 3 | 2 | 66.67% | 50.00% |
注:可按需调整时间粒度(如15分钟、30分钟),将DATE_FORMAT格式改为'%H:%i'并按对应粒度分组;支持多日期对比,增加日期字段分组即可。
38. CSV 存储与加载
业务意义
跨系统数据交互与轻量化存储的核心方案:CSV格式具备通用性强(支持Excel、Python、Java等多工具读取)、体积小(纯文本无冗余格式)的特点,电商场景中用于订单数据备份、第三方平台数据同步(如将订单数据导出给物流系统);金融场景中用于报表导出、监管数据提交;数据分析师可快速通过CSV导入数据进行本地分析,无需依赖数据库连接。
实现思路
1. CSV存储(导出):确定待导出数据来源(如数据库表、查询结果),指定CSV文件存储路径、字段分隔符(默认逗号)、是否包含表头,执行导出命令将数据写入CSV文件;2. CSV加载(导入):创建与CSV字段匹配的数据库表,指定CSV文件路径、分隔符、是否跳过表头,执行导入命令将CSV数据加载到表中;3. 校验:导出后检查CSV字段完整性,导入后核对数据行数与源数据一致性。
输入示例
场景1(存储):将电商订单表(orders)2025-10-01的订单数据导出为CSV;场景2(加载):将订单CSV文件导入到新表(orders_import)。源表数据(orders表,用于导出):
order_id(订单ID) | user_id(用户ID) | order_amount(元) | order_time(下单时间) |
---|---|---|---|
O001 | 1001 | 299.00 | 2025-10-01 09:15:00 |
O002 | 1002 | 159.00 | 2025-10-01 10:30:00 |
O003 | 1003 | 499.00 | 2025-10-01 20:10:00 |
CSV文件内容(用于导入,路径:/data/orders_20251001.csv):
order_id,user_id,order_amount,order_time
O001,1001,299.00,2025-10-01 09:15:00
O002,1002,159.00,2025-10-01 10:30:00
O003,1003,499.00,2025-10-01 20:10:00
SQL示例
-- 以Hive SQL为例,适配分布式环境;MySQL可替换为对应的SELECT ... INTO OUTFILE和LOAD DATA INFILE
-- 1. CSV存储(导出):将2025-10-01订单数据导出为CSV
INSERT OVERWRITE LOCAL DIRECTORY '/data/orders_20251001' -- 本地存储路径(分布式用DIRECTORY,本地加LOCAL)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',' -- 字段分隔符(默认逗号,可改为\t等)
LINES TERMINATED BY '\n' -- 行分隔符
STORED AS TEXTFILE -- CSV本质是文本文件
-- 待导出的查询结果
SELECT order_id, user_id, order_amount, order_time
FROM orders
WHERE DATE(order_time) = '2025-10-01';
-- 2. CSV加载(导入):创建目标表并导入CSV数据
-- 2.1 创建与CSV字段匹配的目标表
CREATE TABLE IF NOT EXISTS orders_import (
order_id VARCHAR(50),
user_id VARCHAR(50),
order_amount DECIMAL(10,2),
order_time DATETIME
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS ORC; -- 导入后按ORC存储,提升查询性能
-- 2.2 从CSV加载数据(跳过表头第1行)
LOAD DATA LOCAL INPATH '/data/orders_20251001.csv' -- CSV文件路径(本地加LOCAL,分布式不加)
OVERWRITE INTO TABLE orders_import
-- 跳过表头(若CSV无表头可删除此句)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
TBLPROPERTIES ('skip.header.line.count'='1');
-- 3. 数据校验:核对导入数据行数与源数据一致
SELECT '源表2025-10-01数据量' AS data_source, COUNT(*) AS row_count FROM orders WHERE DATE(order_time) = '2025-10-01'
UNION ALL
SELECT '导入后表数据量' AS data_source, COUNT(*) AS row_count FROM orders_import;
输出示例
输出1(存储成功提示):
Query ID = hive_20251019100000_123456
Total jobs = 1
Launching Job 1 out of 1
Status: SUCCEEDED
OK
Time taken: 2.3 seconds
输出2(CSV文件内容,路径:/data/orders_20251001/000000_0):
O001,1001,299.00,2025-10-01 09:15:00
O002,1002,159.00,2025-10-01 10:30:00
O003,1003,499.00,2025-10-01 20:10:00
输出3(加载后校验结果):
data_source(数据来源) | row_count(数据行数) |
---|---|
源表2025-10-01数据量 | 3 |
导入后表数据量 | 3 |
注:不同数据库语法略有差异,MySQL导出用「SELECT ... INTO OUTFILE」,导入用「LOAD DATA INFILE」;若CSV含特殊字符(如逗号),需在存储时指定转义符(如ESCAPED BY '\\')。
39. 多列去重统计
业务意义
复合维度数据准确性保障的核心手段:单一字段去重无法满足多场景分析需求,电商中需按“用户ID+商品ID”去重统计真实购买用户数(避免同一用户多次浏览同一商品被重复计数);金融领域按“用户ID+交易类型+交易日期”去重,确保单日同一类型交易仅统计一次,避免重复入账;物流场景按“订单ID+包裹ID”去重,防止重复派件。多列去重是保证统计结果真实性的关键。
实现思路
1. 确定数据源与去重维度:以业务表(如用户行为表、交易表)为数据源,明确多列组合去重规则(如“user_id+product_id”“user_id+trade_type+trade_date”);2. 筛选有效数据:按时间范围、状态等条件过滤无效数据(如取消的交易);3. 分组去重统计:以组合去重字段为分组依据,统计目标指标(如去重后数量、关联次数);4. 结果验证:可通过COUNT(DISTINCT 组合字段)辅助验证去重准确性。
输入示例
场景:统计某电商2025-10-01至2025-10-07“用户-商品”维度的有效浏览次数(同一用户同一商品多次浏览仅算1次有效浏览),数据源为用户行为表(user_behavior)。
behavior_id(行为ID) | user_id(用户ID) | product_id(商品ID) | behavior_type(行为类型) | behavior_time(行为时间) |
B001 | 1001 | P001 | 浏览 | 2025-10-01 09:15:00 |
B002 | 1001 | P001 | 浏览 | 2025-10-01 10:30:00 |
B003 | 1001 | P002 | 浏览 | 2025-10-02 14:20:00 |
B004 | 1002 | P001 | 浏览 | 2025-10-03 08:40:00 |
B005 | 1002 | P001 | 浏览 | 2025-10-03 09:10:00 |
SQL示例
-- 统计2025-10-01至2025-10-07“用户-商品”维度有效浏览次数(多列去重)
SELECT
-- 多列组合去重维度
user_id,
product_id,
-- 统计去重后有效次数(同一用户同一商品多次浏览计1次)
COUNT(DISTINCT behavior_id) AS distinct_behavior_count,
-- 统计原始行为总次数(辅助对比去重效果)
COUNT(behavior_id) AS total_behavior_count,
-- 提取首次/末次行为时间(辅助分析行为周期)
MIN(behavior_time) AS first_behavior_time,
MAX(behavior_time) AS last_behavior_time
FROM user_behavior
-- 筛选时间范围与有效行为类型
WHERE behavior_time BETWEEN '2025-10-01' AND '2025-10-07'
AND behavior_type = '浏览'
-- 按多列去重维度分组
GROUP BY user_id, product_id
-- 按有效浏览次数降序排序
ORDER BY distinct_behavior_count DESC;
-- 验证:计算去重后总有效用户-商品组合数
SELECT '去重后有效组合数' AS stats_type, COUNT(*) AS count
FROM (
SELECT user_id, product_id
FROM user_behavior
WHERE behavior_time BETWEEN '2025-10-01' AND '2025-10-07'
AND behavior_type = '浏览'
GROUP BY user_id, product_id
) AS distinct_groups;
输出示例
user_id(用户ID) | product_id(商品ID) | distinct_behavior_count(有效浏览次数) | total_behavior_count(原始行为次数) | first_behavior_time(首次浏览时间) | last_behavior_time(末次浏览时间) |
1001 | P001 | 1 | 2 | 2025-10-01 09:15:00 | 2025-10-01 10:30:00 |
1001 | P002 | 1 | 1 | 2025-10-02 14:20:00 | 2025-10-02 14:20:00 |
1002 | P001 | 1 | 2 | 2025-10-03 08:40:00 | 2025-10-03 09:10:00 |
验证结果:
stats_type(统计类型) | count(数量) |
去重后有效组合数 | 3 |
注:可根据业务需求扩展去重维度(如“user_id+product_id+date”按日去重),或结合CASE WHEN统计多类型行为的去重结果。
40. Geo 距离计算
业务意义
地理位置关联分析的核心支撑:外卖平台通过计算用户与商家的直线距离,匹配就近商家并估算配送时间;出行平台基于乘客与司机的距离实现智能派单,降低空驶率;电商平台结合用户与仓库的距离,优化发货仓库选择,缩短物流时效;线下零售通过计算门店与用户的距离,定向推送门店优惠活动。Geo距离是地理位置服务(LBS)的核心指标。
实现思路
1. 确定数据源与核心字段:以含地理位置信息的表(如商家表、用户表、门店表)为数据源,核心字段为经度(longitude)、纬度(latitude),关联字段为业务标识(如user_id、merchant_id);2. 选择距离计算公式:常用Haversine公式(计算球面上两点直线距离,适配地球曲率),单位可转换为公里(km)或米(m);3. 关联表计算:通过业务标识关联两张表,代入公式计算距离;4. 筛选与排序:按距离筛选目标数据(如距离小于5km的商家)并排序。
输入示例
场景:外卖平台匹配某用户(user_id=1001)周边5km内的商家,计算用户与各商家的直线距离,数据源为用户地址表(user_address)和商家信息表(merchant_info)。表1:user_address(用户地址表)
user_id(用户ID) | user_name(用户名) | longitude(经度) | latitude(纬度) |
1001 | 张三 | 116.39748 | 39.90882 |
表2:merchant_info(商家信息表)
merchant_id(商家ID) | merchant_name(商家名称) | longitude(经度) | latitude(纬度) | merchant_type(商家类型) |
M001 | 汉堡店A | 116.39848 | 39.90982 | 快餐 |
M002 | 奶茶店B | 116.40748 | 39.91882 | 饮品 |
M003 | 面馆C | 116.50748 | 39.92882 | 正餐 |
SQL示例
-- 计算用户1001与周边商家的直线距离(Haversine公式)
SELECT
u.user_id,
u.user_name,
m.merchant_id,
m.merchant_name,
m.merchant_type,
-- Haversine公式计算直线距离(单位:km,保留2位小数)
ROUND(
6371 * 2 * ASIN(
SQRT(
POWER(SIN((RADIANS(m.latitude) - RADIANS(u.latitude)) / 2), 2) +
COS(RADIANS(u.latitude)) * COS(RADIANS(m.latitude)) *
POWER(SIN((RADIANS(m.longitude) - RADIANS(u.longitude)) / 2), 2)
)
),
2
) AS distance_km,
-- 转换为米(单位:m,保留0位小数)
ROUND(
6371000 * 2 * ASIN(
SQRT(
POWER(SIN((RADIANS(m.latitude) - RADIANS(u.latitude)) / 2), 2) +
COS(RADIANS(u.latitude)) * COS(RADIANS(m.latitude)) *
POWER(SIN((RADIANS(m.longitude) - RADIANS(u.longitude)) / 2), 2)
)
),
0
) AS distance_m
FROM user_address u
-- 关联商家表(此处用CROSS JOIN,实际可按区域筛选减少计算量)
CROSS JOIN merchant_info m
-- 筛选目标用户并限定距离5km内的商家
WHERE u.user_id = '1001'
AND ROUND(
6371 * 2 * ASIN(
SQRT(
POWER(SIN((RADIANS(m.latitude) - RADIANS(u.latitude)) / 2), 2) +
COS(RADIANS(u.latitude)) * COS(RADIANS(m.latitude)) *
POWER(SIN((RADIANS(m.longitude) - RADIANS(u.longitude)) / 2), 2)
)
),
2
) <= 5
-- 按距离升序排序(优先匹配就近商家)
ORDER BY distance_km ASC;
输出示例
user_id(用户ID) | merchant_id(商家ID) | merchant_name(商家名称) | merchant_type(商家类型) | distance_km(距离,km) | distance_m(距离,m) |
1001 | M001 | 汉堡店A | 快餐 | 0.14 | 140 |
1001 | M002 | 奶茶店B | 饮品 | 1.32 | 1320 |
注:6371为地球平均半径(单位:km),若需更精准计算可替换为区域半径;实际应用中可先按“经度范围+纬度范围”粗筛目标数据,再计算距离提升效率。
41. 视频播放完成率
业务意义
视频内容质量评估与用户行为分析的核心指标:短视频/长视频平台通过完成率区分高价值内容(如完成率80%以上的爆款视频)与低质内容,优化内容推荐权重;创作者依据作品完成率调整内容节奏(如缩短前3秒铺垫时长);运营团队针对低完成率用户(如平均完成率低于30%)推送短时长、高节奏内容提升留存;广告平台结合视频完成率评估广告曝光效果,保障广告主权益。
实现思路
1. 确定数据源与核心字段:以视频播放日志表(video_play_log)为数据源,核心字段为user_id(用户ID)、video_id(视频ID)、play_duration(实际播放时长,秒)、video_total_duration(视频总时长,秒)、play_start_time(播放开始时间);2. 定义完成率公式:完成率=实际播放时长/视频总时长×100%,处理异常值(如播放时长超过总时长时按100%计);3. 分层统计:按视频维度、用户维度、时间维度分别统计完成率;4. 结果分析:划分完成率区间(如0-30%、30%-60%、60%-100%),分析不同区间的内容/用户特征。
输入示例
场景:统计某视频平台2025-10-01至2025-10-07各视频的播放完成率及用户行为分布,数据源为video_play_log(视频播放日志表)。
play_id(播放ID) | user_id(用户ID) | video_id(视频ID) | play_duration(播放时长,秒) | video_total_duration(视频总时长,秒) | play_start_time(播放开始时间) |
---|---|---|---|---|---|
P001 | 1001 | V001 | 120 | 120 | 2025-10-01 09:15:00 |
P002 | 1001 | V002 | 45 | 90 | 2025-10-01 10:30:00 |
P003 | 1002 | V001 | 90 | 120 | 2025-10-02 14:20:00 |
P004 | 1002 | V002 | 100 | 90 | 2025-10-03 08:40:00 |
P005 | 1003 | V001 | 20 | 120 | 2025-10-03 09:10:00 |
SQL示例
-- 统计2025-10-01至2025-10-07各视频的播放完成率及相关指标
WITH VideoPlayStats AS (
-- 第一步:计算单条播放记录的完成率,处理异常值
SELECT
video_id,
user_id,
play_duration,
video_total_duration,
-- 完成率计算:播放时长超过总时长时按100%计,保留2位小数
CASE
WHEN play_duration >= video_total_duration THEN 100.00
ELSE ROUND(play_duration / video_total_duration * 100, 2)
END AS completion_rate,
play_start_time
FROM video_play_log
WHERE play_start_time BETWEEN '2025-10-01' AND '2025-10-07'
)
-- 第二步:按视频维度统计核心指标
SELECT
video_id,
-- 基础播放数据
COUNT(DISTINCT user_id) AS unique_view_count, -- 去重观看用户数
COUNT(play_duration) AS total_play_count, -- 总播放次数
-- 完成率相关指标
ROUND(AVG(completion_rate), 2) AS avg_completion_rate, -- 平均完成率
-- 各完成率区间用户占比
CONCAT(ROUND(COUNT(CASE WHEN completion_rate >= 80 THEN user_id END) / COUNT(DISTINCT user_id) * 100, 2), '%') AS high_completion_ratio, -- 高完成率(≥80%)用户占比
CONCAT(ROUND(COUNT(CASE WHEN completion_rate BETWEEN 30 AND 79 THEN user_id END) / COUNT(DISTINCT user_id) * 100, 2), '%') AS mid_completion_ratio, -- 中完成率(30%-79%)用户占比
CONCAT(ROUND(COUNT(CASE WHEN completion_rate < 30 THEN user_id END) / COUNT(DISTINCT user_id) * 100, 2), '%') AS low_completion_ratio -- 低完成率(<30%)用户占比
FROM VideoPlayStats
GROUP BY video_id
ORDER BY avg_completion_rate DESC;
输出示例
video_id(视频ID) | unique_view_count(去重观看用户数) | total_play_count(总播放次数) | avg_completion_rate(平均完成率) | high_completion_ratio(高完成率用户占比) | mid_completion_ratio(中完成率用户占比) | low_completion_ratio(低完成率用户占比) |
---|---|---|---|---|---|---|
V001 | 3 | 3 | 76.67% | 33.33% | 33.33% | 33.33% |
V002 | 2 | 2 | 77.78% | 50.00% | 50.00% | 0.00% |
注:可按用户维度统计个人平均完成率,用于用户分层;或结合视频分类(如剧情类、知识类)统计分类维度完成率,指导内容创作方向。
42. TopN 用户标签
业务意义
用户精准分层与个性化运营的核心依据:内容平台通过提取用户TopN标签(如用户最常浏览的“科技”“美食”标签),实现内容定向推荐(如给科技标签TOP1的用户推送科技类文章);电商平台依据用户TopN消费标签(如“轻奢”“母婴”),定制首页商品展示与营销活动;会员体系通过标签权重识别高潜用户(如“高消费+高频次”标签用户),提供专属权益提升忠诚度。TopN标签是实现“千人千面”运营的关键。
实现思路
1. 确定数据源与标签规则:以用户行为表(如浏览、购买、收藏表)为数据源,定义标签生成规则(如浏览科技类内容≥3次生成“科技”标签,购买母婴商品生成“母婴”标签);2. 计算标签权重:按行为重要性赋值(如购买权重5、收藏权重3、浏览权重1),按用户-标签分组求和得到权重值;3. 提取TopN:使用窗口函数对每个用户的标签按权重排序,取前N个标签(如N=3);4. 结果应用:关联用户基础信息,输出TopN标签用于运营。
输入示例
场景:电商平台提取各用户Top3消费标签(按行为权重:购买=5、收藏=3、浏览=1),数据源为user_behavior(用户行为表)和tag_dict(标签字典表)。表1:tag_dict(标签字典表,行为与标签映射)
behavior_type(行为类型) | product_category(商品分类) | tag(标签) | weight(权重) |
---|---|---|---|
购买 | 手机数码 | 科技 | 5 |
收藏 | 手机数码 | 科技 | 3 |
浏览 | 手机数码 | 科技 | 1 |
购买 | 母婴用品 | 母婴 | 5 |
购买 | 美妆护肤 | 美妆 | 5 |
表2:user_behavior(用户行为表)
behavior_id(行为ID) | user_id(用户ID) | behavior_type(行为类型) | product_category(商品分类) | behavior_time(行为时间) |
---|---|---|---|---|
B001 | 1001 | 购买 | 手机数码 | 2025-10-01 09:15:00 |
B002 | 1001 | 购买 | 母婴用品 | 2025-10-01 10:30:00 |
B003 | 1001 | 浏览 | 美妆护肤 | 2025-10-02 14:20:00 |
B004 | 1002 | 收藏 | 美妆护肤 | 2025-10-03 08:40:00 |
B005 | 1002 | 购买 | 美妆护肤 | 2025-10-03 09:10:00 |
SQL示例
-- 提取各用户Top3消费标签(按标签权重排序)
WITH UserTagWeight AS (
-- 第一步:关联行为表与标签字典,计算用户-标签权重
SELECT
ub.user_id,
td.tag,
SUM(td.weight) AS tag_total_weight -- 按用户-标签分组求和权重
FROM user_behavior ub
INNER JOIN tag_dict td
ON ub.behavior_type = td.behavior_type
AND ub.product_category = td.product_category
-- 限定近30天行为数据,保证标签时效性
WHERE ub.behavior_time BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND CURRENT_DATE()
GROUP BY ub.user_id, td.tag
),
UserTagRank AS (
-- 第二步:对每个用户的标签按权重排序,生成排名
SELECT
user_id,
tag,
tag_total_weight,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY tag_total_weight DESC) AS tag_rank
-- 若权重相同需并列排名,可替换为RANK()函数
FROM UserTagWeight
)
-- 第三步:提取Top3标签,关联用户基础信息(此处简化,可关联users表)
SELECT
utr.user_id,
-- 拼接Top3标签为字符串(便于展示)
CONCAT_WS(',',
MAX(CASE WHEN tag_rank = 1 THEN tag END),
MAX(CASE WHEN tag_rank = 2 THEN tag END),
MAX(CASE WHEN tag_rank = 3 THEN tag END)
) AS top3_tags,
-- 展示各标签权重
CONCAT_WS(',',
MAX(CASE WHEN tag_rank = 1 THEN CONCAT(tag, '(', tag_total_weight, ')') END),
MAX(CASE WHEN tag_rank = 2 THEN CONCAT(tag, '(', tag_total_weight, ')') END),
MAX(CASE WHEN tag_rank = 3 THEN CONCAT(tag, '(', tag_total_weight, ')') END)
) AS top3_tags_with_weight
FROM UserTagRank utr
WHERE tag_rank <= 3 -- 取Top3标签
GROUP BY utr.user_id
ORDER BY utr.user_id ASC;
输出示例
user_id(用户ID) | top3_tags(Top3标签) | top3_tags_with_weight(Top3标签及权重) |
---|---|---|
1001 | 科技,母婴,美妆 | 科技(5),母婴(5),美妆(1) |
1002 | 美妆 | 美妆(8) |
注:可调整N值(如Top5)适配不同运营场景,或按标签类型(如消费标签、内容标签)分别提取TopN;权重赋值需结合业务场景优化(如直播场景可增加“评论”“分享”行为的权重)。
43. 品牌对比分析
业务意义
品牌竞争力评估与运营决策的核心依据:电商平台通过对比不同品牌的销量、客单价、复购率等指标,筛选头部优质品牌深化合作(如独家供货、联合营销);对中腰部品牌制定流量倾斜策略,对低效品牌优化清退;品牌方通过竞品数据定位自身短板(如客单价低于竞品时调整定价);运营团队依据品牌受众画像差异,制定差异化推广方案,提升转化效率。
实现思路
1. 确定数据源与核心字段:以商品表(products)、订单表(orders)为核心数据源,关联字段为product_id,核心分析字段含brand、order_amount、user_id等;2. 数据关联清洗:关联商品表与订单表,筛选已支付有效订单,排除测试单与取消单;3. 指标计算:按品牌分组计算销量、销售额、客单价、复购率等核心指标;4. 结果呈现:指标排序形成对比矩阵,标注关键差异点支撑决策。
输入示例
场景:某电商平台对比2025年9月数码品类三大品牌核心指标,数据源为商品表(products)、订单表(orders)。表1:products(商品表)
product_id(商品ID) | product_name(商品名称) | brand(品牌) | category(品类) | unit_price(单价,元) |
P001 | 手机A1 | 品牌X | 手机 | 3999 |
P002 | 平板B1 | 品牌X | 平板 | 2499 |
P003 | 手机C1 | 品牌Y | 手机 | 2999 |
P004 | 耳机D1 | 品牌Z | 配件 | 499 |
表2:orders(订单表)
order_id(订单ID) | user_id(用户ID) | product_id(商品ID) | order_amount(元) | order_time(订单时间) | pay_status(支付状态) |
O001 | 1001 | P001 | 3999 | 2025-09-05 10:00 | 已支付 |
O002 | 1002 | P003 | 2999 | 2025-09-10 14:30 | 已支付 |
O003 | 1001 | P002 | 2499 | 2025-09-15 09:15 | 已支付 |
O004 | 1003 | P004 | 499 | 2025-09-20 16:40 | 已支付 |
SQL示例
-- 2025年9月数码品牌核心指标对比分析
WITH BrandOrderData AS (
-- 关联商品表与订单表,筛选有效数据
SELECT
p.brand,
o.order_id,
o.user_id,
o.order_amount,
p.unit_price
FROM orders o
INNER JOIN products p ON o.product_id = p.product_id
WHERE
DATE_FORMAT(o.order_time, '%Y-%m') = '2025-09'
AND o.pay_status = '已支付'
AND p.category IN ('手机', '平板', '配件')
),
UserBuyCount AS (
-- 计算用户对各品牌的购买次数(用于复购率)
SELECT
brand,
user_id,
COUNT(DISTINCT order_id) AS buy_count
FROM BrandOrderData
GROUP BY brand, user_id
)
-- 计算品牌核心对比指标
SELECT
bod.brand,
COUNT(DISTINCT bod.order_id) AS total_sales, -- 销量
SUM(bod.order_amount) AS total_revenue, -- 销售额
ROUND(SUM(bod.order_amount)/COUNT(DISTINCT bod.order_id),2) AS avg_price, -- 客单价
COUNT(DISTINCT bod.user_id) AS buyer_count, -- 购买用户数
-- 复购率(购买≥2次用户数/总购买用户数)
CONCAT(ROUND(COUNT(DISTINCT CASE WHEN ubc.buy_count≥2 THEN ubc.user_id END)/COUNT(DISTINCT ubc.user_id)*100,2),'%') AS repurchase_rate
FROM BrandOrderData bod
LEFT JOIN UserBuyCount ubc ON bod.brand = ubc.brand AND bod.user_id = ubc.user_id
GROUP BY bod.brand
ORDER BY total_revenue DESC;
输出示例
brand(品牌) | total_sales(销量) | total_revenue(销售额,元) | avg_price(客单价,元) | buyer_count(购买用户数) | repurchase_rate(复购率) |
品牌X | 2 | 6498 | 3249.00 | 1 | 0.00% |
品牌Y | 1 | 2999 | 2999.00 | 1 | 0.00% |
品牌Z | 1 | 499 | 499.00 | 1 | 0.00% |
注:可扩展对比维度(如品牌新用户占比、地区分布),通过关联用户表获取属性;复购率统计建议延长周期(如3个月),避免短期样本偏差。
44. 用户活跃时段热力图
业务意义
用户行为时间规律可视化的核心工具:直播平台通过热力图识别“19-21点”峰值时段,安排头部主播开播;客服中心依据“10-12点”“14-16点”高峰调整排班;内容平台在“0-6点”低谷期进行服务器维护;营销活动选择峰值时段推送优惠券,提升点击转化。热力图直观呈现“日期-时段”二维活跃特征,比单一时段统计更具决策价值。
实现思路
1. 确定数据源与时间粒度:以用户行为表(如login_log、browse_log)为数据源,采用“日期+小时”二维粒度;2. 提取时间维度:用时间函数提取行为时间的“日期”“小时”字段;3. 统计活跃指标:按“日期-小时”分组,统计去重活跃用户数;4. 格式适配:转换为“行(日期)-列(小时)”矩阵,适配Excel、Tableau等可视化工具。
输入示例
场景:某电商平台统计2025年10月1日-10月3日用户登录活跃时段,数据源为用户登录日志表(login_log)。
login_id(登录ID) | user_id(用户ID) | login_time(登录时间) | login_device(登录设备) |
L001 | 1001 | 2025-10-01 09:15:00 | 手机端 |
L002 | 1002 | 2025-10-01 20:30:00 | PC端 |
L003 | 1001 | 2025-10-02 19:20:00 | 手机端 |
L004 | 1003 | 2025-10-03 21:10:00 | 手机端 |
SQL示例
-- 2025年10月1日-10月3日用户登录活跃时段热力图数据
WITH LoginTimeStats AS (
-- 提取日期、小时,统计活跃用户数
SELECT
DATE(login_time) AS login_date, -- 日期(如2025-10-01)
DATE_FORMAT(login_time, '%H') AS login_hour, -- 小时(如09、20)
COUNT(DISTINCT user_id) AS active_count -- 去重活跃数
FROM login_log
WHERE login_time BETWEEN '2025-10-01 00:00:00' AND '2025-10-03 23:59:59'
GROUP BY login_date, login_hour
)
-- 转换为热力图矩阵(行:日期,列:小时)
SELECT
login_date,
-- 填充00-23时数据,无记录填0
MAX(CASE WHEN login_hour='09' THEN active_count ELSE 0 END) AS hour_09,
MAX(CASE WHEN login_hour='19' THEN active_count ELSE 0 END) AS hour_19,
MAX(CASE WHEN login_hour='20' THEN active_count ELSE 0 END) AS hour_20,
MAX(CASE WHEN login_hour='21' THEN active_count ELSE 0 END) AS hour_21,
-- 可补充其他小时列(00-08、10-18、22-23)
SUM(active_count) AS daily_total -- 当日总活跃
FROM LoginTimeStats
GROUP BY login_date
ORDER BY login_date;
输出示例
输出1:热力图数据源表格(简化版)
login_date(日期) | hour_09(09时) | hour_19(19时) | hour_20(20时) | hour_21(21时) | daily_total(当日总活跃) |
2025-10-01 | 1 | 0 | 1 | 0 | 2 |
2025-10-02 | 0 | 1 | 0 | 0 | 1 |
2025-10-03 | 0 | 0 | 0 | 1 | 1 |
输出2:热力图呈现说明:以“日期”为行、“小时”为列,单元格颜色越深活跃数越高,可直观发现“19-21时”“09时”为核心活跃时段,10月1日活跃用户最多。注:可按设备类型(手机/PC)分维度统计,分析不同设备用户活跃差异;时间粒度可细化为30分钟(如09:00-09:30)适配高频场景。
输出示例
输出1:退款率统计结果
stat_dimension(统计维度) | valid_order_count(有效订单数) | refunded_order_count(退款成功订单数) | refund_rate(退款率) | total_refund_amt(退款总金额,元) | top_refund_reason(Top1退款原因) |
---|---|---|---|---|---|
整体 | 4 | 2 | 50.00% | 498.00 | 无 |
服饰 | 2 | 2 | 100.00% | 498.00 | 尺寸不符 |
数码 | 2 | 0 | 0.00% | 0.00 | 无 |
食品 | 0 | 0 | 0.00% | 0.00 | 无 |
输出2:退款原因分布说明服饰品类为高退款率品类(100%),核心原因包括“尺寸不符”(占比50%)和“质量问题”(占比50%),需优化商品尺寸标注规范并加强入库质量检测;数码品类无退款成功订单,整体表现良好;食品品类无有效订单,需结合流量数据分析销售问题。注:可按时间维度(如每日)统计退款率趋势,或按用户新老层级分析退款差异;退款率计算需排除“售后换货”“部分退款”等特殊场景,避免统计偏差。
45. 多表一致性校验
业务意义
数据质量保障的核心防线:电商场景中,订单表、支付表、物流表的一致性直接影响订单履约(如订单金额与支付金额不一致会导致对账失败);金融领域中,交易表与资金流水表的一致性是合规对账的关键,可避免资金账实不符;数据仓库建设中,多源表一致性校验能防止脏数据进入分析层,确保决策依据可靠。一致性校验是数据生命周期管理中“数据质量控制”的核心环节。
实现思路
1. 确定校验对象与关联字段:明确需校验的关联表(如订单表-支付表、订单表-物流表),核心关联字段为业务唯一标识(如order_id、trade_id);2. 定义校验维度:根据业务规则确定校验指标(如金额一致性、状态一致性、数量一致性);3. 关联校验:通过关联字段连接多表,用条件判断语句校验各维度一致性;4. 异常处理:输出不一致数据明细,标注异常类型,支撑数据问题定位与修复。
输入示例
场景:电商平台校验订单表(orders)、支付表(payments)、物流表(logistics)的一致性,核心关联字段为order_id,校验维度包括金额、状态、订单存在性。表1:orders(订单表)
order_id(订单ID) | user_id(用户ID) | order_amount(订单金额,元) | order_status(订单状态) | create_time(创建时间) |
---|---|---|---|---|
O001 | 1001 | 299.00 | 已支付 | 2025-10-01 09:15:00 |
O002 | 1002 | 159.00 | 已支付 | 2025-10-01 10:30:00 |
O003 | 1003 | 499.00 | 待支付 | 2025-10-01 11:20:00 |
表2:payments(支付表)
pay_id(支付ID) | order_id(订单ID) | pay_amount(支付金额,元) | pay_status(支付状态) | pay_time(支付时间) |
---|---|---|---|---|
P001 | O001 | 299.00 | 支付成功 | 2025-10-01 09:16:00 |
P002 | O002 | 158.00 | 支付成功 | 2025-10-01 10:31:00 |
P003 | O004 | 399.00 | 支付成功 | 2025-10-01 12:00:00 |
表3:logistics(物流表)
logistics_id(物流ID) | order_id(订单ID) | logistics_status(物流状态) | delivery_time(发货时间) |
---|---|---|---|
L001 | O001 | 已发货 | 2025-10-01 14:00:00 |
L002 | O003 | 已发货 | 2025-10-01 15:00:00 |
SQL示例
-- 订单表、支付表、物流表一致性校验(按order_id关联)
WITH MultiTableJoin AS (
-- 第一步:左连接三表,保留所有订单记录以检测缺失关联
SELECT
o.order_id,
o.order_amount AS order_amt,
o.order_status AS order_stat,
p.pay_amount AS pay_amt,
p.pay_status AS pay_stat,
l.logistics_status AS logistics_stat
FROM orders o
LEFT JOIN payments p ON o.order_id = p.order_id
LEFT JOIN logistics l ON o.order_id = l.order_id
-- 限定校验时间范围
WHERE o.create_time BETWEEN '2025-10-01 00:00:00' AND '2025-10-01 23:59:59'
)
-- 第二步:统计一致性情况并输出异常明细
SELECT
-- 1. 整体一致性统计
'整体校验' AS check_type,
COUNT(DISTINCT order_id) AS total_order_count,
-- 金额一致数(订单已支付且金额相等)
COUNT(DISTINCT CASE WHEN order_stat = '已支付' AND pay_stat = '支付成功' AND order_amt = pay_amt THEN order_id END) AS amt_consistent_count,
-- 状态一致数(已支付订单对应支付成功,已发货订单对应已支付)
COUNT(DISTINCT CASE
WHEN (order_stat = '已支付' AND pay_stat = '支付成功')
AND (order_stat != '已发货' OR (order_stat = '已发货' AND logistics_stat = '已发货'))
THEN order_id END) AS status_consistent_count,
-- 关联完整数(订单同时存在支付和物流记录)
COUNT(DISTINCT CASE WHEN pay_amt IS NOT NULL AND logistics_stat IS NOT NULL THEN order_id END) AS full_link_count
FROM MultiTableJoin
UNION ALL
-- 2. 异常明细输出(按异常类型分类)
SELECT
'异常明细' AS check_type,
order_id,
-- 标注金额异常
CASE WHEN order_stat = '已支付' AND pay_stat = '支付成功' AND order_amt != pay_amt THEN '金额不一致' ELSE '' END AS amt_error,
-- 标注状态异常
CASE
WHEN order_stat = '已支付' AND pay_stat != '支付成功' THEN '订单已支付但支付失败'
WHEN order_stat = '待支付' AND pay_stat = '支付成功' THEN '订单待支付但支付成功'
WHEN order_stat = '已发货' AND pay_stat != '支付成功' THEN '未支付订单已发货'
ELSE '' END AS status_error,
-- 标注关联缺失
CASE
WHEN order_stat = '已支付' AND pay_amt IS NULL THEN '已支付订单无支付记录'
WHEN order_stat = '已发货' AND logistics_stat IS NULL THEN '已发货订单无物流记录'
ELSE '' END AS link_error
FROM MultiTableJoin
-- 筛选存在异常的订单
WHERE
(order_stat = '已支付' AND pay_stat = '支付成功' AND order_amt != pay_amt)
OR (order_stat = '已支付' AND pay_stat != '支付成功')
OR (order_stat = '待支付' AND pay_stat = '支付成功')
OR (order_stat = '已发货' AND pay_stat != '支付成功')
OR (order_stat = '已支付' AND pay_amt IS NULL)
OR (order_stat = '已发货' AND logistics_stat IS NULL);
输出示例
输出1:整体一致性统计
check_type(校验类型) | total_order_count(总订单数) | amt_consistent_count(金额一致数) | status_consistent_count(状态一致数) | full_link_count(关联完整数) |
---|---|---|---|---|
整体校验 | 3 | 1 | 1 | 1 |
输出2:异常明细
check_type(校验类型) | order_id(订单ID) | amt_error(金额异常) | status_error(状态异常) | link_error(关联缺失) |
---|---|---|---|---|
异常明细 | O002 | 金额不一致 | 已支付订单无物流记录 | |
异常明细 | O003 | 未支付订单已发货 |
注:可扩展校验维度(如时间一致性:支付时间晚于创建时间、发货时间晚于支付时间),或针对特定业务场景(如跨境订单的报关表校验)定制规则。
46. 订单退款率
业务意义
订单全链路运营质量的核心衡量指标:电商平台中,退款率直接反映商品质量(如服饰类尺寸不符导致高退款)、服务水平(如客服响应慢引发退款)和定价合理性(如性价比低导致退款);运营团队通过退款率分层管理(如高退款率商品下架整改),客服团队依据退款原因优化服务流程;财务部门通过退款率预测资金占用,避免现金流风险。退款率是“增长-留存-复购”闭环中的关键优化信号。
实现思路
1. 确定数据源与核心字段:以订单表(orders)和退款表(refunds)为数据源,核心关联字段为order_id,关键字段为订单状态、退款状态、退款原因、时间维度;2. 定义退款率公式:退款率=退款成功订单数/有效订单数×100%(有效订单指已支付且未取消的订单);3. 分层统计:按时间(日/周/月)、商品品类、用户层级等维度拆分退款率;4. 归因分析:结合退款原因字段,分析高退款率的核心诱因。
输入示例
场景:某电商平台统计2025年10月1日-10月7日的整体退款率及各品类退款率,分析退款原因分布,数据源为订单表(orders)和退款表(refunds)。表1:orders(订单表)
order_id(订单ID) | user_id(用户ID) | product_category(商品品类) | order_amount(元) | order_status(订单状态) | pay_time(支付时间) |
---|---|---|---|---|---|
O001 | 1001 | 服饰 | 299.00 | 已支付 | 2025-10-01 09:15:00 |
O002 | 1002 | 数码 | 1599.00 | 已支付 | 2025-10-02 10:30:00 |
O003 | 1003 | 服饰 | 199.00 | 已支付 | 2025-10-03 14:20:00 |
O004 | 1004 | 食品 | 99.00 | 已取消 | 2025-10-04 08:40:00 |
O005 | 1005 | 数码 | 2999.00 | 已支付 | 2025-10-05 19:20:00 |
表2:refunds(退款表)
refund_id(退款ID) | order_id(订单ID) | refund_amount(退款金额,元) | refund_status(退款状态) | refund_reason(退款原因) | refund_time(退款时间) |
---|---|---|---|---|---|
R001 | O001 | 299.00 | 退款成功 | 尺寸不符 | 2025-10-02 09:00:00 |
R002 | O003 | 199.00 | 退款成功 | 质量问题 | 2025-10-04 10:00:00 |
R003 | O005 | 2999.00 | 退款中 | 功能故障 | 2025-10-06 15:00:00 |
SQL示例
-- 2025年10月1日-10月7日订单退款率统计及归因分析
WITH ValidOrders AS (
-- 第一步:筛选有效订单(已支付且未取消,排除测试订单)
SELECT
order_id,
product_category,
order_amount
FROM orders
WHERE
order_status = '已支付'
AND pay_time BETWEEN '2025-10-01 00:00:00' AND '2025-10-07 23:59:59'
AND order_id NOT LIKE 'TEST%'
),
RefundedOrders AS (
-- 第二步:筛选退款成功的订单
SELECT
order_id,
refund_amount,
refund_reason
FROM refunds
WHERE
refund_status = '退款成功'
AND refund_time BETWEEN '2025-10-01 00:00:00' AND '2025-10-07 23:59:59'
)
-- 第三步:分层统计退款率及归因
-- 3.1 整体退款率
SELECT
'整体' AS stat_dimension,
COUNT(DISTINCT vo.order_id) AS valid_order_count,
COUNT(DISTINCT ro.order_id) AS refunded_order_count,
ROUND(COUNT(DISTINCT ro.order_id) / COUNT(DISTINCT vo.order_id) * 100, 2) AS refund_rate,
SUM(ro.refund_amount) AS total_refund_amt,
'无' AS top_refund_reason
FROM ValidOrders vo
LEFT JOIN RefundedOrders ro ON vo.order_id = ro.order_id
UNION ALL
-- 3.2 各品类退款率
SELECT
product_category AS stat_dimension,
COUNT(DISTINCT vo.order_id) AS valid_order_count,
COUNT(DISTINCT ro.order_id) AS refunded_order_count,
ROUND(COUNT(DISTINCT ro.order_id) / COUNT(DISTINCT vo.order_id) * 100, 2) AS refund_rate,
SUM(ro.refund_amount) AS total_refund_amt,
-- 各品类Top1退款原因
FIRST_VALUE(ro.refund_reason) OVER (PARTITION BY vo.product_category ORDER BY COUNT(ro.refund_reason) DESC) AS top_refund_reason
FROM ValidOrders vo
LEFT JOIN RefundedOrders ro ON vo.order_id = ro.order_id
GROUP BY product_category
ORDER BY refund_rate DESC;
输出示例
输出1:退款率统计结果
stat_dimension(统计维度) | valid_order_count(有效订单数) | refunded_order_count(退款成功订单数) | refund_rate(退款率) | total_refund_amt(退款总金额,元) | top_refund_reason(Top1退款原因) |
---|---|---|---|---|---|
整体 | 4 | 2 | 50.00% | 498.00 | 无 |
服饰 | 2 | 2 | 100.00% | 498.00 | 尺寸不符 |
数码 | 2 | 0 | 0.00% | 0.00 | 无 |
食品 | 0 | 0 | 0.00% | 0.00 | 无 |
输出2:退款原因分布说明服饰品类为高退款率品类(100%),核心原因包括“尺寸不符”(占比50%)和“质量问题”(占比50%),需优化商品尺寸标注规范并加强入库质量检测;数码品类无退款成功订单,整体表现良好;食品品类无有效订单,需结合流量数据分析销售问题。注:可按时间维度(如每日)统计退款率趋势,或按用户新老层级分析退款差异;退款率计算需排除“售后换货”“部分退款”等特殊场景,避免统计偏差。
47. AWS S3 定期清理日志
业务意义
云存储成本管控与合规运营的核心手段:AWS S3存储的访问日志、应用日志会随时间海量积累,占用存储空间导致成本攀升;按行业合规要求(如金融行业日志留存6个月)定期清理可避免违规风险;清理过期日志能简化存储结构,提升日志查询与管理效率;同时减少非必要数据冗余,降低数据泄露的潜在风险。
实现思路
1. 确定清理范围:明确目标S3桶、日志文件前缀(如“access-logs/2025/”)、留存周期(如90天)及清理对象(过期日志文件、空文件夹);2. 选择清理方式:短期高频场景用S3生命周期规则自动执行,复杂筛选场景用AWS Athena查询定位后结合CLI删除;3. 配置执行策略:设置执行周期(如每月1日)、失败重试机制及清理前备份(重要日志归档至低成本存储类);4. 验证清理结果:查询清理后剩余文件数量,核对是否符合留存规则。
输入示例
场景:某企业需清理AWS S3桶“company-log-bucket”中前缀为“app-logs/”的2025年1月1日前的应用日志,留存90天内日志,重要日志归档至“S3-IA”存储类,数据源为S3桶文件清单及日志元数据。表1:S3桶文件清单(简化)
bucket_name(桶名) | object_key(文件路径) | last_modified(最后修改时间) | file_size(文件大小) | storage_class(存储类) |
company-log-bucket | app-logs/20241201/error.log | 2024-12-01 08:00:00 | 100MB | STANDARD |
company-log-bucket | app-logs/20250115/info.log | 2025-01-15 10:30:00 | 80MB | STANDARD |
company-log-bucket | app-logs/20250220/warn.log | 2025-02-20 14:10:00 | 120MB | STANDARD |
company-log-bucket | access-logs/20250105/log.txt | 2025-01-05 09:00:00 | 50MB | STANDARD |
表2:生命周期规则配置需求
规则名称 | 适用前缀 | 留存周期 | 归档策略 | 清理策略 |
app-log-management | app-logs/ | 90天 | 30天未修改转S3-IA | 90天未修改删除 |
SQL示例
-- 1. 用AWS Athena查询S3桶中待清理的过期日志文件(2025-01-01前修改的app-logs)
SELECT
key AS object_key,
last_modified,
size/1024/1024 AS file_size_mb,
storage_class
FROM
"s3_logs_db"."company_log_bucket" -- Athena映射的S3表
WHERE
key LIKE 'app-logs/%' -- 匹配目标前缀
AND last_modified < DATE '2025-01-01' -- 筛选过期文件
ORDER BY
last_modified ASC;
-- 2. (可选)通过AWS CLI执行批量删除(结合查询结果生成删除清单)
-- aws s3 rm s3://company-log-bucket/app-logs/20241201/error.log
-- 批量删除可通过生成delete.json清单后执行:aws s3api delete-objects --bucket company-log-bucket --delete file://delete.json
输出示例
输出1:待清理过期日志查询结果
object_key(文件路径) | last_modified(最后修改时间) | file_size_mb(文件大小MB) | storage_class(存储类) |
app-logs/20241201/error.log | 2024-12-01 08:00:00 | 100.0 | STANDARD |
输出2:生命周期规则配置结果(AWS控制台截图说明)规则“app-log-management”已生效,适用前缀“app-logs/”,配置项:1. 30天未修改文件自动转换为“S3-IA”存储类;2. 90天未修改文件自动删除;3. 执行时间为每日凌晨2点(低峰时段)。输出3:清理执行结果1. 已删除文件:app-logs/20241201/error.log(100MB);2. 已归档文件:app-logs/20250115/info.log(80MB,转至S3-IA);3. 剩余文件:app-logs/20250220/warn.log(120MB,未达归档及清理周期)。注:可通过AWS CloudWatch监控清理任务执行状态,配置执行失败告警;重要日志建议清理前归档至离线存储(如AWS Glacier),满足长期合规留存需求。
48. 数据脱敏处理
业务意义
用户隐私保护与合规运营的核心保障:按《个人信息保护法》《GDPR》等法规要求,对手机号、身份证号、邮箱等敏感数据脱敏,可避免数据泄露导致的法律风险;在数据分析场景中,脱敏后的数据既能保留统计价值(如年龄区间),又能防止个人信息暴露;客服场景中展示脱敏后的用户信息(如手机号隐藏中间四位),降低内部泄露风险,实现“数据可用不可见”。
实现思路
1. 确定脱敏范围与字段:梳理敏感数据字段(如手机号、身份证号、邮箱、银行卡号),明确不同字段的脱敏级别(如身份证号全脱敏、手机号部分脱敏);2. 选择脱敏算法:根据字段类型选择适配方式(掩码法、替换法、加密法、截断法);3. 确定处理时机:实时处理(数据入库时通过ETL脱敏)或离线处理(查询时动态脱敏);4. 验证脱敏效果:检查脱敏后数据是否保留业务价值,且无法反向还原原始信息。
输入示例
场景:某电商平台对用户表(users)中的敏感字段进行脱敏,用于客服系统查询及数据分析,需脱敏字段:手机号(phone)、身份证号(id_card)、邮箱(email),保留用户ID、用户名等非敏感信息。表1:原始用户表(users)
user_id(用户ID) | user_name(用户名) | phone(手机号) | id_card(身份证号) | email(邮箱) | user_age(年龄) |
1001 | 张三 | 13812345678 | 110101199001011234 | zhangsan123@xxx.com | 35 |
1002 | 李四 | 13987654321 | 310101198505056789 | lisi567@yyy.com | 39 |
1003 | 王五 | 13711223344 | 440101199510104321 | wangwu890@zzz.com | 29 |
表2:脱敏规则
字段名称 | 脱敏算法 | 脱敏效果示例 |
phone(手机号) | 掩码法:保留前3位和后4位,中间4位用*代替 | 138****5678 |
id_card(身份证号) | 掩码法:保留前6位和后4位,中间8位用*代替 | 110101********1234 |
email(邮箱) | 掩码法:保留用户名前2位和域名,中间用*代替 | zh****@xxx.com |
SQL示例
-- 对用户表敏感字段进行脱敏处理(以MySQL为例)
SELECT
user_id,
user_name,
-- 手机号脱敏:前3位+****+后4位
CONCAT(SUBSTRING(phone, 1, 3), '****', SUBSTRING(phone, 8, 4)) AS masked_phone,
-- 身份证号脱敏:前6位+********+后4位
CONCAT(SUBSTRING(id_card, 1, 6), '********', SUBSTRING(id_card, 15, 4)) AS masked_id_card,
-- 邮箱脱敏:用户名前2位+****+@+域名
CONCAT(SUBSTRING_INDEX(email, '@', 1) <> ''
? CONCAT(SUBSTRING(SUBSTRING_INDEX(email, '@', 1), 1, 2), '****@', SUBSTRING_INDEX(email, '@', -1))
: email) AS masked_email,
user_age -- 非敏感字段保留原始值
FROM
users
-- 可添加条件筛选特定用户(如客服查询时按user_id筛选)
WHERE
user_id IN (1001, 1002, 1003);
-- 若需批量更新脱敏后的数据至新表(离线脱敏)
CREATE TABLE users_masked AS
SELECT
user_id,
user_name,
CONCAT(SUBSTRING(phone, 1, 3), '****', SUBSTRING(phone, 8, 4)) AS masked_phone,
CONCAT(SUBSTRING(id_card, 1, 6), '********', SUBSTRING(id_card, 15, 4)) AS masked_id_card,
CONCAT(SUBSTRING_INDEX(email, '@', 1) <> ''
? CONCAT(SUBSTRING(SUBSTRING_INDEX(email, '@', 1), 1, 2), '****@', SUBSTRING_INDEX(email, '@', -1))
: email) AS masked_email,
user_age
FROM
users;
输出示例
输出1:脱敏后用户表(查询结果)
user_id(用户ID) | user_name(用户名) | masked_phone(脱敏手机号) | masked_id_card(脱敏身份证号) | masked_email(脱敏邮箱) | user_age(年龄) |
1001 | 张三 | 138****5678 | 110101********1234 | zh****@xxx.com | 35 |
1002 | 李四 | 139****4321 | 310101********6789 | li****@yyy.com | 39 |
1003 | 王五 | 137****3344 | 440101********4321 | wa****@zzz.com | 29 |
输出2:脱敏效果验证结果1. 隐私保护:脱敏后无法获取完整手机号、身份证号等敏感信息,符合合规要求;2. 业务可用性:保留的字段信息可支撑客服核对用户身份(如询问手机号后4位)、数据分析(如按年龄分组统计);3. 不可还原性:通过脱敏后数据无法反向推导原始信息。注:高敏感场景(如金融数据)可采用加密脱敏(如AES加密),需配合密钥管理系统使用;动态脱敏可通过数据库权限控制实现(如普通员工查询时自动脱敏,管理员凭权限查看原始数据)。
49. 数据血缘追踪
业务意义
数据治理与全链路质量管控的核心支撑:电商平台通过追踪“用户表-订单表-销售额统计表”的血缘关系,当销售额数据异常时可快速定位是订单数据采集故障还是统计逻辑错误;金融领域按《数据安全法》要求,通过血缘追溯敏感数据(如银行卡号)的流转路径,满足合规审计需求;数据分析师通过血缘图谱快速理解“复购率”指标的计算逻辑(依赖用户表、订单表的哪些字段),避免重复开发;ETL工程师在系统升级时,通过血缘识别受影响的下游报表,降低变更风险。
实现思路
1. 确定追踪范围与粒度:明确需追踪的数据源(业务库表、ETL任务、报表指标),细化追踪粒度至“表级”(如订单表流向销售额表)或“字段级”(如订单表的order_amount流向销售额表的total_revenue);2. 选择追踪方式:静态解析(解析SQL脚本、ETL配置文件提取表/字段关联关系)或动态采集(监控数据流转过程中的读写操作);3. 构建血缘关系:按“上游数据源→处理过程→下游数据”的链路,关联表、字段、任务信息,形成血缘图谱;4. 呈现与应用:通过表格展示基础链路,结合可视化工具(如Neo4j、Tableau)呈现图谱,支持故障定位、合规审计等场景。
输入示例
场景:某电商平台追踪核心指标“月度品牌销售额”的血缘关系,涉及数据源表、ETL任务、目标报表表,需明确表级及字段级关联关系。表1:数据源表清单(source_tables)
table_id(表ID) | table_name(表名) | table_type(表类型) | description(描述) |
T001 | orders(订单表) | 业务表 | 存储原始订单数据 |
T002 | products(商品表) | 业务表 | 存储商品及品牌信息 |
T003 | monthly_brand_sales(月度品牌销售额表) | 报表表 | 存储各品牌月度销售额指标 |
表2:ETL任务清单(etl_tasks)
task_id(任务ID) | task_name(任务名称) | upstream_tables(上游表) | downstream_table(下游表) | sql_script(核心SQL脚本) |
E001 | 计算月度品牌销售额 | orders,products | monthly_brand_sales | INSERT INTO monthly_brand_sales(brand, sale_month, total_sales) SELECT p.brand, DATE_FORMAT(o.order_time, '%Y-%m'), SUM(o.order_amount) FROM orders o JOIN products p ON o.product_id = p.product_id GROUP BY p.brand, DATE_FORMAT(o.order_time, '%Y-%m') |
表3:字段映射关系(field_mapping)
mapping_id(映射ID) | task_id(任务ID) | upstream_table(上游表) | upstream_field(上游字段) | downstream_table(下游表) | downstream_field(下游字段) |
M001 | E001 | products | brand | monthly_brand_sales | brand |
M002 | E001 | orders | order_time | monthly_brand_sales | sale_month |
M003 | E001 | orders | order_amount | monthly_brand_sales | total_sales |
SQL示例
-- 月度品牌销售额指标的表级+字段级血缘关系查询
WITH TableLevelLineage AS (
-- 表级血缘:关联ETL任务获取上下游表关系
SELECT
upstream_tables AS upstream_table,
downstream_table,
task_name AS process_task,
'表级' AS lineage_level
FROM etl_tasks
WHERE downstream_table = 'monthly_brand_sales' -- 以目标报表表为起点追溯
),
FieldLevelLineage AS (
-- 字段级血缘:关联字段映射获取上下游字段关系
SELECT
CONCAT(upstream_table, '.', upstream_field) AS upstream_field,
CONCAT(downstream_table, '.', downstream_field) AS downstream_field,
t.task_name AS process_task,
'字段级' AS lineage_level
FROM field_mapping f
JOIN etl_tasks t ON f.task_id = t.task_id
WHERE f.downstream_table = 'monthly_brand_sales'
)
-- 合并表级与字段级血缘结果
SELECT
upstream_table AS upstream,
downstream_table AS downstream,
process_task,
lineage_level
FROM TableLevelLineage
UNION ALL
SELECT
upstream_field AS upstream,
downstream_field AS downstream,
process_task,
lineage_level
FROM FieldLevelLineage
ORDER BY lineage_level DESC, process_task;
输出示例
输出1:表级+字段级血缘关系表
upstream(上游数据) | downstream(下游数据) | process_task(处理过程) | lineage_level(血缘级别) |
products.brand | monthly_brand_sales.brand | 计算月度品牌销售额 | 字段级 |
orders.order_time | monthly_brand_sales.sale_month | 计算月度品牌销售额 | 字段级 |
orders.order_amount | monthly_brand_sales.total_sales | 计算月度品牌销售额 | 字段级 |
orders,products | monthly_brand_sales | 计算月度品牌销售额 | 表级 |
输出2:血缘图谱呈现说明:以“monthly_brand_sales”为下游终点,向上追溯至“orders”“products”两个业务表;字段级可明确“total_sales”来自“orders.order_amount”的求和计算,“sale_month”来自“orders.order_time”的月份格式化,“brand”直接取自“products.brand”。注:可扩展追溯方向(从上游业务表向下追踪所有下游报表),或关联数据质量检测结果(如上游字段为空率超标时,标注下游指标受影响风险);复杂场景(如多任务嵌套)可通过递归查询构建完整血缘链路。
Comments