媒体内容版权监控与版税结算数仓实践

将内容资产库、授权合同表和多平台播放流水整合为多层数仓，自动计算版税归属、追踪合同到期风险，并在月结时刻锁定历史播放快照防止数据追溯争议。本文以 15 条内容资产、20 份授权合同、60 条播放记录为数据集，端到端演示 ODS → DWD → DWS → ADS 四层架构，覆盖 OSS PIPE、CDC 合同同步、Dynamic Table 版税归因、Table Stream + MERGE INTO 合同 SCD、Time Travel 月结快照锁定五项核心能力。

概述

媒体版权数仓的核心挑战是：播放数据来自多个平台、合同分成方式不统一（按播放量、按收益比例、固定授权费三种），且月结后不允许追溯修改。

云器 Lakehouse 通过以下组合解决这些问题：

问题解决方案
多平台每日推送播放 CSV 文件OSS PIPE 自动摄取，按文件变更触发，无需手写调度脚本
合同管理系统状态实时变更MySQL CDC 同步 + Table Stream 捕获行级变更，驱动 SCD 处理
三种分成方式统一版税归因计算Dynamic Table 声明式 SQL，每月分组聚合并应用合同条款
月结后平台补报数据导致历史播放变动Time Travel 快照查询，将结算时刻的数据版本固化为凭证
合同临期预警，防止版权空窗ADS 层 
days_to_expiry
days_to_expiry
字段驱动告警

涉及的 SQL 命令

命令 / 函数用途说明
CREATE TABLE
CREATE TABLE
建 ODS 层原始表（内容资产、合同、播放流水）普通表，作为 Dynamic Table 上游
CREATE TABLE STREAM
CREATE TABLE STREAM
在合同表上创建 Table Stream捕获 INSERT / UPDATE / DELETE 变更，驱动合同 SCD
CREATE DYNAMIC TABLE
CREATE DYNAMIC TABLE
创建 DWD / DWS / ADS 层增量计算表系统自动识别上游变更并增量刷新
REFRESH DYNAMIC TABLE
REFRESH DYNAMIC TABLE
手动触发一次刷新初次构建或调试时使用
MERGE INTO
MERGE INTO
将 Stream 变更合并写入合同历史表SCD 场景：新增行插入，状态变更更新
DESC HISTORY
DESC HISTORY
查看表的历史版本记录Time Travel 前先确认可用版本
TIMESTAMP AS OF
TIMESTAMP AS OF
查询历史时间点的数据快照月结快照锁定，防止追溯修改
DATEDIFF
DATEDIFF
计算合同剩余天数ADS 层合同临期预警

前置准备

本文所有示例在 

best_practice_copyright_royalty
best_practice_copyright_royalty
Schema 下运行。

CREATE SCHEMA IF NOT EXISTS best_practice_copyright_royalty;

ODS 层：原始数据接入

内容资产表

CREATE TABLE IF NOT EXISTS best_practice_copyright_royalty.doc_content_assets ( content_id STRING, title STRING, content_type STRING, -- movie / tv_series / music / documentary / podcast / short_video rights_holder STRING, release_year INT, region STRING -- CN / GLOBAL );

写入 15 条内容资产数据：

从本地 CSV 导入（推荐）：

-- 第一步：通过 SQL PUT 将本地 CSV 文件上传到 User Volume PUT '/path/to/doc_content_assets.csv' TO USER VOLUME FILE 'doc_content_assets.csv';

-- 第二步：从 User Volume COPY INTO 表 COPY INTO best_practice_copyright_royalty.doc_content_assets FROM USER VOLUME USING csv OPTIONS('header'='true', 'sep'=',', 'nullValue'='') FILES ('doc_content_assets.csv');

也可直接内联插入小批量测试数据（不需要 CSV 文件）：

INSERT INTO best_practice_copyright_royalty.doc_content_assets VALUES ('C001','The Dragon Legacy', 'movie', 'StarFilms Ltd', 2022,'CN'), ('C002','Sky Warriors Season 1', 'tv_series','BlueSky Media', 2021,'CN'), ('C003','Jazz Nights', 'music', 'Harmony Records', 2023,'GLOBAL'), ('C004','Nature Chronicles', 'documentary','EarthVision Inc', 2020,'CN'), ('C005','Tech Talk Podcast S1', 'podcast', 'CastWave Studio', 2023,'CN'), ('C006','Ocean Odyssey', 'documentary','EarthVision Inc', 2021,'GLOBAL'), ('C007','Pop Hits Vol.3', 'music', 'Harmony Records', 2022,'CN'), ('C008','Drama Kings Season 2', 'tv_series','BlueSky Media', 2022,'CN'), ('C009','The Lost City', 'movie', 'StarFilms Ltd', 2023,'CN'), ('C010','Morning Yoga Flow', 'short_video','WellnessFirst', 2024,'CN'), ('C011','Code & Coffee S2', 'podcast', 'CastWave Studio', 2024,'CN'), ('C012','Street Food Asia', 'documentary','EarthVision Inc', 2022,'GLOBAL'), ('C013','Summer Beats', 'music', 'Harmony Records', 2024,'CN'), ('C014','Thriller Night', 'movie', 'StarFilms Ltd', 2021,'GLOBAL'), ('C015','Innovation Stories S1', 'tv_series','BlueSky Media', 2023,'CN');

