【详述】嵌套子查询进行物理运算,物化视图失效
【背景】
创建物化视图语句:
CREATE
MATERIALIZED VIEW view_total_user_duration_guid_id_index_1d_d
DISTRIBUTED BY HASH(ds) BUCKETS 64
REFRESH ASYNC START
(‘2023-06-11 10:00:00’)
EVERY
(INTERVAL 1 DAY)
AS
SELECT ds
, SUM(visit_cnt_1d) AS visit_cnt_1d
, SUM(use_duration_1d) AS use_duration_1d
, SUM(p1d_visit_cnt_1d) AS p1d_visit_cnt_1d
, bitmap_union(use_duration_user_bitmap) AS use_duration_user_cnt_1d
, bitmap_union(p1d_use_duration_user_bitmap) AS p1d_use_duration_user_cnt_1d
FROM rdm.rdm_user_duration_guid_id_index_1d_d
GROUP BY ds
;
简单查询语句:
SELECT ds
, package_name
, bitmap_union_count(use_duration_user_bitmap) AS today_uv
, SUM(visit_cnt_1d) AS today_pv
, SUM(use_duration_1d) AS use_time
, bitmap_union_count(p1d_use_duration_user_bitmap) AS lastday_uv
, SUM(p1d_visit_cnt_1d) AS lastday_pv
, bitmap_count(bitmap_and(bitmap_union(p1d_use_duration_user_bitmap),
bitmap_union(use_duration_user_bitmap))) AS retention_uv
FROM rdm.rdm_user_duration_guid_id_index_1d_d
WHERE ds = 20230615
GROUP BY ds,package_name;
执行计划截图:
嵌套子查询:
SELECT retention_uv / lastday_uv
FROM (SELECT ds
, package_name
, bitmap_union_count(use_duration_user_bitmap) AS today_uv
, SUM(visit_cnt_1d) AS today_pv
, SUM(use_duration_1d) AS use_time
, bitmap_union_count(p1d_use_duration_user_bitmap) AS lastday_uv
, SUM(p1d_visit_cnt_1d) AS lastday_pv
, bitmap_count(bitmap_and(bitmap_union(p1d_use_duration_user_bitmap),
bitmap_union(use_duration_user_bitmap))) AS retention_uv
FROM rdm.rdm_user_duration_guid_id_index_1d_d
WHERE ds = 20230615
GROUP BY ds,package_name) t1;
执行计划截图:
【业务影响】
【StarRocks版本】例如：3.0.1