建表语句：
CREATE TABLE market_mo_track_event_game_role_level_all(
app_id VARCHAR(16) comment '',
user_id VARCHAR(16) comment '',
channel_id int comment '',
client_ip VARCHAR(64) comment '',
log_id VARCHAR(32) comment '',
`timestamp` int comment '',
server_sid int comment '',
cserver_id int comment '',
role_id string comment '',
`role` string comment '',
level_before int comment '',
level_after int comment '',
package_name string comment '',
version_code string comment '',
version_name string comment '',
sdk_version string comment '',
part_date date
) DUPLICATE KEY(`app_id`,`user_id`)
PARTITION BY RANGE(`part_date`)()
DISTRIBUTED BY HASH(`user_id`) BUCKETS 6
PROPERTIES (
"dynamic_partition.enable" = "true",
"dynamic_partition.create_history_partition" = "true",
"dynamic_partition.history_partition_num" = "20",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p_",
"dynamic_partition.buckets" = "6",
"replication_num" = "3"
);
物化视图：
create materialized view game_level as
select user_id,app_id,role_id,max(timestamp) as timestamp,max(level_after) as level_after
from market_mo_track_event_game_role_level_all group by user_id,app_id,role_id;
查询语句：
explain select user_id,app_id,role_id,max(timestamp) as timestamp,max(level_after) as level_after from market_mo_track_event_game_role_level_all
where user_id ='118650875' group by user_id,app_id,role_id;
执行计划结果：
+---------+
| Explain String(Nereids Planner) |
+---------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS: |
| user_id[#32] |
| app_id[#33] |
| role_id[#34] |
| timestamp[#35] |
| level_after[#36] |
| PARTITION: UNPARTITIONED |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| VRESULT SINK |
| |
| 1594:VEXCHANGE |
| offset: 0 |
| |
| PLAN FRAGMENT 1 |
| |
| PARTITION: HASH_PARTITIONED: mv_user_id[#22], mv_app_id[#23], mv_role_id[#24] |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 1594 |
| UNPARTITIONED |
| |
| 1588:VAGGREGATE (merge finalize) |
| | output: max(partial_max(mva_MAX__timestamp)[#25])[#30], max(partial_max(mva_MAX__level_after)[#26])[#31] |
| | group by: mv_user_id[#22], mv_app_id[#23], mv_role_id[#24] |
| | cardinality=25,875,255 |
| | projections: mv_user_id[#27], mv_app_id[#28], mv_role_id[#29], timestamp[#30], level_after[#31] |
| | project output tuple id: 4 |
| | |
| 1585:VEXCHANGE |
| offset: 0 |
| |
| PLAN FRAGMENT 2 |
| |
| PARTITION: RANDOM |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 1585 |
| HASH_PARTITIONED: mv_user_id[#22], mv_app_id[#23], mv_role_id[#24] |
| |
| 1582:VAGGREGATE (update serialize) |
| | STREAMING |
| | output: partial_max(mva_MAX__timestamp[#3])[#25], partial_max(mva_MAX__level_after[#4])[#26] |
| | group by: mv_user_id[#0], mv_app_id[#1], mv_role_id[#2] |
| | cardinality=51,750,511 |
| | |
| 1576:VOlapScanNode |
| TABLE: default_cluster:dw.market_mo_track_event_game_role_level_all(game_level), PREAGGREGATION: ON |
| PREDICATES: mv_user_id[#0] = '118650875' |
| partitions=364/377 (p_20231114,p_20241109,p_20241110,p_20241111), tablets=364/4004, tabletList=4321173,4321217,4321261 ... |
| cardinality=207002044, avgRowSize=0.0, numNodes=1 |
| pushAggOp=NONE |
+---------+
58 rows in set (0.06 sec)
