87 lines
4.0 KiB
SQL
87 lines
4.0 KiB
SQL
-- 统计L1(A1)等级组件练习退出率
|
||
-- 退出判定：play_result为failed或close视为退出
|
||
-- 统计规则：
|
||
-- 1. 自动统计运行日期前一天的数据
|
||
-- 2. 昨日练习≥10次用昨日全量数据，<10次自动补充历史记录至10次，历史不足10次取全部记录
|
||
-- 3. 自动剔除测试账号（仅保留bi_vala_app_account.status=1的正常用户）
|
||
WITH all_component_records AS (
|
||
SELECT user_id, component_unique_code, play_result, created_at, level, c_type FROM bi_user_component_play_record_0 WHERE deleted_at IS NULL
|
||
UNION ALL
|
||
SELECT user_id, component_unique_code, play_result, created_at, level, c_type FROM bi_user_component_play_record_1 WHERE deleted_at IS NULL
|
||
UNION ALL
|
||
SELECT user_id, component_unique_code, play_result, created_at, level, c_type FROM bi_user_component_play_record_2 WHERE deleted_at IS NULL
|
||
UNION ALL
|
||
SELECT user_id, component_unique_code, play_result, created_at, level, c_type FROM bi_user_component_play_record_3 WHERE deleted_at IS NULL
|
||
UNION ALL
|
||
SELECT user_id, component_unique_code, play_result, created_at, level, c_type FROM bi_user_component_play_record_4 WHERE deleted_at IS NULL
|
||
UNION ALL
|
||
SELECT user_id, component_unique_code, play_result, created_at, level, c_type FROM bi_user_component_play_record_5 WHERE deleted_at IS NULL
|
||
UNION ALL
|
||
SELECT user_id, component_unique_code, play_result, created_at, level, c_type FROM bi_user_component_play_record_6 WHERE deleted_at IS NULL
|
||
UNION ALL
|
||
SELECT user_id, component_unique_code, play_result, created_at, level, c_type FROM bi_user_component_play_record_7 WHERE deleted_at IS NULL
|
||
),
|
||
valid_records AS (
|
||
SELECT
|
||
cr.component_unique_code,
|
||
cr.play_result,
|
||
cr.created_at,
|
||
cr.c_type,
|
||
CASE WHEN DATE(cr.created_at) = CURRENT_DATE - INTERVAL '1 day' THEN 1 ELSE 0 END AS is_yesterday
|
||
FROM all_component_records cr
|
||
JOIN bi_vala_app_character c ON cr.user_id = c.id AND c.deleted_at IS NULL
|
||
JOIN bi_vala_app_account a ON c.account_id = a.id AND a.status = 1 AND a.deleted_at IS NULL
|
||
WHERE cr.play_result IS NOT NULL AND cr.component_unique_code IS NOT NULL AND cr.level = 'A1'
|
||
),
|
||
-- 统计每个组件昨日练习次数
|
||
yesterday_counts AS (
|
||
SELECT
|
||
component_unique_code,
|
||
COUNT(*) AS yesterday_cnt
|
||
FROM valid_records
|
||
WHERE is_yesterday = 1
|
||
GROUP BY component_unique_code
|
||
),
|
||
-- 给记录排序：昨日记录排最前，历史记录按时间倒序
|
||
ranked_records AS (
|
||
SELECT
|
||
vr.component_unique_code,
|
||
vr.play_result,
|
||
vr.c_type,
|
||
vr.is_yesterday,
|
||
ROW_NUMBER() OVER (
|
||
PARTITION BY vr.component_unique_code
|
||
ORDER BY vr.is_yesterday DESC, vr.created_at DESC
|
||
) AS rn
|
||
FROM valid_records vr
|
||
JOIN yesterday_counts yc ON vr.component_unique_code = yc.component_unique_code
|
||
),
|
||
-- 筛选统计样本：
|
||
-- 昨日练习≥10次：取全部昨日记录
|
||
-- 昨日练习<10次：取全部昨日记录 + 最近历史记录补到10次
|
||
filtered_samples AS (
|
||
SELECT
|
||
r.component_unique_code,
|
||
r.play_result,
|
||
r.c_type
|
||
FROM ranked_records r
|
||
JOIN yesterday_counts yc ON r.component_unique_code = yc.component_unique_code
|
||
WHERE
|
||
(yc.yesterday_cnt >= 10 AND r.is_yesterday = 1)
|
||
OR
|
||
(yc.yesterday_cnt < 10 AND r.rn <= 10)
|
||
),
|
||
-- 统计结果，先按数值排序
|
||
component_stats AS (
|
||
SELECT
|
||
component_unique_code AS 组件唯一编码,
|
||
COUNT(*) AS 总练习次数,
|
||
SUM(CASE WHEN play_result IN ('failed', 'close') THEN 1 ELSE 0 END) AS 退出次数,
|
||
ROUND(CASE WHEN COUNT(*) = 0 THEN 0 ELSE
|
||
SUM(CASE WHEN play_result IN ('failed', 'close') THEN 1 ELSE 0 END)::DECIMAL / COUNT(*) * 100 END, 2) AS 退出率数值
|
||
FROM filtered_samples
|
||
GROUP BY component_unique_code
|
||
ORDER BY 退出率数值 DESC
|
||
)
|
||
SELECT 组件唯一编码, 总练习次数, 退出次数, 退出率数值 || '%' AS 退出率 FROM component_stats;
|