Gbase8A-带聚合运算的物化视图

1.开启物化视图参数

set  global gcluster_enable_mview =1;

2.新建测试表： 

CREATE TABLE "dep_dpsit_acct_bal_info_test" (

  "PRDT_NO" varchar(10) DEFAULT NULL,

  "PRDT_NM" varchar(10) DEFAULT NULL,

  "CUST_TPCD" varchar(10) DEFAULT NULL,

  "OPACT_ORGNO" varchar(10) DEFAULT NULL,

  "YACM_BAL" decimal(26,6) DEFAULT NULL,

  "ACCT_BAL" decimal(26,6) DEFAULT NULL,

  "DDW_ETL_DT" date DEFAULT NULL,

  KEY "idx_PRDT_NO" ("PRDT_NO") USING HASH GLOBAL,

  KEY "idx_PRDT_NM" ("PRDT_NM") USING HASH GLOBAL,

  KEY "idx_CUST_TPCD" ("CUST_TPCD") USING HASH GLOBAL,

  KEY "idx_OPACT_ORGNO" ("OPACT_ORGNO") USING HASH GLOBAL

)DISTRIBUTED BY('PRDT_NO', 'OPACT_ORGNO','PRDT_NM','CUST_TPCD') ;

3.插入测试数据：

-- 插入测试数据

INSERT INTO dep_dpsit_acct_bal_info_test

(PRDT_NO, PRDT_NM, CUST_TPCD, OPACT_ORGNO, YACM_BAL, ACCT_BAL, DDW_ETL_DT)

VALUES

-- 2024-11-25（目标日期）数据

('P001', '个人活期存款', '01', 'ORG001', 100000.00, 95000.00, '2024-11-25'),

('P001', '个人活期存款', '02', 'ORG001', 80000.00, 78000.00, '2024-11-25'),

('P002', '企业定期存款', '03', 'ORG002', 500000.00, 495000.00, '2024-11-25'),

('P003', '大额存单', '01', 'ORG003', 2000000.00, 1980000.00, '2024-11-25'),

-- 2024-11-24（前一日）数据

('P001', '个人活期存款', '01', 'ORG001', 98000.00, 93000.00, '2024-11-24'),

('P001', '个人活期存款', '02', 'ORG001', 79000.00, 77500.00, '2024-11-24'),

('P002', '企业定期存款', '03', 'ORG002', 500000.00, 495000.00, '2024-11-24'),

('P003', '大额存单', '01', 'ORG003', 1990000.00, 1970000.00, '2024-11-24'),

-- 2024-10-31（上月末）数据

('P001', '个人活期存款', '01', 'ORG001', 95000.00, 90000.00, '2024-10-31'),

('P001', '个人活期存款', '02', 'ORG001', 75000.00, 74000.00, '2024-10-31'),

('P002', '企业定期存款', '03', 'ORG002', 480000.00, 475000.00, '2024-10-31'),

('P003', '大额存单', '01', 'ORG003', 1950000.00, 1930000.00, '2024-10-31'),

-- 2024-10-26（30天前，同比基准）数据

('P001', '个人活期存款', '01', 'ORG001', 90000.00, 88000.00, '2024-10-26'),

('P001', '个人活期存款', '02', 'ORG001', 70000.00, 69000.00, '2024-10-26'),

('P002', '企业定期存款', '03', 'ORG002', 450000.00, 445000.00, '2024-10-26'),

('P003', '大额存单', '01', 'ORG003', 1900000.00, 1880000.00, '2024-10-26'),

-- 2023-11-25（去年同期，环比基准）数据

('P001', '个人活期存款', '01', 'ORG001', 85000.00, 83000.00, '2023-11-25'),

('P001', '个人活期存款', '02', 'ORG001', 65000.00, 64000.00, '2023-11-25'),

('P002', '企业定期存款', '03', 'ORG002', 400000.00, 395000.00, '2023-11-25'),

('P003', '大额存单', '01', 'ORG003', 1800000.00, 1780000.00, '2023-11-25'),

-- 2023-12-31（上年底）数据

('P001', '个人活期存款', '01', 'ORG001', 88000.00, 86000.00, '2023-12-31'),

('P001', '个人活期存款', '02', 'ORG001', 68000.00, 67000.00, '2023-12-31'),