授权合同表

rate_type
rate_type
支持三种分成方式：
revenue_share
revenue_share
（按收益比例）、
per_play
per_play
（按播放量）、
flat_fee
flat_fee
（固定授权费）。

CREATE TABLE IF NOT EXISTS best_practice_copyright_royalty.doc_license_contracts ( contract_id STRING, content_id STRING, platform_id STRING, start_date DATE, end_date DATE, rate_type STRING, -- revenue_share / per_play / flat_fee rate_value DOUBLE, -- 比例值（revenue_share）、单价（per_play）、年费（flat_fee） min_guarantee DOUBLE, -- 最低保底（年度，元） status STRING -- active / expired / renewed );

写入 20 份合同，覆盖三种分成方式：

从本地 CSV 导入（推荐）：

-- 第一步：通过 SQL PUT 将本地 CSV 文件上传到 User Volume PUT '/path/to/doc_license_contracts.csv' TO USER VOLUME FILE 'doc_license_contracts.csv';

-- 第二步：从 User Volume COPY INTO 表 COPY INTO best_practice_copyright_royalty.doc_license_contracts FROM USER VOLUME USING csv OPTIONS('header'='true', 'sep'=',', 'nullValue'='') FILES ('doc_license_contracts.csv');

也可直接内联插入小批量测试数据（不需要 CSV 文件）：

INSERT INTO best_practice_copyright_royalty.doc_license_contracts VALUES ('CTR001','C001','PLT_A',CAST('2025-01-01' AS DATE),CAST('2025-12-31' AS DATE),'revenue_share',0.30,50000,'active'), ('CTR002','C001','PLT_B',CAST('2025-03-01' AS DATE),CAST('2025-08-31' AS DATE),'revenue_share',0.25,30000,'active'), ('CTR003','C002','PLT_A',CAST('2025-01-01' AS DATE),CAST('2025-06-30' AS DATE),'per_play',0.005,10000,'expired'), ('CTR004','C002','PLT_C',CAST('2025-04-01' AS DATE),CAST('2025-12-31' AS DATE),'revenue_share',0.20,20000,'active'), ('CTR005','C003','PLT_A',CAST('2025-01-01' AS DATE),CAST('2025-12-31' AS DATE),'per_play',0.003,5000,'active'), ('CTR006','C003','PLT_B',CAST('2025-02-01' AS DATE),CAST('2026-01-31' AS DATE),'flat_fee',8000,0,'active'), ('CTR007','C004','PLT_C',CAST('2024-12-01' AS DATE),CAST('2025-11-30' AS DATE),'revenue_share',0.15,15000,'active'), ('CTR008','C005','PLT_A',CAST('2025-01-01' AS DATE),CAST('2025-12-31' AS DATE),'per_play',0.002,3000,'active'), ('CTR009','C006','PLT_B',CAST('2025-01-01' AS DATE),CAST('2025-06-30' AS DATE),'revenue_share',0.18,12000,'expired'), ('CTR010','C007','PLT_A',CAST('2025-03-01' AS DATE),CAST('2025-12-31' AS DATE),'per_play',0.004,6000,'active'), ('CTR011','C008','PLT_C',CAST('2025-01-01' AS DATE),CAST('2025-12-31' AS DATE),'revenue_share',0.22,25000,'active'), ('CTR012','C009','PLT_A',CAST('2025-05-01' AS DATE),CAST('2026-04-30' AS DATE),'revenue_share',0.28,40000,'active'), ('CTR013','C010','PLT_B',CAST('2025-01-01' AS DATE),CAST('2025-12-31' AS DATE),'per_play',0.001,2000,'active'), ('CTR014','C011','PLT_C',CAST('2025-02-01' AS DATE),CAST('2025-07-31' AS DATE),'flat_fee',5000,0,'expired'), ('CTR015','C012','PLT_A',CAST('2025-01-01' AS DATE),CAST('2025-12-31' AS DATE),'revenue_share',0.20,18000,'active'), ('CTR016','C013','PLT_B',CAST('2025-04-01' AS DATE),CAST('2026-03-31' AS DATE),'per_play',0.003,4000,'active'), ('CTR017','C014','PLT_A',CAST('2024-11-01' AS DATE),CAST('2025-10-31' AS DATE),'revenue_share',0.25,35000,'active'), ('CTR018','C014','PLT_C',CAST('2025-01-01' AS DATE),CAST('2025-12-31' AS DATE),'per_play',0.006,8000,'active'), ('CTR019','C015','PLT_B',CAST('2025-03-01' AS DATE),CAST('2025-08-31' AS DATE),'revenue_share',0.18,14000,'active'), ('CTR020','C015','PLT_C',CAST('2025-06-01' AS DATE),CAST('2026-05-31' AS DATE),'revenue_share',0.15,10000,'active');

