🎬 B 站课程：DAX「别裁新解」
https://www.bilibili.com/cheese/play/ss8780
XILEJUN 问题：我最近在做一个航空主题分析项目，我想用来介绍 分析中常见的四种表类型
第一，为了保护常旅客中的关键字段隐私，IT 预先创建了一个 view，其中不包含旅客身份证、电话等真实信息，而是做了特殊的转换处理；
第二，为了完成 “不同年度、热门目的地 的 旅客数量变化”，需要先计算每个旅客的目的地次数，把排名第一的目的地列为旅客的 “热门目的地”，而后以“热门目的地” 和 航班日期 为分类维度，统计旅客变化。
这是一个非常经典且贴合实际业务的场景！在航空业数据分析中，隐私合规（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：
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;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 的四种形态
🎬 B 站课程：DAX「别裁新解」 https://www.bilibili.com/cheese/play/ss8780
4. SELECT airport_code ... (Subquery): 数据的尺子。在 WHERE 子句中临时划定了一个范围（哪些机场是基地），作为筛选标准。
3. passenger_top_dest (CTE): 数据的草稿。在查询头部临时定义了“热门目的地”的计算规则，让主查询更整洁。
2. public.v_safe_passengers (View): 数据的面具。提供了安全的会员等级信息。
1. public.fact_flight_tickets (Base Table): 数据的肉体。提供了具体的 2025 年航班记录。
No comments yet