清理维度表：实现筛选仅显示事实表匹配值的方法

一、最佳建模/清理步骤 #

• 构建精简维度表（推荐）：在数据仓库ETL流程中，创建一个仅包含事实表关联记录的精简维度表。通过定期执行关联同步任务，用事实表的外键匹配维度表，保留有对应事实数据的维度行。前端筛选直接基于该表，既能保证筛选准确性，也能提升查询性能。
• BI工具内置配置：如果使用Tableau、Power BI等工具，在设置筛选器时启用「仅显示与当前数据相关的值」选项（不同工具命名略有差异），工具会自动关联事实表数据，过滤掉无对应事实的维度选项，无需修改底层数据结构。
• 维度表标记字段：在原维度表中新增is_associated_with_fact布尔字段，通过ETL任务定期校验该维度是否在事实表中有关联记录，标记为TRUE或FALSE。前端筛选时仅选择标记为TRUE的选项，既保留原维度表的完整性，又实现筛选控制。

二、SQL实现方案 #

1. 创建实时过滤的维度视图 #

无需修改物理表，创建动态关联事实表的视图，作为筛选器数据源：

CREATE VIEW dim_filtered AS
SELECT d.*
FROM dim_table d
INNER JOIN fact_table f
ON d.dim_id = f.dim_id
GROUP BY d.dim_id, d.dim_name, d.dim_attribute1, d.dim_attribute2; -- 列出维度表所有字段

2. 给维度表添加关联标记字段 #

通过字段标记实现持久化过滤：

-- 新增标记字段
ALTER TABLE dim_table ADD COLUMN is_in_fact BOOLEAN DEFAULT FALSE;

-- 更新标记值
UPDATE dim_table d
SET is_in_fact = EXISTS (
    SELECT 1 FROM fact_table f WHERE f.dim_id = d.dim_id
);

后续筛选时只需添加条件WHERE is_in_fact = TRUE即可。

3. 直接查询筛选可用维度值 #

如果需要临时获取筛选选项，可执行以下查询：

SELECT DISTINCT d.dim_id, d.dim_name
FROM dim_table d
JOIN fact_table f ON d.dim_id = f.dim_id
ORDER BY d.dim_name;

内容的提问来源于stack exchange，提问作者Data_Analyst