Starrocks创建物化视图时不能写select *
CREATE MATERIALIZED VIEW dw_stream.xxx_amv_sr
PARTITION BY date_trunc('DAY', pt)
DISTRIBUTED BY HASH(emp_id) BUCKETS 10 REFRESH ASYNC START('2024-07-17 16:47:02') EVERY(INTERVAL 1 minute)
PROPERTIES ( "replication_num" = "3", "storage_medium" = "HDD", "auto_refresh_partitions_limit" = "5", "partition_refresh_number" = "2", "partition_ttl_number" = "2" ) AS
select emp_id ,emp_name ,pt ,sum(case when item = 'foot' then amount else 0 end) as hot_put_get_send_wo_cnt ,sum(case when item = 'finish' then amount else 0 end) as hot_put_get_finish_wo_cnt ,sum(case when item = 'foot' then amount else 0 end) as gain_put_get_send_wo_cnt ,sum(case when item = 'finish' then amount else 0 end) as gain_put_get_finish_wo_cnt from
( select emp_id ,
emp_name ,
pt ,
item,
amount,
row_number() over(partition by pt,block_id,item order by gmt_modify desc) rn
from dw_stream.xxx_dup_sr
where item in ( 'foot' ,'finish' ) )a1 where rn = 1 group by emp_id,emp_name,pt
不能写成：
CREATE MATERIALIZED VIEW dw_stream.xxx_amv_sr
PARTITION BY date_trunc('DAY', pt)
DISTRIBUTED BY HASH(emp_id) BUCKETS 10 REFRESH ASYNC START('2024-07-17 16:47:02') EVERY(INTERVAL 1 minute)
PROPERTIES ( "replication_num" = "3", "storage_medium" = "HDD", "auto_refresh_partitions_limit" = "5", "partition_refresh_number" = "2", "partition_ttl_number" = "2" ) AS
select emp_id ,emp_name ,pt ,sum(case when item = 'foot' then amount else 0 end) as hot_put_get_send_wo_cnt ,sum(case when item = 'finish' then amount else 0 end) as hot_put_get_finish_wo_cnt ,sum(case when item = 'foot' then amount else 0 end) as gain_put_get_send_wo_cnt ,sum(case when item = 'finish' then amount else 0 end) as gain_put_get_finish_wo_cnt from
( select
*
row_number() over(partition by pt,block_id,item order by gmt_modify desc) rn
from dw_stream.xxx_dup_sr
where item in ( 'foot' ,'finish' ) )a1 where rn = 1 group by emp_id,emp_name,pt
原文地址：https://blog.csdn.net/u012333203/article/details/140501612
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！