1.Hive all conditional judgment embedded functions

Return Type Name(Signature) Description
T if(boolean testCondition, T valueTrue, T valueFalseOrNull) Returns valueTrue when testCondition is true, Returns valueFalseOrNull otherwise.** Returns valueTrue if testCondition is true, and valueFalseOrNull otherwise. If (100 is not null, ‘a=100′,’a=99’) result: a=100if(null,100,00) result: 00
T nvl(T value, T default_value) Returns default value if value is null else returns value (as of HIve 0.11).Default_value is returned if value is NULL, otherwise value is returned
T COALESCE(T v1, T v2, …) Returns the first v that is not NULL, or NULL if all v’s are NULL.COALESCE (NULL,44,55)=44/strong> COALESCE (null,44,55)=44/strong>
T CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END When a = b, returns c; when a = d, returns e; else returns f.CASE 4 WHEN 5 THEN 5 WHEN 4 THEN 4 ELSE 3 END CASE 4 WHEN 5 THEN 4 ELSE 3
T CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END When a = true, returns b; when c = true, returns d; else returns e.CASE WHEN 5>0 THEN 5 WHEN 4>0 THEN 4 ELSE 0 END CASE WHEN 5>0 THEN 4 ELSE 0 CASE WHEN 5<0 THEN 5 WHEN 4<0 THEN 4 ELSE 0 END
boolean isnull( a ) Returns true if a is NULL and false otherwise.Return true if a is null, false otherwise
boolean isnotnull ( a ) Returns true if a is not NULL and false otherwise.Return true if a is non-null, false otherwise

2. Demonstration of common conditional judgment functions

1.If function: Similar to case, If handles the judgment query result of a single column

Syntax: if(Boolean testCondition, T valueTrue, T valueFalse Or Null)

If testCondition is TRUE, valueTrue is returned. Otherwise, valueFalseOrNull is returned

Example: if (conditional expression, result 1, result 2) is equivalent to the ternary operator in Java, except that the type of expression following if can be different.

hive> select if(a=A, "BBBB".111) fromlxw_dual;

           bbbb

hive> select if(1<2.100.200) fromlxw_dual;

          200
Copy the code

2. NVL determination of null values

NVL (T value, T default_value)

Default_value is returned if value is NULL, otherwise value is returned.

hive> select nvl(principal,1)  from mydb.aaaaa

         1
Copy the code

3. Non-empty search function COALESCE

Syntax: COALESCE(T v1, T v2…)

Description: Returns the first non-null value of a parameter; If all values are NULL, return NULL, which can have many arguments.

Hive > select COALESCE (null, "aaa", 50) from lxw_dual; aaaCopy the code

Case A when B THEN C

CASE A WHEN B THEN C [WHENd THEN E]* [ELSE F] END

If a is equal to b, return c; If a is equal to d, return e; Otherwise return f. Notice that there can be many conditions for when.

For example:

hive> Select case 100

        when 50 then 'tom'

        when 100 then 'mary'

         else 'tim' end

 from lxw_dual;

 mary


hive> Select case 200 when 50 then 'tom'when 100 then 'mary' else 'tim' end from lxw_dual;

        tim
Copy the code

4. Conditional judgment function: This case WHEN is used together to process query results for a single column

CASE WHEN a THEN B [WHEN cTHEN D]* [ELSE e] END

Note: if a is TRUE, return b; If c is TRUE, return d; Otherwise return e

For example:

hive> select case when 1=2 then 'tom'when 2=2 then 'mary' else 'tim' end from lxw_dual;

          mary

hive> select case when 1=1 then 'tom'when 2=2 then 'mary' else 'tim' end from lxw_dual;

         tom 
Copy the code

The result of a case match belongs to a new field

select \* from (select name ,id ,casewhen id <=1235 then 'low salary'when id >1235 and id <=1236 then 'middle'else 'very  high'end from mytest_staff_info_demo4\_cp1 where statis_date='20180228') bCopy the code
Query result:  name id _c2 'account1' 1234 low salary 'account2' 1235 low salary 'account3' 1235 low salary 'account4' 1236 middle 'account5' 1237 very highCopy the code

5. Is null and is not null

A is null, a is not null, return true if a is not null, otherwise return false