如何通过元数据库统计BI各类订阅明细情况

文章 › 查看内容

27 0 订阅 & 预警 2026-5-20 16:56 发布者: 观小豪

用于从 BI 元数据库中查询卡片订阅、页面订阅、合并订阅、模板消息订阅、数据集订阅、智能洞察订阅的基础信息和对象明细。

说明：以下 SQL 默认基于元数据库 MySQL，查询前请将 SQL 中的 '你的dom_id' 替换为目标客户的域 ID。

一、订阅类型与查询方式

订阅类型	sc_type	主对象来源	明细查询方式
卡片订阅	0	schedule.resource_id -> card.cd_id	直接关联 card 表
页面订阅	1	schedule.resource_id -> page.pg_id	直接关联 page 表
合并订阅	2	schedule.resource_id -> schedule_multicards_mapping	再拆分 card/page/combined_report
数据集订阅	3	schedule.resource_id -> data_source.ds_id	直接关联 data_source 表
模板消息订阅	4	schedule.resource_id -> schedule_multicards_mapping	通常拆分 card/page
智能洞察订阅	5	schedule.resource_id -> schedule_multicards_mapping	通常拆分智能洞察卡片/page

二、基础信息查询

1、基础信息总表

select

sc_id,

name as schedule_name,

sc_type,

msg_type,

trigger_type,

resource_id,

enabled,

u_id,

validstarttime,

validendtime,

last_send_time,

last_send_status

from schedule

where dom_id = '你的dom_id'

and is_del = 0

order by ctime desc;

2、各订阅类型数量校验

select

sc_type,

count(*) as cnt

from schedule

where dom_id = '你的dom_id'

and is_del = 0

group by sc_type

order by sc_type;

三、各类订阅对象明细查询

1、卡片订阅对象明细

select

s.sc_id,

s.name as schedule_name,

s.u_id,

s.msg_type,

s.trigger_type,

s.enabled,

s.validstarttime,

s.validendtime,

s.last_send_time,

s.last_send_status,

c.cd_id as obj_id,

c.name as obj_name

from schedule s

join card c

on s.resource_id = c.cd_id

and s.dom_id = c.dom_id

where s.dom_id = '你的dom_id'

and s.is_del = 0

and s.sc_type = 0;

2、页面订阅对象明细

select

s.sc_id,

s.name as schedule_name,

s.u_id,

s.msg_type,

s.trigger_type,

s.enabled,

s.validstarttime,

s.validendtime,

s.last_send_time,

s.last_send_status,

p.pg_id as obj_id,

p.name as obj_name

from schedule s

join page p

on s.resource_id = p.pg_id

and s.dom_id = p.dom_id

where s.dom_id = '你的dom_id'

and s.is_del = 0

and s.sc_type = 1;

3、合并订阅对象明细

select

s.sc_id,

s.name as schedule_name,

'CARD' as obj_type,

m.cd_id as obj_id,

c.name as obj_name

from schedule s

join schedule_multicards_mapping m

on s.resource_id = m.resource_id

and s.dom_id = m.dom_id

join card c

on m.cd_id = c.cd_id

and m.dom_id = c.dom_id

where s.dom_id = '你的dom_id'

and s.is_del = 0

and s.sc_type = 2

and m.cd_id is not null

union all

select

s.sc_id,

s.name as schedule_name,

'PAGE' as obj_type,

m.pg_id as obj_id,

p.name as obj_name

from schedule s

join schedule_multicards_mapping m

on s.resource_id = m.resource_id

and s.dom_id = m.dom_id

join page p

on m.pg_id = p.pg_id

and m.dom_id = p.dom_id

where s.dom_id = '你的dom_id'

and s.is_del = 0

and s.sc_type = 2

and m.pg_id is not null

union all

select

s.sc_id,

s.name as schedule_name,

'COMBINED_REPORT' as obj_type,

m.cr_id as obj_id,

cr.name as obj_name

from schedule s

join schedule_multicards_mapping m

on s.resource_id = m.resource_id

and s.dom_id = m.dom_id

join combined_report cr

on m.cr_id = cr.rep_id

