修改完整版的sql存储（积分兑换脚本），改写出可执行的hive脚本create procedure "rptdev"."sp_wjq_jf_value_dhfz_show"( in @data_date integer default cast("dateformat"("now"()-1,'yyyymmdd') as integer), in @is_run_flag1 integer default 1, in @is_run_flag2 integer default 10 ) on exception resume /****************************************************************** -- Purpose : 兑换积分分析报表 -- Auther: xxx -- Date : 20210923 *******************************************************************/ begin declare @stat_date varchar(8); --统计日期 declare @month_id integer; --统计月份 declare @month_first_date varchar(8); --统计月的第一天 declare @month_last_date varchar(8); --统计月的最后一天 declare @last_month_first_date varchar(8); --上月的第一天 declare @last_month_last_date varchar(8); --上月的最后一天 declare @last_month integer; --上月 declare @last_last_month integer; --上上月 declare @last_month_this_day varchar(8); --上月当天 declare @last_year_this_month integer; --上年当月 declare @this_year_first_month integer; --本年首月 declare @last_year_last_month integer; --上年尾月 set @stat_date = convert(varchar(8),@data_date); set @month_id = @data_date/100; set @month_first_date = "dateformat"(@data_date,'yyyymm01'); --统计月的第一天 set @month_last_date = "dateformat"("dateadd"("dd",-1,"dateformat"("dateadd"("mm",1,@data_date),'yyyymm01')),'yyyymmdd'); --统计月的最后一天 set @last_month_first_date = "dateformat"("dateadd"("mm",-1,@data_date),'yyyymm01'); --上月第一天 set @last_month_last_date = "dateformat"("dateadd"("dd",-1,"dateformat"("dateadd"("mm",1,@data_date),'yyyymm01')),'yyyymmdd'); set @last_month = cast("dateformat"("dateadd"("month",-1,@data_date),'yyyymm') as integer); --上月 set @last_last_month = cast("dateformat"("dateadd"("month",-2,@data_date),'yyyymm') as integer); --上上月 set @last_month_this_day = "dateformat"("dateadd"("mm",-1,convert(date,@data_date)),'yyyymmdd'); --上月当天 set @last_year_this_month = convert(integer,"dateformat"("dateadd"("yy",-1,@last_month_this_day),'yyyymm')); --上年当月

--将存储过程改写成Hive脚本
--设置变量
SET data_date = from_unixtime(unix_timestamp()-86400,'yyyyMMdd');
SET is_run_flag1 = 1;
SET is_run_flag2 = 10;

--获取日期相关变量
SET stat_date = CAST(data_date AS VARCHAR(8));
SET month_id = CAST(data_date/100 AS INT);
SET month_first_date = CONCAT(SUBSTR(data_date, 1, 6), '01');
SET month_last_date = DATE_FORMAT(DATE_ADD(month_first_date, INTERVAL 1 MONTH), 'yyyyMMdd') - 1;
SET last_month_first_date = DATE_FORMAT(DATE_ADD(month_first_date, INTERVAL -1 MONTH), 'yyyyMMdd');
SET last_month_last_date = DATE_FORMAT(DATE_ADD(month_first_date, INTERVAL -1 DAY), 'yyyyMMdd');
SET last_month = CAST(DATE_FORMAT(DATE_ADD(data_date, INTERVAL -1 MONTH), 'yyyyMM') AS INT);
SET last_last_month = CAST(DATE_FORMAT(DATE_ADD(data_date, INTERVAL -2 MONTH), 'yyyyMM') AS INT);
SET last_month_this_day = DATE_FORMAT(DATE_ADD(data_date, INTERVAL -1 MONTH), 'yyyyMMdd');
SET last_year_this_month = CAST(DATE_FORMAT(DATE_ADD(last_month_this_day, INTERVAL -1 YEAR), 'yyyyMM') AS INT);
SET this_year_first_month = CAST(DATE_FORMAT(DATE_ADD(data_date, INTERVAL -MONTH(data_date) MONTH), 'yyyyMM') AS INT);
SET last_year_last_month = CAST(DATE_FORMAT(DATE_ADD(data_date, INTERVAL -1 YEAR), 'yyyyMM') AS INT);

