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

Common function

The system has its own function

Coalesce (): returns the first non-NULL value in the list, or NULL if all values in the list are NULL

Eg:

Concat (): string concatenation function

Eg:

Least (): returns the smallest of the input parameters

Greatest (): returns the largest of the input parameters (var1,var2 can be bigint,double,datetime or string. Returns NULL if all values are NULL.

Return value: the maximum value of the input parameter, which returns the same input parameter type when there is no implicit conversion. NULL is the minimum. When the input parameter types are different, the comparison between double,bigint,string is converted to double;string,datetime comparison to datetime. Other implicit conversions are not allowed)

Decode (): implement the function of branch selection

Eg:select decode (customer_id

1, 'Taobao'

2, 'Alipay'

3, 'Aliyun'

NULL, 'Ngo A'

'Others') as result

From sale_detail

The above decode function implements the functions in the following if-then-else statement:

If customer_id = 1 then

Result: = 'Taobao'

Elsif customer_id = 2 then

Result: = 'Alipay'

Elsif customer_id = 3 then

Result: = 'Aliyun'

...

Else

Result: = 'Others'

End if

If function: if (logical condition, coumn1,coumn2) means output 1 if the condition is met, otherwise the value of output 2

Eg:if (cap_direction not in), null, cast (cap_direction as bigint)

Substr (): returns the string str, a substring of length length starting from start_position

Eg: substr ("abc", 2) = "bc"; substr ("abc", 2,1) = "b"

To_char (): converts the Boolean type, bigint type, decimal type, or double type to the corresponding string type representation

Eg:to_char (123) = '123 (true) =' TRUE';to_char (1.23) = '1.23 (null) = NULL

To_char (): Datetime type, the date value to be converted. If you enter string type, it will be implicitly converted to datetime type and participate in the operation. Other types will throw an exception.

Eg:to_char (getdate (), 'yyyymmdd')

Concat (coumn1,',',coumn2): string concatenation function

Match two-digit precision:

Concat (substr (to_char (lng), 1meme 6),',', substr (to_char (lat), 1meme 5)) like '120.08 Jol 30.28'

Regexp_extract (coumn,'',number): string splitting function

Such as: the intersection of Lindong Road and Huoshentang Road

Regexp_extract (inter_name,' (. *) (road)', 1) = Lindong

Regexp_extract (inter_name,' and (. *?) (intersection)', 1) = Vulcan Tang Road

Regexp_replace: string substitution function

Regexp_replace (round_name,'-','',1).-replace it with null.

Split_part string splitting function

Split_part ('Huanbei-Midu Bridge','-', 2) = Midu Bridge

Instr: calculates the position of a substring str2 in the string str1

Instr ('Tech on the net', 'e') = 2 Tech on the net', instr (' Tech on the net', 'eholders, 1,1) = 2

Cast

Coors_convert (lng,lat,1): Google turns to Coors _ convert (120.2334214pr 30.21829241)

WHERE judge_location (split_part (coors_convert (a.lngrecovera. Lata _ (1)),',', 1), split_part (coors_convert (a.lng _ _ (a.lng) a. Lata _ (1)),',', 2)) = 1

Window function

Statistics: count,sum,avg,max/min,median,stddev,stddev_samp

Ranking: row_unmber,rank,dense_rank,percent_rank

Other classes: lag,lead,cluster_sample

-

Basic usage; dividing data into groups according to certain conditions is called windowing, and each group is called a window.

The partition by section is used to specify the column that opens the window

Rows with the same value in the partition column are considered to be in the same window

Order by is used to specify how data is sorted within a window

Usage restrictions: can only appear in the select clause

Do not nest window functions and aggregate functions in window functions

Cannot be used with aggregate functions at the same level

You can use up to 5 window functions in an odps sql statement

When Partition opens a window, the same window contains up to 100 million rows of data.

When opening a window with rows, XBI y must be an integer constant greater than or equal to 0, with a range of 0-10000, and a value of 0 indicates the current line.

You must use order by to specify the window range in rows mode

Not all window functions can specify the window opening method with rows. The window functions that support this usage are avg,count,max,min,stddev and sum.

--

Take a chestnut.

Select *, rank () over (partition by monitor_id order by distance) as mindistance_monitor_id from ()

Custom function

Make the corresponding custom function based on Aliyun odps

Note: in this example, due to the low version of odps, Ali Cloud example is not used to package maven step by step when it is created, but it is packaged by itself. This is because there is a problem with the jar that comes out step by step of the example (the jar does not have class resources, only configuration file resources).

The noun explains:

UDF: a user-defined scalar-valued function (user defined scalar function) whose input and output have an one-to-one relationship. It reads a row of data (which can have multiple parameters) and writes out an output value.

UDTF: custom table-valued function (user defined table valued function), which is used to solve the scenario of outputting multiple rows of data in one function call, and is the only custom function that can return multiple fields.

UDAF: custom aggregate function (user defined aggregation function), whose inputs and outputs are many-to-one, aggregating multiple input records into a single output value (can be used with group by statements)

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.