（数据仓库分层设计练习）根据存款账户明细表和借据表两张基础层表，设计中间汇总层模型（DWS/指标层），用以支撑存贷款明细报表的报送

Irene1991

403人浏览 · 2026-05-23 13:12:32

Irene1991 · 2026-05-23 13:12:32 发布

本文设计了一个存贷款明细报表的数据架构方案，采用三层架构设计：DWD层存储原始交易数据，DIM层通过期限映射表将原始期限标准化为7个区间，DWS层按期限区间进行汇总。

方案包含完整的建表语句、测试数据初始化、ETL加工流程和最终报表查询SQL，实现了从明细数据到汇总报表的转换。

文中详细记录了开发过程中遇到的典型问题（如表不存在、数据插入失败、结果不完整等）及其解决方法，并总结了"分层验证"的调试方法论：先验证明细数据，再检查映射关联，最后确认汇总结果。

该方案既保证了数据规范性，又能满足报表报送需求。

数据仓库分层设计练习

一、需求

根据 存款账户明细表 和 借据表 两张基础层表，设计中间汇总层模型（DWS/指标层），用以支撑存贷款明细报表的报送。

报表输出格式

项目	各项存款	各项贷款（余额）
三个月以内	xxx	xxx
三个月至六个月	xxx	xxx
六个月至一年	xxx	xxx
一年至两年	xxx	xxx
两年至三年	xxx	xxx
三年至五年	xxx	xxx
五年以上	xxx	xxx

二、数据架构设计

2.1 整体分层架构

text

┌─────────────────┐     ┌──────────────────────────────────────┐     ┌─────────────────┐
│   DWD层（明细层） │     │            DWS层（汇总层）             │     │   ADS层（应用层） │
├─────────────────┤     ├──────────────────────────────────────┤     ├─────────────────┤
│                 │     │                                      │     │                 │
│ DEPOSIT_ACCOUNT │────→│     DWS_DEPOSIT_TERM_SUM            │     │                 │
│    _DETAIL      │     │   存款按期限区间汇总表                 │     │   存贷款明细报表  │
│                 │     │                                      │────→│   （最终输出）    │
├─────────────────┤     ├──────────────────────────────────────┤     │                 │
│                 │     │                                      │     │                 │
│   LOAN_DETAIL   │────→│     DWS_LOAN_TERM_SUM               │     │                 │
│    借据表        │     │   贷款按期限区间汇总表                 │     │                 │
│                 │     │                                      │     │                 │
└─────────────────┘     └──────────────────────────────────────┘     └─────────────────┘

                              ↓
                    ┌─────────────────────┐
                    │   DIM_TERM_MAPPING   │
                    │    期限区间映射表      │
                    │  （将原始期限标准化）   │
                    └─────────────────────┘

2.2 设计说明

设计要点	说明
DWD层	保留明细数据，存储存款和贷款的原始交易记录
DIM层	期限映射表，将原始期限枚举值（如 `1Y`、`3Y`、`活期`）标准化到7个报表区间
DWS层	按期限区间进行轻度汇总，提升报表查询性能
ADS层	最终报表查询，合并存款和贷款数据

2.3 期限区间映射规则

区间编号	标准区间名称	存款期限枚举	贷款期限枚举
3	三个月以内	活期、3M	3M
4	三个月至六个月	6M	6M
5	六个月至一年	9M、1Y	1Y
6	一年至两年	2Y	2Y
7	两年至三年	3Y	3Y
8	三年至五年	5Y	5Y
9	五年以上	5Y+	5Y+

三、建表语句

3.1 存款明细表（DWD层）

sql

-- 存款账户明细表
CREATE TABLE DEPOSIT_ACCOUNT_DETAIL (
    ACCOUNT_NO      VARCHAR2(30)  NOT NULL,
    CUST_NO         VARCHAR2(20)  NOT NULL,
    ACCOUNT_BALANCE NUMBER(18,2)  NOT NULL,
    DEPOSIT_TERM    VARCHAR2(20)  NOT NULL,
    CREATE_TIME     DATE          DEFAULT SYSDATE
);

ALTER TABLE DEPOSIT_ACCOUNT_DETAIL ADD CONSTRAINT PK_DEPOSIT_ACCOUNT PRIMARY KEY (ACCOUNT_NO);

COMMENT ON TABLE DEPOSIT_ACCOUNT_DETAIL IS '存款账户明细表';
COMMENT ON COLUMN DEPOSIT_ACCOUNT_DETAIL.ACCOUNT_NO IS '存款账号';
COMMENT ON COLUMN DEPOSIT_ACCOUNT_DETAIL.CUST_NO IS '客户号';
COMMENT ON COLUMN DEPOSIT_ACCOUNT_DETAIL.ACCOUNT_BALANCE IS '账户余额';
COMMENT ON COLUMN DEPOSIT_ACCOUNT_DETAIL.DEPOSIT_TERM IS '存款期限';

3.2 借据表（DWD层）

sql

-- 借据明细表
CREATE TABLE LOAN_DETAIL (
    LOAN_NO         VARCHAR2(20)  NOT NULL,
    CUST_NO         VARCHAR2(20)  NOT NULL,
    LOAN_BALANCE    NUMBER(15,2)  NOT NULL,
    LOAN_AMOUNT     NUMBER(15,2)  NOT NULL,
    LOAN_TERM       VARCHAR2(20)  NOT NULL,
    CREATE_TIME     DATE          DEFAULT SYSDATE
);

ALTER TABLE LOAN_DETAIL ADD CONSTRAINT PK_LOAN_DETAIL PRIMARY KEY (LOAN_NO);

