CREATE materialized VIEW mv_bill_test AS
select
org_id,
bill_id,
checkout_time,
holiday_able,
bill_amount,
receivable_amount,
actual_amount,
discount_amount,
bill_type,
ticket_amount,
food_amount,
room_amount,
goods_amount,
project_amount,
leisure_amount,
bath_amount,
other_amount,
checkin_ids,
ticket_nos,
total_cnt,
adult_num,
man_cnt,
feman_cnt,
child_cnt,
man_amount,
man_avg_amount,
feman_amount,
feman_avg_amount,
child_amount,
child_avg_amount,
member_able,
member_union_id
from
(
select
a.org_id,
a.bill_id,
a.checkout_time,
a.holiday_able,
a.bill_amount,
a.receivable_amount,
a.actual_amount,
a.discount_amount,
a.bill_type,
a.ticket_amount,
a.food_amount,
a.room_amount,
a.goods_amount,
a.project_amount,
a.leisure_amount,
a.bath_amount,
a.other_amount,
a.checkin_ids,
a.ticket_nos,
a.total_cnt,
a.adult_num,
a.man_cnt,
round(a.man_amount, 2) as man_amount,
if(
a.man_amount > 0,
round(
a.man_amount / a.man_cnt,
2), 0) as man_avg_amount,
a.feman_cnt,
a.feman_amount,
if(
a.feman_amount > 0,
round(
a.feman_amount / a.feman_cnt,
2
),
0
) as feman_avg_amount,
a.child_cnt,
a.child_amount,
if(
a.child_amount > 0,
round(
a.child_amount / a.child_cnt,
2
),
0
) as child_avg_amount,
a.member_able,
a.member_union_id
from
(
select
a.org_id,
a.bill_id,
a.checkout_time,
a.holiday_able,
a.bill_amount,
a.receivable_amount,
a.actual_amount,
a.discount_amount,
a.bill_type,
a.ticket_amount,
a.food_amount,
a.room_amount,
a.goods_amount,
a.project_amount,
a.leisure_amount,
a.bath_amount,
a.other_amount,
group_concat(distinct b.checkin_id) as checkin_ids,
group_concat(distinct b.ticket_no) as ticket_nos,
sum(
b.adult_num + b.children_num + b.free_num
) as total_cnt,
sum(
if(
b.leave_type_id = 1
and b.ticket_type_id in (1, 2),
b.adult_num,
0
)
) as adult_num,
sum(
if(
b.leave_type_id = 1
and b.ticket_type_id = 3,
b.adult_num,
0
)
) as child_cnt,
sum(
if(
b.leave_type_id = 1
and b.ticket_type_id = 1,
b.adult_num,
0
)
) as man_cnt,
sum(
if(
b.leave_type_id = 1
and b.ticket_type_id = 2,
b.adult_num,
0
)
) as feman_cnt,
sum(
if(
b.leave_type_id = 1
and b.ticket_type_id = 1,
b.actual_amount,
0
)
) as man_amount,
sum(
if(
b.leave_type_id = 1
and b.ticket_type_id = 2,
b.actual_amount,
0
)
) as feman_amount,
sum(
if(
b.leave_type_id = 1
and b.ticket_type_id = 3,
b.actual_amount,
0
)
) as child_amount,
if(
sum(
mbmber_consumption.member_cnt
) > 0,
1,
0
) as member_able,
min(
mbmber_consumption.member_union_id
) as member_union_id
from
tenant_olap_szm.dwd_golden_bill AS a
left join tenant_olap_szm.dwd_golden_checkin_history AS b on a.org_id = b.org_id
and a.bill_id = b.bill_id
left join (
select
dwd_golden_member_card_consumption.org_id,
dwd_golden_member_card_consumption.bill_id,
group_concat(
distinct dwd_golden_member_card_consumption.member_union_id
) as member_union_ids,
count(
distinct dwd_golden_member_card_consumption.member_union_id
) as member_cnt,
min(
dwd_golden_member_card_consumption.member_union_id
) as member_union_id
from
tenant_olap_szm.dwd_golden_member_card_consumption
where
dwd_golden_member_card_consumption.balance_change_type_id = 3
group by
dwd_golden_member_card_consumption.org_id,
dwd_golden_member_card_consumption.bill_id
) mbmber_consumption on a.org_id = mbmber_consumption.org_id
and a.bill_id = mbmber_consumption.bill_id
group by
a.org_id,
a.bill_id,
a.checkout_time,
a.holiday_able,
a.bill_amount,
a.receivable_amount,
a.actual_amount,
a.discount_amount,
a.bill_type,
a.ticket_amount,
a.food_amount,
a.room_amount,
a.goods_amount,
a.project_amount,
a.leisure_amount,
a.bath_amount,
a.other_amount
) a
) bill;
ERROR 1054 (42S22): errCode = 2, detailMessage = Unknown column 'org_id' in 'tenant_olap_szm.a'
物化视图相关配置如下
SHOW VARIABLES like '%materialized%';
+------------------------------------------------------------------+-------+---------------+---------+
| Variable_name | Value | Default_Value | Changed |
+------------------------------------------------------------------+-------+---------------+---------+
| enable_materialized_view_nest_rewrite | true | false | 1 |
| allow_modify_materialized_view_data | false | false | 0 |
| deprecated_enable_materialized_view_union_rewrite | true | true | 0 |
| enable_dml_materialized_view_rewrite | true | true | 0 |
| enable_dml_materialized_view_rewrite_when_base_table_unawareness | false | false | 0 |
| enable_materialized_view_rewrite | true | true | 0 |
| materialized_view_relation_mapping_max_count | 8 | 8 | 0 |
| materialized_view_rewrite_enable_contain_external_table | false | false | 0 |
| materialized_view_rewrite_success_candidate_num | 3 | 3 | 0 |
+------------------------------------------------------------------+-------+---------------+---------+
9 rows in set (0.00 sec)
