Current location - Training Enrollment Network - Mathematics courses - Which of the following sql functions does Hive support?
Which of the following sql functions does Hive support?
1. relational operation: 1. Equivalent comparison: =

Grammar: A=B

Operation Type: All Basic Types

Description: true; If expression a is equal to expression b; Otherwise it's fake.

For example:

Hive & gt select 1 where1=1from lxw_dual;

1

2. Unequal comparison:

Grammar: a

Operation Type: All Basic Types

Description: If Expression A is NULL or Expression B is NULL, null is returned; True; If expression a and expression b are not equal; Otherwise it's fake.

For example:

Hive & gt select1from lxw _ dual where1<; & gt2;

1

3. Less than comparison:

Grammar: a

Operation Type: All Basic Types

Description: If Expression A is NULL or Expression B is NULL, null is returned; True; If expression a is less than expression b; Otherwise it's fake.

For example:

Hive & gt select1from lxw _ dual where1<; 2;

1

4. Less than or equal to the comparison value:

Grammar: a

Operation Type: All Basic Types

Description: If Expression A is NULL or Expression B is NULL, null is returned; True; If expression a is less than or equal to expression b; Otherwise it's fake.

For example:

Hive & gt select1from lxw _ dual where1<; = 1;

1

5. greater than comparison: >

Grammar: A > B

Operation Type: All Basic Types

Description: If Expression A is NULL or Expression B is NULL, null is returned; True; If expression a is greater than expression b; Otherwise it's fake.

For example:

hive & gtselect 1 from lxw _ dual where 2 & gt; 1;

1

6. greater than or equal to comparison: > =

Grammar: A > = B

Operation Type: All Basic Types

Description: If Expression A is NULL or Expression B is NULL, null is returned; True; If expression a is greater than or equal to expression b; Otherwise it's fake.

For example:

hive & gtselect 1 from lxw _ dual where 1 & gt; = 1;

1

Note: Pay attention to the comparison of strings (common time comparison can be made before to_date).

hive & gtselect * from lxw _ dual

good

20 1 1 1 1 120900:00:00 20 1 1 1 1 1209

Hive & gt selects a, b, a<b, a>b, and a=b comes from lxw _ dual.

20111/kloc-0: 00: 00 2011/kloc-0-0.

7. Null value judgment: null.

Syntax: A is empty.

Operation type: all types

Description: true; If the value of expression a is empty; Otherwise it's fake.

For example:

Hive & gt select1from lxw _ dual where null is null;

1

8. Non-NULL judgment: not null

Syntax: A is not empty.

Operation type: all types

Description: false; If the value of expression a is empty; Otherwise it's true.

For example:

Hive & gt select 1 from lxw _ dual where1is not empty;

1

9. like contrast: like it

Grammar: A is like B.

Operation type: string

Description: If string A or string B is NULL, null is returned; True; If string a conforms to the regular grammar of expression b; Otherwise it's fake. The character "_" in B represents any single character, while the character "%"represents any number of characters.

For example:

hive & gtselect 1 from lxw _ dual where ' football ' like ' foot % ';

1

Hive & gt select1from lxw _ dual where' football' is like' ‘foot _ _ _ _';

1

Note: use notlike b when negative comparison.

hive & gtselect 1 from lxw _ dual where NOT ' football ' like ' fff % ';

1

Like the operation of 10. JAVA: RLIKE

Grammar: A RLIKE B

Operation type: string

Description: If string A or string B is NULL, null is returned; True; If the string A conforms to the regular syntax of JAVA regular expression B; Otherwise it's fake.

For example:

hive & gtselect 1 from lxw _ dual where‘footbalr’like '^f.*r$';

1

Note: To determine whether a string is all numbers:

hive & gtselect 1 from lxw _ dual where ' 123456 ' r like '^\\d+$';

1

hive & gtselect 1 from lxw _ dual where ' 123456 aa ' like '^\\d+$';

1 1. Regular expression operation: regular expression

