关于spark:HiveSqlSparkSQL常用函数

38次阅读

共计 7094 个字符，预计需要花费 18 分钟才能阅读完成。

一、获取以后工夫

  1. current_date 获取以后日期
    2018-04-09
  2. current_timestamp/now()获取以后工夫
    2018-04-09 15:20:49.247

二、从日期工夫中提取字段

  1. year,month,day/dayofmonth,hour,minute,second
    Examples:
 > SELECT day('2009-07-30');
     30` 

*   1
*   2
  1. dayofweek (1 = Sunday, 2 = Monday, …, 7 = Saturday),dayofyear
    Examples:
 > SELECT dayofweek('2009-07-30');  
     5` 

*   1
*   2
  1. weekofyear
    weekofyear(date) – Returns the week of the year of the given date. A week is considered to start on a Monday and week 1 is the first week with >3 days.
    Examples:
 > SELECT weekofyear('2008-02-20');  
      8` 

*   1
*   2
  1. trunc 截取某局部的日期，其余局部默认为 01
    第二个参数 [“year”,“yyyy”,“yy”,“mon”,“month”,“mm”]
    Examples:
 > SELECT trunc('2009-02-12', 'MM');
     2009-02-01
    > SELECT trunc('2015-10-27', 'YEAR');
     2015-01-01` 

*   1
*   2
*   3
*   4
  1. date_trunc [“YEAR”,“YYYY”,“YY”,“MON”,“MONTH”,“MM”,“DAY”,“DD”,“HOUR”,“MINUTE”,“SECOND”,“WEEK”,“QUARTER”]
    Examples:
 > SELECT date_trunc('2015-03-05T09:32:05.359', 'HOUR');  
     2015-03-05T09:00:00` 

*   1
*   2
  1. date_format 将工夫转化为某种格局的字符串
    Examples:
 > SELECT date_format('2016-04-08', 'y');    
     2016` 

*   1
*   2

三、日期工夫转换

  1. unix_timestamp 返回以后工夫的 unix 工夫戳
    Examples:
 > SELECT unix_timestamp();  1476884637
    > SELECT unix_timestamp('2016-04-08', 'yyyy-MM-dd');   1460041200` 

*   1
*   2
  1. from_unixtime 将工夫戳换算成以后工夫，to_unix_timestamp 将工夫转化为工夫戳
    Examples:
 > SELECT from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');  1970-01-01 00:00:00
    > SELECT to_unix_timestamp('2016-04-08', 'yyyy-MM-dd');  1460041200` 

*   1
*   2
  1. to_date/date 将字符串转化为日期格局，to_timestamp（Since: 2.2.0）
 > SELECT to_date('2009-07-30 04:17:52');  2009-07-30
    > SELECT to_date('2016-12-31', 'yyyy-MM-dd');   2016-12-31
    > SELECT to_timestamp('2016-12-31 00:12:00');   2016-12-31 00:12:00` 

*   1
*   2
*   3
  1. quarter 将 1 年 4 等分(range 1 to 4)
    Examples:
 > SELECT quarter('2016-08-31'); 
     3` 

*   1
*   2

四、日期、工夫计算

  1. months_between 两个日期之间的月数
    months_between(timestamp1, timestamp2) – Returns number of months between timestamp1 and timestamp2.
    Examples:
 > SELECT months_between('1997-02-28 10:30:00', '1996-10-30');  
    3.94959677` 

*   1
*   2
  1. add_months 返回日期后 n 个月后的日期
    Examples:
 > SELECT add_months('2016-08-31', 1);  
    2016-09-30` 

*   1
*   2
*   3
  1. last_day(date),next_day(start_date, day_of_week)
    Examples:
 > SELECT last_day('2009-01-12');  2009-01-31
    > SELECT next_day('2015-01-14', 'TU');  2015-01-20` 

*   1
*   2
  1. date_add,date_sub(减)
    date_add(start_date, num_days) – Returns the date that is num_days after start_date.
    Examples:
 > SELECT date_add('2016-07-30', 1);  2016-07-31` 

*   1
  1. datediff（两个日期间的天数）
    datediff(endDate, startDate) – Returns the number of days from startDate to endDate.
    Examples:
 > SELECT datediff('2009-07-31', '2009-07-30');
     1` 

*   1
*   2
  1. 对于 UTC 工夫
  • to_utc_timestamp
    to_utc_timestamp(timestamp, timezone) – Given a timestamp like‘2017-07-14 02:40:00.0’, interprets it as a time in the given time zone, and renders that time as a timestamp in UTC. For example,‘GMT+1’would yield‘2017-07-14 01:40:00.0’.
    Examples:
 > SELECT to_utc_timestamp('2016-08-31', 'Asia/Seoul');  
    2016-08-30 15:00:00` 

