HIVE sql 学习（2）

目录

 

1. SQL分组进阶:利用Grouping sets完成多维重组

2.Hive UNION ALL的使用和Hive子查询

3.SUBSTRING(grab_time, 1, 13)

4. 新建一个列

5. 拼接几个列--多列变一列

6. 多行变一行

7.时间戳

---

 

1. SQL分组进阶:利用Grouping sets完成多维重组

需求：统计某个特定行为每日的数据以及每个月份的汇总数据(2019-04-01和2019-04-30的每日销售额及月份汇总)

之前的时候都是写个group by day然后再写个group by month ，费时费力。

应用上这个知识点可以在一次运行就完成数据提取了

group by day, month 

grouping sets((month,day),(month)) 

4月份汇总时，结果中对应的day 为null，结果如下：

month	day	销售额
04	null	*
04	01	*
04	02	*
......	......

参考链接：http://zhouchen.tech/2018/08/12/SQL%E5%88%86%E7%BB%84%E8%BF%9B%E9%98%B6/

2.Hive UNION ALL的使用和Hive子查询

结论：

   1. 在Hive-0.12.0及之前的版本中，Union只能在子查询中使用，在Hive-0.13.0版本中，去除了该限制，Union查询可以作为独立的查询使用。

    2.Hive 1.2.0之前的版本仅支持UNION ALL，其中重复的行不会被删除。 
       Hive 1.2.0和更高版本中，UNION的默认行为是从结果中删除重复的行。

    3.用来合并多个select的查询结果，需要保证select中字段须一致，每个select语句返回的列的数量和名字必须一样，否则，一个语法错误会被抛出。

union语句：

SELECT t3.col
FROM (
  SELECT a+b AS col
  FROM t1
  UNION ALL
  SELECT c+d AS col
  FROM t2
) t3

参考文章：https://blog.youkuaiyun.com/skywalker_only/article/details/39294183

https://blog.youkuaiyun.com/u010521842/article/details/77461537

https://blog.youkuaiyun.com/buster2014/article/details/50143247

https://blog.youkuaiyun.com/liuguangfudan/article/details/78623074

 

3.SUBSTRING(grab_time, 1, 13)

字符串截取函数：

4. 新建一个列

select
  id,
  1 as num --表示新建一个列叫num其中的值全部为1
FROM
    db;

5. 拼接几个列--多列变一列

concat_ws('-', year, month, day, hour) 

https://blog.youkuaiyun.com/waiwai3/article/details/79071544  --hive中的concat，concat_ws

select user
    ,concat_ws(',',
                    collect_set(concat（order_type,'(',order_number,')'）)                
                )  order 
from table 
group by user

6. 多行变一行

collect_set、collect_list用法

collect_set去除重复元素；collect_list不去除重复元素

+------+-----------------------------------+------------------------------------+
|gender|concat_ws(,, collect_set(children))|concat_ws(,, collect_list(children))|
+------+-----------------------------------+------------------------------------+
|female|                             no,yes|                    no,yes,no,no,yes|
|  male|                             no,yes|                    no,yes,no,yes,no|
+------+-----------------------------------+------------------------------------+

参照链接：https://blog.youkuaiyun.com/YWF331/article/details/80693382

 

7.时间戳

注意from_unixtime() 函数中的日期形式的格式中MM和mm是不同的，MM表示月份，mm表示分钟。

 

select unix_timestamp('2019-01-01 00:03:00');  --1546272180

select from_unixtime(1546272180,'yyyy-MM-dd HH:mm:ss') ; --2019-01-01 00:03:00

select from_unixtime(1546272180,'yyyy-MM-dd') ;    --2019-01-01    #根据时间戳能够得出yyyy、MM、dd、HH、mm、ss的值，                                                                                                                   根据后面的设定给出结果

select unix_timestamp('2019-03-01','yyyy-mm-dd');   --1546272180

select unix_timestamp('2019-03-01','yyyy-MM-dd');  --1546272000 

 

sublime text的快捷键：
command+u-- 删除刚刚输入的代码

command+shift+d--复制整行