学习

Data cleaning case-1 (USELOG) · Jimmy Huang's Blog

Data cleaning case-1 (USELOG)

本页目录· 13 条

Summary
1) 业务目标 → 数仓建模（先搭骨架）
2) 先做数据探索与校验（sanity check），别盲信业务库
3) 找到“脏数据”的根因与证据
4) 清洗策略与修复
5) 清洗后再建事实表，并再次比对结果
6) 维度连接要用“键”，别用“名字”
说明
任务
解决方案
对操作库做计数
对数仓表做计数与对比
回答最初的三个查询需求

Summary

这份讲义的核心，就是用一个小型数据仓库（DW）案例把“数据清洗 + 数据核对与验证（sanity check）”的思维打牢。它不只是教你写几句 SQL，而是反复强调：别盲信业务库，先做探索与校验，再清洗，再建模与汇总

Task Summary

1) 业务目标 → 数仓建模（先搭骨架）

目标：按时段/专业/班级统计实验室使用量，并跨学期比较。
做法：建立维度表（学期、时段、专业、班级）和事实表；先从业务库抽取明细（tempfact），再按学期、时段打标签，最后 GROUP BY 聚合为事实表。
边界案例：夜间跨越 18:01–次日 06:00，要用 OR 覆盖跨日时间段。

2) 先做数据探索与校验（sanity check），别盲信业务库

期望值推理：dw.student 与 dw.uselog 是 1-多，因此连接产出的 tempfact 行数应等于 uselog 行数。但实际 170,610 ≠ 108,267，说明不对劲。
结论：问题不在代码，在数据本身。要用计数、抽样查看、排序等方式做数据探索，定位异常。

3) 找到“脏数据”的根因与证据

学生表有大量重复：dw.student 中 14,288 个 student_id 重复，导致连接放大行数。
日志表也不干净：dw.uselog 中有 6 条完全重复记录。

说明

大学计算机实验室主任会记录实验室的使用情况，以使用实验室的学生人数来衡量。这个功能对预算制定尤为重要。主任指派你开发一个小型数据仓库，用于跟踪实验室使用统计。该数据库的主要需求包括：

按不同时间段（如上午、下午、夜间）展示使用人数；
按“时间段 × 专业 × 学生所在年级（班级）”展示使用人数；
对不同专业与学期（例如第 1 学期 vs 第 2 学期）的使用人数进行比较。

请使用已提供的数据库，其中包含以下表：USELOG、STUDENT、MAJOR、CLASS。它们的含义如下：

USELOG：学生进出实验室的访问数据
字段：USELOG (Log_Date, Log_Time, Student_ID, Act)
STUDENT：学生信息
字段：STUDENT (Student_ID, Sex, Full/Part, Type, Class_ID, Major_Code)
MAJOR：专业信息
字段：MAJOR (Major_Name, Major_Code)
CLASS：年级/班级信息
字段：CLASS (Class_Description, Class_ID)

任务

本案例已在本周课程中讨论。你今天的任务是在 Oracle 中实现该数据仓库。已为你提供以下业务（操作型）数据库表，你无需把它们复制到自己的账户中，直接使用即可：

dw.Class：存储班级/年级 ID 与描述；
dw.Major：存储专业代码与描述；
dw.Student：存储学生信息；
dw.Uselog：存储实验室使用记录。

解决方案

第一步：创建各维度表

填充学期与时间段维度

第二步：从 USELOG 与 STUDENT 提取事实明细（临时表）并派生时间段/学期键

注意：在最后一个 update 里使用了 OR，以覆盖 18:01–06:00 的跨夜时间段：

第三步：按（学期 × 时间段 × 班级 × 专业）聚合生成事实表

看起来一切都 OK 吗？先别急。如果你不去观察数据，你永远不知道发生了什么。下面这几个问题，是开展数据探索的良好起点：

1）操作库里有多少条记录？2）数仓里有多少条记录？3）操作库里都是什么数据？4）数仓里的各表长什么样？

对操作库做计数

对数仓表做计数与对比

由 170,610 条明细到 1,363 条事实记录，是因为 GROUP BY 把同一分组（班级、专业、学期、时间段相同）的多行汇总为一行，并计算聚合值（这里是 count(student_id)）。因此，事实表行数减少是预期现象。接着对比操作库与数仓：tempfact_uselog 本质上是 dw.uselog 与 dw.student 以 student_id 连接的结果。按理说，若二者是一对多（一个学生可多次进出），连接后的记录数应恰好等于 dw.uselog 的记录数（108,267）。但我们得到的是 170,610 ——多出了 62,343 条，这意味着临时事实表是不正确的。