Syntax: A REGEXP B

Operation type: string

Note: the function is the same as RLIKE.

For example:

hive & gtselect 1 from lxw _ dual where ' footbar ' regexp '^f.*r$';

1

2. Mathematical operation: 1. Addition operation:+

Grammar: A+B

Operation type: all numeric types

Description: Returns the result of adding A and B, and the numerical type of the result is equal to the minimum parent type of A and B (see the inheritance relationship of data types for details). For example, int+int generally produces int type, and int+double generally produces double type.

For example:

Hive & gt select1+9 from lxw_dual;

10

hive & gtcreate table lxw _ dual as select 1+ 1.2 from lxw _ dual;

Hive & gt describes lxw _ dual

_c0 double precision

2. Subtraction operation:-

Grammar: a–b

Operation type: all numeric types

Description: Returns the result of subtraction of A and B, and the numerical type of the result is equal to the minimum parent type of A and B (see the inheritance relationship of data types for details). For example, int–int usually produces an int type, while int-double usually produces a double type.

For example:

Hive & gt select10–5 from lxw_dual;

five

Hive & gt creates the table lxw_dual as select 5.6-4 from lxw _ dual;;

Hive & gt describes lxw _ dual

_c0 double precision

3. Multiplication operation: *

Grammar: A * B

Operation type: all numeric types

Description: Returns the result of multiplication of A and B, and the numerical type of the result is equal to the minimum parent type of A and B (see the inheritance relationship of data types for details). Note that if the result of multiplying a by b exceeds the numerical range of the default result type, you need to convert the result to a wider numerical type through cast.

For example:

Hive & gt choose 40 * 5 from lxw_dual;

200

4. Division operation:/

Grammar: A/B

Operation type: all numeric types

Description: Returns the result of dividing a by b, and the numerical type of the result is double.

For example:

Hive & gt choose 40/5 from lxw_dual;

8.0

Note: The data type with the highest precision in hive is double, which is only accurate to 16 digits after the decimal point. Pay special attention when doing division.

Hive & gt choose CEIL (28.0/6.9999) from lxw_duallimit 1;

The result is 4.

Hive & gt choose CEIL (28.0/6.9999) from lxw_dual limit 1;

The result is 5.

5. Remainder operation:%

Grammar: A% B

Operation type: all numeric types

Description: Returns the remainder of a divided by b, and the numerical type of the result is equal to the minimum parent type of a type and b type (see the inheritance relationship of data types for details).

For example:

Hive & gt choose 41%5 from lxw_dual;

1

Hive & gt selects 8.4% 4 from lxw_dual;

0.40000000000000036

Note: accuracy is a big problem of hive, and it is best to specify accuracy through round for operations like this.

Hive & gt selects round (8.4% 4,2) from lxw_dual;

0.4

6. Bitwise AND operation:&;

Grammar: a &;; B

Operation type: all numeric types

Remarks: Returns the bitwise AND result of A and B, and the numerical type of the result is equal to the minimum parent type of A and B (see the inheritance relationship of data types for details).

For example:

Hive & gt chooses 4 & amp8 from lxw _ dual.

Hive & gt chooses 6 & amp4 from lxw _ dual.

four

7. Bit or operation: |

Grammar: A | B

Operation type: all numeric types

Remarks: Returns the result of bitwise OR operation of A and B. The numerical type of the result is equal to the minimum parent type of type A and type B (see the inheritance relationship of data types for details).

For example:

Hive & gt select 4 | 8 from lxw_dual;

12

Hive & gt select 6 | 8 from lxw_dual;

14

8. Bitwise XOR operation:

Grammar: a b

Operation type: all numeric types

Remarks: Returns the bitwise XOR result of A and B. The numerical type of the result is equal to the smallest parent type of A and B (see the inheritance relationship of data types for details).

For example:

Hive & gt choose 4 8 from lxw_dual;

12

Hive & gt choose 6 4 from lxw_dual;

2

9. bit inversion operation: ~