COMMENT ON TABLE LOAN_DETAIL IS '借据明细表';
COMMENT ON COLUMN LOAN_DETAIL.LOAN_NO IS '借据号';
COMMENT ON COLUMN LOAN_DETAIL.CUST_NO IS '客户号';
COMMENT ON COLUMN LOAN_DETAIL.LOAN_BALANCE IS '贷款余额';
COMMENT ON COLUMN LOAN_DETAIL.LOAN_AMOUNT IS '贷款金额';
COMMENT ON COLUMN LOAN_DETAIL.LOAN_TERM IS '贷款期限';

3.3 期限映射表（DIM层）

sql

-- 期限区间映射表
CREATE TABLE DIM_TERM_MAPPING (
    SOURCE_TERM     VARCHAR2(20)  NOT NULL,
    TERM_TYPE       VARCHAR2(10)  NOT NULL,
    TERM_INTERVAL   VARCHAR2(30)  NOT NULL,
    INTERVAL_ORDER  NUMBER(2)     NOT NULL,
    CREATE_TIME     DATE          DEFAULT SYSDATE
);

ALTER TABLE DIM_TERM_MAPPING ADD CONSTRAINT PK_TERM_MAPPING PRIMARY KEY (SOURCE_TERM, TERM_TYPE);

3.4 存款汇总表（DWS层）

sql

-- DWS层：存款按期限区间汇总表
CREATE TABLE DWS_DEPOSIT_TERM_SUM (
    STAT_DATE       DATE          NOT NULL,
    TERM_INTERVAL   VARCHAR2(30)  NOT NULL,
    INTERVAL_ORDER  NUMBER(2)     NOT NULL,
    TOTAL_BALANCE   NUMBER(20,2)  NOT NULL,
    ACCOUNT_COUNT   NUMBER(10)    NOT NULL,
    UPDATE_TIME     DATE          DEFAULT SYSDATE
);

ALTER TABLE DWS_DEPOSIT_TERM_SUM ADD CONSTRAINT PK_DWS_DEPOSIT_SUM PRIMARY KEY (STAT_DATE, TERM_INTERVAL);

3.5 贷款汇总表（DWS层）

sql

-- DWS层：贷款按期限区间汇总表
CREATE TABLE DWS_LOAN_TERM_SUM (
    STAT_DATE       DATE          NOT NULL,
    TERM_INTERVAL   VARCHAR2(30)  NOT NULL,
    INTERVAL_ORDER  NUMBER(2)     NOT NULL,
    TOTAL_BALANCE   NUMBER(20,2)  NOT NULL,
    LOAN_COUNT      NUMBER(10)    NOT NULL,
    UPDATE_TIME     DATE          DEFAULT SYSDATE
);

ALTER TABLE DWS_LOAN_TERM_SUM ADD CONSTRAINT PK_DWS_LOAN_SUM PRIMARY KEY (STAT_DATE, TERM_INTERVAL);

四、映射表数据初始化

sql

-- 存款期限映射
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('活期', 'DEPOSIT', '三个月以内', 3);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('3M', 'DEPOSIT', '三个月以内', 3);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('6M', 'DEPOSIT', '三个月至六个月', 4);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('9M', 'DEPOSIT', '六个月至一年', 5);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('1Y', 'DEPOSIT', '六个月至一年', 5);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('2Y', 'DEPOSIT', '一年至两年', 6);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('3Y', 'DEPOSIT', '两年至三年', 7);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('5Y', 'DEPOSIT', '三年至五年', 8);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('5Y+', 'DEPOSIT', '五年以上', 9);

-- 贷款期限映射
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('3M', 'LOAN', '三个月以内', 3);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('6M', 'LOAN', '三个月至六个月', 4);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('1Y', 'LOAN', '六个月至一年', 5);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('2Y', 'LOAN', '一年至两年', 6);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('3Y', 'LOAN', '两年至三年', 7);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('5Y', 'LOAN', '三年至五年', 8);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('5Y+', 'LOAN', '五年以上', 9);

COMMIT;

五、测试数据

5.1 存款测试数据

sql

INSERT INTO DEPOSIT_ACCOUNT_DETAIL (ACCOUNT_NO, CUST_NO, ACCOUNT_BALANCE, DEPOSIT_TERM) VALUES ('ACC001', 'CUST01', 50000.00, '活期');
INSERT INTO DEPOSIT_ACCOUNT_DETAIL (ACCOUNT_NO, CUST_NO, ACCOUNT_BALANCE, DEPOSIT_TERM) VALUES ('ACC002', 'CUST02', 30000.00, '3M');
INSERT INTO DEPOSIT_ACCOUNT_DETAIL (ACCOUNT_NO, CUST_NO, ACCOUNT_BALANCE, DEPOSIT_TERM) VALUES ('ACC003', 'CUST03', 80000.00, '6M');
INSERT INTO DEPOSIT_ACCOUNT_DETAIL (ACCOUNT_NO, CUST_NO, ACCOUNT_BALANCE, DEPOSIT_TERM) VALUES ('ACC004', 'CUST04', 120000.00, '1Y');
INSERT INTO DEPOSIT_ACCOUNT_DETAIL (ACCOUNT_NO, CUST_NO, ACCOUNT_BALANCE, DEPOSIT_TERM) VALUES ('ACC005', 'CUST01', 60000.00, '9M');
INSERT INTO DEPOSIT_ACCOUNT_DETAIL (ACCOUNT_NO, CUST_NO, ACCOUNT_BALANCE, DEPOSIT_TERM) VALUES ('ACC006', 'CUST05', 200000.00, '2Y');
INSERT INTO DEPOSIT_ACCOUNT_DETAIL (ACCOUNT_NO, CUST_NO, ACCOUNT_BALANCE, DEPOSIT_TERM) VALUES ('ACC007', 'CUST06', 150000.00, '3Y');
INSERT INTO DEPOSIT_ACCOUNT_DETAIL (ACCOUNT_NO, CUST_NO, ACCOUNT_BALANCE, DEPOSIT_TERM) VALUES ('ACC008', 'CUST07', 300000.00, '5Y');
INSERT INTO DEPOSIT_ACCOUNT_DETAIL (ACCOUNT_NO, CUST_NO, ACCOUNT_BALANCE, DEPOSIT_TERM) VALUES ('ACC009', 'CUST08', 500000.00, '5Y+');
COMMIT;