合同分成方式分布：

SELECT rate_type, COUNT(*) AS cnt, ROUND(AVG(rate_value), 4) AS avg_rate FROM best_practice_copyright_royalty.doc_license_contracts GROUP BY rate_type;

rate_type | cnt | avg_rate ---------------+-----+--------- flat_fee | 2 | 6500 per_play | 7 | 0.0034 revenue_share | 11 | 0.2145

平台播放流水表

OSS PIPE 场景下，视频平台每日将 FTP 推送的播放量报表（CSV/Parquet）落到 OSS Volume，PIPE 自动摄取到此表。

CREATE TABLE IF NOT EXISTS best_practice_copyright_royalty.doc_platform_plays ( play_id STRING, content_id STRING, platform_id STRING, play_date DATE, play_count BIGINT, revenue DOUBLE );

从本地 CSV 导入（推荐）：

-- 第一步：通过 SQL PUT 将本地 CSV 文件上传到 User Volume PUT '/path/to/doc_platform_plays.csv' TO USER VOLUME FILE 'doc_platform_plays.csv';

-- 第二步：从 User Volume COPY INTO 表 COPY INTO best_practice_copyright_royalty.doc_platform_plays FROM USER VOLUME USING csv OPTIONS('header'='true', 'sep'=',', 'nullValue'='') FILES ('doc_platform_plays.csv');

也可直接内联插入小批量测试数据（不需要 CSV 文件）：

写入 60 条播放记录，覆盖 PLT_A / PLT_B / PLT_C 三个平台、2025 年 1-7 月多个月份：