--创建临时表
DROP TABLE IF EXISTS tmp_jf_value_dhfz_show;
CREATE TABLE tmp_jf_value_dhfz_show AS
SELECT
t1.cust_id,
t1.cust_name,
t2.channel_name,
t1.consume_value,
t1.exchange_value,
t1.this_month_value,
t1.last_month_value,
t1.last_last_month_value,
t1.last_year_this_month_value,
t1.this_year_first_month_value,
t1.last_year_last_month_value,
t1.month_id
FROM
(
--获取本月和上月的兑换积分数
SELECT
cust_id,
cust_name,
SUM(CASE WHEN consume_date >= month_first_date AND consume_date <= month_last_date THEN consume_value ELSE 0 END) AS consume_value,
SUM(CASE WHEN exchange_date >= month_first_date AND exchange_date <= month_last_date THEN exchange_value ELSE 0 END) AS exchange_value,
SUM(CASE WHEN consume_date >= last_month_first_date AND consume_date <= last_month_last_date THEN consume_value ELSE 0 END) AS last_month_value,
SUM(CASE WHEN exchange_date >= last_month_first_date AND exchange_date <= last_month_last_date THEN exchange_value ELSE 0 END) AS last_month_exchange_value,
SUM(CASE WHEN consume_date >= DATE_FORMAT(DATE_ADD(last_month_first_date, INTERVAL -1 MONTH), 'yyyyMMdd')
AND consume_date <= DATE_FORMAT(DATE_ADD(last_month_last_date, INTERVAL -1 MONTH), 'yyyyMMdd') THEN consume_value ELSE 0 END) AS last_last_month_value,
SUM(CASE WHEN consume_date >= DATE_FORMAT(DATE_ADD(last_month_this_day, INTERVAL -1 YEAR), 'yyyyMMdd')
AND consume_date <= DATE_FORMAT(DATE_ADD(last_month_this_day, INTERVAL -1 YEAR MONTH), 'yyyyMM') THEN consume_value ELSE 0 END) AS last_year_this_month_value,
SUM(CASE WHEN consume_date >= DATE_FORMAT(DATE_ADD(data_date, INTERVAL -MONTH(data_date) MONTH), 'yyyyMMdd')
AND consume_date <= month_last_date THEN consume_value ELSE 0 END) AS this_month_value,
SUM(CASE WHEN consume_date >= DATE_FORMAT(DATE_ADD(data_date, INTERVAL -1 YEAR), 'yyyyMM')
AND consume_date <= DATE_FORMAT(DATE_ADD(data_date, INTERVAL -1 MONTH), 'yyyyMM') THEN consume_value ELSE 0 END) AS last_year_last_month_value,
SUM(CASE WHEN consume_date >= DATE_FORMAT(DATE_ADD(data_date, INTERVAL -MONTH(data_date) MONTH), 'yyyyMM')
AND consume_date <= DATE_FORMAT(DATE_ADD(data_date, INTERVAL -1 MONTH), 'yyyyMM') THEN consume_value ELSE 0 END) AS this_year_first_month_value,
month_id
FROM
jf_value_detail
GROUP BY
cust_id,
cust_name,
month_id
) t1
LEFT JOIN
(
--获取渠道名称
SELECT
channel_id,
channel_name
FROM
channel_info
) t2
ON t1.channel_id = t2.channel_id;

--根据条件筛选记录
DROP TABLE IF EXISTS jf_value_dhfz_show;
CREATE TABLE jf_value_dhfz_show AS
SELECT
cust_id,
cust_name,
channel_name,
consume_value,
exchange_value,
this_month_value,
last_month_value,
last_last_month_value,
last_year_this_month_value,
this_year_first_month_value,
last_year_last_month_value,
month_id
FROM
tmp_jf_value_dhfz_show
WHERE
((is_run_flag1 = 1 AND consume_value > 0) OR (is_run_flag1 = 0))
AND ((is_run_flag2 = 10 AND channel_id = 10) OR (is_run_flag2 <> 10 AND channel_id <> 10));

--删除临时表
DROP TABLE IF EXISTS tmp_jf_value_dhfz_show;