91 lines
4.0 KiB
SQL
91 lines
4.0 KiB
SQL
-- 统计L1(A1)等级组件Oops率
|
||
-- 修改统计日期时替换下面的'2026-04-12'为目标日期
|
||
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 ('Oops', 'Opps') THEN 1
|
||
WHEN play_result = 'pass' AND (c_type LIKE '%core_%' OR c_type LIKE '%scence_%') THEN 1
|
||
ELSE 0
|
||
END) AS Oops次数,
|
||
ROUND(CASE WHEN COUNT(*) = 0 THEN 0 ELSE
|
||
SUM(CASE
|
||
WHEN play_result IN ('Oops', 'Opps') THEN 1
|
||
WHEN play_result = 'pass' AND (c_type LIKE '%core_%' OR c_type LIKE '%scence_%') THEN 1
|
||
ELSE 0
|
||
END)::DECIMAL / COUNT(*) * 100 END, 2) AS Oops率数值
|
||
FROM filtered_samples
|
||
GROUP BY component_unique_code
|
||
ORDER BY Oops率数值 DESC
|
||
)
|
||
SELECT 组件唯一编码, 总练习次数, Oops次数, Oops率数值 || '%' AS Oops率 FROM component_stats;
|