DROP TABLE IF EXISTS rhd_ods_damage_inc_1d;
CREATE TABLE rhd_ods_damage_inc_1d
(
`event_id` STRING ,
`steam_id` STRING ,
`weapon_id` STRING ,
`map_id` STRING ,
`zombie_id` STRING ,
`body_area` STRING ,
`buff_list` STRING ,
`damage` DECIMAL(16 , 2) ,
ts STRING comment "2024-05-06 23:15:32 982"
) COMMENT '原始伤害表'
partitioned by (`dt` string comment '统计日期')
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
NULL DEFINED AS ''
LOCATION '/warehouse/rhd/ods/damage_inc_1d';
DROP TABLE IF EXISTS rhd_dwd_damage_inc;
CREATE TABLE rhd_dwd_damage_inc
(
`steam_id` STRING ,
`damage` DECIMAL(16 , 2)
) COMMENT '伤害表'
partitioned by (`dt` string comment '统计日期')
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
NULL DEFINED AS ''
LOCATION '/warehouse/rhd/dwd/damage_inc_1d';
DROP TABLE IF EXISTS rhd_dws_damage_inc_1d;
CREATE TABLE rhd_dws_damage_inc_1d
(
`steam_id` STRING ,
`sum_damage` DECIMAL(16 , 2)
) COMMENT '1日伤害表汇总表'
partitioned by (`dt` string comment '统计日期')
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
NULL DEFINED AS ''
LOCATION '/warehouse/rhd/dws/damage_inc';
DROP TABLE IF EXISTS rhd_dws_damage_inc_nd;
CREATE TABLE rhd_dws_damage_inc_nd
(
`steam_id` STRING ,
`sum_damage` DECIMAL(16 , 2) ,
`recent_days` TINYINT COMMENT '最近天数: 1,7,30,90,365'
) COMMENT '1日伤害表汇总表'
partitioned by (`dt` string comment '统计日期')
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
NULL DEFINED AS ''
LOCATION '/warehouse/rhd/dws/damage_inc';
insert overwrite table rhd_dwd_damage_inc partition
(
dt = "2024-08-05"
)
select
steam_id
, damage
from rhd_ods_damage_inc_1d
where
dt = "2024-08-05";
insert overwrite table rhd_dwd_damage_inc_1d partition
(
dt = "2024-08-05"
)
select
steam_id
, sum( damage )
from rhd_dwd_damage_inc
where
dt = '2024-08-05'
group by
steam_id;
insert overwrite table rhd_dws_damage_inc_nd partition
(
dt = "2024-08-05"
)
select
steam_id
, sum( sum_damage )
, recent_day
from
(
select *
from rhd_dws_damage_inc_1d lateral view explode( array( 1 , 7 , 30 , 90 , 365 ) ) tmp as recent_day
) t1
where
dt between date_sub( "2024-08-05" , recent_day - 1 ) and "2024-08-05"
group by
steam_id
, recent_day
之后定期清空ODS的1日数据表和 DWD的明细表就可以了,只要每日执行上面的SQL 改一改dt就可以完成伤害的1日7日30日90日365日数据统计,如果统计指标比较简单 可以直接把 dws的nd表删掉,提高到应用层
这是HQL语法!
