CREATE MATERIALIZED VIEW ld
(scr_id,prsn_id,prsn_name,party_id,party_name,photo,activity,research,recommend,forecast_year,target_price,profit,rate)
BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 10 HOUR
DISTRIBUTED BY RANDOM BUCKETS 2
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"bloom_filter_columns" = "scr_id",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_duplicate_without_keys_by_default" = "true"
)
AS WITH
tmp_person
AS (
SELECT
DISTINCT prsn_id
, prsn_name
, stat_cycle
, prsn_type
, is_valid
, party_id
, party_name
, scr_id
, photo
FROM
paimon.test_ywhjld.prsn_evaluation
WHERE
stat_cycle = 180
AND prsn_type = 5
AND is_valid = 1
AND (
dim_category IN (30, 40)
OR dim_type = 23
)
)
, tmp_person_activity
AS (
SELECT
A.scr_id
, A.prsn_id
, CAST(SUM(B.result) AS STRING) AS activity
FROM
tmp_person AS A
INNER JOIN
paimon.test_ywhjld.prsn_evaluation AS B
ON
A.prsn_id = B.prsn_id
AND A.scr_id = B.scr_id
AND A.stat_cycle = B.stat_cycle
AND A.prsn_type = B.prsn_type
AND A.is_valid = B.is_valid
AND B.dim_category = 40
GROUP BY
A.scr_id
, A.prsn_id
)
, tmp_person_research
AS (
SELECT
A.scr_id
, A.prsn_id
,SUM(B.result) AS research
FROM
tmp_person AS A
INNER JOIN
paimon.test_ywhjld.prsn_evaluation AS B
ON
A.prsn_id = B.prsn_id
AND A.scr_id = B.scr_id
AND A.stat_cycle = B.stat_cycle
AND A.prsn_type = B.prsn_type
AND A.is_valid = B.is_valid
AND B.dim_category = 30
GROUP BY
A.scr_id
, A.prsn_id
)
, tmp_person_recommend
AS (
SELECT
A.scr_id
, A.prsn_id
, 0 + CAST(ROUND(B.result, 2) AS CHAR(50)) AS recommend
FROM
tmp_person AS A
INNER JOIN
paimon.test_ywhjld.prsn_evaluation AS B
ON
A.prsn_id = B.prsn_id
AND A.scr_id = B.scr_id
AND A.stat_cycle = B.stat_cycle
AND A.prsn_type = B.prsn_type
AND A.is_valid = B.is_valid
AND B.dim_type = 23
)
, tmp_person_merge
AS (
SELECT
A.scr_id
, A.prsn_id
, A.prsn_name
, A.party_id
, A.party_name
, A.photo
, B.activity
, C.research
, D.recommend
FROM
tmp_person AS A
LEFT JOIN
tmp_person_activity AS B
ON
A.prsn_id = B.prsn_id
AND A.scr_id = B.scr_id
LEFT JOIN
tmp_person_research AS C
ON
A.prsn_id = C.prsn_id
AND A.scr_id = C.scr_id
LEFT JOIN
tmp_person_recommend AS D
ON
A.prsn_id = D.prsn_id
AND A.scr_id = D.scr_id
)
, tmp_forecast
AS (
SELECT
A.prsn_id
, C.f3_4105
, D.f13_4103
, C.f15_4105
, E.f3_1002
, ROW_NUMBER() OVER (PARTITION BY A.prsn_id ORDER BY B.f1_4102 DESC, C.f3_4105) AS rank_num
FROM
tmp_person AS A
INNER JOIN
paimon.test_ywhjld.tb_4102 AS B
ON
A.party_id = B.f4_4102
INNER JOIN
paimon.test_ywhjld.tb_4105 AS C
ON
B.id = C.f1_4105
AND C.f2_4105 = A.scr_id
AND C.f4_4105 = 1
LEFT JOIN
paimon.test_ywhjld.tb_4103 AS D
ON
B.id = D.f1_4103
LEFT JOIN
paimon.test_ywhjld.tb_1002 AS E
ON
D.f14_4103 = E.f4_1002
AND E.f2_1002 = 104800900
)
SELECT
A.scr_id
, A.prsn_id
, A.prsn_name
, A.party_id
, A.party_name
, A.photo
, A.activity
, A.research
, A.recommend
, B.f3_4105 AS forecast_year
, 0 + CAST(ROUND(B.f13_4103, 2) AS CHAR(50)) AS target_price
, 0 + CAST(ROUND(B.f15_4105 / 10000, 2) AS CHAR(50)) AS profit
, B.f3_1002 AS rate
FROM
tmp_person_merge AS A
LEFT JOIN
tmp_forecast AS B
ON
A.prsn_id = B.prsn_id
AND B.rank_num = 1；
查询语句： 创建物化视图语句一样也不能命中
最终目的在这个物化视图上面进行where scr_id=? 进行查询，但是原来一样语句不能命中，where 一样不能命中，
MaterializedViewRewriteFail:
Name: internal#test#ld
FailSummary: View struct info is invalid
Name: internal#test#ld1
FailSummary: View struct info is invalid 开启的相关参数
