Impala常见函数操作

常见日期函数操作:

hive 日期函数
增加月份
add_months(timestamp date, int months)
add_months(timestamp date, bigint months)
Return type: timestamp
usage:add_months(now(),1)
增加日期
adddate(timestamp startdate, int days), 
adddate(timestamp startdate, bigint days)
Return type: timestamp
usage:adddate(now(),1)
当前时间戳
current_timestamp()和now()等价
日期相减
datediff(string enddate, string startdate)
Return type: int
usage:datediff("2018-08-05", "2018-08-03")
得到天，得到月份
day(string date)
Return type: int
usage: day("2018-08-05")
得到星期英文
dayname(string date) 
Return type: string
usage:dayname("2018-08-05") Sunday
得到这一天是这周的第几天
dayofweek(string date)  1 (Sunday) to 7 (Saturday).
Return type: int
usage:dayofweek("2018-08-06")
加天数
days_add(timestamp startdate, int days) 
Return type: timestamp
usage:days_add(now(),2)
减天数
days_sub(timestamp startdate, int days)
Return type: timestamp
usage:days_sub(now(), 2)
格式化日期
from_unixtime(bigint unixtime[, string format])
Return type: string
注意参数
usage:from_unixtime(1392394861,"yyyy-MM-dd");
得到小时
hour(string date)
Return type: int
usage:hour("2018-08-06 12:32:54")
增加小时
hours_add(timestamp date, int hours)
Return type: timestamp
usage:hours_add(now(),2)
减少
hours_sub(timestamp date, int hours)
Return type: timestamp
usage:hours_sub(now(),2)
得到分钟
minute(string date)
Return type: int
usage:minute(now())
增加分钟
minutes_add(timestamp date, int minutes)
Return type: timestamp
usage:minutes_add(now(),2)
减少分钟
minutes_sub(timestamp date, int minutes)
Return type: timestamp
usage:minutes_sub(now(),2)
得到月份
month(string date)
Return type: int
usage:month("2018-08-06 12:32:54")
月份相加
months_add(timestamp date, int months)
Return type: timestamp
usage:months_add(now(),3)
减月份
months_sub(timestamp date, int months)
Return type: timestamp
months_sub(now(),3)
得到秒
second(string date)
Return type: int
秒加
seconds_add(timestamp date, int seconds)
Return type: timestamp
秒减
seconds_sub(timestamp date, int seconds)
Return type: timestamp
得到日期(也可以时间戳转日期)
to_date(now())
得到1970到今秒
unix_timestamp(), 
unix_timestamp(string datetime),
unix_timestamp(string datetime, string format),
unix_timestamp(timestamp datetime)
Return type: bigint
得到这周是这年的多少周
weekofyear(string date)
Return type: int
usage:weekofyear("2018-08-06 12:32:54")
周加
weeks_add(timestamp date, int weeks)
Return type: timestamp
usage:weeks_add("2018-08-06 12:32:54", 1)
周减
weeks_sub(timestamp date, int weeks)
Return type: timestamp
usage:weeks_sub("2018-08-06 12:32:54", 1)
得到年
year(string date)
Return type: int
年加
years_add(timestamp date, int years)
Return type: timestamp
年减
years_sub(timestamp date, int years)
Return type: timestamp
等到当前年月：
from_unixtime(unix_timestamp() ,'yyyyMM')
得到去年当前月份
from_timestamp( months_sub(now(),13),'yyyyMM')

case when

code:
 CASE
                 WHEN (tax<50000) THEN '5万以下'
                 WHEN (tax<100000
                       AND tax>=50000) THEN '5-10万'
                 WHEN (tax<200000
                       AND tax>=100000) THEN '10-20万'
                 WHEN (tax<500000
                       AND tax>=200000) THEN '20-50万'
                 WHEN (tax<1000000
                       AND tax>=500000) THEN '50-100万'
                 WHEN (tax<2000000
                       AND tax>=1000000) THEN '100-200万'
                 ELSE '200以上'
             END AS SCOPE,

做除数运算去两位拼接%

code:
    concat(cast(round(num/total_num,2)*100 AS String),'%')客户占比

sum() over窗口函数:

code:
    sum(discounts_tax_amount) over(partition BY substr(calmonth,1,4),material_desc,company_code,company_name)

calmonth 转timestamp

code:
    from_timestamp(months_sub(from_unixtime(unix_timestamp(concat('201901','01'),'yyyyMMdd'),'yyyy-MM-dd'),1),'yyyyMM')
     SELECT from_timestamp(months_add(from_unixtime(unix_timestamp(concat('201901','01')),'yyyy-MM-dd'),11),'yyyy');

impala去除字段nan并赋予默认值

code:
        SELECT if(is_nan(POW(-1, 0.5)),0.0,1)

impala计算当前日月天数

code:
    select now(),
    select
    32-dayofmonth(date_add(from_unixtime(unix_timestamp(concat('201901','01'),'yyyyMMdd'),'yyyy-MM-dd'),
    +32-dayofmonth(from_unixtime(unix_timestamp(concat('201901','01'),'yyyyMMdd'),'yyyy-MM-dd')))) t