为什么会多？ 代码本身看不出错，因为错误在数据里。我们需要做数据探索：先从简单的 SELECT 看起，包含排序或只取关键字段，再盯着 tempfact_uselog 对照检查。很快你会发现同一学生在同一时刻出现两次这样的重复，这当然不可能。继续追查，发现学生表里存在重复学生记录。例如学生 38RVRR377 在 dw.student 中出现两次。

不止一个学生重复，共有 14,288 名学生在 dw.student 中重复。教训：不要盲信操作型数据库——它们可能有误、缺失或不一致。因此，在构建数据仓库时，你必须先做数据清洗。一个直观做法是把操作库表（如 dw.student）复制到本地后去重；另一种做法是在重建临时事实表时直接用 DISTINCT 来去除连接导致的重复。

此时 tempfact_uselog2 只有 108,261 行，但 dw.uselog 是 108,267 行，还差 6 行。继续排查操作库：检查是否存在非法学生/专业/班级记录（结果均为“no rows selected”），再检查 dw.uselog 中是否也有重复日志——确实发现了 6 条完全重复的日志行。由于我们在构造 tempfact_uselog2 时使用了 DISTINCT，这些重复也被消除了，所以 tempfact_uselog2 才是正确的。

在修正后的临时事实上，继续派生键与建事实表

接下来比较 fact_uselog 与 fact_uselog2：两者行数虽然相同，但字段 total_usage 的值不同，原因正是先前学生重复所致。

回答最初的三个查询需求

sql

--first create the dimensions
--create semester dimension
create table semesterDIM
(SemID      varchar2(10),
 Sem_Desc   varchar2(20),
 begin_date date,
 end_date   date);

-- create time dimension (note do not use time as a
-- table name, it is a reserve keyword)
create table labtimeDIM
(TimeID     number,
Time_Desc   varchar2(15),
begin_time  date,
end_time    date);

-- create major and class dimensions
create table majorDIM as
select * from dw.major;

create table classDIM as
select * from dw.class;

sql

-- populate semester dimension
-- (the begin and end date can be changed)

insert into semesterDIM values ('S1', 'Semester1',
to_date('01-JAN', 'DD-MON'), to_date('15-JUL', 'DD-MON'));

insert into semesterDIM values ('S2', 'Semester2',
to_date('16-JUL', 'DD-MON'), to_date('31-DEC', 'DD-MON'));

--populate labtime dimension
insert into labtimeDIM values(1, 'morning', to_date('06:01',
'HH24:MI'), to_date('12:00', 'HH24:MI'));

insert into labtimeDIM values(2, 'afternoon', to_date('12:01',
'HH24:MI'), to_date('18:00', 'HH24:MI'));

insert into labtimeDIM values(3, 'night', to_date('18:01',
'HH24:MI'), to_date('06:00', 'HH24:MI'));

sql

-- secondly, create a temp table to extract from uselog table
create table tempfact_uselog as
select U.log_date , U.log_time,
U.student_ID, S.class_id, S.major_code
from dw.uselog U, dw.student S
where U.student_id = S.student_id;

-- add a column in the tempfact table to store timeid
-- (cannot directly do this in the tempfact table because
-- log_time was of DATE type and timeid is of NUMBER type).

alter table tempfact_uselog
add (timeid number);

update tempfact_uselog
set timeid = 1
where  to_char(log_time, 'HH24:MI') >= '06:01'
and to_char(log_time, 'HH24:MI') <='12:00';

-- 62832 rows updated.

update tempfact_uselog
set timeid = 2
where  to_char(log_time, 'HH24:MI') >= '12:01'
and to_char(log_time, 'HH24:MI') <='18:00';

-- 76113 rows updated.

sql

update tempfact_uselog
set timeid = 3
where to_char(log_time, 'HH24:MI') >= '18:01'
or to_char(log_time, 'HH24:MI') <='06:00';

-- 31665 rows updated.

-- alternatively, you may want to update timeid=3
-- for all other records where the time_id is still empty
-- update tempfact_uselog
-- set timeid = 3
-- where timeid is NULL;

-- add a column in the tempfact_uselog table to store semid
-- (cannot directly do this in the test table because
-- log_date was of DATE type and semid is of VARCHAR type.)

alter table tempfact_uselog
add (semid varchar2(10));

-- populate the new attribute semid by summarizing
-- the date(log_date)

