学习

Lab-03a Uselog · Jimmy Huang's Blog

dw.student 有多少行？
37951
dw.student 长什么样？写 SQL 查看全部列与记录；
sql
```
SELECT * FROM dw.student FETCH FIRST 20 ROWS ONLY;
```
dw.uselog 有多少行？
108267

dw.uselog 长什么样？

sql

SELECT * FROM dw.uselog  FETCH FIRST 20 ROWS ONLY;

dw.major 有多少行？
172

dw.major 长什么样？

sql

SELECT * FROM dw.major   FETCH FIRST 20 ROWS ONLY;

dw.class 有多少行？
10

dw.class 长什么样？

sql

SELECT * FROM dw.class   FETCH FIRST 20 ROWS ONLY;

比较 tempfact_uselog 与 fact_uselog 的行数，为什么不同？

（构建 tempfact_uselog 时，我们使用如下 SQL 将 dw.uselog 与 dw.student 进行连接。）

sql

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;

dw.student 与 dw.uselog 是一对多关系：每个学生在 dw.uselog 中可对应多条记录。tempfact_uselog 的行数是否“看起来不太对”？

sql

-- 行数对比
SELECT COUNT(*) AS cnt_tempfact FROM tempfact_uselog;
-- 170,610
SELECT COUNT(*) AS cnt_fact     FROM fact_uselog;
-- 1363

-- 验证：fact 的行数 = temp 里 (semid,timeid,class_id,major_code) 的去重组合数
SELECT COUNT(*) AS distinct_groups_in_temp
FROM ( SELECT DISTINCT semid, timeid, class_id, major_code
       FROM tempfact_uselog );
-- 1363

-- 额外一致性校验：聚合后的总计是否等于明细行数
SELECT
  (SELECT SUM(total_usage) FROM fact_uselog) AS sum_total_usage_in_fact,
  (SELECT COUNT(*)        FROM tempfact_uselog) AS rows_in_temp
FROM dual;
-- 170610, 170610

结合第11、12问的信息，哪张表包含脏数据？是什么类型的脏数据？
- 在样例对比中，出现“同一 log_date+log_time+student_id 在 tempfact_uselog 出现多行”的现象，而在 dw.uselog 中只有一行 → 提示 dw.student 存在重复的 student_id，导致内连接被放大。
- 由于题10统计显示 tempfact_uselog (170,610) > dw.uselog (108,267)，与上面的观察一致。
- 仍需进一步验证是否还存在：① 孤儿 uselog（在 dw.student 找不到学生）；② 重复日志事件（log_date+log_time+student_id+act 完全重复）。这会影响后续统计的准确性

写出 SQL 来检查第11问中识别的脏数据（可参考 Additional Material – Data Cleaning 的代码）

14-a）检查 dw.student 是否有重复主键

sql

-- 有多少个 student_id 出现了多于 1 次？
SELECT COUNT(*) AS duplicated_student_keys
FROM (
  SELECT student_id
  FROM dw.student
  GROUP BY student_id
  HAVING COUNT(*) > 1
);
-- 14,288

-- 列出重复程度最高的前 10 个 student_id
SELECT student_id, COUNT(*) AS dup_rows
FROM dw.student
GROUP BY student_id
HAVING COUNT(*) > 1
ORDER BY dup_rows DESC
FETCH FIRST 10 ROWS ONLY;

14-b）直接证据：连接后被放大了哪些日志（同一事件在 temp 里多行）

sql

-- 哪些 (log_date, log_time, student_id) 在 join 后变成了多行？
SELECT u.log_date,
       TO_CHAR(u.log_time,'HH24:MI') AS log_time,
       u.student_id,
       COUNT(*) AS copies_after_join
FROM dw.uselog u
JOIN dw.student s
  ON s.student_id = u.student_id
GROUP BY u.log_date, TO_CHAR(u.log_time,'HH24:MI'), u.student_id
HAVING COUNT(*) > 1
ORDER BY copies_after_join DESC
FETCH FIRST 20 ROWS ONLY;

如果这里返回了多行记录（copies_after_join > 1），就“实锤”了student 重复导致的放大。

14-c）检查 孤儿 uselog（找不到对应学生，内连接会丢行）

14-d）检查 重复日志事件（同一事件被记录多次）

我们已在操作型数据库中发现重复数据。还有哪些可能的脏数据类型？例如关系约束问题等。请写 SQL 进一步调查（同样可参考 Additional Material – Data Cleaning）
15-1 关系约束 / 外键有效性（Relationship problems）
15-2 维表自身的主键重复（Duplication problems）
15-3 空值问题（Null value problems）
15-4 值域/一致性检查（Inconsistent / Incorrect values）

sql

