hive、oracle、mysql函数

hive

• 集合函数

1. size(列名)：求array、map长度
2. map_keys(列名)：拿map里所有key值 ,  map_values  拿所有value值
3. array_contains(列名,'足球'):查看array中是否包含
4. sort_array(列名):对array排序

• 类型转换函数

1. cast(列名as int)：列转为int类型

• 日期函数

1. from_unixtime(1632384337,'yyyy-MM-dd HH:mm:ss'), --时间戳转年月日
2. unix_timestamp(), --年月日转时间戳

        unix_timestamp("2021-09-23 16:05:37"), --年月日转时间戳

        unix_timestamp("2021-09-23","yyyy-MM-dd"), --年月日转时间戳（无时分秒）

1. to_date("1970-01-01 00:00:00") = "1970-01-01"：拿时间字符串的日期部分
2. year("1970-01-02"), --拿年  month("1970-01-02")：拿月份

1. dayofmonth("1980-5-4"), --月的第几天
2. weekofyear("1980-5-4"), --年的多少周  
3. datediff("2021-5-6",'2021-5-1'), --两个时间之差
4. date_add("2021-9-23",1),  --加一天，date_sub 减一天
5. current_date(), -- 当前日期   current_timestamp(), -- 当前日期时间
6. add_months("2021-9-23",-3),  -- 月份加减
7. last_day("2021-9-23"),  -- 返回这个月的最后一天
8. next_day("2021-9-23","fr"), -- 今天开始下一个星期几
9. date_format("2021-09-23","yyyy-MM")  -- 时间格式化

• 条件函数

1. If（列判断，TRUE返回值，FALSE返回值）
2. NVL( string1, replace_with)： 判断string1是否为null，如果为null，使用replace_with替换null，否则不做操作！
3. Case when then

        select case gender

        when 'male' then 1

        when 'female' then 2

        else 0

        end

        from ods_personas_raw.ods_users

• 字符函数

1. concat:   字符串拼接。 可以在参数中传入多个string类型的字符串，一旦有一个参数为null，返回Null！
2. concat_ws:   使用指定的分隔符完成字符串拼接！

        concat_ws(',',列名,'aaa')

1. find_in_set('ab','abc,b,ab,e'), -- 在后面位置找ab的位置
2. format_number(12345,2),  -- 格式化数字，保留两位
3. get_json_object('{"a":0,"b":{"c":20,"d":"ccc"},"e":[{"a":6},{"a":7}]}','$.e[1].a')  -- 从json字符串中获取对象
4. instr('abcdefg','a'),  -- 查找第一个出现位置，注意位置为从1开始的
5. locate('a','abcdefabb',5),  -- 从指定位置开始找（从第五个位置开始找a）
6. length('abcd'),  -- 字符长度
7. lower('SBss'),  -- 小写  upper()  大写
8. lpad('9',2,'0')  -- 左边填充0，一共2位--> 09   rpad('9',2,'0')  -- 右边填充
9. trim('  acb  def  '),  -- 去两边空格  左边去:ltrim   右边去：rtrim
10. regexp_replace('  acb  def  ',' ','') -- 正则替换  (1：列值 2：要替换的符号  3：替换的符号)
11. concat(space(5),'a'),  -- 添加空格space(int n)

1. str_to_map('a:b,c:e'),  -- 字符转map
2. substr('abcdefg',1,3)  -- 截1-3
3. translate('abcd','ac','fg'),  -- a换f  c换g
4. initcap('abc cde'), -- 首字母大写，只认空格（空格开头为一个单词）
5. variance(cost)  方差
6. json_tuple('{"a":"hello","b":"world"}',"a","b")  -- 一次性拿json

        Explode  explode(split(列，分隔符))  一般用于侧视图
        lateral view posexplode(split(likes,',')) a as loc,ind

 sql

类 型	MySQL	Oracle
数 值 型	绝对值：abs(-1) 取余：mod（10,3） 向上取整：ceil(1.23)、向下取整：floor（1.23） 四舍五入：round(5.4) 随机值：rand() 返回x的幂y次幂：power(x,y) 取小数后几位:truncate(0.09,2)	绝对值：abs(-1) 取余：mod（10,3） 向上取整：ceil(1.23)、向下取整：floor（1.23） 四舍五入：round(5.4) 随机值：dbms_random.value(1,100) 可以指定范围 dbms_random.string(‘a’,5) 随机五位   返回x的幂y次幂：power(x,y) 取小数后几位:trunc(0.09,2)
字 符 型	字符拼接：concat(‘abf,’def’)、concat_ws 全部大写：upper  小写：lower 查找字符位置：instr（'bcd’,’c’） 字符长度：length lpad（'10’,’2’,’1’）左边加两个1  右边加：rpad() 去空格：trim(‘    abcd   ‘) 替换：replace('hello,world','hello','hehe') 左截取：left('abcd',2),  右截取：right('abcd',2) 字符串截取：substr('abcde',2,3) 字符串反转：reverse('hello')	字符拼接：concat(‘abf,’def’) 全部大写：upper  小写：lower 查找字符位置：instr（'bcd’,’c’,1,3）可以指定始末位置 字符长度：length 左边加：lpad（'10’,’2’,’1’）左边加两个1  右边加：rpad() 去空格：trim(‘    abcd   ‘) 去除* : trim(‘***abcd***’,’*’) 替换：replace('hello,world','hello','hehe') 正则替换： translate('hello,world','ho','a') –> aell,wrld 字符串截取：substr('abcde',2,3) 字符串反转：reverse('hello') 首字母大写：initcap()
日 期	现在时间：sysdate()、now() 日期添加：date_add(now(),interval 3 hour) 可变 最后一天：last_day(‘2020-2-4’) 返回日期之差：datediff(sysdate(),'2000-2-3') 返回一周第几天：dayofweek(sysdate()) 返回一个月多少天：DAYOFMONTH(sysdate()) 返回一年中多少天：DAYOFYEAR(sysdate()) 返回第几季度：quarter(sysdate()) 数据类型转换：cast（‘123‘ as signed）	现在时间：sysdate 、 当前会话时区时间：current_date 添加月份：Add_months(sysdate,36)  添加天数：sysdate+1 最后一天：last_day(to_date(‘2020-2-3’,’yyyy-MM-dd’)) 格式化日期 返回月份之差：months_between(sysdate,to_date('2000-2-3','yyyy-MM-dd'))  时间之差：sysdate-to_date('2000-2-3','yyyy-MM-dd') 返回一个月第几天：extract(day from sysdate) 数据类型转换：cast（‘123‘ as int） 格式化时间：to_date('1999-5-6 16:42:33','yyyy-MM-dd hh24:mi:ss') 四舍五入：round(sysdate) 最近0点日期, round(sysdate,'day') 最近星期日, round(sysdate,'month') 最近月初, round(sysdate,'q') 最近季初日期, round(sysdate,'year') 最近年初日期 返回第一天：trunc(sysdate) 今天日期, trunc(sysdate,'day') 本周星期日, trunc(sysdate,'month') 本月初, trunc(sysdate,'q') 本季初日期, trunc(sysdate,'year') 本年初日期 下一个周日：next_day(sysdate,1) 数字或者时间转为字符串： to_char(1210.73, '9,999.99') to_char(sysdate,'d') 每周第几天 to_char(sysdate,'dd') 每月第几天 to_char(sysdate,'ddd') 每年第几天 to_char(sysdate,'ww') 每年第几周 to_char(sysdate,'mm') 每年第几月 to_char(sysdate,'q') 每年第几季 to_char(sysdate,'yyyy') 年
其他	Ifnull	Nvl