今天想到了一个方法,可以只用一个shell 和一个sql来做一个“模型”跑多个不同时间范围的数据,并插入到多个表。
create table tmp_wlh_cc_***_${day_type}day_0 as
select
data_date,
company_code,
industry_type,
sku_no,
sg_rep
from rpt_cc_***
where to_date(data_date) >= (CASE '${day_type}' WHEN 7 THEN date_add('${end_y_m_d}',-13)
WHEN 14 THEN date_add('${after_week1}',-7)
WHEN 30 THEN date_add('${end_y_m_d}',-59)
WHEN 60 THEN '${before_last_month_y_m_01}'
ELSE '' END)
and to_date(data_date) <= (CASE '${day_type}' WHEN 7 THEN '${end_y_m_d}'
WHEN 14 THEN '${after_week7}'
WHEN 30 THEN '${end_y_m_d}'
WHEN 60 THEN '${last_month_y_m_31}'
ELSE '' END)
但是写完后发现 根本不用再where后面写这么多乱七八糟的东西,完全可以把时间的范围控制在shell中完成、
方法:
shell脚本 中写如下变量,hql中用 day_type做判断 选择不同的变量(day_type=$3、date_flage=$5 )
PS: 周 取数日期展示年周,跑数日期展示datetime
月 取数日期展示年月,跑数日期展示datetime
while [ 1 ]
do
if [ -f ${depend_name} ]; then
#每天跑一次
sh ${currPath}/${scr_name}.sh ${inc_y_m_d} ${inc_y_m_2d} 1 ${scr_name}_day 0 &
sh ${currPath}/${scr_name}.sh ${inc_y_m_7d} ${inc_y_m_14d} 7 ${scr_name}_7day 0 &
sh ${currPath}/${scr_name}.sh ${before_after_week1} ${after_week7} 14 ${scr_name}_14day ${last_week_num} &
sh ${currPath}/${scr_name}.sh ${inc_y_m_30d} ${inc_y_m_60d} 30 ${scr_name}_30day 0 &
sh ${currPath}/${scr_name}.sh ${before_last_month_y_m_01} ${last_month_y_m_31} 60 ${scr_name}_60day ${last_month_y_m} &