CREATE TABLE semesterDIM
( SemID      VARCHAR2(10)   CONSTRAINT pk_semesterDIM PRIMARY KEY,
  Sem_Desc   VARCHAR2(20)   NOT NULL,
  begin_date DATE           NOT NULL,
  end_date   DATE           NOT NULL
);

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'));

COMMIT;

-- 验证
SELECT * FROM semesterDIM;

sql

CREATE TABLE labtimeDIM
( TimeID     NUMBER         CONSTRAINT pk_labtimeDIM PRIMARY KEY,
  Time_Desc  VARCHAR2(15)   NOT NULL,
  begin_time DATE           NOT NULL,
  end_time   DATE           NOT NULL
);

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'));

COMMIT;

-- 验证
SELECT * FROM labtimeDIM ORDER BY TimeID;

sql

CREATE TABLE majorDIM AS SELECT * FROM dw.major;
CREATE TABLE classDIM AS SELECT * FROM dw.class;

-- 补充主键/约束（以教学示例为主键形态，按源表实际字段名为准）
ALTER TABLE majorDIM ADD CONSTRAINT pk_majorDIM PRIMARY KEY (major_code);
ALTER TABLE classDIM  ADD CONSTRAINT pk_classDIM  PRIMARY KEY (class_id);

-- 验证
SELECT COUNT(*) AS majors FROM majorDIM;
SELECT COUNT(*) AS classes FROM classDIM;

sql

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE tempfact_uselog PURGE';
EXCEPTION WHEN OTHERS THEN NULL; END;
/

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
JOIN   dw.student S
  ON   U.student_id = S.student_id;

-- 基础检查
SELECT COUNT(*) AS rows_in_temp FROM tempfact_uselog;
SELECT * FROM tempfact_uselog FETCH FIRST 10 ROWS ONLY;

sql

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';

UPDATE tempfact_uselog
SET timeid = 2
WHERE TO_CHAR(log_time, 'HH24:MI') >= '12:01'
  AND TO_CHAR(log_time, 'HH24:MI') <= '18:00';

-- “night” 跨日，用 OR
UPDATE tempfact_uselog
SET timeid = 3
WHERE TO_CHAR(log_time, 'HH24:MI') >= '18:01'
   OR TO_CHAR(log_time, 'HH24:MI') <= '06:00';

COMMIT;

-- 验证每个时段的分布
SELECT timeid, COUNT(*) AS cnt
FROM tempfact_uselog
GROUP BY timeid
ORDER BY timeid;

sql

ALTER TABLE tempfact_uselog ADD (semid VARCHAR2(10));

UPDATE tempfact_uselog
SET semid = 'S1'
WHERE TO_CHAR(log_date, 'MMDD') >= '0101'
  AND TO_CHAR(log_date, 'MMDD') <= '0715';

UPDATE tempfact_uselog
SET semid = 'S2'
WHERE TO_CHAR(log_date, 'MMDD') >= '0716'
  AND TO_CHAR(log_date, 'MMDD') <= '1231';

COMMIT;

-- 验证每个学期的分布
SELECT semid, COUNT(*) AS cnt
FROM tempfact_uselog
GROUP BY semid
ORDER BY semid;

sql

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE fact_uselog PURGE';
EXCEPTION WHEN OTHERS THEN NULL; END;
/

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;

-- 索引（可选，提升维度过滤性能）
CREATE INDEX ix_fact_sem   ON fact_uselog(semid);
CREATE INDEX ix_fact_time  ON fact_uselog(timeid);
CREATE INDEX ix_fact_class ON fact_uselog(class_id);
CREATE INDEX ix_fact_major ON fact_uselog(major_code);

-- 验证行数与示例输出
SELECT COUNT(*) AS rows_in_fact FROM fact_uselog;

SELECT *
FROM fact_uselog
ORDER BY semid, timeid, class_id, major_code
FETCH FIRST 20 ROWS ONLY;

sql

-- 1/3/5/7：各表行数
SELECT 'dw.student' AS t, COUNT(*) c FROM dw.student
UNION ALL SELECT 'dw.uselog',  COUNT(*) FROM dw.uselog
UNION ALL SELECT 'dw.major',   COUNT(*) FROM dw.major
UNION ALL SELECT 'dw.class',   COUNT(*) FROM dw.class;

-- 2/4/6/8：查看表内容（示例限制前 20 行）
SELECT * FROM dw.student FETCH FIRST 20 ROWS ONLY;
SELECT * FROM dw.uselog  FETCH FIRST 20 ROWS ONLY;
SELECT * FROM dw.major   FETCH FIRST 20 ROWS ONLY;
SELECT * FROM dw.class   FETCH FIRST 20 ROWS ONLY;

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';