('P002', '企业定期存款', '03', 'ORG002', 420000.00, 415000.00, '2023-12-31'),

('P003', '大额存单', '01', 'ORG003', 1850000.00, 1830000.00, '2023-12-31'); 

4.新建物化视图: 

create materialized view sum_agg_sql refresh complete on viewread as

SELECT

                PRDT_NO,

PRDT_NM,

CUST_TPCD,

OPACT_ORGNO,

curr_yacm_bal AS '年累计余额',

curr_acct_bal AS '账户余额',

curr_yacm_bal - prev_day_yacm_bal AS '日增年累计余额',

curr_acct_bal - prev_day_acct_bal AS '日增账户余额',

curr_yacm_bal - last_month_yacm_bal AS '月增年累计余额',

curr_acct_bal - last_month_acct_bal AS '月增账户余额'

from (

SELECT

    t.PRDT_NO,

    t.PRDT_NM,

    t.CUST_TPCD,

    t.OPACT_ORGNO,

    SUM(CASE WHEN t.DDW_ETL_DT = dp.curr_dt THEN t.YACM_BAL ELSE 0 END) AS curr_yacm_bal,

    SUM(CASE WHEN t.DDW_ETL_DT = dp.curr_dt THEN t.ACCT_BAL ELSE 0 END) AS curr_acct_bal,

    SUM(CASE WHEN t.DDW_ETL_DT = dp.prev_day_dt THEN t.YACM_BAL ELSE 0 END) AS prev_day_yacm_bal,

    SUM(CASE WHEN t.DDW_ETL_DT = dp.prev_day_dt THEN t.ACCT_BAL ELSE 0 END) AS prev_day_acct_bal,

    SUM(CASE WHEN t.DDW_ETL_DT = dp.last_month_end_dt THEN t.YACM_BAL ELSE 0 END) AS last_month_yacm_bal,

    SUM(CASE WHEN t.DDW_ETL_DT = dp.last_month_end_dt THEN t.ACCT_BAL ELSE 0 END) AS last_month_acct_bal,

    SUM(CASE WHEN t.DDW_ETL_DT = dp.prev_30day_dt THEN t.YACM_BAL ELSE 0 END) AS prev_30day_yacm_bal,

    SUM(CASE WHEN t.DDW_ETL_DT = dp.prev_30day_dt THEN t.ACCT_BAL ELSE 0 END) AS prev_30day_acct_bal,

    SUM(CASE WHEN t.DDW_ETL_DT = dp.prev_year_dt THEN t.YACM_BAL ELSE 0 END) AS prev_year_yacm_bal,

    SUM(CASE WHEN t.DDW_ETL_DT = dp.prev_year_dt THEN t.ACCT_BAL ELSE 0 END) AS prev_year_acct_bal,

    SUM(CASE WHEN t.DDW_ETL_DT = dp.last_year_end_dt THEN t.YACM_BAL ELSE 0 END) AS last_year_yacm_bal,

    SUM(CASE WHEN t.DDW_ETL_DT = dp.last_year_end_dt THEN t.ACCT_BAL ELSE 0 END) AS last_year_acct_bal

FROM dep_dpsit_acct_bal_info_test t

CROSS JOIN ( SELECT

            cast( '2024-11-25' as date) as curr_dt , -- 2024-11-25  当天

            cast('2024-11-24' as date) as prev_day_dt ,  -- 2024-11-24 前一天

            (cast(substr('2024-11-25',1,8)||'01' as date) -30)  as last_month_end_dt, -- 2024-10-02  距今30天的

            (cast(substr('2024-11-25',1,8)||'01' as date) -365) as prev_year_dt, -- 2023-11-02 距今1年的

            (cast(substr('2024-11-25',1,8)||'01' as date) -1)  as  prev_30day_dt,   -- 上月底

            (cast(substr('2024-11-25',1,5)||'01-01' as date) -1)  as last_year_end_dt -- 上年末

             from dual ) dp

WHERE t.DDW_ETL_DT IN (dp.curr_dt, dp.prev_day_dt, dp.last_month_end_dt, dp.prev_30day_dt, dp.prev_year_dt, dp.last_year_end_dt)

GROUP BY t.PRDT_NO, t.PRDT_NM, t.CUST_TPCD, t.OPACT_ORGNO) a  ;

5.查询物化视图： select  * from  sum_agg_sql   limit 10 ;