Grammar: ~A

Operation type: all numeric types

Description: The bitwise negation result returned. The numeric type of the result is equal to the type of.

For example:

Hive & gt selects ~ 6 from lxw_dual;

-7

Hive & gt selects ~ 4 from lxw_dual;

-5

Three. Logical operation: 1. Logic and operation: AND

Grammar: a and b

Operation type: Boolean

Description: If both A and B are true, it is true; Otherwise it's fake. If a is empty or b is empty, it is empty.

For example:

Hive & gt select 1 from lxw_dual, where 1= 1, 2 = 2;

1

2. Logical OR operation: or

Grammar: a or b

Operation type: Boolean

Description: If A is true, or B is true, or both A and B are true, it is true; Otherwise it's fake.

For example:

Hive & gt select 1 from lxw_dual, where 1=2 or 2 = 2;

1

3. Logical NOT operation: NOT

Grammar: No.

Operation type: Boolean

Description: true; If a is false or a is empty; Otherwise it's fake.

For example:

Hive & gt selects 1 from lxw_dual, where not1= 2;

1

4. Numerical calculation 1. Rounding function: rounding

Grammar: round (double a)

Return value: BIGINT

Description: Returns the integer value part of type double (following rounding).

For example:

Hive & gt select round (3.1415926) from lxw_dual;

three

Hive & gt select round (3.5) from lxw_dual;

four

Hive & gt creates a table lxw_dual from lxw_dual as a select round (9542.158);

Hive & gt describes lxw _ dual

_c0 bigint

2. Specify the exact rounding function: round.

Syntax: round(double a, int d)

Return value: DOUBLE

Description: Returns a double-precision value with the specified precision d.

For example:

Hive & gt select round (3.1415926,4) from lxw_dual;

3. 14 16

3. Rounding down function: lower limit

Grammar: floor (double a)

Return value: BIGINT

Description: Returns the largest integer equal to or less than the double variable.

For example:

Hive & gt select the floor from lxw_dual (3.1415926);

three

Hive & gt select the floor from lxw_dual (25);

25

4. Integer function: ceil

Grammar: ceil (double A)

Return value: BIGINT

Description: Returns the smallest integer equal to or greater than a double variable.

For example:

Hive & gt choose CEIL (3.1415926) from lxw_dual;

four

Hive & gt chooses Ceil (46) from lxw_dual;

46

5. Round-up function: upper limit

Grammar: ceiling (double A)

Return value: BIGINT

Description: Same function as ceiling.

For example:

Hive & gt selects the ceiling from lxw_dual (3.1415926);

four

Hive & gt selects ceiling (46) from lxw_dual;

46

6. Take random number function: rand

Grammar: rand (), rand(int seed)

Return value: double

Description: Returns a random number from 0 to 1 If seed is specified, you will wait for a stable random number sequence.

For example:

Hive & gt choose rand () from lxw_dual;

0.5577432776034763

Hive & gt choose rand () from lxw_dual;

0.6638336467363424

Hive & gt selects rand from lxw_dual (100);

0.7220096548596434

Hive & gt selects rand from lxw_dual (100);

0.7220096548596434

7. Natural exponential function: exp

Grammar: exp (double a)

Return value: double

Description: Returns the power of natural logarithm e.

For example:

hive & gtselect exp(2)from lxw _ dual;

7.38905609893065

Natural logarithmic function: ln

Grammar: ln (double a)

Return value: double

Description: The natural logarithm returned.

For example:

Hive & gt chooses ln (7.7000) from lxw_dual;

2.0

8. Logarithmic function with radix 10: log 10.

Syntax: log 10 (double a)

Return value: double

Description: Returns the logarithm of a based on 10.

For example:

Hive & gt select log10 (100) from lxw_dual;

2.0

9. log2 is the logarithm function with base 2.

Syntax: log2 (double A)

Return value: double

Description: Returns the base 2 logarithm.

For example:

Hive & gt select log2 (8) from lxw_dual;

