【SQL 别裁新解】分析过程中常见的四种数据表类型

XILEJUN

喜乐君 Tableau Visionary ✦ 5

📊 业务数据分析「专家」· 敏捷 BI 布道师

📚 《数据可视化分析》《业务可视化分析》多本书作者

🎓 中国地质大学（武汉）经管学院 MBA 校外导师

🤝 以 Tableau 会友，致力于构建业务分析通识框架

🌐 XILEJUN.com 全球 🌐 VIZWISE.cn 国内

XILEJUN 问题：我最近在做一个航空主题分析项目，我想用来介绍 分析中常见的四种表类型

这是一个非常经典且贴合实际业务的场景！在航空业数据分析中，隐私合规（GDPR/PIPL）与业务洞察（热门航线分析）往往是并存的。

在这个场景中，我们将角色设定为一名航司经营分析师。

场景映射

我们先定义一下四种“表”在这个故事里分别扮演什么角色：

1. Base Table (原始食材): fact_flight_tickets —— 包含每一张机票的详细记录（谁、什么时候、飞哪儿）。
2. View (脱敏半成品): v_safe_frequent_flyers —— IT 部门专门为你准备的常旅客表，把手机号隐藏了，身份证加密了，只留下了会员等级和注册年份。
3. CTE (中间逻辑): passenger_top_dest —— 你为了这次分析，临时计算出来的“每个旅客最爱去的城市”。
4. Subquery (临时过滤): 用于在计算过程中，排除掉那些非商业航班（如调机、包机）或特定无效数据的逻辑。

我将通过航空分析案例，一步步构建出 SQL 的完整形态。

1. 基础层 (Base Tables)：业务的物理载体

这是数据库中真实存储数据的地方（硬盘上的 Heap File）。在我们的场景中，有三张核心的 Base Table：

• A. 客票明细表 (fact_flight_tickets)
  • 业务意义: 这是全量交易流水，每一行代表一张机票。
  • 主要字段:
    • ticket_id: 客票唯一主键
    • passenger_id: 旅客ID
    • flight_date: 航班日期
    • dept_airport: 出发机场三字码 (e.g., PEK)
    • arr_airport: 到达机场三字码
    • ticket_status: 客票状态 (OPEN, CHECKED_IN, FLOWN, REFUNDED)
    • route_type: 航线类型 (DOMESTIC, INTERNATIONAL)
• B. 机场基地字典表 (dim_airport_bases)
  • 业务意义: 公司的静态资源配置表，记录了哪些机场有公司的过夜飞机、机组基地。
  • 主要字段:
    • airport_code: 机场三字码
    • has_overnight_base: 是否有过夜基地 (Boolean / 1,0)
• C. 原始旅客信息表 (raw_passengers)
  • 业务意义: 存储旅客注册时的原始信息，包含高敏感隐私数据。
  • 主要字段: passenger_id, id_card (身份证), phone (电话), member_tier (会员等级)。

2. 封装层 (View)：安全与简化的接口

作为分析师，我们不需要（也不应该）接触旅客的明文身份证和电话。IT 部门为了合规，创建了一个 View。

这就好比给原始数据穿了一层“磨砂玻璃”，既能看到你需要的数据形状（会员等级），又看不清具体的隐私细节。

建表逻辑 (DBA/IT 操作):SQL

-- 创建一个视图，永久保存在数据库字典中
CREATE OR REPLACE VIEW v_safe_passengers AS
SELECT 
    passenger_id,
    member_tier,
    -- 数据脱敏：隐藏手机号中间4位
    CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4)) AS masked_phone,
    -- 数据转换：只提取身份证的出生年份，用于计算年龄段，而不暴露具体生日
    CAST(SUBSTRING(id_card FROM 7 FOR 4) AS INTEGER) AS birth_year
FROM public.raw_passengers;

在后续的查询中，我们不再引用 raw_passengers，而是直接引用 v_safe_passengers。对于分析师来说，它就像一张真的表，但它是逻辑存在的。