INSERT INTO best_practice_copyright_royalty.doc_platform_plays VALUES ('P001','C001','PLT_A',CAST('2025-01-15' AS DATE),12500,18750.00), ('P002','C001','PLT_A',CAST('2025-02-15' AS DATE),14200,21300.00), ('P003','C001','PLT_A',CAST('2025-03-15' AS DATE),16800,25200.00), ('P004','C001','PLT_A',CAST('2025-04-15' AS DATE),15000,22500.00), ('P005','C009','PLT_A',CAST('2025-05-15' AS DATE),25000,37500.00), ('P006','C009','PLT_A',CAST('2025-06-15' AS DATE),28000,42000.00), ('P007','C009','PLT_A',CAST('2025-07-15' AS DATE),31000,46500.00), ('P008','C014','PLT_A',CAST('2025-02-15' AS DATE),21000,31500.00), ('P009','C014','PLT_A',CAST('2025-03-15' AS DATE),19500,29250.00), ('P010','C014','PLT_A',CAST('2025-05-15' AS DATE),20000,30000.00), ('P011','C003','PLT_A',CAST('2025-01-15' AS DATE),300000,900.00), ('P012','C003','PLT_A',CAST('2025-04-15' AS DATE),400000,1200.00), ('P013','C005','PLT_A',CAST('2025-06-15' AS DATE),350000,700.00), ('P014','C007','PLT_A',CAST('2025-03-15' AS DATE),250000,1000.00), ('P015','C012','PLT_A',CAST('2025-07-15' AS DATE),300000,6000.00), ('P016','C015','PLT_A',CAST('2025-06-15' AS DATE),100000,18000.00), ('P017','C005','PLT_A',CAST('2025-05-15' AS DATE),61000,24803.00), ('P018','C001','PLT_B',CAST('2025-03-15' AS DATE),10000,8000.00), ('P019','C001','PLT_B',CAST('2025-04-15' AS DATE),12000,9000.00), ('P020','C001','PLT_B',CAST('2025-05-15' AS DATE),15000,9500.00), ('P021','C003','PLT_B',CAST('2025-02-15' AS DATE),80000,6000.00), ('P022','C003','PLT_B',CAST('2025-05-15' AS DATE),100000,4000.00), ('P023','C006','PLT_B',CAST('2025-01-15' AS DATE),120000,7000.00), ('P024','C006','PLT_B',CAST('2025-06-15' AS DATE),160000,6500.00), ('P025','C010','PLT_B',CAST('2025-01-15' AS DATE),200000,2200.00), ('P026','C010','PLT_B',CAST('2025-05-15' AS DATE),120000,1200.00), ('P027','C013','PLT_B',CAST('2025-03-15' AS DATE),210000,2100.00), ('P028','C013','PLT_B',CAST('2025-04-15' AS DATE),190000,1900.00), ('P029','C001','PLT_B',CAST('2025-06-15' AS DATE),8000,6000.00), ('P030','C003','PLT_B',CAST('2025-07-15' AS DATE),50000,5000.00), ('P031','C006','PLT_B',CAST('2025-02-15' AS DATE),40000,4800.00), ('P032','C010','PLT_B',CAST('2025-07-15' AS DATE),30000,3000.00), ('P033','C013','PLT_B',CAST('2025-06-15' AS DATE),20000,2000.00), ('P034','C001','PLT_B',CAST('2025-07-15' AS DATE),7000,5000.00), ('P035','C003','PLT_B',CAST('2025-01-15' AS DATE),25000,1500.00), ('P036','C006','PLT_B',CAST('2025-03-15' AS DATE),15000,2000.00), ('P037','C010','PLT_B',CAST('2025-02-15' AS DATE),12000,1000.00), ('P038','C013','PLT_B',CAST('2025-07-15' AS DATE),9800,424.00), ('P039','C002','PLT_C',CAST('2025-04-15' AS DATE),30000,6000.00), ('P040','C002','PLT_C',CAST('2025-05-15' AS DATE),40000,7000.00), ('P041','C004','PLT_C',CAST('2025-01-15' AS DATE),45000,9000.00), ('P042','C004','PLT_C',CAST('2025-05-15' AS DATE),50000,8000.00), ('P043','C008','PLT_C',CAST('2025-02-15' AS DATE),60000,12000.00), ('P044','C008','PLT_C',CAST('2025-05-15' AS DATE),70000,3000.00), ('P045','C011','PLT_C',CAST('2025-07-15' AS DATE),80000,10000.00), ('P046','C014','PLT_C',CAST('2025-03-15' AS DATE),90000,15000.00), ('P047','C014','PLT_C',CAST('2025-05-15' AS DATE),100000,1000.00), ('P048','C015','PLT_C',CAST('2025-06-15' AS DATE),70000,9000.00), ('P049','C002','PLT_C',CAST('2025-06-15' AS DATE),5000,7000.00), ('P050','C004','PLT_C',CAST('2025-02-15' AS DATE),4000,6000.00), ('P051','C008','PLT_C',CAST('2025-03-15' AS DATE),3000,5000.00), ('P052','C011','PLT_C',CAST('2025-04-15' AS DATE),4000,5000.00), ('P053','C014','PLT_C',CAST('2025-06-15' AS DATE),3000,4000.00), ('P054','C015','PLT_C',CAST('2025-07-15' AS DATE),3000,3000.00), ('P055','C011','PLT_C',CAST('2025-05-15' AS DATE),46300,471.00), ('P056','C002','PLT_C',CAST('2025-07-15' AS DATE),1000,1000.00), ('P057','C004','PLT_C',CAST('2025-03-15' AS DATE),1000,1000.00), ('P058','C008','PLT_C',CAST('2025-04-15' AS DATE),1000,1000.00), ('P059','C011','PLT_C',CAST('2025-06-15' AS DATE),700,792.00), ('P060','C001','PLT_A',CAST('2025-05-15' AS DATE),17200,25800.00);

验证各平台总体数据量：

SELECT platform_id, SUM(play_count) AS total_plays, ROUND(SUM(revenue), 2) AS total_revenue, COUNT(DISTINCT content_id) AS unique_contents FROM best_practice_copyright_royalty.doc_platform_plays GROUP BY platform_id ORDER BY total_revenue DESC;

platform_id | total_plays | total_revenue | unique_contents ------------+-------------+---------------+---------------- PLT_A | 1981200 | 382903 | 8 PLT_C | 707000 | 114263 | 6 PLT_B | 1433800 | 88124 | 5

PLT_A 收益最高，主要由《The Lost City》（C009）和《Thriller Night》（C014）这类高单价 

revenue_share
revenue_share
合同驱动。

配置 OSS PIPE（正式环境）

正式环境中，先创建 OSS Storage Connection 和 Volume，再创建 PIPE 持续扫描平台推送目录：

-- 创建存储连接（以阿里云 OSS 为例） CREATE STORAGE CONNECTION IF NOT EXISTS oss_media_conn TYPE = 'OSS' ACCESS_ID = '<your-access-id>' ACCESS_KEY = '<your-access-key>' ENDPOINT = '<oss-cn-hangzhou.aliyuncs.com>'; -- 创建 Volume，指向平台每日报表目录 CREATE EXTERNAL VOLUME IF NOT EXISTS vol_platform_plays TYPE = 'OSS' BUCKET = '<your-bucket>' PATH = '/media/platform-plays/' CONNECTION = oss_media_conn; -- 创建 PIPE，自动摄取新增 Parquet 文件 CREATE PIPE IF NOT EXISTS best_practice_copyright_royalty.pipe_platform_plays VIRTUAL_CLUSTER = 'DEFAULT' AUTO_PURGE = FALSE AS COPY INTO best_practice_copyright_royalty.doc_platform_plays FROM VOLUME vol_platform_plays USING parquet;

