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
