Shulou(Shulou.com)06/03 Report--

Good programmer big data learning route hive internal function, continue to update the big data learning route for everyone, hope to be helpful to the friends who are learning big data.

1. Take the random number function: rand ()

Syntax: rand (), rand (int seed) return value: double description: returns a random number in the range of 0 to 1. If you specify seed, you will get a stable sequence of random numbers

Select rand ()

Select rand (10)

2. Split string function: split (str,splitor)

Syntax: split (string str, string pat) return value: array description: split str according to pat string, the split string array will be returned. Note the escape of special separators.

Select split (5.0, ".") [0]

Select split (rand (10) 100, ".") [0]

3. String intercepting function: substr,substring

Syntax: substr (string A, int start), substring (string A, int start) return value: string description: returns string A from the position of start to the end of the string

Syntax: substr (string A, int start, int len), substring (string A, int start, int len) return value: string description: return string A begins at start position and has a length of len

Select substr (rand () 100pc0pl 2)

Select substring (rand () 100pc0pl 2)

4. If function: if

Syntax: if (boolean testCondition, T valueTrue, T valueFalseOrNull) return value: t description: return valueTrue; if condition testCondition is TRUE, otherwise return valueFalseOrNull

Select if, "this is true", "this is false")

Select if (2x 1, "male", "female")

Select if (1: 1, "male", (if (1: 2, "female", "don't know")

Select if (3: 1, "male", (if (3: 2, "female", "don't know")

5. Conditional judgment function: CASE

The first format:

Syntax: CASE WHEN a THEN b [WHEN c THEN d] [ELSE e] END return value: t description: return b if an is TRUE; return d if c is TRUE; otherwise return e

The second format:

Syntax: CASE a WHEN b THEN c [WHEN d THEN e] * [ELSE f] END return value: t description: if an is equal to b, then c is returned; if an is equal to d, e is returned; otherwise, f is returned

Select

Case 6

When 1 then "100"

When 2 then "200"

When 3 then "300"

When 4 then "400"

Else "others"

End

# # creating a Table

Create table if not exists cw (

Flag int

)

Load data local inpath'/ home/flag' into table cw

# # the first format

Select

Case c.flag

When 1 then "100"

When 2 then "200"

When 3 then "300"

When 4 then "400"

Else "others"

End

From cw c

# # second format

Select

Case

When 1=c.flag then "100"

When 2=c.flag then "200"

When 3=c.flag then "300"

When 4=c.flag then "400"

Else "others"

End

From cw c

6. Regular expression replacement function: regexp_replace

Syntax: regexpreplace (string A, string B, string C) return value: string description: replace the part of the string A that conforms to the java regular expression B with C. Note that escape characters are used in some cases, similar to the regexpreplace function in oracle

Select regexp_replace ("1.jsp", ".jsp", ".html")

7. Type conversion function: cast

Syntax: cast (expr as) return value: Expected "=" to follow "type" description: returns the converted data type

Select 1

Select cast (1 as double)

Select cast ("12" as int)

8. String concatenation function: concat; delimited string concatenation function: concat_ws

Syntax: concat (string A, string B...) Return value: string description: returns the result of input string concatenation. Any input string is supported.

Syntax: concat_ws (string SEP, string A, string B...) Return value: string description: returns the result after the input string is concatenated. SEP represents the delimiter between each string.

Select Qianfeng + 1603 + Class

Select concat (Qianfeng, 1603, Class)

Select concat_ws ("|", "Qianfeng", "1603", "Class")

9. Ranking function:

Rownumber (): rank not juxtaposed rank (): rank juxtaposition, but vacancy denserank (): rank juxtaposition, but not vacancy

# # data

Id class score

1 1 90

2 1 85

3 1 87

4 1 60

5 2 82

6 2 70

7 2 67

8 2 88

9 2 93

1 1 90 1

3 1 87 2

2 1 85 3

9 2 93 1

8 2 88 2

5 2 82 3

Create table if not exists uscore (

Uid int

Classid int

Score double

)

Row format delimited fields terminated by'\ t'

Load data local inpath'/ home/uscore' into table uscore

Select

U.uid

U.classid

U.score

From uscore u

Group by u.classid,u.uid,u.score

Limit 3

Select

U.uid

U.classid

U.score

Row_number () over (distribute by u.classid sort by u.score desc) rn

From uscore u

Take the top three

Select

T.uid

T.classid

T.score

From

(

Select

U.uid

U.classid

U.score

Row_number () over (distribute by u.classid sort by u.score desc) rn

From uscore u

) t

Where t.rn < 4

View the differences between the three rankings

Select

U.uid

U.classid

U.score

Row_number () over (distribute by u.classid sort by u.score desc) rn

Rank () over (distribute by u.classid sort by u.score desc) rank

Dense_rank () over (distribute by u.classid sort by u.score desc) dr

From uscore u

10. Aggregate function:

Min () max () count () count (distinct) sum () avg ()

Count (1): accumulate 1 count (*) as long as it appears, regardless of whether the positive row has a value or not: as long as one of the positive values is not empty, the class is counted 1 count (col): if the col column has a value, it accumulates 1 count (distinct col): the col column has a value and is different.

11.null value operation

Almost any number and NULL operation returns NULL

Select 1+null

Select 1/0

Select null%2

twelve。 Equivalent operation

Select null=null; # null

Select nullnull;#true

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.