ODS 层：合同变更 Table Stream

合同管理系统通过 CDC 将 MySQL 数据同步到 

doc_license_contracts
doc_license_contracts
后，在该表上建立 Table Stream 捕获行级变更，驱动合同历史表的 SCD 处理。

CREATE TABLE STREAM IF NOT EXISTS stream_contract_changes ON TABLE doc_license_contracts WITH PROPERTIES ('TABLE_STREAM_MODE' = 'STANDARD');

建立合同历史表，存储每次状态变更的前后值：

CREATE TABLE IF NOT EXISTS best_practice_copyright_royalty.doc_contract_history ( history_id STRING, contract_id STRING, content_id STRING, platform_id STRING, old_status STRING, new_status STRING, change_reason STRING, change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP() );

模拟一次合同续签操作，将 CTR003 的状态从 

expired
expired
改为 
renewed
renewed
：

UPDATE best_practice_copyright_royalty.doc_license_contracts SET status = 'renewed' WHERE contract_id = 'CTR003';

查看 Stream 中捕获的变更（STANDARD 模式下 UPDATE 产生 

UPDATE_BEFORE
UPDATE_BEFORE
+ 
UPDATE_AFTER
UPDATE_AFTER
两行）：

SELECT contract_id, content_id, platform_id, status, __change_type FROM best_practice_copyright_royalty.stream_contract_changes LIMIT 5;

contract_id | content_id | platform_id | status | __change_type ------------+------------+-------------+---------+-------------- CTR003 | C002 | PLT_A | renewed | UPDATE_AFTER CTR003 | C002 | PLT_A | expired | UPDATE_BEFORE

用 MERGE INTO 消费 Stream，将合同变更写入历史表：

MERGE INTO best_practice_copyright_royalty.doc_contract_history AS t USING ( SELECT CONCAT(contract_id, '_', CAST(CURRENT_TIMESTAMP() AS STRING)) AS history_id, contract_id, content_id, platform_id, CASE WHEN __change_type = 'UPDATE_AFTER' THEN NULL WHEN __change_type = 'UPDATE_BEFORE' THEN status WHEN __change_type = 'DELETE' THEN status ELSE NULL END AS old_status, CASE WHEN __change_type = 'UPDATE_AFTER' THEN status WHEN __change_type = 'INSERT' THEN status ELSE NULL END AS new_status, __change_type AS change_reason FROM best_practice_copyright_royalty.stream_contract_changes WHERE __change_type IN ('UPDATE_AFTER', 'INSERT', 'DELETE') ) AS s ON t.contract_id = s.contract_id AND t.new_status = s.new_status WHEN NOT MATCHED THEN INSERT (history_id, contract_id, content_id, platform_id, old_status, new_status, change_reason) VALUES (s.history_id, s.contract_id, s.content_id, s.platform_id, s.old_status, s.new_status, s.change_reason);

DWD 层：播放事实宽表

DWD 层将播放流水与内容资产做 JOIN，补充内容元数据，并按月份做分区标记，方便后续 DWS 按月聚合。

CREATE DYNAMIC TABLE IF NOT EXISTS best_practice_copyright_royalty.dwd_play_facts AS SELECT p.play_id, p.content_id, p.platform_id, p.play_date, p.play_count, p.revenue, c.title, c.content_type, c.rights_holder, c.release_year, c.region, DATE_FORMAT(p.play_date, 'yyyy-MM') AS stat_month FROM best_practice_copyright_royalty.doc_platform_plays p LEFT JOIN best_practice_copyright_royalty.doc_content_assets c ON p.content_id = c.content_id;

手动触发首次刷新：

REFRESH DYNAMIC TABLE best_practice_copyright_royalty.dwd_play_facts; SELECT COUNT(*) AS row_count FROM best_practice_copyright_royalty.dwd_play_facts;

row_count --------- 60

DWS 层：版税归因计算

DWS 层是版税数仓的核心层。按 

content_id + platform_id + stat_month
content_id + platform_id + stat_month
三维聚合播放数据，关联合同条款，根据 
rate_type
rate_type
应用不同计算公式，并取保底值的月度摊销值进行比较。

三种分成公式：

rate_type版税计算逻辑
revenue_share
revenue_share
MAX(本月平台收益 × 比例, 年保底 / 12)
MAX(本月平台收益 × 比例, 年保底 / 12)
per_play
per_play
MAX(本月播放量 × 单价, 年保底 / 12)
MAX(本月播放量 × 单价, 年保底 / 12)
flat_fee
flat_fee
年授权费 / 12
年授权费 / 12
（固定，不随播放量浮动）

