背景:一个巨复杂的sql,关联了好几个表,数据量有很大,查起来很耗时
方案:先变成视图,再变成物化视图,然后搞一个定时器每天晚上刷新物化视图(前提;我的数据是T+1,没有要求实时更新)
1.创建视图
CREATE VIEW BB_V_ZDZYJKYJ_SUM
AS
select YEAR, SUBSTR(RULEEXETIME, 6, 2) AS MONTH, SUBSTR(PROVINCE, 1, 2) AS AREAID ,DATAOWNER,COUNT(*) AS YCL_COUNT,SUM(AMT) AS YCL_AMT
FROM
BB_V_ZDZYJKYJ_0830_CLICKHOUSE1
WHERE "处理状态" in('已整改','认定正常')
GROUP BY
YEAR, SUBSTR(RULEEXETIME, 6, 2), SUBSTR(PROVINCE, 1, 2),DATAOWNER
) A LEFT JOIN (
--上月已处理
select YEAR, SUBSTR