5.2 贷款测试数据

sql

INSERT INTO LOAN_DETAIL (LOAN_NO, CUST_NO, LOAN_BALANCE, LOAN_AMOUNT, LOAN_TERM) VALUES ('LOAN001', 'CUST01', 30000.00, 50000.00, '3M');
INSERT INTO LOAN_DETAIL (LOAN_NO, CUST_NO, LOAN_BALANCE, LOAN_AMOUNT, LOAN_TERM) VALUES ('LOAN002', 'CUST02', 60000.00, 100000.00, '6M');
INSERT INTO LOAN_DETAIL (LOAN_NO, CUST_NO, LOAN_BALANCE, LOAN_AMOUNT, LOAN_TERM) VALUES ('LOAN003', 'CUST03', 150000.00, 200000.00, '1Y');
INSERT INTO LOAN_DETAIL (LOAN_NO, CUST_NO, LOAN_BALANCE, LOAN_AMOUNT, LOAN_TERM) VALUES ('LOAN004', 'CUST04', 250000.00, 300000.00, '2Y');
INSERT INTO LOAN_DETAIL (LOAN_NO, CUST_NO, LOAN_BALANCE, LOAN_AMOUNT, LOAN_TERM) VALUES ('LOAN005', 'CUST05', 350000.00, 500000.00, '3Y');
INSERT INTO LOAN_DETAIL (LOAN_NO, CUST_NO, LOAN_BALANCE, LOAN_AMOUNT, LOAN_TERM) VALUES ('LOAN006', 'CUST01', 180000.00, 200000.00, '3Y');
INSERT INTO LOAN_DETAIL (LOAN_NO, CUST_NO, LOAN_BALANCE, LOAN_AMOUNT, LOAN_TERM) VALUES ('LOAN007', 'CUST06', 450000.00, 600000.00, '5Y');
INSERT INTO LOAN_DETAIL (LOAN_NO, CUST_NO, LOAN_BALANCE, LOAN_AMOUNT, LOAN_TERM) VALUES ('LOAN008', 'CUST07', 800000.00, 1000000.00, '5Y+');
COMMIT;

六、ETL加工语句

6.1 存款汇总（DWD → DWS）

sql

TRUNCATE TABLE DWS_DEPOSIT_TERM_SUM;

INSERT INTO DWS_DEPOSIT_TERM_SUM (STAT_DATE, TERM_INTERVAL, INTERVAL_ORDER, TOTAL_BALANCE, ACCOUNT_COUNT, UPDATE_TIME)
SELECT 
    TRUNC(SYSDATE) AS STAT_DATE,
    M.TERM_INTERVAL,
    M.INTERVAL_ORDER,
    SUM(D.ACCOUNT_BALANCE) AS TOTAL_BALANCE,
    COUNT(DISTINCT D.ACCOUNT_NO) AS ACCOUNT_COUNT,
    SYSDATE AS UPDATE_TIME
FROM DEPOSIT_ACCOUNT_DETAIL D
INNER JOIN DIM_TERM_MAPPING M
    ON D.DEPOSIT_TERM = M.SOURCE_TERM
    AND M.TERM_TYPE = 'DEPOSIT'
GROUP BY M.TERM_INTERVAL, M.INTERVAL_ORDER;

6.2 贷款汇总（DWD → DWS）

sql

TRUNCATE TABLE DWS_LOAN_TERM_SUM;

INSERT INTO DWS_LOAN_TERM_SUM (STAT_DATE, TERM_INTERVAL, INTERVAL_ORDER, TOTAL_BALANCE, LOAN_COUNT, UPDATE_TIME)
SELECT 
    TRUNC(SYSDATE) AS STAT_DATE,
    M.TERM_INTERVAL,
    M.INTERVAL_ORDER,
    SUM(L.LOAN_BALANCE) AS TOTAL_BALANCE,
    COUNT(DISTINCT L.LOAN_NO) AS LOAN_COUNT,
    SYSDATE AS UPDATE_TIME
FROM LOAN_DETAIL L
INNER JOIN DIM_TERM_MAPPING M
    ON L.LOAN_TERM = M.SOURCE_TERM
    AND M.TERM_TYPE = 'LOAN'
GROUP BY M.TERM_INTERVAL, M.INTERVAL_ORDER;

COMMIT;

七、最终报表查询

sql

SELECT 
    COALESCE(D.TERM_INTERVAL, L.TERM_INTERVAL) AS "项目",
    NVL(D.TOTAL_BALANCE, 0) AS "各项存款",
    NVL(L.TOTAL_BALANCE, 0) AS "各项贷款（余额）",
    COALESCE(D.INTERVAL_ORDER, L.INTERVAL_ORDER) AS "排序"