CREATE DYNAMIC TABLE IF NOT EXISTS best_practice_copyright_royalty.dws_royalty_calc AS SELECT f.content_id, f.platform_id, f.stat_month, f.title, f.content_type, f.rights_holder, SUM(f.play_count) AS total_plays, SUM(f.revenue) AS total_revenue, c.contract_id, c.rate_type, c.rate_value, c.min_guarantee, c.end_date, CASE WHEN c.rate_type = 'revenue_share' THEN GREATEST(SUM(f.revenue) * c.rate_value, c.min_guarantee / 12) WHEN c.rate_type = 'per_play' THEN GREATEST(SUM(f.play_count) * c.rate_value, c.min_guarantee / 12) WHEN c.rate_type = 'flat_fee' THEN c.rate_value / 12 ELSE 0 END AS estimated_royalty, DATEDIFF(c.end_date, CURRENT_DATE()) AS days_to_expiry FROM best_practice_copyright_royalty.dwd_play_facts f LEFT JOIN best_practice_copyright_royalty.doc_license_contracts c ON f.content_id = c.content_id AND f.platform_id = c.platform_id GROUP BY f.content_id, f.platform_id, f.stat_month, f.title, f.content_type, f.rights_holder, c.contract_id, c.rate_type, c.rate_value, c.min_guarantee, c.end_date;

REFRESH DYNAMIC TABLE best_practice_copyright_royalty.dws_royalty_calc;

查看收益最高的 8 条版税记录：

SELECT content_id, platform_id, stat_month, title, total_plays, ROUND(total_revenue, 2) AS total_revenue FROM best_practice_copyright_royalty.dws_royalty_calc ORDER BY total_revenue DESC LIMIT 8;

content_id | platform_id | stat_month | title | total_plays | total_revenue -----------+-------------+------------+--------------------+-------------+-------------- C009 | PLT_A | 2025-07 | The Lost City | 31000 | 46500 C009 | PLT_A | 2025-06 | The Lost City | 28000 | 42000 C009 | PLT_A | 2025-05 | The Lost City | 25000 | 37500 C014 | PLT_A | 2025-02 | Thriller Night | 21000 | 31500 C014 | PLT_A | 2025-05 | Thriller Night | 20000 | 30000 C014 | PLT_A | 2025-03 | Thriller Night | 19500 | 29250 C001 | PLT_A | 2025-05 | The Dragon Legacy | 17200 | 25800 C001 | PLT_A | 2025-03 | The Dragon Legacy | 16800 | 25200

《The Lost City》（C009）在 PLT_A 平台按 28% 

revenue_share
revenue_share
分成，7 月单月收益 46,500 元，版税 13,020 元，是版税最高的单月记录。

ADS 层：结算报表与合同临期告警

ADS 层在 DWS 基础上加入合同状态标签，输出可直接用于对账的结算报表和临期告警。

CREATE DYNAMIC TABLE IF NOT EXISTS best_practice_copyright_royalty.ads_settlement_report AS SELECT content_id, platform_id, title, content_type, rights_holder, stat_month, contract_id, rate_type, total_plays, ROUND(total_revenue, 2) AS total_revenue, ROUND(estimated_royalty, 2) AS estimated_royalty, days_to_expiry, CASE WHEN days_to_expiry < 0 THEN 'expired' WHEN days_to_expiry <= 30 THEN 'expiring_soon' WHEN days_to_expiry <= 90 THEN 'expiring_in_quarter' ELSE 'active' END AS contract_status FROM best_practice_copyright_royalty.dws_royalty_calc WHERE estimated_royalty > 0;

REFRESH DYNAMIC TABLE best_practice_copyright_royalty.ads_settlement_report;

按权利人和月份汇总版税：

SELECT rights_holder, stat_month, ROUND(SUM(estimated_royalty), 2) AS total_royalty, COUNT(DISTINCT content_id) AS content_cnt FROM best_practice_copyright_royalty.ads_settlement_report GROUP BY rights_holder, stat_month ORDER BY total_royalty DESC, rights_holder, stat_month DESC LIMIT 10;

rights_holder | stat_month | total_royalty | content_cnt ----------------+------------+---------------+------------ StarFilms Ltd | 2025-05 | 28906.67 | 3 StarFilms Ltd | 2025-03 | 18039.17 | 2 StarFilms Ltd | 2025-07 | 15520 | 2 StarFilms Ltd | 2025-06 | 14926.67 | 3 StarFilms Ltd | 2025-02 | 14265 | 2 StarFilms Ltd | 2025-04 | 9250 | 1 StarFilms Ltd | 2025-01 | 5625 | 1 BlueSky Media | 2025-05 | 3750 | 2 BlueSky Media | 2025-04 | 3750 | 2 BlueSky Media | 2025-06 | 3016.67 | 2

结果解读：StarFilms Ltd 连续数月版税总额领先，主要由《The Dragon Legacy》《The Lost City》和《Thriller Night》多部高收益影片的 