3.0

10. Logarithmic function: log

Syntax: log (double radix, double A)

Return value: double

Description: Returns the base logarithm.

For example:

Hive & gt select log (4,256) from lxw_dual;

4.0

1 1. power supply operation function: power supply

Grammar: pow (double a, double p)

Return value: double

Description: returns a to the power of p.

For example:

Hive & gt choose pow (2,4) from lxw_dual;

16.0

12. Power supply operation function: power supply

Grammar: power (double a, double p)

Return value: double

Description: Returns the power of a to the power of p, the same as pow.

For example:

Hive & gt selects power (2,4) from lxw_dual;

16.0

13. Square root function: sqrt

Grammar: sqrt (double a)

Return value: double

Description: Returns.

For example:

Hive & gt selects sqrt (16) from lxw_dual;

4.0

14. Binary function: bin

Grammar: bin(BIGINT a)

Return value: string

Description: Returns.

For example:

Hive & gt selects bin (7) from lxw_dual;

1 1 1

15. Hexadecimal function

Grammar: hexadecimal (BIGINT a)

Return value: string

Description: If the variable type is int, the hexadecimal representation of a is returned; If the variable is a string type, the hexadecimal representation of the string is returned.

For example:

Hive & gt selects hexadecimal from lxw_dual (17);

1 1

Hive & gt selects hexadecimal ('ABC') from lxw_dual;

6 16263

16. Anti-hexadecimal function: unhex

Syntax: unhex (string A)

Return value: string

Description: Returns the string encoded by this hexadecimal string.

For example:

Hive & gt Select Unhex ('616263') from lxw_dual;

alphabet

Hive & gt Select Unhex ('11') from lxw_dual;

-

Hive & gt choose unhax (616263) from lxw_dual;

alphabet

17. binary conversion function: conv

Syntax: Conv (bigint num, int from _ base, int to _ base)

Return value: string

Description: Converts the numeric value num from _base to _base.

For example:

Hive & gt select conv( 17, 10,16) from lxw_dual;

1 1

Hive & gt select conv( 17,10,2) from lxw_dual;

1000 1

18. Absolute value function: abs

Grammar: abs (double a) abs(int a)

Return value: double int

Description: Returns the absolute value of a.

For example:

Hive & gt selects ABS (-3.9) from lxw_dual;

3.9

Hive & gt selects ABS from lxw_dual (10.9);

10.9

19. Positive complementary function: pmod

Grammar: pmod (int a, int b), pmod (double a, double b)

Return value: int double

Description: returns the remainder of positive a divided by b.

For example:

Hive & gt selects pmod (9,4) from lxw_dual;

1

Hive & gt selects pmod (-9,4) from lxw_dual;

three

20. Sine function: sine

Grammar: sin (double a)

Return value: double

Description: Returns.

For example:

Hive & gt selects sin (0.8) from lxw_dual;

0.7 173560908995228

2 1. arcsine function: asin

Grammar: asin (double a)

Return value: double

Description: Returns.

For example:

Hive & gt choose asin (0.7173560908995228) from lxw_dual;

0.8

22. Cosine function: cos

Grammar: cos (double a)

Return value: double

Description: Returns.

For example:

Hive & gt selects cos (0.9) from lxw_dual;

0.62 16099682706644

23. anti-cosine function: acos

Grammar: acos (double a)

Return value: double

Remarks: Return.

For example:

Hive & gt choose acos (0.6216099682706644) from lxw_dual;

0.9

24. Positive function: positive

Grammar: positive (int a), positive (double a)

Return value: int double

Description: Return to

For example:

Hive & gt selects a positive number (-10) from lxw_dual;

- 10

Hive & gt selects positive from lxw_dual (12);

12

25. Negative function: negative

Grammar: negative (int a), negative (double a)

Return value: int double

Description: Return to -a

For example:

Hive & gt selects negative (-5) from lxw_dual;

five

Hive & gt selects negative (8) from lxw_dual;

-8