跳转到内容

06-user-import-reuse-query-spec

默认周度查询:合集运营与增量主题

Section titled “默认周度查询:合集运营与增量主题”

本规范默认不执行下方旧完整QA快照。冻结当前registry水位lower和本轮MAX(id) upper,只取lower < canonical_import_id ≤ upper的导入ID、标题、来源、创建时间、来源时长、公开/私有范围与必要处理状态。用户ID不返回;正文、全库sentence_stats、旧HOLD、历史音频offset全扫不进入默认采集。元数据来自24h内本轮快照时直接复用原观察时间。

默认查询先核验实际数据库身份与相关表schema;只有单条SELECT/WITH。平台明确匹配YouTube/Bilibili,其他类型单列,不能用ELSE猜平台。去重保留YouTube/BVID大小写和分P;不能将包含文章等类型的upper当视频成功水位。标题主题归类后,如需独立用户覆盖,对该主题明确源键和同一lower/upper范围做COUNT(DISTINCT pluto_user_id),只返回聚合;逐条人数不能相加,缺跨期cohort则持续性UNKNOWN。

合集表现来自生产iOS公开目录与GA实际合集上下文聚合,不能把staging相同ID当生产成员映射。已有库存仅作ID/标题/来源索引。最终候选唯一成员才查必要播放、字幕、版权、难度、地区、完整已知合集权益与alias;重复成员共享证据。查不到保持UNKNOWN并block,不用质量机器人flag当已确认缺陷。原保护/人工QA/历史候选不改。

以下原文保留供明确调用旧QA工具时查阅;其中全量与HOLD重查不再是默认周度行为。它不是新默认run.mjs的输入采集要求。

用户导入复用周报:只读数据契约与查询规范

Section titled “用户导入复用周报:只读数据契约与查询规范”

Python 规则引擎不持有数据库或 Admin 凭证。每周 heartbeat 通过 mysql-access-mcp 从当次确认的环境读取只读数据,先生成不含原始用户 ID 的快照,再交给 scripts/yomiya_user_import_reuse_radar.py。默认环境是当前设计基线 yomiya-staging;任何真实分发前必须重新确认环境。

核心表:

  • user_content_imports:导入事实、水位、URL 和元数据。
  • transcription_tasks、news、news_sentences、audio_sentence_offsets:处理、可见性与轻审事实。
  • editorial_collections、editorial_collection_items、editorial_collection_imports:已有合集和条目冲突。
  • featured_home_layouts、YouTube/Bilibili playlist 表:活动首页及其内容冲突。

所有 SQL 必须是单条 SELECT / WITH。不得执行 INSERT、UPDATE、DELETE、DDL 或存储过程。

  1. ping 返回目标 database,写入快照 environment。
  2. 上述核心表均可读,并记录各表 schema;任一核心表/查询失败,设置 core_complete=false。
  3. 读取活动首页 is_active=1,如果有多条活动布局要 SKIP,不得任选一条。
  4. 读取全部 EditorialCollections(包括空合集),不能只检查本流程创建的合集。
  5. 快照完成前不推进登记表水位。

以下是字段契约;可按 canonical_import_id > :last_id 分页,每页最多 200 行。第一次/全量对账从 0 开始。SQL 聚合用户数量,但不返回 pluto_user_id。