3. 逻辑层 (CTE)：复杂逻辑的“临时暂存区”

我们需要计算“旅客的热门目的地”。这不是一个简单的字段，而是一个聚合逻辑：“过去一段时间飞得次数最多的那个城市”。

为什么不用 View?

因为这个逻辑很灵活。今天定义“热门”是按次数，明天可能按里程；今天看全生命周期，明天可能只看近一年。为了这种特定分析场景建一个永久 View 太重了，也不利于维护。

使用 CTE (Common Table Expression)。我们在查询的最开始，先定义好这个计算逻辑。把它当做一次“临时备菜”。

4. 综合查询 (Final SQL)：各类 Table 的大融合

下面这条 SQL 完美融合了 Base Table (获取数据)、View (获取脱敏属性)、CTE (处理复杂排名逻辑) 和 Subquery (作为谓词筛选基地)。

分析目标:找出 2025 年，乘坐过本公司“基地始发”航班的国内旅客，统计他们的“热门目的地”分布。

SQL

/* SQL 结构演示：
   1. WITH 子句定义 CTE (逻辑中间表)
   2. 主查询引用 Base Table 和 View
   3. WHERE 子句使用 Subquery (不记名逻辑表) 进行过滤
*/
-- Step 1: CTE - 计算每个旅客的“首选热门目的地”
WITH passenger_top_dest AS (
    SELECT DISTINCT ON (passenger_id)  -- PG语法：每个旅客只取第一行
        passenger_id,
        arr_airport AS top_destination,
        COUNT(*) as visit_count
    FROM public.fact_flight_tickets
    WHERE ticket_status = 'FLOWN'      -- 只看实际成行的
    GROUP BY passenger_id, arr_airport
    ORDER BY passenger_id, visit_count DESC -- 按次数倒序，第一名即为热门
)
-- Step 2: 主查询
SELECT 
    ptd.top_destination,                -- 来自 CTE
    vsp.member_tier,                    -- 来自 View
    COUNT(DISTINCT t.passenger_id) AS passenger_count,
    SUM(1) AS total_flights
FROM 
    public.fact_flight_tickets t        -- 【Base Table: 事实表】
-- 关联 CTE (逻辑层)
JOIN passenger_top_dest ptd 
    ON t.passenger_id = ptd.passenger_id
-- 关联 View (封装层)
JOIN public.v_safe_passengers vsp       -- 【View: 脱敏视图】
    ON t.passenger_id = vsp.passenger_id
WHERE 
    -- 基础过滤条件
    t.flight_date >= '2025-01-01' AND t.flight_date <= '2025-12-31'
    AND t.route_type = 'DOMESTIC'       -- 只看国内
    AND t.ticket_status = 'FLOWN'
    -- Step 3: Subquery (作为谓词)
    -- 这里的 Subquery 是一个完全匿名的“逻辑表”，用完即焚
    -- 业务含义：只筛选出发地是公司“过夜基地”的航班 (如 HAK, PEK, SHA, TFU)
    AND t.dept_airport IN (
        SELECT airport_code 
        FROM public.dim_airport_bases   -- 【Base Table: 字典表】
        WHERE has_overnight_base = 1
    )
GROUP BY 
    ptd.top_destination,
    vsp.member_tier
ORDER BY 
    passenger_count DESC;

总结：在这个 SQL 中，Table 的四种形态

1. public.fact_flight_tickets (Base Table): 数据的肉体。提供了具体的 2025 年航班记录。
2. public.v_safe_passengers (View): 数据的面具。提供了安全的会员等级信息。
3. passenger_top_dest (CTE): 数据的草稿。在查询头部临时定义了“热门目的地”的计算规则，让主查询更整洁。
4. SELECT airport_code ... (Subquery): 数据的尺子。在 WHERE 子句中临时划定了一个范围（哪些机场是基地），作为筛选标准。