# Hive common functions

Posted by sp2hari on Sun, 10 Oct 2021 15:07:31 +0200

## 1. Writing order

```-select
-from
-join
-on
-where
-group by
-having
-order by
-limit```

## 2. Execution sequence

```- from
- where
- join
- on
- select
- group by
- having
- distinct
- order by
- limit
- union / union on```

# 3.Hive relational operator [common]

 != Not equal to <> Not equal to == Wait for = be equal to + plus - reduce * ride / except >= Greater than or equal to <= Less than or equal to

# 4. Mathematical function [common]

### 4.1 rounding function: round

```select round(11.214563);
--Rounded to the nearest whole number

2. Specify precision rounding function: round
round(double a, int b)
Example:
select round(11.256,2);  -- 11.26```

### 4.2 rounding down function: floor

```SELECT floor(10.954) ;
Return 10```

### four point three   Round up ceil/   ceiling

```select ceil(12.006); / select ceiling(12.006);
Return 13
```

### 4.4 random number function: rand

```grammar: rand(),rand(int seed)

Return value: double

explain:Return a double Random number in the range of type 0 to 1. If you specify a seed seed，Will wait until a stable sequence of random numbers

select rand();
return: 0.7400880291262728

-- The specified seed returns the same result
select rand(50);
0.7297136425657874

select rand(50);
0.7297136425657874```

### 4.5 power operation function: pow

```grammar: pow(double a, double p), power(double a, double p)

Return value: double

explain:return a of p Power

select pow(2,5);
Return 32.0```

### four point six   Square function: sqrt

```grammar: sqrt(double x)

Return value: double

explain:return x Square root of

select sqrt(4);
Return 2.0```

### four point seven   Binary function: bin

```grammar: bin(BIGINT x)

Return value: string

explain:return x Binary string for

select bin(4);
Return 100```

### 4.8 absolute value function: abs

```grammar: abs(double x),abs(int x)

Return value: double OR int

explain:Return value x Absolute value of

select abs(-2.0);
Return 2```

### 4.9 judging positive and negative functions: sign

```grammar: sign(double a)

Return value: double

explain:If a A positive number returns 1.0，Returns a negative number-1.0，Otherwise, 0 is returned.0

select sign(10);
Return 1.0

select sign(-10.0);
return -1.0

select sign(0);
Return 0.0```

### four point one zero   Inverse function

```grammar: negative(int x), negative(double x)

Return value: int or double

explain:return-x or x Opposite number of

select negative(-5);
Return 5
select negative(5);
return -5

```

### 4.11 mathematical function: pi

```grammar: pi()

Return value: double

explain:Mathematical constantπ

select pi();
Return 3.141592653589793```

### 4.12 function for finding the maximum number of N: greatest

```grammar:greatest(T v1, T v2, ...) N Maximum number of

Return value:T

explain:Find the maximum value

select greatest(1,2,3);
Return 3 ```

### 4.13 function for finding the minimum number of N: least

```grammar:least(T v1, T v2, ...)

Return value:T

explain:Find the minimum value

select least(1,2,3);
Return 1```

### 4.14 banker rounding function: broad

```grammar:bround(double a)

Return value:double

explain:Banker's rounding method

select bround(5.5)
Return 6
select bround(4.5)
Return 4```

# 5, Date function

### five point one   UNIX timestamp to date function: from_unixtime

```grammar: from_unixtime(bigint unixtime, [string format])

Return value: string
explain: format The format can be“ yyyy-MM-dd hh:mm:ss","yyyy-MM-dd hh","yyyy-MM-dd hh:mm"etc.

select from_unixtime(1634000000,'yyyy-MM-dd');
Back to 2021-10-12

select from_unixtime(1634000000,'yyyy-MM-dd HH:mm:ss')
Back to 2021-10-12 00:53:20```

### five point two   Get current UNIX timestamp function: unix_timestamp

```grammar: unix_timestamp()

Return value: bigint

explain:Gets the current time zone UNIX time stamp

select unix_timestamp();

SELECT from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:dd');
Back to 2021-10-08 15:48:10```

### five point three   Date to UNIX timestamp function: unix_timestamp

```grammar: unix_timestamp(string date)

Return value: bigint

explain:The format can only be'yyyy-MM-dd HH:mm:ss'Converts the time string of to a timestamp. Returns 0 if the conversion fails.

SELECT unix_timestamp('2021-10-08 15:50:00');

SELECT unix_timestamp('2021-10-08');
Return 0```

### five point four   Specify date format to UNIX timestamp function: unix_timestamp

