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);
Return to 11 
--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();
Return to 1633679199

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');
Return to 1633708200

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');
Return to 1633651200

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');

Return to 15

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 

5.14 date addition function: date_add

grammar: date_add(string startdate, int days)

Return value: string

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

select date_add(2021-10-01,7);
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

Function added in May 18: add_months

grammar:add_months(string start_date, int num_months)

Return value:string

explain:Return to current time and add num_months Date of month

select add_months('2021-10-08',2);
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

select * from ads_class
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

select * from ads_class
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

class	name	age	event_day	gongzi
1	Zhang San	15	2021-05-01	100
2	Li Si	14	2021-05-03	200
3	Wang Wu	13	2021-05-05	100
1	Zhao Liu	15	2021-04-30	200
2	Zhang San	17	2021-06-01	300

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]
have access to group by plus count To replace completion

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;