WITH sentence_stats AS (
SELECT
ns.news_id,
COUNT(*) AS body_sentence_count,
SUM(CASE WHEN LOWER(COALESCE(ns.content_language, '')) IN ('ja', 'ja-jp') THEN 1 ELSE 0 END) AS japanese_sentence_count,
SUM(CASE WHEN aso.sentence_id IS NOT NULL THEN 1 ELSE 0 END) AS aligned_sentence_count,
MIN(aso.offset) AS first_offset_ms,
MAX(aso.offset + aso.duration_ms) AS last_offset_end_ms
FROM news_sentences ns
LEFT JOIN audio_sentence_offsets aso
ON aso.article_id = ns.news_id
AND aso.sentence_id = ns.id
WHERE LOWER(COALESCE(ns.type, 'body')) NOT IN ('title', 'translation')
GROUP BY ns.news_id
), import_demand AS (
SELECT
content_type,
unique_id,
COUNT(*) AS import_count,
COUNT(DISTINCT pluto_user_id) AS distinct_user_count,
MAX(id) AS canonical_import_id
FROM user_content_imports
GROUP BY content_type, unique_id
), ranked_imports AS (
SELECT
uci.*,
ROW_NUMBER() OVER (
PARTITION BY uci.content_type, uci.unique_id
ORDER BY
CASE WHEN tt.status = 'COMPLETED' THEN 1 ELSE 0 END DESC,
CASE WHEN uci.news_id IS NOT NULL THEN 1 ELSE 0 END DESC,
uci.id DESC
) AS row_rank
FROM user_content_imports uci
LEFT JOIN transcription_tasks tt ON tt.id = uci.transcription_task_id
), existing_editorial_news AS (
SELECT news_id FROM editorial_collection_items
UNION
SELECT news_id FROM editorial_collection_imports WHERE news_id IS NOT NULL
), existing_editorial_keys AS (
SELECT DISTINCT unique_id
FROM editorial_collection_imports
WHERE unique_id IS NOT NULL AND unique_id <> ''
), home_youtube_playlists AS (
SELECT DISTINCT
CAST(
SUBSTRING_INDEX(SUBSTRING_INDEX(src.source_id, '-', 2), '-', -1)
AS UNSIGNED
) AS playlist_id
FROM featured_home_layouts fhl
JOIN JSON_TABLE(fhl.sections, '$[*]' COLUMNS(section JSON PATH '$')) sec
JOIN JSON_TABLE(sec.section, '$.sources[*]' COLUMNS(
source_type VARCHAR(64) PATH '$.type',
source_id VARCHAR(64) PATH '$.id'
)) src
WHERE fhl.is_active = 1
AND src.source_type IN ('youtube_playlist', 'youtube_playlist_items')
), active_home_youtube_videos AS (
SELECT DISTINCT ypv.video_id
FROM youtube_playlist_videos ypv
JOIN home_youtube_playlists hp ON hp.playlist_id = ypv.youtube_playlist_id
WHERE ypv.removed_at IS NULL
)
SELECT
d.canonical_import_id,
ri.created_at AS imported_at,
CASE
WHEN LOWER(ri.content_type) LIKE '%bilibili%' OR ri.unique_id LIKE 'Bilibili_%' THEN 'BILIBILI'
ELSE 'YOUTUBE'
END AS platform,
ri.unique_id,
ri.url,
ri.title,
ri.thumbnail_url,
ri.duration AS duration_seconds,
ri.availability,
tt.status AS task_status,
tt.review_status,
ri.news_id,
n.visibility AS news_visibility,
n.source_scope AS news_source_scope,
n.content_language,
n.duration_seconds AS news_duration_seconds,
COALESCE(ss.body_sentence_count, 0) AS body_sentence_count,
COALESCE(ss.aligned_sentence_count, 0) AS aligned_sentence_count,
COALESCE(ss.japanese_sentence_count, 0) AS japanese_sentence_count,
CASE WHEN COALESCE(ss.body_sentence_count, 0) = 0 THEN NULL
ELSE ss.japanese_sentence_count / ss.body_sentence_count END AS japanese_sentence_ratio,
ss.first_offset_ms,
ss.last_offset_end_ms,
CASE WHEN ahv.video_id IS NULL THEN FALSE ELSE TRUE END AS in_active_home,
CASE WHEN een.news_id IS NULL AND eek.unique_id IS NULL THEN FALSE ELSE TRUE END AS in_existing_editorial,
d.import_count,
d.distinct_user_count
FROM import_demand d
JOIN ranked_imports ri
ON ri.content_type = d.content_type
AND ri.unique_id = d.unique_id
AND ri.row_rank = 1
LEFT JOIN transcription_tasks tt ON tt.id = ri.transcription_task_id
LEFT JOIN news n ON n.id = ri.news_id
LEFT JOIN sentence_stats ss ON ss.news_id = ri.news_id
LEFT JOIN existing_editorial_news een ON een.news_id = ri.news_id
LEFT JOIN existing_editorial_keys eek ON eek.unique_id = ri.unique_id
LEFT JOIN active_home_youtube_videos ahv
ON ahv.video_id = CASE
WHEN ri.unique_id LIKE 'YouTube_%'
THEN SUBSTRING(ri.unique_id, LENGTH('YouTube_') + 1)
ELSE NULL
END
WHERE d.canonical_import_id > :last_id
ORDER BY d.canonical_import_id
LIMIT 200;

分页结束条件是返回行数少于 200;合并后再次按脚本的平台键去重:YouTube 为 video_id,Bilibili 为 bvid + cid/page。不得只按母 BVID 去重。

活动首页必须保留 id、name、updated_at、全部 section/source 的 type/id,并解析出首页实际覆盖的 news ID。合集快照至少包含:

SELECT
ec.id,
ec.title,
ec.description,
ec.deleted_at,
ec.created_at,
ec.updated_at,
COUNT(DISTINCT eci.news_id) AS item_count,
COUNT(DISTINCT eci2.transcription_task_id) AS import_count
FROM editorial_collections ec
LEFT JOIN editorial_collection_items eci ON eci.collection_id = ec.id
LEFT JOIN editorial_collection_imports eci2 ON eci2.collection_id = ec.id
GROUP BY ec.id, ec.title, ec.description, ec.deleted_at, ec.created_at, ec.updated_at;

受管合集的 OPEN、容量、专题 8 周期限以本地登记 JSON 为准;数据库里的历史合集不得被脚本自行标成受管 OPEN。

平台库存只统计当前可见内容,用于 45%–60% 监控:

SELECT
CASE
WHEN c.source_type = 'bilibili' THEN 'BILIBILI'
WHEN c.source_type = 'youtube' THEN 'YOUTUBE'
END AS platform,
n.visibility,
COUNT(*) AS count
FROM news n
JOIN channels c ON c.id = n.channel_id
WHERE c.source_type IN ('bilibili', 'youtube')
AND n.visibility IN ('VISIBLE', 'MEMBERSHIP_VISIBLE', 'TIME_LIMIT_VISIBLE')
GROUP BY platform, n.visibility;

必须排除 news、podcast 等其他 channels.source_type;不能把所有非 Bilibili 内容都归入 YouTube。2026-08-31 的实时对账证明,错误的 ELSE 'YOUTUBE' 会把新闻与播客库存计入 YouTube,显著扭曲 VIP 比例。

需求集中度只能返回聚合数字:每个平台导入人数、导入总数、Top 1/Top 5 占比。查询内部可以使用用户 ID,输出快照不得包含用户 ID、邮箱、令牌或其他后台敏感数据。

处理供应商必须按去重后的已完成内容实时重算,并写入 processing_provider_summary。优先使用 actual_transcription_provider,否则回退到 transcription_provider,按 news.duration_seconds 计算分钟;不能长期沿用启动基线里的 ElevenLabs 时长。至少输出平台、供应商、内容数和完成分钟数。

周任务不修改首页。对于登记为 OPEN / DISTRIBUTED 且有 distributed_at 的受管合集,只有上线满 14 天后才读取 collection_metrics:

{
"collection_id": 88,
"collection_view": 35,
"click_through_rate": 0.22,
"platform_click_through_median": 0.20,
"effective_learning_rate": 0.60,
"platform_effective_learning_median": 0.55,
"source_stable": true,
"fills_home_gap": true,
"duplicates_home_promise": false
}

缺少任一字段或同平台中位数时必须为 SKIP。全部通过时只标记 READY_HOME_PROPOSAL,仍须另提首页方案并单独确认。

source_playable 只接受以下证据:

  • YouTube:当前视频公开元数据/播放器端点明确可用;private、deleted、unavailable 为失败。
  • Bilibili:公开 view API 返回 code=0,母 BVID 存在,并且指定 cid/page 仍存在。
  • 已缓存检查最多复用 7 天;待分发内容在执行 Admin 操作前必须当天再查。

网络超时或响应不确定时写 null,状态进入 HOLD_QA;不能把超时当作可播放。明确下架进入 SOURCE_REMOVED。

{
"schema_version": 1,
"environment": "yomiya-staging",
"generated_at": "ISO-8601",
"core_complete": true,
"watermark": {"maximum_import_id": 709},
"active_home_layout": {},
"editorial_collections": [],
"platform_visibility": [],
"user_concentration": [],
"processing_provider_summary": [],
"collection_metrics": [],
"imports": []
}
故障报告水位SlackAdmin
MySQL / 核心表失败SKIP不推进仅异常摘要/预览无写入
Admin/首页解析失败SKIP不推进仅异常摘要/预览无写入
源播放检查失败对应内容 HOLD_QA核心查询完整时可推进放入 Thread 3无写入
Slack 明确失败本地报告保留已按核心数据规则处理重试一次无写入
Slack 超时/不确定本地报告保留已按核心数据规则处理不重发,防重复无写入
Terminal window
python3 scripts/yomiya_user_import_reuse_radar.py \
--snapshot product/YomiyaContentOperator/reports/YYYY-MM-DD-user-import-reuse-source.json \
--date YYYY-MM-DD \
--update-registry \
--print-slack

默认只生成 Slack 预览。第一次真实测试必须另行确认;不要在 heartbeat 提示词中绕过登记表双重开关。