hive、oracle、mysql函数

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

类型转换函数 cast(列名as int)：列转为int类型

日期函数 from_unixtime(1632384337,'yyyy-MM-dd HH:mm:ss'), --时间戳转年月日unix_timestamp(), --年月日转时间戳

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

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

to_date("1970-01-01 00:00:00") = "1970-01-01"：拿时间字符串的日期部分year("1970-01-02"), --拿年  month("1970-01-02")：拿月份 dayofmonth("1980-5-4"), --月的第几天weekofyear("1980-5-4"), --年的多少周  datediff("2021-5-6",'2021-5-1'), --两个时间之差date_add("2021-9-23",1),  --加一天，date_sub 减一天current_date(), -- 当前日期   current_timestamp(), -- 当前日期时间add_months("2021-9-23",-3),  -- 月份加减last_day("2021-9-23"),  -- 返回这个月的最后一天next_day("2021-9-23","fr"), -- 今天开始下一个星期几date_format("2021-09-23","yyyy-MM")  -- 时间格式化

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

        select case gender

        when 'male' then 1

        when 'female' then 2

        else 0

        end

        from ods_personas_raw.ods_users

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

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

find_in_set('ab','abc,b,ab,e'), -- 在后面位置找ab的位置format_number(12345,2),  -- 格式化数字，保留两位get_json_object('{"a":0,"b":{"c":20,"d":"ccc"},"e":[{"a":6},{"a":7}]}','$.e[1].a')  -- 从json字符串中获取对象instr('abcdefg','a'),  -- 查找第一个出现位置，注意位置为从1开始的locate('a','abcdefabb',5),  -- 从指定位置开始找（从第五个位置开始找a）length('abcd'),  -- 字符长度lower('SBss'),  -- 小写  upper()  大写lpad('9',2,'0')  -- 左边填充0，一共2位--> 09   rpad('9',2,'0')  -- 右边填充trim('  acb  def  '),  -- 去两边空格  左边去:ltrim   右边去：rtrimregexp_replace('  acb  def  ',' ','') -- 正则替换  (1：列值 2：要替换的符号  3：替换的符号)concat(space(5),'a'),  -- 添加空格space(int n) str_to_map('a:b,c:e'),  -- 字符转mapsubstr('abcdefg',1,3)  -- 截1-3translate('abcd','ac','fg'),  -- a换f  c换ginitcap('abc cde'), -- 首字母大写，只认空格（空格开头为一个单词）variance(cost)  方差json_tuple('{"a":"hello","b":"world"}',"a","b")  -- 一次性拿json

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

类

型

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’）字符长度：lengthlpad（'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