hive中如何求两个时间点之间相差月份数，我只知道datediff函数可以求天数

select substr(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1,7)

select substr(current_date,1,7)

       在hive中，我们经常需要进行日期的计算，可是，经常会出现这样一种情况，hive仓库中日期的存储格式是yyyyMMdd,例如20200520，在进行日期的计算时，hive支持的函数例如，add_months 仅识别yyyy-MM-dd格式的函数，如果输入yyyyMMdd的参数，返回结果是null。所以我们在对日期的字段进行计算时，优先需要进行格式的转换，将yyyyMMdd转换成yyyy-MM-dd格式

转换方式如下：亲测可用

DATE(from_unixtime(unix_timestamp('20171205','yyyymmdd'),'yyyy-mm-dd'))

目前没有这样的udf，你可能需要自己用java写个dayofweek函数，下面的连接是目前hive所支持的日期函数

>

日期变换：

(1)dt转日期

to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd')))

(2)日期转dt

regexp_replace('${date}','-','')

(3)dt转当月1号日期

to_date(from_unixtime(unix_timestamp(concat(substr('${dt}',1,6),'01'),'yyyyMMdd')))

trunc(to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd'))),'MM')

-- 下月1号日期

trunc(add_months(to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd'))),1),'MM')

(4)dt转当周星期一日期

next_day(date_add(to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd'))), -7), 'Mo')

date_sub(next_day(to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd'))),'MO'),7)

-- 下周星期一日期

next_day(to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd'))),'MO')

(5)dt前六天日期(dt为星期天时得到的是本周周一的日期)

date_add(to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd'))), -6)

(5)dt转当季第一天日期

if(length(floor(substr('${dt}',5,2)/31)3+1)=1,concat(substr('${dt}',1,4),'-0',floor(substr('${dt}',5,2)/31)3+1,'-01'),concat(substr('${dt}',1,4),'-',floor(substr('${dt}',5,2)/31)3+1,'-01'))

(6)dt转半年第一天日期

if(length(floor(substr('${dt}',5,2)/61)6+1)=1,concat(substr('${dt}',1,4),'-0',floor(substr('${dt}',5,2)/61)6+1,'-01'),concat(substr('${dt}',1,4),'-',floor(substr('${dt}',5,2)/61)6+1,'-01'))

(7)dt转当年1号日期

concat(substr('${dt}',1,4),'-01-01')(8)在同时有日周月粒度时要注意数据的时间范围，有时每月的第一个自然周会跨月，比如2019年3月的第一周的日期是20190225-20190303where agent_business_date between date_add_day('${dt}',-31) and to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd')))where dt between regexp_replace(date_add_day('${dt}',-31),'-','') and '${dt}'

------------------------------------------------------------------------------------------

-- 日期维度表表结构edw_publicdim_esf_edw_pub_date

------------------------------------------------------------------------------------------

col_name data_type comment

------------------------------------------------------------------------

calendar_date string 日期，格式为"YYYY-MM-DD"

week_english_name string 星期英文名

week_chinese_name string 星期中文名

day_of_week_number int 所属一周当中的第几天

calendar_month_code string 日期所属月份，格式为"YYYY-MM"

calendar_month_number int 所属月份数字

month_english_name string 月份英文名

month_chinese_name string 月份中文名

day_of_month_number int 所属月份当中的第几天

calendar_quater_code string 日期所属季度，格式为"YYYY-QT"

calendar_quater_number int 所属季度数字

day_of_quater_number int 所属季度当中的第几天

calendar_half_year_code string 日期所属半年，格式为"YYYY-HY"

calendar_half_year_number int 所属半年数字，1为上半年，2为下半年

calendar_year_code string 日期所属年份，格式为"YYYY"

day_of_year_number int 所属年份当中的第几天

work_day_flag string 工作日标志: Y - 是/ N - 否

holiday_flag string 节假日标志: Y - 是/ N - 否

-- 日期维度表的使用

-- 当天日期

SELECT

calendar_date

FROM

edw_publicdim_esf_edw_pub_date

WHERE

calendar_date = regexp_replace('${dt}','(\\d{4})(\\d{2})(\\d{2})','$1-$2-$3')

-- Finereport中日周月季半年年 各周期末日期的算法

select

${if(粒度 == 1," case when date(max(calendar_date))>=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date(max(calendar_date)) end as period_end_date","")}

${if(粒度 == 2," distinct case when day_of_week_number = 1 and date_add('day',6,date(calendar_date)) >=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) when day_of_week_number = 7 and date(calendar_date) >=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) when day_of_week_number = 1 then date_add('day',6,date(calendar_date)) when day_of_week_number = 7 then date(calendar_date) else date(calendar_date) end as period_end_date ","")}

${if(粒度 == 3," case when date(max(calendar_date))>=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date(max(calendar_date)) end as period_end_date ","")}

${if(粒度 == 4," case when date(max(calendar_date))>=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date(max(calendar_date)) end as period_end_date ","")}

${if(粒度 == 5," case when date(max(calendar_date))>=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date(max(calendar_date)) end as period_end_date ","")}

${if(粒度 == 6," case when date(max(calendar_date))>=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date(max(calendar_date)) end as period_end_date ","")}

from

edw_publicdim_esf_edw_pub_date

where calendar_date >= '${开始时间}' and calendar_date <= '${结束时间}'

${if(粒度 == 1," group by calendar_date ","")}

${if(粒度 == 2," and day_of_week_number in (1,7) ","")}

${if(粒度 == 3," group by calendar_month_code ","")}

${if(粒度 == 4," group by calendar_quater_code ","")}

${if(粒度 == 5," group by calendar_year_code ","")}

${if(粒度 == 6," group by calendar_half_year_code ","")}

-- Finereport中日周月季半年年 各周期期初期末日期的算法(这种计算方法当前日期是20190330，输入的日期范围是2019-03-01至2091-03-28则输出的月日期范围是2019-03-29)

select

${if(粒度 == 1,"date(calendar_date) as period_start_date, date(calendar_date) as period_end_date ","")}

${if(粒度 == 2,"case when day_of_week_number = 1 then date(calendar_date) when day_of_week_number = 7 then date_add('day',-6, date(calendar_date)) end as period_start_date, case when day_of_week_number = 1 and date_add('day',6, date(calendar_date)) >=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) when day_of_week_number = 7 and date(calendar_date)>=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) when day_of_week_number = 1 then date_add('day',6, date(calendar_date)) when day_of_week_number = 7 then date(calendar_date) end as period_end_date ","")}

${if(粒度 == 3,"date(calendar_date) as period_start_date, case when date_add('day',-day(date(calendar_date)),date_add('month',1,(date(calendar_date))))>=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date_add('day',-day(date(calendar_date)),date_add('month',1,(date(calendar_date)))) end as period_end_date ","")}

${if(粒度 == 4,"calendar_date as period_start_date,date_add('day',-1,date_add('month',1,date(substr(calendar_date,1,4)||'-'||cast(cast(floor(cast(substr(calendar_date,6,2) as int)/31)3+3 as int) as varchar)||'-01'))) as period_end_date ","")}

${if(粒度 == 5,"date(concat(substr(calendar_date,1,4),'-01','-01')) as period_start_date,case when date(concat(substr(calendar_date,1,4),'-12','-31'))>= date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date(concat(substr(calendar_date,1,4),'-12','-31')) end as period_end_date","")}

${if(粒度 == 6,"date(min(calendar_date)) as period_start_date,case when date(max(calendar_date))>= date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date(max(calendar_date)) end as period_end_date","")}

from

edw_publicdim_esf_edw_pub_date

where calendar_date >= '${开始时间}' and calendar_date <= '${结束时间}'

${if(粒度 == 1," and 1 = 1 ","")}

${if(粒度 == 2," and day_of_week_number in (1,7) ","")}

${if(粒度 == 3," and day_of_month_number = 1","")}

${if(粒度 == 4," and day_of_quater_number = 1","")}

${if(粒度 == 5," and day_of_year_number = 1","")}

${if(粒度 == 6," group by calendar_half_year_code ","")}

------------------------------------------------------------------------------------------------

-- 根据输入的时间范围计算期末日期

------------------------------------------------------------------------------------------------

select t1

from

-- 日周月季年半年不同粒度的统计数据各存为了一张表

edw_reportsadm_xf_edw_house_sub_project_report_00${dtype}ly_di t1--日报

join

(

-- 日

SELECT

calendar_date

FROM

edw_publicdim_esf_edw_pub_date

WHERE

calendar_date BETWEEN '${bdt}' AND '${edt}'

AND '${dtype}' = '1_dai'

UNION

-- 月

SELECT

MAX(calendar_date) AS calendar_date

FROM

edw_publicdim_esf_edw_pub_date

WHERE

calendar_date BETWEEN '${bdt}' AND '${edt}'

AND '${dtype}' = '2_dai'

GROUP BY

calendar_month_number

UNION

-- 周

SELECT

calendar_date

FROM

edw_publicdim_esf_edw_pub_date

WHERE

calendar_date BETWEEN '${bdt}' AND '${edt}'

AND day_of_week_number = 7

AND '${dtype}' = '3_dai'

UNION

-- 季

SELECT

MAX(calendar_date) AS calendar_date

FROM

edw_publicdim_esf_edw_pub_date

WHERE

calendar_date BETWEEN '${bdt}' AND '${edt}'

AND '${dtype}' = '4_dai'

GROUP BY

calendar_quater_code

UNION

-- 年

SELECT

MAX(calendar_date) AS calendar_date

FROM

edw_publicdim_esf_edw_pub_date

WHERE

calendar_date BETWEEN '${bdt}' AND '${edt}'

AND '${dtype}' = '5_dai'

GROUP BY

calendar_year_code

UNION

-- 半年

SELECT

MAX(calendar_date) AS calendar_date

FROM

edw_publicdim_esf_edw_pub_date

WHERE

calendar_date BETWEEN '${bdt}' AND '${edt}'

AND '${dtype}' = '6_dai'

GROUP BY

calendar_half_year_code

UNION

SELECT

MAX(calendar_date) AS calendar_date

FROM

edw_publicdim_esf_edw_pub_date

WHERE

calendar_date BETWEEN '${bdt}' AND '${edt}'

ORDER BY

calendar_date

) t2

on t1statistic_date = t2calendar_date

where

statistic_date between '${bdt}' and '${edt}'

${if(len(tenant_name) == 0,"","and house_sub_project_organization_short_name = '" + tenant_name + "'")}

${if(len(status) == 0,"","and house_sub_project_cooperation_status_code = " + status)}

${if(len(tenant_type) == 0,"","and house_sub_project_organization_business_type_code= " + tenant_type)}

${if(len(project_type) == 0,"","and house_sub_project_cooperation_type_code= " + project_type)}

order by statistic_date

方法一：

date_format：将日期进行格式化。注意月份是大写MM，小写mm代表成分钟了。

方法二：split+concat 不知是啥

方法三：from_unixtime

方法四：translate函数

用法：translate(input, from, to)，input即是要被替换的整个 字符 ，from这里也是你想要替换的字符，注意这里的字符不是字符串，to 即想用哪些字符替换或者删除

方法五：regexp_replace函数

函数说明：

NVL ：给值为 NULL 的数据赋值，

它的格式是

NVL( string1, replace_with)。

它的功能是如果string1 为 NULL，则 NVL 函数返回 replace_with 的值，否则返回 string1 的值，如果两个参数都为 NULL ，则返回NULL。

date_format:格式化时间

date_add:时间跟天数相加

date_sub:时间跟天数相减，类似与add，使用一个就ok

select date_sub('2019-06-29',5);

datediff:两个时间相减

select datediff('2019-06-29','2019-06-24');

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

CONCAT_WS(separator, str1, str2,)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。

分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL， 返回值也将为 NULL。

这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

select concat_ws('-',str1,str2) from XX; //str为string类型的列名

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，

产生 array 类型字段。

练习：把星座和血型一样的人归类到一起

第一步转换：

白羊座,A 孙悟空

射手座,A 大海

白羊座,B 宋宋

白羊座,A 猪八戒

射手座,A 凤姐

合并

EXPLODE(col)：将hive 一列中复杂的 array 或者 map 结构拆分成多行。

LATERAL VIEW //侧写

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和 split, explode 等UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

练习：将**分类中的数组数据展开

select explode(category) from movie_info;

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化；

/ 在over(里面使用) /

CURRENT ROW：当前行；

n PRECEDING：往前 n 行数据；

n FOLLOWING：往后 n 行数据；

UNBOUNDED：起点，

UNBOUNDED PRECEDING 表示从前面的起点，UNBOUNDEDFOLLOWING 表示到后面的终点；

/ 在over外面使用/

LAG(col,n)：往前第n 行数据；

LEAD(col,n)：往后第 n 行数据；

NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从 1 开始，

对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。

数据：

一、查询在 2017 年 4 月份购买过的顾客及总人数

group分组一组一个值，over给每一条数据独立开窗

查询顾客的购买明细及购买总额

要将 cost 按照日期进行累加

先排序

再累加

查询顾客上次的购买时间

下次的购买时间

查询前 20%时间的订单信息

RANK() 排序相同时会重复，总数不会变，可以并列

DENSE_RANK() 排序相同时会重复，总数会减少

ROW_NUMBER() 会根据顺序计算

练习：计算每门学科成绩排名

计算每门学科成绩排名

以上就是关于hive中如何求两个时间点之间相差月份数，我只知道datediff函数可以求天数全部的内容，包括:hive中如何求两个时间点之间相差月份数，我只知道datediff函数可以求天数、Hive sql及窗口函数、hive怎么把日期转化成yyyy-mm格式等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！