*   1
*   2
  • from_utc_timestamp
    from_utc_timestamp(timestamp, timezone) – Given a timestamp like‘2017-07-14 02:40:00.0’, interprets it as a time in UTC, and renders that time as a timestamp in the given time zone. For example,‘GMT+1’would yield‘2017-07-14 03:40:00.0’.
    Examples:
 > SELECT from_utc_timestamp('2016-08-31', 'Asia/Seoul');  
    2016-08-31 09:00:00

五、Hive 罕用函数

1. 数学函数

round(DOUBLE d) : 返回 DOUBLE 型的 d 的 BIGINT 类型的近似值
round(DOUBLE d，INT) : 返回 DOUBLE 型的 d 的保留 n 位小数的 DOUBLE 类型的近似值
floor(DOUBLE d): d 是 DOUBLE 类型的，返回 <= d 的最大的 BIGINT 值
ceil(DOUBLE d): d 是 DOUBLE 类型的，返回 >= d 的最小的 BIGINT 值
rand() rand(INT seed): 每行返回一个 DOUBLE 型的随机数，整数 seed 是随机因子
exp(DOUBLE d)：返回 e 的 d 幂次方
ln(DOUBLE d): 以自然数为底 d 的对数
log10(DOUBLE d): 以 10 为底的 d 的对数
log2(DOUBLE d): 以 2 为底的 d 的对数
log（DOUBLE base,DOUBLE d）以 base 为底的 d 的对数
pow(DOUBLE d,DOUBLE p) power(DOUBLE d,DOUBLE p): 计算 d 的 p 次幂
sqrt(DOUBLE d): d 的平方根
bin(DOUBLE i): 计算二进制值 i 的 STRING 类型值
hex(BIGINT i): 计算十六进制值 i 的 STRING 类型值
unhex(STRING i)：hex 的逆办法
conv(STRING num,INT from_base,INT to_base): 将 STRING 类型的 num 从 from_base 进制转化为 to_base 进制
abs(DOUBLE d): 计算 d 的绝对值
pmod(INT i1 ,INT i2): i1 对 i2 取模
sin(DOUBLE d): 返回 d 的正弦值
cos(DOUBLE d): 返回 d 的余弦值
asin(DOUBLE d): 返回 d 的反正弦值
acos(DOUBLE d): 返回 d 的反余弦值
tan(DOUBLE d): 返回 d 的正切值
atan(DOUBLE d): 返回 d 的反正切值
degrees(DOUBLE d): 将弧度值 d 转化为角度值
radians(DOUBLE d): 将角度值 d 转化为弧度值
positive(DOUBLE d): 返回 +d
negative(DOUBLE d): 返回 -d
sign(DOUBLE d): 如果 d 是负数，则返回 +1.0，如果 d 是正数，则返回 -1.0，否则为 0

2. 日期函数

to_date(string timestamp): 返回工夫字符串中的日期局部, 如 to_date('1970-01-01 00:00:00')='1970-01-01'
current_date: 返回以后日期
year(date)：返回日期 date 的年, 类型为 int 如 year('2019-01-01')=2019
month(date)：返回日期 date 的月, 类型为 int, 如 month('2019-01-01')=1
day(date):  返回日期 date 的天, 类型为 int, 如 day('2019-01-01')=1
weekofyear(date1)：返回日期 date1 位于该年第几周。如 weekofyear('2019-03-06')=10
datediff(date1,date2): 返回日期 date1 与 date2 相差的天数，如 datediff('2019-03-06','2019-03-05')=1
date_add(date1,int1): 返回日期 date1 加上 int1 的日期, 如 date_add('2019-03-06',1)='2019-03-07'
date_sub(date1,int1): 返回日期 date1 减去 int1 的日期, 如 date_sub('2019-03-06',1)='2019-03-05'
months_between(date1,date2): 返回 date1 与 date2 相差月份, 如 months_between('2019-03-06','2019-01-01')=2
add_months(date1,int1): 返回 date1 加上 int1 个月的日期，int1 可为正数。如 add_months('2019-02-11',-1)='2019-01-11'
last_day(date1): 返回 date1 所在月份最初一天。如 last_day('2019-02-01')='2019-02-28'
next_day(date1,day1): 返回日期 date1 的下个星期 day1 的日期。day1 为星期 X 的英文前两字母如 next_day('2019-03-06','MO') 返回 '2019-03-11'
trunc(date1,string1): 返回日期最开始年份或月份。string1 可为年 (YYYY/YY/YEAR) 或月(MONTH/MON/MM)。如 trunc('2019-03-06','MM')='2019-03-01'，trunc('2019-03-06','YYYY')='2019-01-01'
unix_timestamp(): 返回以后工夫的 unix 工夫戳，可指定日期格局。如 unix_timestamp('2019-03-06','yyyy-mm-dd')=1546704180
from_unixtime(): 返回 unix 工夫戳的日期，可指定格局。如 select from_unixtime(unix_timestamp('2019-03-06','yyyy-mm-dd'),'yyyymmdd')='20190306'