FROM 
    DWS_DEPOSIT_TERM_SUM D
FULL OUTER JOIN 
    DWS_LOAN_TERM_SUM L
    ON D.TERM_INTERVAL = L.TERM_INTERVAL
ORDER BY "排序";

八、执行结果

8.1 DWS存款汇总表

STAT_DATE	TERM_INTERVAL	INTERVAL_ORDER	TOTAL_BALANCE	ACCOUNT_COUNT
2026-05-23	三个月以内	3	80,000	2
2026-05-23	三个月至六个月	4	80,000	1
2026-05-23	六个月至一年	5	180,000	2
2026-05-23	一年至两年	6	200,000	1
2026-05-23	两年至三年	7	150,000	1
2026-05-23	三年至五年	8	300,000	1
2026-05-23	五年以上	9	500,000	1

8.2 DWS贷款汇总表

STAT_DATE	TERM_INTERVAL	INTERVAL_ORDER	TOTAL_BALANCE	LOAN_COUNT
2026-05-23	三个月以内	3	30,000	1
2026-05-23	三个月至六个月	4	60,000	1
2026-05-23	六个月至一年	5	150,000	1
2026-05-23	一年至两年	6	250,000	1
2026-05-23	两年至三年	7	530,000	2
2026-05-23	三年至五年	8	450,000	1
2026-05-23	五年以上	9	800,000	1

8.3 最终报表输出

项目	各项存款	各项贷款（余额）	排序
三个月以内	80,000	30,000	3
三个月至六个月	80,000	60,000	4
六个月至一年	180,000	150,000	5
一年至两年	200,000	250,000	6
两年至三年	150,000	530,000	7
三年至五年	300,000	450,000	8
五年以上	500,000	800,000	9

九、附录：简化版解法

作为对比，以下是更简洁的设计方案，跳过映射表直接按原始期限聚合：

sql

-- 创建汇总表
CREATE TABLE BAL_SUM (
    TERM         VARCHAR2(20),
    BALANCE      NUMBER(18,2),
    LOAN_BALANCE NUMBER(15,2)
);

-- ETL加工
INSERT INTO BAL_SUM
SELECT NVL(T3.DEPOSIT_TERM, T4.LOAN_TERM) AS TERM
      ,T3.SUM_BALANCE AS BALANCE
      ,T4.SUM_LOAN_BALANCE AS LOAN_BALANCE
FROM (
      SELECT DEPOSIT_TERM, SUM(ACCOUNT_BALANCE) AS SUM_BALANCE
      FROM DEPOSIT_ACCOUNT_DETAIL
      GROUP BY DEPOSIT_TERM
     ) T3
FULL JOIN (
      SELECT LOAN_TERM, SUM(LOAN_BALANCE) AS SUM_LOAN_BALANCE
      FROM LOAN_DETAIL
      GROUP BY LOAN_TERM
) T4 ON T3.DEPOSIT_TERM = T4.LOAN_TERM;

SELECT * FROM BAL_SUM;

两种解法对比

对比维度	分层设计解法	简化版解法
表数量	5张	1张
期限标准化	统一到7档区间	保留原始枚举值
可扩展性	新增期限只需维护映射表	需修改应用层SQL
适用场景	规范数仓、长期维护	快速出数、一次性需求

十、总结

本练习完成了以下工作：

✅ 设计了符合数仓分层规范的 DWD、DIM、DWS 三层架构
✅ 实现了期限枚举值到标准报表区间的映射转换
✅ 编写了完整的建表、初始化、ETL、查询 SQL 脚本
✅ 提供了覆盖所有期限区间的测试数据
✅ 输出了符合要求的存贷款明细报表

drop所有相关表，重新测试

--drop所有相关表，重新测试
-- 删除所有相关表（按依赖顺序，先删子表，再删主表）
DROP TABLE DWS_DEPOSIT_TERM_SUM PURGE;
DROP TABLE DWS_LOAN_TERM_SUM PURGE;
DROP TABLE DIM_TERM_MAPPING PURGE;
DROP TABLE DEPOSIT_ACCOUNT_DETAIL PURGE;
DROP TABLE LOAN_DETAIL PURGE;
DROP TABLE BAL_SUM PURGE;

-- 确认已全部删除
SELECT table_name FROM user_tables
WHERE table_name IN ('DWS_DEPOSIT_TERM_SUM', 'DWS_LOAN_TERM_SUM', 'DIM_TERM_MAPPING',
                     'DEPOSIT_ACCOUNT_DETAIL', 'LOAN_DETAIL', 'BAL_SUM');

-- =========================================
-- 1. 存款明细表（DWD层）
-- =========================================
CREATE TABLE DEPOSIT_ACCOUNT_DETAIL (
    ACCOUNT_NO      VARCHAR2(30)  NOT NULL,
    CUST_NO         VARCHAR2(20)  NOT NULL,
    ACCOUNT_BALANCE NUMBER(18,2)  NOT NULL,
    DEPOSIT_TERM    VARCHAR2(20)  NOT NULL,
    CREATE_TIME     DATE          DEFAULT SYSDATE
);

ALTER TABLE DEPOSIT_ACCOUNT_DETAIL ADD CONSTRAINT PK_DEPOSIT_ACCOUNT PRIMARY KEY (ACCOUNT_NO);