```grammar: unix_timestamp(string date, string pattern)

Return value: bigint

explain:Converts the specified time string format string to Unix Timestamp. Returns 0 if conversion fails

select unix_timestamp('2021-10-08','yyyy-MM-dd');

### five point five   Date time to date function: to_date

```grammar: to_date(string timestamp)

Return value: string

explain:Returns the date portion of the date time field

select to_date('2021-10-08 15:00:00');
Back to 2021-10-08```

### five point six   Date to year function: year

```grammar: year(string date)

Return value: int

explain:Returns the year in the date time field

select year('2021-10-08 15:00:00');
select year('2021-10-08');
Back to 2021```

### five point seven   Date to month function: month

```grammar: month(string date)

Return value: int

explain:Returns the month in the date time field

select month('2021-10-08 15:00:00');
select month('2021-10-08');
Return 10```

### five point eight   Date conversion function: day

```grammar: day(string date)

Return value: int

explain:Returns the date in the date time field

select day('2021-10-08 15:00:00');
select day('2021-10-08');
Return 8```

### five point nine   Date to hour function: hour

```grammar: hour(string date)

Return value: int

explain:Returns the date in the date time field,Note that if the format is wrong, 0 is returned

select hour('2021-10-08 15:00:00');

select hour('2021-10-08');
Return 0```

### 5.10 date to minute function: minute

```10. Date to minute function: minute
grammar: minute (string date)

Return value: int

explain:Returns the minute in the date.

select minute('2021-10-08 15:10:00');
Return 10```

### 5.11 date to second function: Second

```grammar: second (string date)

Return value: int

explain:Returns the second in the date.

select second('2011-12-08 10:03:01');
Return 1```

### 5.12 date to week function: weekofyear

```grammar: weekofyear (string date)

Return value: int

explain:The return time string is the week ordinal of the year

select weekofyear('2021-10-08 15:00:00');
Return 40```

### 5.13 date comparison function: datediff

```grammar: datediff(string enddate, string startdate)

Return value: int

explain:Returns the number of days from the end date minus the start date.

select datediff('2021-10-08','2021-10-01');
Return 7 ```

```grammar: date_add(string startdate, int days)

Return value: string

explain:Return start date startdate increase days Date in days.

Back to 2021-10-08```

### 5.15 date reduction function: date_sub

```grammar: date_sub(string startdate, int days)

Return value: string

explain: Return start date startdate reduce days Date in days.

select date_sub('2021-10-08',8);
Back to 2021-10-01```

### 5.16 current time date function: current_date

```grammar:current_date()

Return value:date

explain:Returns the current time and date

select current_date();

Back to 2021-10-08```

### 5.17 current time date function: current_timestamp

```grammar:current_timestamp()

Return value:timestamp

explain:Returns the current timestamp

select current_timestamp();
select last_day(current_timestamp());

Back to 2021-10-08 12:25:22.339```

```grammar:add_months(string start_date, int num_months)

Return value:string

2021-12-08
```

### 5.19 date function of last day: last_day

```grammar:last_day(string date)

Return value:string

explain:Returns the date of the last day of the month, ignoring the hours, minutes and seconds( HH:mm:ss)

select last_day(current_date());
select last_day(current_timestamp()); -- Ignore the hours, minutes and seconds section( HH:mm:ss)

Back to 2021-10-31```

### 5.20 date function corresponding to next week X: next_day

```grammar:next_day(string start_date, string day_of_week)

Return value:string

explain:Returns the next week of the current time X The corresponding date is as follows: next_day('2015-01-14', 'TU')
= 2015-01-20 To 2015-01-14 Is the start time, and the corresponding date of the next Tuesday is 2015-01-20

select next_day(current_date(),'su');
2021-10-10```

### 5.21 specified format return time function: date_format

```grammar:date_format(date/timestamp/string ts, string fmt)

Return value:string

explain:Returns the time in the specified format date For example: date_format("2016-06-22","MM-dd")=06-22

select date_format(current_date(),'MM-dd');

Return 10-08```

### 5.22 current week function: dayofweek

```grammar:dayofweek(date)

Return value:int

explain:Day of the week on the return date

select dayofweek(current_date());

Return 6

```

# 6, Conditional function

### 6.1 If function: if

```grammar: if(boolean testCondition, T valueTrue, T valueFalseOrNull)

Return value: T