revenue_share
revenue_share
合同贡献。2025 年 5 月为全年峰值（28,906.67 元），对应多部影片同月播放量高峰叠加。

查看合同临期预警（合同到期后 

days_to_expiry
days_to_expiry
为负数）：

SELECT c.content_id, a.title, a.rights_holder, c.contract_id, c.platform_id, c.end_date, DATEDIFF(c.end_date, CURRENT_DATE()) AS days_remaining FROM best_practice_copyright_royalty.doc_license_contracts c LEFT JOIN best_practice_copyright_royalty.doc_content_assets a ON c.content_id = a.content_id ORDER BY days_remaining ASC LIMIT 8;

content_id | title | rights_holder | contract_id | platform_id | end_date | days_remaining -----------+------------------------+------------------+-------------+-------------+------------+--------------- C002 | Sky Warriors Season 1 | BlueSky Media | CTR003 | PLT_A | 2025-06-30 | -344 C006 | Ocean Odyssey | EarthVision Inc | CTR009 | PLT_B | 2025-06-30 | -344 C011 | Code & Coffee S2 | CastWave Studio | CTR014 | PLT_C | 2025-07-31 | -313 C001 | The Dragon Legacy | StarFilms Ltd | CTR002 | PLT_B | 2025-08-31 | -282 C015 | Innovation Stories S1 | BlueSky Media | CTR019 | PLT_B | 2025-08-31 | -282 C014 | Thriller Night | StarFilms Ltd | CTR017 | PLT_A | 2025-10-31 | -221 C004 | Nature Chronicles | EarthVision Inc | CTR007 | PLT_C | 2025-11-30 | -191 C001 | The Dragon Legacy | StarFilms Ltd | CTR001 | PLT_A | 2025-12-31 | -160

Dynamic Table 调度配置

Dynamic Table DDL 中不写 

REFRESH INTERVAL
REFRESH INTERVAL
，改在 Studio 中创建定时刷新任务，便于在同一任务上附加数据质量检查和告警规则。

在 Studio 的 best_practices/copyright_royalty/ 路径下创建三个刷新任务：

任务名目标 Dynamic Table建议调度周期说明
refresh_dwd_play_facts
refresh_dwd_play_facts
dwd_play_facts
dwd_play_facts
每日 02:00等待平台 T+1 推送完成后刷新
refresh_dws_royalty_calc
refresh_dws_royalty_calc
dws_royalty_calc
dws_royalty_calc
每日 03:00DWD 刷新完成后下游计算版税
refresh_ads_settlement_report
refresh_ads_settlement_report
ads_settlement_report
ads_settlement_report
每日 04:00生成当日最新结算快照

Time Travel：月结快照锁定

月结后平台方可能补报或修正历史播放数据，导致已确认的结算金额发生变动，引发版税争议。通过 Time Travel 固化月结时刻的数据版本，作为对账凭证。

先查看表的历史版本：

DESC HISTORY best_practice_copyright_royalty.doc_platform_plays;

version | time | total_rows | operation | job_id --------+----------------------------+------------+------------+------------------------ 2 | 2026-06-06T23:52:05.794 | 60 | INSERT_INTO| 2026060623520546600024922 1 | 2026-06-06T23:51:10.277 | 0 | CREATE | 2026060623511018800033372

以月结时刻的时间戳查询播放快照（这里以 5 月结算截止为例）：

SELECT SUM(play_count) AS total_plays, ROUND(SUM(revenue), 2) AS total_revenue FROM best_practice_copyright_royalty.doc_platform_plays TIMESTAMP AS OF '2026-06-06 23:53:00' WHERE play_date >= CAST('2025-05-01' AS DATE) AND play_date <= CAST('2025-05-31' AS DATE);

total_plays | total_revenue ------------+-------------- 664500 | 152274

将历史快照数据导出到结算归档表（仅追加，不可更改）：

CREATE TABLE IF NOT EXISTS best_practice_copyright_royalty.doc_settlement_archive ( settlement_id STRING, settle_month STRING, snapshot_ts STRING, content_id STRING, platform_id STRING, total_plays BIGINT, total_revenue DOUBLE, estimated_royalty DOUBLE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP() );

ZettaPark Python Task：复杂版税计算

对于阶梯费率、最低保底叠加超额奖励等复杂业务规则，通过 ZettaPark Python Task 处理：