UPDATE tempfact_uselog2
   SET timeid = 2
 WHERE TO_CHAR(log_time, 'HH24:MI') >= '12:01'
   AND TO_CHAR(log_time, 'HH24:MI') <= '18:00';

-- night 跨越 18:01 → 次日 06:00，用 OR 覆盖两段
UPDATE tempfact_uselog2
   SET timeid = 3
 WHERE TO_CHAR(log_time, 'HH24:MI') >= '18:01'
    OR TO_CHAR(log_time, 'HH24:MI') <= '06:00';

COMMIT;

sql

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';

UPDATE tempfact_uselog2
   SET semid = 'S2'
 WHERE TO_CHAR(log_date, 'MMDD') >= '0716'
   AND TO_CHAR(log_date, 'MMDD') <= '1231';

COMMIT;

sql

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;

-- fact table before data cleaning process
SELECT COUNT(*) AS rows_in_fact1 FROM fact_uselog;

-- 1363

-- fact table after data cleaning process
SELECT COUNT(*) AS rows_in_fact2 FROM fact_uselog2;

-- 1363

sql

-- 有多少条 uselog 在 student 表里找不到？
SELECT COUNT(*) AS orphan_uselog_rows
FROM dw.uselog u
LEFT JOIN dw.student s
  ON u.student_id = s.student_id
WHERE s.student_id IS NULL;
-- 0

-- 看看是哪些 student_id 导致的（Top 10）
SELECT u.student_id, COUNT(*) AS cnt
FROM dw.uselog u
LEFT JOIN dw.student s
  ON u.student_id = s.student_id
WHERE s.student_id IS NULL
GROUP BY u.student_id
ORDER BY cnt DESC
FETCH FIRST 10 ROWS ONLY;
-- None

sql

WITH g AS (
  SELECT log_date,
         TO_CHAR(log_time,'HH24:MI') AS log_time,
         student_id,
         act,
         COUNT(*) AS c
  FROM dw.uselog
  GROUP BY log_date, TO_CHAR(log_time,'HH24:MI'), student_id, act
)
SELECT COUNT(*)        AS duplicate_event_groups,   -- 有多少个“重复的事件组”
       SUM(c)          AS rows_in_groups,           -- 这些组里总共有多少行
       SUM(c - 1)      AS extra_rows_due_to_dupes   -- 多出来的重复行数量
FROM g
WHERE c > 1;

sql

-- 例子：列出重复程度最高的 20 个事件
WITH g AS (
  SELECT log_date, TO_CHAR(log_time,'HH24:MI') AS log_time, student_id, act, COUNT(*) AS c
  FROM dw.uselog
  GROUP BY log_date, TO_CHAR(log_time,'HH24:MI'), student_id, act
)
SELECT *
FROM g
WHERE c > 1
ORDER BY c DESC
FETCH FIRST 20 ROWS ONLY;

sql

-- student → major 外键有效性
SELECT COUNT(*) AS bad_major_fk
FROM dw.student s
LEFT JOIN dw.major m
  ON s.major_code = m.major_code
WHERE m.major_code IS NULL;

-- bad_major_fk = 122

SELECT s.major_code, COUNT(*) AS cnt
FROM dw.student s
LEFT JOIN dw.major m
  ON s.major_code = m.major_code
WHERE m.major_code IS NULL
GROUP BY s.major_code
ORDER BY cnt DESC
FETCH FIRST 20 ROWS ONLY;

-- student → class 外键有效性
SELECT COUNT(*) AS bad_class_fk
FROM dw.student s
LEFT JOIN dw.class c
  ON s.class_id = c.class_id
WHERE c.class_id IS NULL;

-- bad_class_fk = 0

SELECT s.class_id, COUNT(*) AS cnt
FROM dw.student s
LEFT JOIN dw.class c
  ON s.class_id = c.class_id
WHERE c.class_id IS NULL
GROUP BY s.class_id
ORDER BY cnt DESC
FETCH FIRST 20 ROWS ONLY;

sql

-- major 主键是否重复
SELECT COUNT(*) AS duplicated_major_keys
FROM (
  SELECT major_code
  FROM dw.major
  GROUP BY major_code
  HAVING COUNT(*) > 1
);

-- duplicated_major_keys = 0

-- class 主键是否重复
SELECT COUNT(*) AS duplicated_class_keys
FROM (
  SELECT class_id
  FROM dw.class
  GROUP BY class_id
  HAVING COUNT(*) > 1
);

-- duplicated_class_keys = 0

sql

-- materials 里的示例：检查维表关键属性为空
SELECT COUNT(*) AS null_major_name FROM dw.major WHERE major_name IS NULL;

-- null_major_name = 0

SELECT COUNT(*) AS null_class_desc FROM dw.class WHERE class_description IS NULL;