update tempfact_uselog
set semid = 'S1'
where to_char(log_date, 'MMDD') >= '0101'
and to_char(log_date, 'MMDD') <= '0715';

-- 91188 rows updated.

update tempfact_uselog
set semid = 'S2'
where to_char(log_date, 'MMDD') >= '0716'
and to_char(log_date, 'MMDD') <= '1231';

-- 79422 rows updated.

sql

-- Now, create the fact table,
-- make sure to include the TOTAL aggregate.
-- This is an aggregate table of the earlier tempfact table.

create table fact_uselog as
select t.semid, t.timeid, t.class_id,
t.major_code, count(t.student_id) as total_usage
from tempfact_uselog t
group by t.semid, t.timeid, t.class_id, t.major_code;

sql

Select count(*) from dw.student;

  COUNT(*)
----------
     37951

Select count(*) from dw.uselog;

  COUNT(*)
----------
    108267

Select count(*) from dw.major;

  COUNT(*)
----------
       172

Select count(*) from dw.class;

  COUNT(*)
----------
        10

sql

create table tempfact_uselog2 as
select distinct U.log_date , U.log_time,
U.student_ID, S.class_id, S.major_code
from dw.uselog U, dw.student S
where U.student_id = S.student_id;

  COUNT(*)
----------
   108261

sql

alter table tempfact_uselog2
add (timeid number);

update tempfact_uselog2
set timeid = 1
where  to_char(log_time, 'HH24:MI') >= '06:01'
and to_char(log_time, 'HH24:MI') <='12:00';

-- 39921 rows updated.

update tempfact_uselog2
set timeid = 2
where  to_char(log_time, 'HH24:MI') >= '12:01'
and to_char(log_time, 'HH24:MI') <='18:00';

-- 48261 rows updated.

update tempfact_uselog2
set timeid = 3
where to_char(log_time, 'HH24:MI') >= '18:01'
or to_char(log_time, 'HH24:MI') <='06:00';

-- 20079 rows updated.

alter table tempfact_uselog2
add (semid varchar2(10));

update tempfact_uselog2
set semid = 'S1'
where to_char(log_date, 'MMDD') >= '0101'
and to_char(log_date, 'MMDD') <= '0715';

-- 57612 rows updated.

update tempfact_uselog2
set semid = 'S2'
where to_char(log_date, 'MMDD') >= '0716'
and to_char(log_date, 'MMDD') <= '1231';

-- 50649 rows updated.

create table fact_uselog2 as
select t.semid, t.timeid, t.class_id,
t.major_code, count(t.student_id) as total_usage
from tempfact_uselog2 t
group by t.semid, t.timeid, t.class_id, t.major_code;

sql

--query a) answer
select u.timeid, l.time_desc, sum(u.total_usage)
from fact_uselog2 u, labtimeDIM l
where u.timeid = l.timeid
group by u.timeid, l.time_desc;

TIMEID TIME_DESC       SUM(U.TOTAL_USAGE)
---------- --------------- ------------------
         3 night                        20079
         2 afternoon                    48261
         1 morning                      39921

sql

--query b) answer
select u.timeid, u.major_code, u.class_id, sum(u.total_usage)
from fact_uselog2 u
group by u.timeid, u.major_code, u.class_id;

--alternative solution which gives more meaningful results
select t.time_desc, m.major_name, c.class_description, sum(u.total_usage)
from fact_uselog2 u, majorDIM m, classDIM c, labtimeDIM t
where u.major_code = m.major_code
and u.class_id = c.class_id
and u.timeid = t.timeid
group by t.time_desc, m.major_name, c.class_description;

-- 提示（译）：请注意 MajorDIM 中**多个专业有相同描述**，
-- 所以上述“更有意义”的联结方案会把这些专业**合并**，行数从 773 变为 722，因此结果不准确。

Data cleaning case-1 (USELOG)

Summary

1) 业务目标 → 数仓建模（先搭骨架）

2) 先做数据探索与校验（sanity check），别盲信业务库

3) 找到“脏数据”的根因与证据

说明

任务

解决方案

对操作库做计数

对数仓表做计数与对比

回答最初的三个查询需求

Data cleaning case-1 (USELOG)

Summary

1) 业务目标 → 数仓建模（先搭骨架）

2) 先做数据探索与校验（sanity check），别盲信业务库

3) 找到“脏数据”的根因与证据

说明

任务

解决方案

对操作库做计数

对数仓表做计数与对比

回答最初的三个查询需求

5) 清洗后再建事实表，并再次比对结果

6) 维度连接要用“键”，别用“名字”