COMMENT ON TABLE DEPOSIT_ACCOUNT_DETAIL IS '存款账户明细表';
COMMENT ON COLUMN DEPOSIT_ACCOUNT_DETAIL.ACCOUNT_NO IS '存款账号';
COMMENT ON COLUMN DEPOSIT_ACCOUNT_DETAIL.CUST_NO IS '客户号';
COMMENT ON COLUMN DEPOSIT_ACCOUNT_DETAIL.ACCOUNT_BALANCE IS '账户余额';
COMMENT ON COLUMN DEPOSIT_ACCOUNT_DETAIL.DEPOSIT_TERM IS '存款期限';

-- =========================================
-- 2. 借据表（DWD层）
-- =========================================
CREATE TABLE LOAN_DETAIL (
    LOAN_NO         VARCHAR2(20)  NOT NULL,
    CUST_NO         VARCHAR2(20)  NOT NULL,
    LOAN_BALANCE    NUMBER(15,2)  NOT NULL,
    LOAN_AMOUNT     NUMBER(15,2)  NOT NULL,
    LOAN_TERM       VARCHAR2(20)  NOT NULL,
    CREATE_TIME     DATE          DEFAULT SYSDATE
);

ALTER TABLE LOAN_DETAIL ADD CONSTRAINT PK_LOAN_DETAIL PRIMARY KEY (LOAN_NO);

COMMENT ON TABLE LOAN_DETAIL IS '借据明细表';
COMMENT ON COLUMN LOAN_DETAIL.LOAN_NO IS '借据号';
COMMENT ON COLUMN LOAN_DETAIL.CUST_NO IS '客户号';
COMMENT ON COLUMN LOAN_DETAIL.LOAN_BALANCE IS '贷款余额';
COMMENT ON COLUMN LOAN_DETAIL.LOAN_AMOUNT IS '贷款金额';
COMMENT ON COLUMN LOAN_DETAIL.LOAN_TERM IS '贷款期限';

-- =========================================
-- 3. 期限映射表（维度层）
-- =========================================
CREATE TABLE DIM_TERM_MAPPING (
    SOURCE_TERM     VARCHAR2(20)  NOT NULL,
    TERM_TYPE       VARCHAR2(10)  NOT NULL,
    TERM_INTERVAL   VARCHAR2(30)  NOT NULL,
    INTERVAL_ORDER  NUMBER(2)     NOT NULL,
    CREATE_TIME     DATE          DEFAULT SYSDATE
);

ALTER TABLE DIM_TERM_MAPPING ADD CONSTRAINT PK_TERM_MAPPING PRIMARY KEY (SOURCE_TERM, TERM_TYPE);

COMMENT ON TABLE DIM_TERM_MAPPING IS '期限区间映射表';
COMMENT ON COLUMN DIM_TERM_MAPPING.SOURCE_TERM IS '源表期限值';
COMMENT ON COLUMN DIM_TERM_MAPPING.TERM_TYPE IS '类型：DEPOSIT/LOAN';
COMMENT ON COLUMN DIM_TERM_MAPPING.TERM_INTERVAL IS '标准区间名称';
COMMENT ON COLUMN DIM_TERM_MAPPING.INTERVAL_ORDER IS '区间排序编号';

-- =========================================
-- 4. DWS层：存款按期限区间汇总表
-- =========================================
CREATE TABLE DWS_DEPOSIT_TERM_SUM (
    STAT_DATE       DATE          NOT NULL,
    TERM_INTERVAL   VARCHAR2(30)  NOT NULL,
    INTERVAL_ORDER  NUMBER(2)     NOT NULL,
    TOTAL_BALANCE   NUMBER(20,2)  NOT NULL,
    ACCOUNT_COUNT   NUMBER(10)    NOT NULL,
    UPDATE_TIME     DATE          DEFAULT SYSDATE
);

ALTER TABLE DWS_DEPOSIT_TERM_SUM ADD CONSTRAINT PK_DWS_DEPOSIT_SUM PRIMARY KEY (STAT_DATE, TERM_INTERVAL);

-- =========================================
-- 5. DWS层：贷款按期限区间汇总表
-- =========================================
CREATE TABLE DWS_LOAN_TERM_SUM (
    STAT_DATE       DATE          NOT NULL,
    TERM_INTERVAL   VARCHAR2(30)  NOT NULL,
    INTERVAL_ORDER  NUMBER(2)     NOT NULL,
    TOTAL_BALANCE   NUMBER(20,2)  NOT NULL,
    LOAN_COUNT      NUMBER(10)    NOT NULL,
    UPDATE_TIME     DATE          DEFAULT SYSDATE
);

ALTER TABLE DWS_LOAN_TERM_SUM ADD CONSTRAINT PK_DWS_LOAN_SUM PRIMARY KEY (STAT_DATE, TERM_INTERVAL);

-- 存款期限映射
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('活期', 'DEPOSIT', '三个月以内', 3);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('3M', 'DEPOSIT', '三个月以内', 3);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('6M', 'DEPOSIT', '三个月至六个月', 4);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('9M', 'DEPOSIT', '六个月至一年', 5);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('1Y', 'DEPOSIT', '六个月至一年', 5);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('2Y', 'DEPOSIT', '一年至两年', 6);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('3Y', 'DEPOSIT', '两年至三年', 7);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('5Y', 'DEPOSIT', '三年至五年', 8);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('5Y+', 'DEPOSIT', '五年以上', 9);

-- 贷款期限映射
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('3M', 'LOAN', '三个月以内', 3);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('6M', 'LOAN', '三个月至六个月', 4);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('1Y', 'LOAN', '六个月至一年', 5);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('2Y', 'LOAN', '一年至两年', 6);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('3Y', 'LOAN', '两年至三年', 7);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('5Y', 'LOAN', '三年至五年', 8);
INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER) VALUES ('5Y+', 'LOAN', '五年以上', 9);

