hive 上 date_add(current_date(),-1) as statis_date 改成yyyymmdd

--将存储过程改写成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;

在Hive SQL中，如果你想比较一个日期字段`effdate`处理后的格式与另一个固定的日期格式，你可以使用类似下面的表达式： ```sql SELECT DATE_FORMAT( TO_DATE( CAST(effdate AS STRING), -- Cast effdate to string before converting to date 'yyyyMMdd' ), 'YYYYMM' ) = DATE_FORMAT( DATE_SUB(DATE('2024-11-05'), INTERVAL 1 MONTH), -- Subtracting 1 month from the reference date 'YYYYMM' ) AS result FROM your_table; -- Replace your_table with the actual table name 这个查询首先将`effdate`转换为字符串，然后解析为日期并格式化为'YYYYMM'形式，再与2024年11月前一月的日期进行比较。