explain: When condition testCondition by TRUE When, return valueTrue；
Otherwise return valueFalseOrNull(valueTrue，valueFalseOrNull (is generic)
Similar to ternary expression

select if(10>5,1,0);
Return 1

select if(10<5,1,0);
Return 0```

### 6.2 empty lookup function: nvl

```grammar: nvl(T value, T default_value)
Return value: T

explain:If value Value is NULL Just return default_value,Otherwise return value

select nvl(null,1);
Return 1

select nvl(2,1);
Return 2

be careful: select nvl('',1); return ''```

### 6.3 non null lookup function: COALESCE

```grammar: COALESCE(T v1, T v2,...)

Return value: T

explain: Returns the first non null value in the parameter; if all values are NULL，Then return NULL

select coalesce(1,2,null,...);
Return 1
select coalesce(null,null,2,...);
Return 2

select coalesce(null,null,null,...);
return null```

### 6.4 condition judgment function: CASE

```grammar: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END

Return value: T

Note: if a be equal to b，Then return c；If a be equal to d，Then return e；Otherwise return f

select CASE 4 WHEN 5 THEN 5 WHEN 4 THEN 4 ELSE 3 END;
Return 4```

### 6.5 condition judgment function: CASE

```grammar: CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END

Return value: T

Note: if a by TRUE,Then return b；If c by TRUE，Then return d；Otherwise return e

select
case
when a > 10  and a < 20 then '10-19 year'
when a >= 20 and a < 30 then '20-30 year'
else '30 Over years old'
end as 'age group'```

### 6.6 null value judgment function: isnull

```grammar: isnull( a )
Return value:boolean

Note: if a by null Just return true，Otherwise return false

select isnull(5);
return false
select isnull(null);
return true

where classid is null;
Returns results that meet the criteria```

### 6.7 non null value judgment function: isnotnull

```grammar: isnotnull ( a )

Return value:boolean

Note: if a For non null Just return true，Otherwise return false

select isnotnull(5);
return true

where classid is not null;

Returns results that meet the criteria```

# 7, Aggregate function [generally used with grouping function or windowing function]

### Do not count null values seven point one   Number statistics function: count

```grammar:count(*), count(expr), count(DISTINCT expr[, expr...])

Return value: BIGINT

explain: count(*)Count the number of retrieved rows, including NULL Rows of values;
count(expr)Returns the number of non null values of the specified field;
count(DISTINCTexpr[, expr_.])Statistics provide non NULL And after weight removal expr Number of rows of expression value

select count(1) from  tb_class;
Usually used with grouping functions```

### seven point two   sum statistics function: sum

```2. Summation statistical function: sum
grammar: sum(col), sum(DISTINCT col)

Return value: double

explain: sum(col)Statistical result set col The result of the addition of; sum(DISTINCT col)In the statistical results col Result of adding different values

select sum(money) from order ``` ### seven point three   Average statistical function: avg

```grammar: avg(col), avg(DISTINCT col)

Return value: double

explain: avg(col)Statistical result set col Average value of; avg(DISTINCT col)In the statistical results col Average of different values added```

### seven point four   Minimum statistical function: min

```grammar: min(col)

Return value: double

explain:Statistical result set col Minimum value of the field```

### seven point five   Maximum statistical function: max

```grammar: maxcol)

Return value: double

explain:Statistical result set col Maximum value of the field```

# 8, Grouping function

classnameageevent_daygongzi
1Zhang San152021-05-01100
2Li Si142021-05-03200
3Wang Wu132021-05-05100
1Zhao Liu152021-04-30200
2Zhang San172021-06-01300
```group by According to by The data is grouped according to which field, or which fields.
grammar: group by Followed by grouping fields,Multiple fields can be separated by commas

select   field    from   Table name   where    condition     group   by       field

perhaps

select   field    from   Table name   group  by    field    having    Filter condition

Note: for filter conditions, you can use where，Reuse group  by Or use it first group  by，Reuse having
group by Cannot appear after where function,because where Pre production line
```

### 8.1 examples:

```Group individual fields
select class, count(1) as cnt
from tb_class
group by class       -- be careful select Fields that do not participate in grouping cannot appear after grouping

Grouping multiple fields[Number of people with the same name in each class]
select class,name,count(1) as cnt
from tb_class
group by class,name```

### 8.2 often used with aggregate functions   count() , sum() , avg() , max() , min()

```1. count() --Number of students in each class
select class,count(1) as cnt from tb_class group by class;
be careful: If count(distinct field) Low efficiency,If there is a large amount of data,Not recommended
[Because use count(distinct)Syntax aggregation reduce Will become a to complete the aggregation]

2.sum() -- Total salary of each class
select class,sum(gongzi) as total_gongzi from tb_class group by class;

3.avg() --Average age per class
select class, avg(age) from tb_class group by class;

4.max() --Maximum age per class
select class, max(age) from tb_class group by class;

5.min() --Minimum age per class
select class, min(age) from tb_class group by class;```

Topics: hive SQL