COMMIT;

-- =========================================
-- 存款测试数据（9条，覆盖所有期限）
-- =========================================
INSERT INTO DEPOSIT_ACCOUNT_DETAIL (ACCOUNT_NO, CUST_NO, ACCOUNT_BALANCE, DEPOSIT_TERM) VALUES ('ACC001', 'CUST01', 50000.00, '活期');
INSERT INTO DEPOSIT_ACCOUNT_DETAIL (ACCOUNT_NO, CUST_NO, ACCOUNT_BALANCE, DEPOSIT_TERM) VALUES ('ACC002', 'CUST02', 30000.00, '3M');
INSERT INTO DEPOSIT_ACCOUNT_DETAIL (ACCOUNT_NO, CUST_NO, ACCOUNT_BALANCE, DEPOSIT_TERM) VALUES ('ACC003', 'CUST03', 80000.00, '6M');
INSERT INTO DEPOSIT_ACCOUNT_DETAIL (ACCOUNT_NO, CUST_NO, ACCOUNT_BALANCE, DEPOSIT_TERM) VALUES ('ACC004', 'CUST04', 120000.00, '1Y');
INSERT INTO DEPOSIT_ACCOUNT_DETAIL (ACCOUNT_NO, CUST_NO, ACCOUNT_BALANCE, DEPOSIT_TERM) VALUES ('ACC005', 'CUST01', 60000.00, '9M');
INSERT INTO DEPOSIT_ACCOUNT_DETAIL (ACCOUNT_NO, CUST_NO, ACCOUNT_BALANCE, DEPOSIT_TERM) VALUES ('ACC006', 'CUST05', 200000.00, '2Y');
INSERT INTO DEPOSIT_ACCOUNT_DETAIL (ACCOUNT_NO, CUST_NO, ACCOUNT_BALANCE, DEPOSIT_TERM) VALUES ('ACC007', 'CUST06', 150000.00, '3Y');
INSERT INTO DEPOSIT_ACCOUNT_DETAIL (ACCOUNT_NO, CUST_NO, ACCOUNT_BALANCE, DEPOSIT_TERM) VALUES ('ACC008', 'CUST07', 300000.00, '5Y');
INSERT INTO DEPOSIT_ACCOUNT_DETAIL (ACCOUNT_NO, CUST_NO, ACCOUNT_BALANCE, DEPOSIT_TERM) VALUES ('ACC009', 'CUST08', 500000.00, '5Y+');

-- =========================================
-- 贷款测试数据（8条，覆盖所有期限）
-- =========================================
INSERT INTO LOAN_DETAIL (LOAN_NO, CUST_NO, LOAN_BALANCE, LOAN_AMOUNT, LOAN_TERM) VALUES ('LOAN001', 'CUST01', 30000.00, 50000.00, '3M');
INSERT INTO LOAN_DETAIL (LOAN_NO, CUST_NO, LOAN_BALANCE, LOAN_AMOUNT, LOAN_TERM) VALUES ('LOAN002', 'CUST02', 60000.00, 100000.00, '6M');
INSERT INTO LOAN_DETAIL (LOAN_NO, CUST_NO, LOAN_BALANCE, LOAN_AMOUNT, LOAN_TERM) VALUES ('LOAN003', 'CUST03', 150000.00, 200000.00, '1Y');
INSERT INTO LOAN_DETAIL (LOAN_NO, CUST_NO, LOAN_BALANCE, LOAN_AMOUNT, LOAN_TERM) VALUES ('LOAN004', 'CUST04', 250000.00, 300000.00, '2Y');
INSERT INTO LOAN_DETAIL (LOAN_NO, CUST_NO, LOAN_BALANCE, LOAN_AMOUNT, LOAN_TERM) VALUES ('LOAN005', 'CUST05', 350000.00, 500000.00, '3Y');
INSERT INTO LOAN_DETAIL (LOAN_NO, CUST_NO, LOAN_BALANCE, LOAN_AMOUNT, LOAN_TERM) VALUES ('LOAN006', 'CUST01', 180000.00, 200000.00, '3Y');
INSERT INTO LOAN_DETAIL (LOAN_NO, CUST_NO, LOAN_BALANCE, LOAN_AMOUNT, LOAN_TERM) VALUES ('LOAN007', 'CUST06', 450000.00, 600000.00, '5Y');
INSERT INTO LOAN_DETAIL (LOAN_NO, CUST_NO, LOAN_BALANCE, LOAN_AMOUNT, LOAN_TERM) VALUES ('LOAN008', 'CUST07', 800000.00, 1000000.00, '5Y+');

COMMIT;

-- 清空汇总表
TRUNCATE TABLE DWS_DEPOSIT_TERM_SUM;
TRUNCATE TABLE DWS_LOAN_TERM_SUM;

-- 存款 ETL
INSERT INTO DWS_DEPOSIT_TERM_SUM (STAT_DATE, TERM_INTERVAL, INTERVAL_ORDER, TOTAL_BALANCE, ACCOUNT_COUNT, UPDATE_TIME)
SELECT
    TRUNC(SYSDATE) AS STAT_DATE,
    M.TERM_INTERVAL,
    M.INTERVAL_ORDER,
    SUM(D.ACCOUNT_BALANCE) AS TOTAL_BALANCE,
    COUNT(DISTINCT D.ACCOUNT_NO) AS ACCOUNT_COUNT,
    SYSDATE AS UPDATE_TIME
