全部产品
阿里云办公

Lambda表达式和函数

更新时间:2017-10-17 17:20:31

Lambda Expression

Lambda表达式写为 ->:

  1. x -> x + 1
  2. (x, y) -> x + y
  3. x -> regexp_like(x, 'a+')
  4. x -> x[1] / x[2]
  5. x -> IF(x > 0, x, -x)
  6. x -> COALESCE(x, 0)
  7. x -> CAST(x AS JSON)

大多数SQL表达式可以在lambda体中使用,少数的需要注意: 不支持子查询. TRY 函数不支持. (try_cast() 支持.) * Capture is not supported yet:

  • Columns or relations cannot be referenced.
  • Only lambda variables from the inner-most lambda expression can be referenced.

Lambda Functions

filter(array<T>, function<T, boolean>) → ARRAY<T>

从 array 的元素中获取可以使 function 返回true的元素构成新的数组:

  1. SELECT filter(ARRAY [], x -> true); -- []
  2. SELECT filter(ARRAY [5, -6, NULL, 7], x -> x > 0); -- [5, 7]
  3. SELECT filter(ARRAY [5, NULL, 7, NULL], x -> x IS NOT NULL); -- [5, 7]

map_filter(map<K, V>, function<K, V, boolean>) → MAP<K,V>

从 map 的元素中获取使 function 返回true的元素构成新的数组:

  1. SELECT map_filter(MAP(ARRAY[], ARRAY[]), (k, v) -> true); -- {}
  2. SELECT map_filter(MAP(ARRAY[10, 20, 30], ARRAY['a', NULL, 'c']), (k, v) -> v IS NOT NULL); -- {10 -> a, 30 -> c}
  3. SELECT map_filter(MAP(ARRAY['k1', 'k2', 'k3'], ARRAY[20, 3, 15]), (k, v) -> v > 10); -- {k1 -> 20, k3 -> 15}

transform(array<T>, function<T, U>) → ARRAY<U>

收集 array 的每个元素作为 function 的输入得到的结果构建新的数组:

  1. SELECT transform(ARRAY [], x -> x + 1); -- []
  2. SELECT transform(ARRAY [5, 6], x -> x + 1); -- [6, 7]
  3. SELECT transform(ARRAY [5, NULL, 6], x -> COALESCE(x, 0) + 1); -- [6, 1, 7]
  4. SELECT transform(ARRAY ['x', 'abc', 'z'], x -> x || '0'); -- ['x0', 'abc0', 'z0']
  5. SELECT transform(ARRAY [ARRAY [1, NULL, 2], ARRAY[3, NULL]], a -> filter(a, x -> x IS NOT NULL)); -- [[1, 2], [3]]

reduce(array<T>, initialState S, inputFunction<S, T, S>, outputFunction<S, R>) → R

reduce array 的结果是单个值. inputFunction 将会按顺序读取 array 的元素. 在获取元素的同时, inputFunction 获取当前的状态, 初始化 initialState, 并返回一个新的值. outputFunction 将会被唤醒将结果置为最终状态. 它可能是 (i -> i) 这样的函数. 例如:

  1. SELECT reduce(ARRAY [], 0, (s, x) -> s + x, s -> s); -- 0
  2. SELECT reduce(ARRAY [5, 20, 50], 0, (s, x) -> s + x, s -> s); -- 75
  3. SELECT reduce(ARRAY [5, 20, NULL, 50], 0, (s, x) -> s + x, s -> s); -- NULL
  4. SELECT reduce(ARRAY [5, 20, NULL, 50], 0, (s, x) -> s + COALESCE(x, 0), s -> s); -- 75
  5. SELECT reduce(ARRAY [5, 20, NULL, 50], 0, (s, x) -> IF(x IS NULL, s, s + x), s -> s); -- 75
  6. SELECT reduce(ARRAY [2147483647, 1], CAST (0 AS BIGINT), (s, x) -> s + x, s -> s); -- 2147483648
  7. SELECT reduce(ARRAY [5, 6, 10, 20], -- calculates arithmetic average: 10.25
  8. CAST(ROW(0.0, 0) AS ROW(sum DOUBLE, count INTEGER)),
  9. (s, x) -> CAST(ROW(x + s.sum, s.count + 1) AS ROW(sum DOUBLE, count INTEGER)),
  10. s -> IF(s.count = 0, NULL, s.sum / s.count));