Data cleaning case-2 (ROBCOR)
本页目录· 17 条
这一页有部分内容暂时无法显示，其余内容完整。
Summary
Task Summary
一、先把“故事”讲清楚（我们要做什么）
- 公司有航班记录（日期、机型、距离、耗油、飞行小时）和
这一页有部分内容暂时无法显示，其余内容完整。
同一趟航班的营收/耗油被算了两次（因为一趟航班被两个人“各占一次”）。
小例子：
此处有一段内容暂不支持显示。
能否把 A 和 B 合成一张？
此处有一段内容暂不支持显示。
count(*) 是否合理；ROBCOR, Inc. 提供“按需”包机服务，机队由多种机型组成。公司快速增长后，聘请你为首任数据库管理员。你的首要任务是开发一个用于分析包机数据的决策支持系统。运营经理希望分析：飞行总时长、燃油总量、总营收（航程 × 机型每英里收费），并能按飞行员、机型、时间下钻查看。系统主要需求：
a) 按月/年展示总营收；b) 按飞行员展示总飞行时长；c) 按机型展示燃油总消耗。
数据库已有若干表（可直接从 dw 账号复制或查询），并给出了需要完成的四件事：1）为包机数据建立星型模式；2）定义维度与属性；3）给出实现星型模式的 SQL；4）编写三类报表 SQL。
仅看上面的 SQL 并不能保证结果正确，必须先探索业务库与数仓数据。
E/R 图里有 Pilot_1 实体，但库里找不到对应表；只有 Pilot 表（5 条记录）。实际查 dw.charter，字段里既有 char_pilot 也有 char_copilot，说明两者都指向 Pilot 表；E/R 图画法不准确（设计者可能想表达“同一维度被引用两次”）。
紧接着对 pilot / copilot 做完整性检查：
dw.charter 总记录数；char_trip 字段重复发现有两条不同记录共用同一 char_trip。但由于 char_trip 不参与事实表计算，这类不一致对仓库指标无影响，可记录为脏数据但不阻塞。
当前 charter_fact 仅按 正驾驶（char_pilot） 聚合，因此会漏掉“同一名员工作为 copilot 的飞行时长/燃油/营收”。以员工 101 为例：先看其 charter_fact 记录，再检索 1997-04 月里他作为 pilot 或 copilot 的原始航班，发现确有一次以 copilot 身份飞行——这部分没有入事实。
于是新增第二张事实表，专门汇总 copilot：
现在我们有两个星型模式：Pilot 版与 Co-pilot 版。维度一致，事实内容不同。
最直接的想法是 UNION 两张事实，生成 charter_fact3，再按（time_id,mod_code,emp_num）汇总：
从定义上看，charter_fact3 的确把 pilot 与 copilot 的数据合并并重算了。
但这对吗？答案：不对。以员工 101 的 1997-04 为例，作为 pilot 的 8.2 小时 + 作为 copilot 的 5.6 小时，对“员工维度”而言合计 13.8 小时是对的；然而对“月份总量”或“公司级汇总”而言，这 13.8 会双计——因为同一趟航班的飞行时长同时被 pilot 与 copilot 各算了一次。故 不能简单把两张事实直接并入一张。
若只取 Emp_Num（员工） 一条维度来合并 pilot / copilot，两种写法本质等价：
（两种方法结果相同。）
Total Charter Hours（总飞行时长）在 fact3b 中是合理的，因为“飞行时长”是个人属性（每位员工都有自己的飞行小时数）。Total Fuel 与 Total Revenue在 fact3b 中会误导：同一趟航班既计入 pilot 又计入 copilot，从而双计燃油与营收，应剔除。为什么会这样？因为时长是个人的；而燃油与营收属于航班/公司级，不是“人”的度量。一次航班无论 pilot 是否有 copilot，总营收仍是 $1000，不应“人头拆分后再相加变 $2000”。
因此，若只为“按员工统计总飞行时长”，可以建 charter_fact3c（仅 emp_num + tot_char_hours），但用途有限——直接用两张事实 UNION 也能查询到同样结果。更实用的做法是保留两张事实：一张 pilot、一张 copilot。
--First create the dimensions
create table time As
select Distinct to_char(char_date, 'YYYYMM') as Time_ID,
to_char(char_date, 'Month') as Time_Month,
to_char(char_date, 'YYYY') as Time_Year
from dw.Charter;
create table model as
select * from dw.model;
create table pilot as
select * from dw.pilot;
--Second, create the Charter_fact (the fact table) table
create table charter_fact as
select C.Char_Pilot as EMP_Num,
M.Mod_Code,
to_char(C.Char_Date, 'YYYYMM') as Time_ID,
sum(C.Char_Hours_Flown) as Tot_Char_Hours,
sum(C.Char_Fuel_Gallons) as Tot_Fuel,
sum(C.Char_Distance * M.Mod_chg_mile) as Revenue
from dw.Charter C, dw.Model M, dw.Aircraft A
where C.AC_Number=A.AC_Number and A.Mod_Code=M.Mod_Code
group by C.Char_Pilot, M.Mod_Code, to_char(C.Char_Date, 'YYYYMM');
create table charter_fact2 as
select C.Char_coPilot as EMP_Num,
M.Mod_Code,
to_char(C.Char_Date, 'YYYYMM') as Time_ID,
sum(C.Char_Hours_Flown) as Tot_Char_Hours,
sum(C.Char_Fuel_Gallons) as Tot_Fuel,
sum(C.Char_Distance * M.Mod_chg_mile) as Revenue
from dw.Charter C, dw.Model M, dw.Aircraft A
where C.AC_Number=A.AC_Number and A.Mod_Code=M.Mod_Code
group by C.Char_Pilot, M.Mod_Code, to_char(C.Char_Date, 'YYYYMM');
create table charter_fact3 as
select time_id,
mod_code,
emp_num,
sum(tot_char_hours) as tot_char_hours,
sum(tot_fuel) as tot_fuel,
sum(revenue) as revenue
from (
select * from charter_fact
union
select * from charter_fact2
)
group by time_id, mod_code, emp_num;
-- 法一：先 UNION 再 group by emp_num
create table charter_fact3b as
select emp_num,
sum(tot_char_hours) as tot_char_hours,
sum(tot_fuel) as tot_fuel,
sum(revenue) as revenue
from (
select * from charter_fact
union
select * from charter_fact2
)
group by emp_num
order by emp_num;
-- 法二：各自先 group by emp_num，再 UNION，最后再 group by
create table charter_fact3b as
select emp_num,
sum(tot_char_hours) as tot_char_hours,
sum(tot_fuel) as tot_fuel,
sum(revenue) as revenue
from (
select emp_num, sum(tot_char_hours) as tot_char_hours,
sum(tot_fuel) as tot_fuel, sum(revenue) as revenue
from charter_fact
group by emp_num
union
select emp_num, sum(tot_char_hours) as tot_char_hours,
sum(tot_fuel) as tot_fuel, sum(revenue) as revenue
from charter_fact2
group by emp_num
)
group by emp_num
order by emp_num;
课程目录