FROM DEPOSIT_ACCOUNT_DETAIL D
INNER JOIN DIM_TERM_MAPPING M
    ON D.DEPOSIT_TERM = M.SOURCE_TERM
    AND M.TERM_TYPE = 'DEPOSIT'
GROUP BY M.TERM_INTERVAL, M.INTERVAL_ORDER;

-- 贷款 ETL
INSERT INTO DWS_LOAN_TERM_SUM (STAT_DATE, TERM_INTERVAL, INTERVAL_ORDER, TOTAL_BALANCE, LOAN_COUNT, UPDATE_TIME)
SELECT
    TRUNC(SYSDATE) AS STAT_DATE,
    M.TERM_INTERVAL,
    M.INTERVAL_ORDER,
    SUM(L.LOAN_BALANCE) AS TOTAL_BALANCE,
    COUNT(DISTINCT L.LOAN_NO) AS LOAN_COUNT,
    SYSDATE AS UPDATE_TIME
FROM LOAN_DETAIL L
INNER JOIN DIM_TERM_MAPPING M
    ON L.LOAN_TERM = M.SOURCE_TERM
    AND M.TERM_TYPE = 'LOAN'
GROUP BY M.TERM_INTERVAL, M.INTERVAL_ORDER;

COMMIT;

-- 存款汇总表（应有 7 行）
SELECT * FROM DWS_DEPOSIT_TERM_SUM ORDER BY INTERVAL_ORDER;

-- 贷款汇总表（应有 7 行）
SELECT * FROM DWS_LOAN_TERM_SUM ORDER BY INTERVAL_ORDER;

-- 最终报表
SELECT 
    COALESCE(D.TERM_INTERVAL, L.TERM_INTERVAL) AS "项目",
    NVL(D.TOTAL_BALANCE, 0) AS "各项存款",
    NVL(L.TOTAL_BALANCE, 0) AS "各项贷款（余额）",
    COALESCE(D.INTERVAL_ORDER, L.INTERVAL_ORDER) AS "排序"
FROM 
    DWS_DEPOSIT_TERM_SUM D
FULL OUTER JOIN 
    DWS_LOAN_TERM_SUM L
    ON D.TERM_INTERVAL = L.TERM_INTERVAL
ORDER BY "排序";

存贷款明细报表练习问题排查与解决总结

一、问题概览

在完成本次练习过程中，主要遇到了以下几类问题：

问题类型	具体表现	严重程度
表不存在	ORA-00942: 表或视图不存在	🔴 严重
数据插入0行	ETL执行后DWS表为空	🔴 严重
查询结果不完整	只显示2行，应有7行	🟡 中等
数据错位	期限字段存储了账号值	🟡 中等
映射表缺失	部分期限无法关联	🟢 轻微

二、详细问题排查与解决

问题1：ORA-00942 表或视图不存在

错误信息：

text

ORA-00942: table or view does not exist

原因分析：

执行查询时，表还没有创建
表创建在不同的用户/模式下
表名拼写错误

排查步骤：

sql

-- 查看当前用户下所有表
SELECT table_name FROM user_tables;

-- 查看所有可访问的表
SELECT table_name FROM all_tables WHERE owner = '当前用户名';

解决方法：

按依赖顺序创建表（先创建DWD层，再创建DIM层，最后创建DWS层）
确保在同一用户下执行所有SQL
查询时使用正确的表名（Oracle默认大写）

问题2：ETL执行后插入0行

错误信息：

text

0 rows inserted

原因分析：

明细表中没有数据
映射表中没有对应的期限值
关联条件不匹配（大小写、空格）
GROUP BY 子句使用不当

排查步骤：

步骤1：检查明细表是否有数据

sql

SELECT COUNT(*) FROM DEPOSIT_ACCOUNT_DETAIL;
SELECT COUNT(*) FROM LOAN_DETAIL;

步骤2：检查映射表是否有对应值

sql

-- 查看明细表中的所有期限
SELECT DISTINCT DEPOSIT_TERM FROM DEPOSIT_ACCOUNT_DETAIL;

-- 查看映射表中的所有期限
SELECT DISTINCT SOURCE_TERM FROM DIM_TERM_MAPPING WHERE TERM_TYPE = 'DEPOSIT';

步骤3：检查关联是否成功

sql

-- 使用LEFT JOIN找出无法关联的记录
SELECT 
    D.DEPOSIT_TERM,
    M.SOURCE_TERM,
    CASE WHEN M.SOURCE_TERM IS NULL THEN '❌ 匹配失败' ELSE '✅ 匹配成功' END AS 状态
FROM DEPOSIT_ACCOUNT_DETAIL D
LEFT JOIN DIM_TERM_MAPPING M 
    ON D.DEPOSIT_TERM = M.SOURCE_TERM 
    AND M.TERM_TYPE = 'DEPOSIT';

步骤4：检查GROUP BY是否正确

sql

-- Oracle要求：SELECT中的非聚合列必须在GROUP BY中
-- ✅ 正确写法
GROUP BY M.TERM_INTERVAL, M.INTERVAL_ORDER

-- ❌ 错误写法
GROUP BY M.TERM_INTERVAL  -- 如果SELECT中有INTERVAL_ORDER会报错

解决方法：

补全映射表中缺失的期限值
确保关联字段的格式一致（去除空格、统一大小写）
使用 INNER JOIN 测试关联结果
修正 GROUP BY 子句

问题3：数据错位（期限字段存了账号值）

错误表现：

text