from clickzetta_zettapark.session import Session def calc_complex_royalty(session): """ 阶梯费率示例： - 月播放量 < 100,000：按 0.003 元/次 - 月播放量 100,000~500,000：0.003 + 超额部分按 0.005 元/次 - 月播放量 > 500,000：0.003 + 超额部分按 0.008 元/次 最低月保底：合同年保底 / 12 """ df = session.sql(""" SELECT content_id, platform_id, stat_month, total_plays, min_guarantee FROM best_practice_copyright_royalty.dws_royalty_calc WHERE rate_type = 'per_play' """).to_pandas() def tiered_rate(plays, min_guarantee_monthly): if plays <= 100000: royalty = plays * 0.003 elif plays <= 500000: royalty = 100000 * 0.003 + (plays - 100000) * 0.005 else: royalty = 100000 * 0.003 + 400000 * 0.005 + (plays - 500000) * 0.008 return max(royalty, min_guarantee_monthly) df['tiered_royalty'] = df.apply( lambda r: tiered_rate(r['total_plays'], r['min_guarantee'] / 12), axis=1 ) # 写回结算表 session.write_pandas(df[['content_id','platform_id','stat_month','tiered_royalty']], 'best_practice_copyright_royalty.doc_tiered_royalty_result', overwrite=True) return df.shape[0]

在 Studio 的 best_practices/copyright_royalty/ 路径下创建 Python Task 

calc_tiered_royalty
calc_tiered_royalty
，挂载上述脚本，在 
refresh_ads_settlement_report
refresh_ads_settlement_report
任务完成后触发执行。

数仓对象总览

SHOW TABLES IN best_practice_copyright_royalty;

schema_name | table_name | is_dynamic ---------------------------------+---------------------------+----------- best_practice_copyright_royalty | doc_content_assets | false best_practice_copyright_royalty | doc_contract_history | false best_practice_copyright_royalty | doc_license_contracts | false best_practice_copyright_royalty | doc_platform_plays | false best_practice_copyright_royalty | doc_settlement_archive | false best_practice_copyright_royalty | dwd_play_facts | true best_practice_copyright_royalty | dws_royalty_calc | true best_practice_copyright_royalty | ads_settlement_report | true

数据链路总览：

OSS Volume（平台报表 CSV/Parquet） │ OSS PIPE ▼ doc_platform_plays（ODS 播放流水） │ ├── MySQL CDC → doc_license_contracts（ODS 合同） │ │ Table Stream (STANDARD) │ │ → MERGE INTO → doc_contract_history（合同变更 SCD） │ └── doc_content_assets（ODS 内容资产） │ ▼ Dynamic Table（每日 02:00 刷新） dwd_play_facts（DWD 播放事实宽表） │ ▼ Dynamic Table（每日 03:00 刷新） dws_royalty_calc（DWS 版税归因） revenue_share / per_play / flat_fee GREATEST(计算值, 月保底) │ ▼ Dynamic Table（每日 04:00 刷新） ads_settlement_report（ADS 结算报表 + 临期告警） │ ▼ Time Travel（TIMESTAMP AS OF） doc_settlement_archive（月结快照归档，不可变）

注意事项

  • 合同表更新后 Dynamic Table 会触发增量刷新：

    dws_royalty_calc
    dws_royalty_calc
    以 
    doc_license_contracts
    doc_license_contracts
    为上游之一，当合同费率或到期日被修改后，下次刷新时受影响的合同-月份组合会重新计算版税。月结已确认后，建议将当月数据导出到 
    doc_settlement_archive
    doc_settlement_archive
    ，防止后续合同变更影响已结算金额。

  • Table Stream 消费位点不可回溯：一旦 MERGE INTO 消费了 Stream，消费位点向前推移，已处理的变更不会再次出现。若 MERGE INTO 执行失败，需通过 

    SHOW TABLE STREAMS
    SHOW TABLE STREAMS
    确认位点，再重新消费。

  • OSS PIPE 只处理新增文件：PIPE 创建后只扫描并摄取新增到 Volume 的文件，不处理已存在文件。历史数据（PIPE 创建前已在 Volume 的文件）需通过 

    COPY INTO
    COPY INTO
    手动补录。

  • Time Travel 时间戳只接受字面量：

    TIMESTAMP AS OF
    TIMESTAMP AS OF
    不支持 
    CURRENT_TIMESTAMP() - INTERVAL
    CURRENT_TIMESTAMP() - INTERVAL
    这类动态表达式。建议将结算截止时间戳持久化到结算工单表，对账时从表中读取后传入查询。

  • days_to_expiry
    days_to_expiry
    基于查询执行时间：ADS 层 
    days_to_expiry
    days_to_expiry
    用 
    DATEDIFF(end_date, CURRENT_DATE())
    DATEDIFF(end_date, CURRENT_DATE())
    计算，每次查询结果不同。若需稳定的临期预警报表，应将刷新时的 
    CURRENT_DATE()
    CURRENT_DATE()
    固化为一个参数字段，或结合调度任务在固定时间生成快照。

相关文档

HR 员工全生命周期数仓
监管报表数仓（BCBS 239 / IFRS 9）
Yunqi © 2024 Yunqi, Inc. All Rights Reserved.
联系我们
预约咨询
微信咨询
电话咨询
邮件咨询