Shulou(Shulou.com)05/31 Report--

Use UDF and lambda functions in SQL to use example analysis. For this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Summary: the scene log service has built-in 20 + class SQL functions. In the face of users' complex business scenarios, such as using json to precipitate business data, ordinary SQL functions may not be able to meet the requirements and require some user-defined processing logic. In order to deal with the business data of the json class, we can expand the json into multiple rows for statistical analysis. Today we introduce the use of UDF (lambda) to write custom logic to deal with json, array, map type data.

Scene

The log service has built-in 20 + class SQL functions. In the face of users' complex business scenarios, such as using json to precipitate business data, ordinary SQL functions may not be able to meet the requirements and require some user-defined processing logic. In order to deal with the business data of the json class, we can expand the json into multiple rows for statistical analysis. Today we introduce the use of UDF (lambda) to write custom logic to deal with json, array, map type data.

Sample data:

_ _ source__: 11.164.232.105The tagged goods are covered by a variety of hostnameplates: vm-req-170103232316569850-tianchi111932.tc__topic__: TestTopic_4array_column: [1Magazine 2] double_column: 1.23map_column: {"a": 1, "b": 2} text_column: commodity lambda function to find the average of data of array type

In order to iterate through each array element and calculate the mean of all the elements, we calculate it through the reduce function.

| | select array_column, reduce (cast (json_parse (array_column) as array (bigint)), CAST (ROW (0.0,0) AS ROW (sum DOUBLE, count INTEGER)), (smeme x)-> cast (row (x + s.sum, s.count+1) as ROW (sum double, count INTEGER)), s-> IF (s.count = 0, NULL, s.sum / s.count)) |

The specific semantics of the reduce function refer to the syntax document. The parameters are divided into four parts.

Cast (json_parse (array_column) as array (bigint)) represents the input array data

CAST (ROW (0.0,0) AS ROW (sum DOUBLE, count INTEGER)) defines the starting state as a complex row type, recording sum and count, respectively.

For each element, the cumulative value is calculated, (cast x)-> cast (row (x + s.sum, s.count+1) as ROW (sum double, count INTEGER)) s represents the existing state, x represents the newly entered element, and the calculation result is forcibly defined as the row type through cast

Finally, for the final state, calculate the avg value, s-> IF (s.count = 0, NULL, s.sum / s.count). S represents the final state.

Avg:* the array elements of all lines | select sum (rows.sum) / sum (rows.count) from (select array_column, reduce (json_parse (array_column) as array (bigint)), CAST (ROW (0.0,0) AS ROW (sum DOUBLE, count INTEGER)), (sdepartment x)-> cast (row (x + s.sum, s.count+1) as ROW (sum double, count INTEGER)) S-> s) as rows from log)

By subquery, first reduce the sum and count of the array of each row. After that, in the nested query, we get the sum and count of all rows, and then divide each other to get avg:

This is the answer to the analysis of questions using UDF and lambda functions in SQL. I hope the above content can be of some help to you. If you still have a lot of doubts to solve, you can follow the industry information channel for more related knowledge.

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.