and m.dom_id = cr.dom_id

where s.dom_id = '你的dom_id'

and s.is_del = 0

and s.sc_type = 2

and m.cr_id is not null;

4、模板消息订阅对象明细

select

s.sc_id,

s.name as schedule_name,

'CARD' as obj_type,

m.cd_id as obj_id,

c.name as obj_name

from schedule s

join schedule_multicards_mapping m

on s.resource_id = m.resource_id

and s.dom_id = m.dom_id

join card c

on m.cd_id = c.cd_id

and m.dom_id = c.dom_id

where s.dom_id = '你的dom_id'

and s.is_del = 0

and s.sc_type = 4

and m.cd_id is not null

union all

select

s.sc_id,

s.name as schedule_name,

'PAGE' as obj_type,

m.pg_id as obj_id,

p.name as obj_name

from schedule s

join schedule_multicards_mapping m

on s.resource_id = m.resource_id

and s.dom_id = m.dom_id

join page p

on m.pg_id = p.pg_id

and m.dom_id = p.dom_id

where s.dom_id = '你的dom_id'

and s.is_del = 0

and s.sc_type = 4

and m.pg_id is not null;

5、数据集订阅对象明细

select

s.sc_id,

s.name as schedule_name,

s.u_id,

s.msg_type,

s.trigger_type,

s.enabled,

s.validstarttime,

s.validendtime,

s.last_send_time,

s.last_send_status,

d.ds_id as obj_id,

d.name as obj_name

from schedule s

join data_source d

on s.resource_id = d.ds_id

and s.dom_id = d.dom_id

where s.dom_id = '你的dom_id'

and s.is_del = 0

and s.sc_type = 3;

6、智能洞察订阅对象明细

elect

s.sc_id,

s.name as schedule_name,

'INTELLIGENT_INSIGHT_CARD' as obj_type,

m.cd_id as obj_id,

c.name as obj_name

from schedule s

join schedule_multicards_mapping m

on s.resource_id = m.resource_id

and s.dom_id = m.dom_id

join card c

on m.cd_id = c.cd_id

and m.dom_id = c.dom_id

where s.dom_id = '你的dom_id'

and s.is_del = 0

and s.sc_type = 5

and m.cd_id is not null

union all

select

s.sc_id,

s.name as schedule_name,

'PAGE' as obj_type,

m.pg_id as obj_id,

p.name as obj_name

from schedule s

join schedule_multicards_mapping m

on s.resource_id = m.resource_id

and s.dom_id = m.dom_id

join page p

on m.pg_id = p.pg_id

and m.dom_id = p.dom_id

where s.dom_id = '你的dom_id'

and s.is_del = 0

and s.sc_type = 5

and m.pg_id is not null;

四、基础字段含义说明

字段名	含义	补充说明
sc_id	订阅唯一 ID	每条订阅计划一条 sc_id，可用于关联发送历史与明细
schedule_name	订阅名称	即界面中的“订阅名称”
sc_type	订阅类型	0卡片、1页面、2合并、3数据集、4模板消息、5智能洞察
msg_type	通知渠道类型	如邮件、钉钉、企业微信、飞书、机器人、自定义渠道等
trigger_type	触发方式	如定时、手动、数据更新后、API/URL 触发、失败重试等
resource_id	订阅主资源 ID	单对象订阅时直接对应资源 ID，多对象订阅时需再查 mapping 表
enabled	是否启用	1/true 表示已启用，0/false 表示未启用
u_id	创建者用户 ID	可关联 user.u_id 获取创建者姓名
validstarttime	有效期开始日期	为空时通常表示不限制开始日期
validendtime	有效期结束日期	为空时通常可理解为长期有效
last_send_time	最近一次发送时间	用于判断最近发送记录
last_send_status	最近一次发送状态	0成功、1失败、2首次发送中、3重试发送中，空值通常表示未发送
obj_type	对象类型	明细查询中的资源类型，如 CARD、PAGE、COMBINED_REPORT
obj_id	对象 ID	对应卡片、页面、组合报表、数据集等资源主键
obj_name	对象名称	对应资源名称，便于导出后直接阅读