3. 条件函数

if(boolean,t1,t2): 若布尔值成立，则返回 t1, 反正返回 t2。如 if(1>2,100,200)返回 200
case when boolean then t1 else t2 end: 若布尔值成立，则 t1, 否则 t2, 可加多重判断
coalesce(v0,v1,v2): 返回参数中的第一个非空值, 若所有值均为 null, 则返回 null。如 coalesce(null,1,2)返回 1
isnull(a): 若 a 为 null 则返回 true，否则返回 false

4. 字符串函数

length(string1): 返回字符串长度
concat(string1,string2): 返回拼接 string1 及 string2 后的字符串
concat_ws(sep,string1,string2): 返回按指定分隔符拼接的字符串
lower(string1): 返回小写字符串，同 lcase(string1)。upper()/ucase()：返回大写字符串
trim(string1): 去字符串左右空格，ltrim(string1): 去字符串左空格。rtrim(string1): 去字符串右空格
repeat(string1，int1)：返回反复 string1 字符串 int1 次后的字符串
reverse(string1): 返回 string1 反转后的字符串。如 reverse('abc')返回 'cba'
rpad(string1,len1,pad1): 以 pad1 字符右填充 string1 字符串，至 len1 长度。如 rpad('abc',5,'1')返回 'abc11'。lpad()：左填充
split(string1,pat1): 以 pat1 正则分隔字符串 string1, 返回数组。如 split('a,b,c',',')返回["a","b","c"]
substr(string1,index1,int1): 以 index 地位起截取 int1 个字符。如 substr('abcde',1,2)返回 'ab'

5. 聚合函数

count(): 统计行数
sum(col1): 统计指定列和
avg(col1): 统计指定列平均值
min(col1): 返回指定列最小值
max(col1): 返回指定列最大值

6. 表生成函数

explode (array): 返回多行 array 中对应的元素。如 explode(array('A','B','C'))返回 
explode(map): 返回多行 map 键值对对应元素。如 explode(map(1,'A',2,'B',3,'C'))返回

7. 窗口函数

row_number()  over(partitiion by .. order by ..): 依据 partition 排序, 雷同值取不同序号，不存在序号跳跃
rank() over(partition by ..  order by .): 依据 partition 排序，雷同值取雷同序号，存在序号跳跃
dense_rank() over(partition by .. order by ..): 依据 partition 排序，雷同值取雷同序号，不存在序号跳跃
sum() over(partition by .. order by ..)
count() over(partition by .. order by ..)
lag(col,n) over(partition by .. order by ..)：查看以后行的上第 n 行
lead(col,n) over(partition by .. order by ..)：查看以后行的下第 n 行
first_value() over(partition by .. order by ..): 满足 partition 及排序的第一个值
last_value() over(partition by .. order by ..): 满足 partition 及排序的最初值
ntile(n) over(partition by .. order by ..): 满足 partition 及排序的数据分成 n 份

partition 内更细的划分，可应用 windows 子句。常见子句为：preceding：往前
following：往后
current row：以后行
unbounded：终点，unbounded preceding 示意从后面的终点，unbounded following：示意到前面的起点
应用如：、sum(col) over(partition by .. order by .. rows between 1 preceding and current row): 以后行与前一行做聚合

8. 行列转换

concat_ws(sep, collect_set(col1)) : 同组不同行合并成一列，以 sep 分隔符分隔。collect_set 在无反复的状况下也能够 collect_list()代替。collect_set()去重，collect_list()不去重
lateral  view explode(split(col1,',')) : 同组同列的数据拆分成多行，以 sep 分隔符辨别

正文完
spark
发表至： spark
2021-03-15
 0
关于spark:Spark学习记录之SparkCore核心属性
关于spark:SparkESClickHouse-构建DMP用户画像
关于spark:SparkESClickHouse-构建DMP用户画像
关于spark:Spark-javascala项目打包jar