1
ROLLBACK;
2

3
INSTALL excel;
4
LOAD excel;
5

6
BEGIN;
7

8
--- 自增 index
9
CREATE SEQUENCE IF NOT EXISTS seq_deposit_id START 1;
10

11
--- 建表
12
CREATE TABLE IF NOT EXISTS deposit_fact (
13
    id BIGINT PRIMARY KEY DEFAULT nextval('seq_deposit_id'),
14

15
    "统计日期" DATE,
16
    "客户名称" VARCHAR,
17
    "客户编号" VARCHAR,
18
    "客户账号名称" VARCHAR,
19
    "客户账号" VARCHAR ,
20
    "币种" VARCHAR,
21

22
    "存款余额" DECIMAL(38,2),
23
    "人民币存款余额" DECIMAL(38,2),
24
    "折合美元存款余额" DECIMAL(38,2),
25
    "折合人民币存款余额" DECIMAL(38,2),
26
    "对公存款账户年日均余额" DECIMAL(38,2),
27
    "对公存款账户年日均余额折美元" DECIMAL(38,2),
28
    "对公存款账户年日均余额折人民币" DECIMAL(38,2),
29

30
    "可售产品名称" VARCHAR,
31
    "资金用途" VARCHAR,
32
    "网点机构编号" VARCHAR,
33
    "网点机构中文全称" VARCHAR,
34
    "所属支行机构编号" VARCHAR,
35
    "所属支行机构中文全称" VARCHAR,
36
    "所属二级分行机构编号" VARCHAR,
37
    "所属二级分行机构中文全称" VARCHAR,
38

39
    "客户账号个数" INT,
40

41
    --- 自动计算列
42
    "客户账号_md5" VARCHAR
43
        GENERATED ALWAYS AS (
44
          lower(md5(trim("客户账号")))
45
        ) VIRTUAL,
46

47
    "余额" DECIMAL(38,2)
48
        GENERATED ALWAYS AS (
49
          "人民币存款余额" + "折合人民币存款余额"
50
        ) VIRTUAL,
51

52
    "年日均余额" DECIMAL(38,2)
53
        GENERATED ALWAYS AS (
54
          "对公存款账户年日均余额折人民币"
55
        ) VIRTUAL
56
);
57

58
INSERT INTO deposit_fact (
59
    "统计日期",
60
    "客户名称","客户编号","客户账号名称","客户账号","币种",
61
    "存款余额","人民币存款余额","折合美元存款余额","折合人民币存款余额","对公存款账户年日均余额","对公存款账户年日均余额折美元","对公存款账户年日均余额折人民币",
62
    "可售产品名称","资金用途","网点机构编号","网点机构中文全称","所属支行机构编号","所属支行机构中文全称","所属二级分行机构编号","所属二级分行机构中文全称",
63
    "客户账号个数"
64
)
65
SELECT
66
    --- 日期： yyyy-mm-dd；用 try_strptime 防御脏数据，失败返回 NULL :contentReference[oaicite:9]{index=9}
67
    CAST(try_strptime(trim("统计日期"),'%Y-%m-%d') AS DATE) AS "统计日期",
68

69
    --- 字符串列需要删除前后空白字符
70
    TRIM("客户名称") AS "客户名称",
71
    TRIM("客户编号") AS "客户编号",
72
    TRIM("客户账号名称") AS "客户账号名称",
73
    TRIM("客户账号") AS "客户账号",
74
    TRIM("币种") AS "币种",
75

76
    --- 金额列：先去空格，再去千分位逗号，再 TRY_CAST 成 DECIMAL（精确）:contentReference[oaicite:10]{index=10}
77
    TRY_CAST(REPLACE(NULLIF(TRIM("存款余额"),''),',','') AS DECIMAL(38,2)) AS "存款余额",
78
    TRY_CAST(REPLACE(NULLIF(TRIM("人民币存款余额"),''),',','') AS DECIMAL(38,2)) AS "人民币存款余额",
79
    TRY_CAST(REPLACE(NULLIF(TRIM("折合美元存款余额"),''),',','') AS DECIMAL(38,2)) AS "折合美元存款余额",
80
    TRY_CAST(REPLACE(NULLIF(TRIM("折合人民币存款余额"),''),',','') AS DECIMAL(38,2)) AS "折合人民币存款余额",
81
    TRY_CAST(REPLACE(NULLIF(TRIM("对公存款账户年日均余额"),''),',','') AS DECIMAL(38,2)) AS "对公存款账户年日均余额",
82
    TRY_CAST(REPLACE(NULLIF(TRIM("对公存款账户年日均余额折美元"),''),',','') AS DECIMAL(38,2)) AS "对公存款账户年日均余额折美元",
83
    TRY_CAST(REPLACE(NULLIF(TRIM("对公存款账户年日均余额折人民币"),''),',','') AS DECIMAL(38,2)) AS "对公存款账户年日均余额折人民币",
84

85
    --- 其他字符列
86
    TRIM("可售产品名称") AS "可售产品名称",
87
    TRIM("资金用途") AS "资金用途",
88
    TRIM("网点机构编号") AS "网点机构编号",
89
    TRIM("网点机构中文全称") AS "网点机构中文全称",
90
    TRIM("所属支行机构编号") AS "所属支行机构编号",
91
    TRIM("所属支行机构中文全称") AS "所属支行机构中文全称",
92
    TRIM("所属二级分行机构编号") AS "所属二级分行机构编号",
93
    TRIM("所属二级分行机构中文全称") AS "所属二级分行机构中文全称",
94

95
    --- 整数列
96
    TRY_CAST(NULLIF(TRIM("客户账号个数"),'') AS INT) AS "客户账号个数"
97
FROM READ_XLSX('C:\Users\jacf\Desktop\存款\汇总.xlsx',header=true,range='B:W',all_varchar=true)
98
;
99

100
--- 单列可重复索引
101
CREATE INDEX IF NOT EXISTS idx_deposit_stat_date   ON deposit_fact("统计日期");
102
CREATE INDEX IF NOT EXISTS idx_deposit_cust_name   ON deposit_fact ("客户名称");
103
CREATE INDEX IF NOT EXISTS idx_deposit_acct        ON deposit_fact ("客户账号");
104
CREATE INDEX IF NOT EXISTS idx_deposit_product     ON deposit_fact ("可售产品名称");
105
CREATE INDEX IF NOT EXISTS idx_deposit_usage       ON deposit_fact ("资金用途");
106

107
--- 符合索引
108
CREATE INDEX IF NOT EXISTS idx_deposit_date_acct   ON deposit_fact ("统计日期","客户账号");
109
CREATE INDEX IF NOT EXISTS idx_deposit_date_prod   ON deposit_fact ("统计日期","可售产品名称");
110

111
COMMIT;