一、HIVE
over中的partition by与group by
name orderdate cost
jack 2017-01-01 10
jack 2017-02-03 23
jack 2017-01-05 46
jack 2017-04-06 42
jack 2017-01-08 55
mart 2017-04-08 62
mart 2017-04-09 68
mart 2017-04-11 75
mart 2017-04-13 94
=======================gruop by=======================
select
name,
count(*)
from
overdemo
where
date_format(orderdate,'yyyy-MM')='2017-04'
group by
name;
--------结果---------
name _c1
jack 1
mart 4
=======================partition by========================
select
name,
count(*) over(partition by name)
from
overdemo
where
date_format(orderdate,'yyyy-MM')='2017-04';
--------结果---------
name count_window_0
jack 1
mart 4
mart 4
mart 4
mart 4
- 差别:partition by 将所有符合过滤条件的列分组选中,故group by 可看成partition by 的去重版
- 执行顺序:group by -> over(),故group by分组去重,再partition by结合聚合函数(count/sum)分区计算
=====同时有group by name和over(partition by name)=====
select
name,
count(*) over(partition by name)
from
overdemo
where
date_format(orderdate,'yyyy-MM')='2017-04'
group by
name;
---------结果--------
name count_window_0
jack 1
mart 1
=====同时有group by name和over()=====over()无参数时结合聚合函数对全量数据进行运算
select
name,
count(*) over()
from
overdemo
where
date_format(orderdate,'yyyy-MM')='2017-04'
group by
name;
----------结果-------
name count_window_0
mart 2
jack 2
- 当over()无参时,结合聚合函数 count(),对得到的数据进行计数,得到的结果=2,并且会在mart和Jack后显示2(因为没有进行partition by name,mart和Jack是在同一个分区的)
- ②当over(partition by name)之后,group by得到的数据会再次进行分区,分为mart和jack,两个分区中进行count()计算,都只有1个,所以在两个分区中分别显示为1
- 此时,再解释上面只使用over(partition by name)时,为什么mart会显示4次,而且count()也是4。因为符合过滤条件的mart有4条,都被partition by分到了一个分区,所以count()的结果就是4,而且over(partition by name)并不会去重,所以显示了4次。
- 综上,over(partition by)可以视为group by的升级版:①会保留所有进入分区的数据,不去重(没有数据损失);②在group by之后执行,可以进行更为复杂个性化的操作
hive 函数分类
关系|数学|逻辑|数值|日期|条件|字符串|集合统计|…
关系运算
1.1 1、等值比较: =
1.2 2、不等值比较:
1.3 3、小于比较: <
1.4 4、小于等于比较: <=
1.5 5、大于比较: >
1.6 6、大于等于比较: >=
1.7 7、空值判断: IS NULL
1.8 8、非空判断: IS NOT NULL
1.9 9、LIKE比较: LIKE
1.10 10、JAVA的LIKE操作: RLIKE
1.11 11、REGEXP操作: REGEXP
2.1 1、加法操作: +
2.2 2、减法操作: -
2.3 3、乘法操作: *
2.4 4、除法操作: /
2.5 5、取余操作: %
2.6 6、位与操作: &
2.7 7、位或操作: |
2.8 8、位异或操作: ^
2.9 9.位取反操作: ~
逻辑运算:
3.1 1、逻辑与操作: AND
3.2 2、逻辑或操作: OR
3.3 3、逻辑非操作: NOT
数值计算
1、取整函数: round
语法: round(double a)
返回值: BIGINT
说明: 返回double类型的整数值部分 (遵循四舍五入)
hive> ``select` `round(3.1415926) ``from` `iteblog;``3``hive> ``select` `round(3.5) ``from` `iteblog;``4``hive> ``create` `table` `iteblog ``as` `select` `round(9542.158) ``from` `iteblog;``hive> describe iteblog;``_c0 ``bigint
2、指定精度取整函数: round
语法: round(double a, int d)
返回值: DOUBLE
说明: 返回指定精度d的double类型
hive> ``select` `round(3.1415926,4) ``from` `iteblog;``3.1416
3、向下取整函数: floor
语法: floor(double a)
返回值: BIGINT
说明: 返回等于或者小于该double变量的最大的整数
hive> ``select` `floor(3.1415926) ``from` `iteblog;``3``hive> ``select` `floor(25) ``from` `iteblog;``25
4、向上取整函数: ceil
语法: ceil(double a)
返回值: BIGINT
说明: 返回等于或者大于该double变量的最小的整数
hive> ``select` `ceil(3.1415926) ``from` `iteblog;``4``hive> ``select` `ceil(46) ``from` `iteblog;``46
5、向上取整函数: ceiling
语法: ceiling(double a)
返回值: BIGINT
说明: 与ceil功能相同
hive> ``select` `ceiling(3.1415926) ``from` `iteblog;``4``hive> ``select` `ceiling(46) ``from` `iteblog;``46
6、取随机数函数: rand
语法: rand(),rand(int seed)
返回值: double
说明: 返回一个0到1范围内的随机数。如果指定种子seed,则会等到一个稳定的随机数序列
hive> ``select` `rand() ``from` `iteblog;``0.5577432776034763``hive> ``select` `rand() ``from` `iteblog;``0.6638336467363424``hive> ``select` `rand(100) ``from` `iteblog;``0.7220096548596434``hive> ``select` `rand(100) ``from` `iteblog;``0.7220096548596434
7、自然指数函数: exp
语法: exp(double a)
返回值: double
说明: 返回自然对数e的a次方
hive> ``select` `exp(2) ``from` `iteblog;``7.38905609893065``<strong>自然对数函数</strong>: ln``<strong>语法</strong>: ln(``double` `a)``<strong>返回值</strong>: ``double``<strong>说明</strong>: 返回a的自然对数``1``hive> ``select` `ln(7.38905609893065) ``from` `iteblog;``2.0
8、以10为底对数函数: log10
语法: log10(double a)
返回值: double
说明: 返回以10为底的a的对数
hive> ``select` `log10(100) ``from` `iteblog;``2.0
9、以2为底对数函数: log2
语法: log2(double a)
返回值: double
说明: 返回以2为底的a的对数
hive> ``select` `log2(8) ``from` `iteblog;``3.0
10、对数函数: log
语法: log(double base, double a)
返回值: double
说明: 返回以base为底的a的对数
hive> ``select` `log(4,256) ``from` `iteblog;``4.0
11、幂运算函数: pow
语法: pow(double a, double p)
返回值: double
说明: 返回a的p次幂
hive> ``select` `pow(2,4) ``from` `iteblog;``16.0
12、幂运算函数: power
语法: power(double a, double p)
返回值: double
说明: 返回a的p次幂,与pow功能相同
hive> ``select` `power(2,4) ``from` `iteblog;``16.0
13、开平方函数: sqrt
语法: sqrt(double a)
返回值: double
说明: 返回a的平方根
hive> ``select` `sqrt(16) ``from` `iteblog;``4.0
14、二进制函数: bin
语法: bin(BIGINT a)
返回值: string
说明: 返回a的二进制代码表示
hive> ``select` `bin(7) ``from` `iteblog;``111
15、十六进制函数: hex
语法: hex(BIGINT a)
返回值: string
说明: 如果变量是int类型,那么返回a的十六进制表示;如果变量是string类型,则返回该字符串的十六进制表示
hive> ``select` `hex(17) ``from` `iteblog;``11``hive> ``select` `hex(‘abc’) ``from` `iteblog;``616263
16、反转十六进制函数: unhex
语法: unhex(string a)
返回值: string
说明: 返回该十六进制字符串所代码的字符串
hive> ``select` `unhex(‘616263’) ``from` `iteblog;``abc``hive> ``select` `unhex(‘11’) ``from` `iteblog;``-``hive> ``select` `unhex(616263) ``from` `iteblog;``abc
17、进制转换函数: conv
语法: conv(BIGINT num, int from_base, int to_base)
返回值: string
说明: 将数值num从from_base进制转化到to_base进制
hive> ``select` `conv(17,10,16) ``from` `iteblog;``11``hive> ``select` `conv(17,10,2) ``from` `iteblog;``10001
18、绝对值函数: abs
语法: abs(double a) abs(int a)
返回值: double int
说明: 返回数值a的绝对值
hive> ``select` `abs``(-3.9) ``from` `iteblog;``3.9``hive> ``select` `abs``(10.9) ``from` `iteblog;``10.9
19、正取余函数: pmod
语法: pmod(int a, int b),pmod(double a, double b)
返回值: int double
说明: 返回正的a除以b的余数
hive> ``select` `pmod(9,4) ``from` `iteblog;``1``hive> ``select` `pmod(-9,4) ``from` `iteblog;``3
20、正弦函数: sin
语法: sin(double a)
返回值: double
说明: 返回a的正弦值
hive> ``select` `sin(0.8) ``from` `iteblog;``0.7173560908995228
21、反正弦函数: asin
语法: asin(double a)
返回值: double
说明: 返回a的反正弦值
hive> ``select` `asin(0.7173560908995228) ``from` `iteblog;``0.8
22、余弦函数: cos
语法: cos(double a)
返回值: double
说明: 返回a的余弦值
hive> ``select` `cos(0.9) ``from` `iteblog;``0.6216099682706644
23、反余弦函数: acos
语法: acos(double a)
返回值: double
说明: 返回a的反余弦值
hive> ``select` `acos(0.6216099682706644) ``from` `iteblog;``0.9
24、positive函数: positive
语法: positive(int a), positive(double a)
返回值: int double
说明: 返回a
hive> ``select` `positive(-10) ``from` `iteblog;``-10``hive> ``select` `positive(12) ``from` `iteblog;``12
25、negative函数: negative
语法: negative(int a), negative(double a)
返回值: int double
说明: 返回-a
hive> ``select` `negative(-5) ``from` `iteblog;``5``hive> ``select` `