date_add hive

--将存储过程改写成

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(

SUB

STR(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;

版权声明：

本文来源网络，所有图片文章版权属于原作者，如有侵权，联系删除。

本文网址：https://www.mushiming.com/mjsbk/9315.html