SELECT DEPOSIT_TERM FROM DEPOSIT_ACCOUNT_DETAIL;
-- 返回结果：ACC001, ACC002, ACC003...（应该是1Y、3Y等）

原因分析：

INSERT语句没有指定列名，且字段顺序与建表语句不一致

错误写法：

sql

-- 建表顺序：ACCOUNT_NO, CUST_NO, ACCOUNT_BALANCE, DEPOSIT_TERM
-- 但插入时顺序错误
INSERT INTO DEPOSIT_ACCOUNT_DETAIL VALUES ('1Y', 'CUST01', 100000, 'ACC001', SYSDATE);
-- 结果：1Y被存入了ACCOUNT_NO，ACC001被存入了DEPOSIT_TERM

解决方法：

sql

-- ✅ 正确写法：明确指定列名
INSERT INTO DEPOSIT_ACCOUNT_DETAIL (ACCOUNT_NO, CUST_NO, ACCOUNT_BALANCE, DEPOSIT_TERM)
VALUES ('ACC001', 'CUST01', 100000.00, '1Y');

最佳实践： 所有INSERT语句都显式指定列名，避免依赖字段顺序。

问题4：查询结果不完整（只有2行）

错误表现：

text

| 项目 | 各项存款 | 各项贷款（余额） |
| 六个月至一年 | 200000 | 300000 |
| 两年至三年 | 100000 | 400000 |
-- 缺少其他5个区间

原因分析：

明细表中缺少其他期限的测试数据
映射表缺少对应期限的映射
ETL执行不完整

排查步骤：

步骤1：确认明细表数据分布

sql

SELECT DEPOSIT_TERM, COUNT(*), SUM(ACCOUNT_BALANCE) 
FROM DEPOSIT_ACCOUNT_DETAIL 
GROUP BY DEPOSIT_TERM 
ORDER BY DEPOSIT_TERM;

步骤2：确认映射表覆盖情况

sql

SELECT TERM_INTERVAL, COUNT(*) 
FROM DIM_TERM_MAPPING 
WHERE TERM_TYPE = 'DEPOSIT' 
GROUP BY TERM_INTERVAL;
-- 应该显示7行

步骤3：确认DWS表数据

sql

SELECT * FROM DWS_DEPOSIT_TERM_SUM ORDER BY INTERVAL_ORDER;
-- 应该有7行

解决方法：

补全所有期限的测试数据
确保映射表包含所有7个区间的映射
重新执行完整的ETL流程

问题5：映射表数据重复插入提示0行

表现：

text

0 rows inserted

原因分析：

主键冲突：PRIMARY KEY (SOURCE_TERM, TERM_TYPE) 约束
数据已存在，使用了 INSERT 而不是 INSERT OR UPDATE

解决方法：

方法1：使用MERGE（推荐）

sql

MERGE INTO DIM_TERM_MAPPING M
USING (SELECT '1Y' AS SOURCE_TERM, 'DEPOSIT' AS TERM_TYPE FROM DUAL) S
ON (M.SOURCE_TERM = S.SOURCE_TERM AND M.TERM_TYPE = S.TERM_TYPE)
WHEN NOT MATCHED THEN
    INSERT (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER)
    VALUES ('1Y', 'DEPOSIT', '六个月至一年', 5);

方法2：使用NOT EXISTS（兼容性好）

sql

INSERT INTO DIM_TERM_MAPPING (SOURCE_TERM, TERM_TYPE, TERM_INTERVAL, INTERVAL_ORDER)
SELECT '1Y', 'DEPOSIT', '六个月至一年', 5 FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM DIM_TERM_MAPPING 
    WHERE SOURCE_TERM = '1Y' AND TERM_TYPE = 'DEPOSIT'
);

三、问题排查方法论总结

3.1 排查流程图

text

开始
  ↓
问题出现
  ↓
确认错误类型 ←──┐
  ↓              │
ORA-00942?       │
  ↓ YES          │
检查表是否存在 ──┘
  ↓ NO
创建缺失的表
  ↓
插入0行？
  ↓ YES
检查明细表数据 → 无数据 → 插入测试数据
  ↓ 有数据
检查映射表关联 → 关联失败 → 补全映射表/检查格式
  ↓ 关联成功
检查GROUP BY → 语法错误 → 修正GROUP BY
  ↓ 正确
查询结果不完整？
  ↓ YES
检查数据分布 → 数据不全 → 补全测试数据
  ↓ 数据完整
问题解决

3.2 核心排查SQL速查表

排查目的	SQL语句
查看所有表	`SELECT table_name FROM user_tables;`
查看明细表数据量	`SELECT COUNT(*) FROM 表名;`
查看字段值分布	`SELECT 字段, COUNT(*) FROM 表名 GROUP BY 字段;`
检查关联是否成功	`LEFT JOIN` + `CASE WHEN ... IS NULL`
查看映射表覆盖情况	`SELECT * FROM 映射表 ORDER BY INTERVAL_ORDER;`
验证DWS汇总结果	`SELECT * FROM DWS表 ORDER BY INTERVAL_ORDER;`

3.3 关键经验教训

经验	说明
指定列名	所有INSERT语句都显式指定列名，避免字段顺序错误
先验证再ETL	先用SELECT验证关联结果，确认无误后再执行INSERT
逐步构建	不要一次性执行大段SQL，分步执行并验证中间结果
使用LEFT JOIN定位问题	当关联失败时，LEFT JOIN能快速找出无法匹配的记录
维护数据字典	保持映射表的完整性，确保覆盖所有业务值
事务提交	执行INSERT后记得COMMIT