Oracle：统计数据仓库被使用表，匹配查询中指定表名

嘿，这个需求我之前帮不少朋友解决过——本质就是从存储的查询语句里精准匹配你的目标表名，然后按Report维度输出对吧？下面给你拆解具体的实现思路和SQL示例，适配不同数据库场景：

我们要完成两个关键动作：

• 关联存储查询语句的业务表（假设叫query_reports，包含report_id、report_name、query_text这类字段）和目标数据仓库表名列表（可以是临时表、CTE或者现成的映射表，比如target_tables，存table_name字段）
• 精准匹配查询文本中的表名，避免误匹配（比如把user和user_detail当成同一个表）

方案1：临时批量匹配（用CTE定义目标表） #

如果你的目标表名是临时整理的列表，用CTE（公共表表达式）来定义会很方便，然后通过正则匹配表名的边界来关联：

WITH target_tables AS (
    -- 替换成你的目标表名列表
    SELECT 'dim_user' AS table_name UNION ALL
    SELECT 'fact_order' AS table_name UNION ALL
    SELECT 'dim_product' AS table_name
)
SELECT 
    q.report_id,
    q.report_name,
    t.table_name AS used_dwh_table,
    q.query_text -- 可选：输出原查询语句用于验证
FROM 
    query_reports q
JOIN 
    target_tables t ON 
    -- 不同数据库的正则语法略有差异，选对应你的即可
    -- PostgreSQL 写法：匹配独立的表名（单词边界）
    q.query_text ~ ('\m' || t.table_name || '\M')
    -- MySQL 写法：
    -- q.query_text REGEXP CONCAT('[[:<:]]', t.table_name, '[[:>:]]')
    -- SQL Server 写法：
    -- PATINDEX('%[^a-zA-Z0-9_]' + t.table_name + '[^a-zA-Z0-9_]%', q.query_text) > 0
ORDER BY 
    q.report_id, t.table_name;

方案2：按Report聚合输出（一行展示所有用到的表） #

如果需要每个Report一行，汇总所有用到的目标表，用字符串聚合函数即可：

WITH target_tables AS (
    SELECT 'dim_user' AS table_name UNION ALL
    SELECT 'fact_order' AS table_name UNION ALL
    SELECT 'dim_product' AS table_name
)
SELECT 
    q.report_id,
    q.report_name,
    -- 不同数据库聚合函数不同
    STRING_AGG(t.table_name, ', ') AS used_dwh_tables -- PostgreSQL
    -- GROUP_CONCAT(t.table_name SEPARATOR ', ') AS used_dwh_tables -- MySQL
    -- STRING_AGG(t.table_name, ', ') AS used_dwh_tables -- SQL Server 2017+
FROM 
    query_reports q
JOIN 
    target_tables t ON 
    q.query_text ~ ('\m' || t.table_name || '\M') -- 替换成对应数据库的正则
GROUP BY 
    q.report_id, q.report_name
ORDER BY 
    q.report_id;

• 大小写敏感问题：如果你的数据库是大小写敏感的，记得统一转换为小写/大写再匹配，比如LOWER(q.query_text) ~ ('\m' || LOWER(t.table_name) || '\M')
• 表别名干扰：如果查询里给表起了别名（比如FROM dim_user u），基础正则可能匹配不到。如果你的团队有查询规范（比如别名是表名缩写），可以调整匹配规则；如果需要完全精准解析SQL，可能需要用Apache Calcite这类专业SQL解析工具，但一般业务场景正则足够覆盖大部分情况
• 性能优化：如果query_reports数据量很大，建议给query_text建全文索引（比如PostgreSQL的tsvector索引、SQL Server的全文索引），或者先过滤掉明显不包含目标表的记录再关联，提升查询速度

内容的提问来源于stack exchange，提问作者Ahmad Al-Khazraji