Hive SQL计算5天前的时间戳

Hive中给的时间戳函数不多，加上公司的数据仓库还不完全支持。

在计算5天前的时间戳的时候，我的第一次写法是：

但是后来发现这样写行不通，主要问题是date_sub这个函数只返回日期，没有时间。

后来发现可以直接用当时的时间戳减去5天的秒数，真的是2了，这个之前没想到。

select floor((unix_timestamp(substr('201402',1,6),'yyyyMM')-unix_timestamp(substr('20141112',1,6),'yyyyMM'))/2629495);

解释：

格式：两个时间的格式自己随意指定

数字2629495解释。一年有365天4小时58分56秒。折算下秒数再除以12，得到2629495。

然后自己理解下这个数字就明白了。

函数说明：

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() 会根据顺序计算

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

计算每门学科成绩排名

   说明：

   unix_timestamp() 是hive系统时间，格式是timestamp，精确到秒。

   unix_timestamp(ymdhms) 是把时间转换成timestamp格式，是 2018-05-23 07:15:50 格式。

   unix_timestamp() - unix_timestamp(ymdhms) 是两个时间转换为timestamp之后相减，timestamp单位是秒，相减之后是两个时间之间相差的秒数。

   CAST((unix_timestamp() - unix_timestamp(ymdhms)) % 60 AS int) 是相差的秒数。

   CAST((unix_timestamp() - unix_timestamp(ymdhms)) / 60 AS int) % 60 是相差的分钟数。

   CAST((unix_timestamp() - unix_timestamp(ymdhms)) / (60 60) AS int) % 24 是相差的小时数。

   concat(CAST((unix_timestamp() - unix_timestamp(ymdhms)) / (60 60 24) AS int) 是相差的天数。

   查询结果如下：

参考

以上就是关于Hive SQL计算5天前的时间戳全部的内容，包括:Hive SQL计算5天前的时间戳、hive中如何求两个时间点之间相差月份数，我只知道datediff函数可以求天数、Hive常用查询函数等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！