-- null_class_desc = 0

-- 学生关键列为空（会影响关联/聚合）
SELECT
  SUM(CASE WHEN student_id  IS NULL THEN 1 ELSE 0 END) AS null_student_id,
  SUM(CASE WHEN major_code  IS NULL THEN 1 ELSE 0 END) AS null_major_code,
  SUM(CASE WHEN class_id    IS NULL THEN 1 ELSE 0 END) AS null_class_id
FROM dw.student;

-- NULL_CLASS_DESC = 0

-- 日志关键列为空
SELECT
  SUM(CASE WHEN log_date   IS NULL THEN 1 ELSE 0 END) AS null_log_date,
  SUM(CASE WHEN log_time   IS NULL THEN 1 ELSE 0 END) AS null_log_time,
  SUM(CASE WHEN student_id IS NULL THEN 1 ELSE 0 END) AS null_student_id,
  SUM(CASE WHEN act        IS NULL THEN 1 ELSE 0 END) AS null_act
FROM dw.uselog;

--  null_log_date, null_log_time, null_student_id, null_act = 0

sql

-- act 的取值（是否只有允许的若干值？）
SELECT act, COUNT(*) AS cnt
FROM dw.uselog
GROUP BY act
ORDER BY cnt DESC;

-- 如果需要：标出“非法 act”值（假设合法值只有 'P','E'，按你们实际为准）
SELECT COUNT(*) AS bad_act
FROM dw.uselog
WHERE act NOT IN ('P','E');  -- 如无官方字典，可先仅查看 distinct

-- 时间范围是否合理（理论上 00:00~23:59）
SELECT MIN(TO_CHAR(log_time,'HH24:MI')) AS min_time,
       MAX(TO_CHAR(log_time,'HH24:MI')) AS max_time
FROM dw.uselog;

学习

学习

学习

Lab-03a Uselog

本页目录

学习

Lab-03a Uselog

本页目录

说明（Description）

Part A：实现星型模式（Implement Star Schema）

Step 1. 创建维度表（Dimensions）

1.1 学期维度 `semesterDIM`

1.2 时段维度 `labtimeDIM`

1.3 专业与班级维度（从源表复制）

Step 2. 搭建“临时事实表”并补派生键

2.1 从业务表抽取到 `tempfact_uselog`

2.2 派生 `TimeID`（早/午/晚）

2.3 派生 `SemID`（S1/S2）

Step 3. 生成事实表 `fact_uselog`

Part B：数据探索（Data Exploration）

Step B1：摸清四张业务表的“量与貌”（对应题 1–8）

Step B2：对比中间层与源日志（对应题 9–11）

Part C：数据清洗 & 重新实现星型模式

1) 用 `DISTINCT` 重建临时事实表 `tempfact_uselog2`

2) 给 `timeid` 与 `semid` 赋值，并统计每类数量（Q18–Q22）

3) 用清洗后的临时表生成新事实表 `fact_uselog2` 并对比（Q23）

完成 Assignment 2 的提示（Tips）

Lab-03a Uselog

Lab-03a Uselog

说明（Description）

Part A：实现星型模式（Implement Star Schema）

Step 1. 创建维度表（Dimensions）

1.1 学期维度 semesterDIM

1.2 时段维度 labtimeDIM

1.3 专业与班级维度（从源表复制）

Step 2. 搭建“临时事实表”并补派生键

2.1 从业务表抽取到 tempfact_uselog

2.2 派生 TimeID（早/午/晚）

2.3 派生 SemID（S1/S2）

Step 3. 生成事实表 fact_uselog

Part B：数据探索（Data Exploration）

Step B1：摸清四张业务表的“量与貌”（对应题 1–8）

Step B2：对比中间层与源日志（对应题 9–11）

Part C：数据清洗 & 重新实现星型模式

1) 用 DISTINCT 重建临时事实表 tempfact_uselog2

2) 给 timeid 与 semid 赋值，并统计每类数量（Q18–Q22）

3) 用清洗后的临时表生成新事实表 fact_uselog2 并对比（Q23）

完成 Assignment 2 的提示（Tips）

1.1 学期维度 `semesterDIM`

1.2 时段维度 `labtimeDIM`

2.1 从业务表抽取到 `tempfact_uselog`

2.2 派生 `TimeID`（早/午/晚）

2.3 派生 `SemID`（S1/S2）

Step 3. 生成事实表 `fact_uselog`

1) 用 `DISTINCT` 重建临时事实表 `tempfact_uselog2`

2) 给 `timeid` 与 `semid` 赋值，并统计每类数量（Q18–Q22）

3) 用清洗后的临时表生成新事实表 `fact_uselog2` 并对比（Q23）