Help Center/ MapReduce Service/ Component Operation Guide (LTS)/ Using HetuEngine/ Common HetuEngine SQL Syntax/ SQL Functions and Operators/ Lambda Expression
Updated on 2024-10-25 GMT+08:00
View PDF
Share

Lambda Expression

The Lambda expression can be represented by ->.

x->x+1
(x,y)->x+y
x->regexp_like(x,'a+')
x->x[1]/x[2]
x->IF(x>0,x,-x)
x->COALESCE(x,0)
x->CAST(xASJSON)
x->x+TRY(1/0)

Most SQL expressions can be used in the Lambda function body except in the following scenarios:

  • Subqueries are not supported. 
    x -> 2 + (SELECT 3)
  • Aggregate functions are not supported. 
    x -> max(y)

Examples

  • Use the transform() function to obtain the square of array elements. 
    SELECT numbers, transform(numbers, n -> n * n) as squared_numbers FROM (VALUES (ARRAY[1, 2]),(ARRAY[3, 4]),(ARRAY[5, 6, 7])) AS t(numbers);
  numbers  | squared_numbers 
-----------|-----------------
 [1, 2]    | [1, 4]          
 [3, 4]    | [9, 16]         
 [5, 6, 7] | [25, 36, 49]    
(3 rows)
  • Use the transform() function to convert array elements into strings. If the conversion fails, the array elements will be converted into NULL to avoid errors. 
    SELECT transform(prices, n -> TRY_CAST(n AS VARCHAR) || '$') as price_tags FROM (VALUES (ARRAY[100, 200]),(ARRAY[30, 4])) AS t(prices);
  price_tags  
--------------
 [100$, 200$] 
 [30$, 4$]    
(2 rows)
  • When an operation is performed on an array element, other columns can also be used in the operation. For example, use transform() to calculate the linear equation f(x) =ax + b. 
    SELECT xvalues, a, b, transform(xvalues, x -> a * x + b) as linear_function_values FROM (VALUES (ARRAY[1, 2], 10, 5), (ARRAY[3, 4], 4, 2)) AS t(xvalues, a, b);
 xvalues | a  | b | linear_function_values 
---------|----|---|------------------------
 [1, 2]  | 10 | 5 | [15, 25]               
 [3, 4]  |  4 | 2 | [14, 18]               
(2 rows)
  • Use any_match() to search for an array where at least one element is greater than 100. 
    SELECT numbers FROM (VALUES (ARRAY[1,NULL,3]), (ARRAY[10,200,30]), (ARRAY[100,20,300])) AS t(numbers) WHERE any_match(numbers, n ->  COALESCE(n, 0) > 100);
    numbers     
----------------
 [10, 200, 30]  
 [100, 20, 300] 
(2 rows)
  • Use regexp_replace() to capitalize the first letter. 
    SELECT regexp_replace('once upon a time ...', '^(\w)(\w*)(\s+.*)$',x -> upper(x[1]) || x[2] || x[3]); -- Once upon a time ...
  • Use the Lambda expression in the aggregate function. For example, use reduce_agg() to calculate the sum of elements by column. 
    SELECT reduce_agg(value, 0, (a, b) -> a + b, (a, b) -> a + b) sum_values FROM (VALUES (1), (2), (3), (4), (5)) AS t(value);
 sum_values 
------------
         15 
(1 row)