ReaderMX.
PostgreSQL码农集散地

DuckDB:list,struct,map 类型(支持lambda计算)

Pdigoal约 6 分钟

文章开始前推荐2个学习环境: 

1、欢迎使用镜像快速体验PostgreSQL/DuckDB强大功能:《最好的PostgreSQL学习镜像》

2、欢迎使用云起实验室: 《免费体验PolarDB开源数据库》

3、PolarDB开源数据库内核、应用等学习图谱:  https://www.aliyun.com/database/openpolardb/activity 

背景

DuckDB支持三种嵌套类型:

  • list: 有序数组, 每个元素的类型必须一致

  • struct: kv字典, key必须是字符串类型, 值可以是任意类型

  • map: kv字典, key和value可以是任意类型, 但是所有key的类型必须统一, 所有value的类型必须统一.(实际测试发现并不需要统一, 不知道是不是bug)

当作为字段类型时, 还有一些约束:

  • list: 每一行的数组元素类型必须一致, 但是元素个数可以不一样. INT[] [1, 2, 3]

  • struct: 每一行的key name必须一致 STRUCT(i INT, j VARCHAR) {'i': 42, 'j': 'a'}

  • map: 每一行的key name可以不一样 MAP(INT, VARCHAR) map([1,2],['a','b'])

list,struct,map可以任意嵌套.

-- Struct with lists  
SELECT {'birds': ['duck', 'goose', 'heron'], 'aliens': NULL, 'amphibians': ['frog', 'toad']};  
  -- Struct with list of maps  
SELECT {'test': [map([1, 5], [42.1, 45]), map([1, 5], [42.1, 45])]};

详见:
https://duckdb.org/docs/sql/data_types/overview

https://duckdb.org/docs/sql/data_types/list

https://duckdb.org/docs/sql/data_types/struct

https://duckdb.org/docs/sql/data_types/map

list,struct,map相关的函数用法:

https://duckdb.org/docs/sql/functions/nested

list和pg的数组比较像, 但是duckdb list提供了更多内置的函数, 用起来更灵活, 例如排序、统计(sum,avg,distinct,中位数,柱状图等等)、pop、push等:

  • list_prepend

  • list_append

  • array_pop_front

  • array_pop_back

  • array_sort

  • array_reverse_sort

-- default sort order and default NULL sort order  
SELECT list_sort([1, 3, NULL, 5, NULL, -5])  
----  
[NULL, NULL, -5, 1, 3, 5]  
  -- only providing the sort order  
SELECT list_sort([1, 3, NULL, 2], 'ASC')  
----  
[NULL, 1, 2, 3]  
  -- providing the sort order and the NULL sort order  
SELECT list_sort([1, 3, NULL, 2], 'DESC', 'NULLS FIRST')  
----  
[NULL, 3, 2, 1]  
  -- default NULL sort order  
SELECT list_sort([1, 3, NULL, 5, NULL, -5])  
----  
[NULL, NULL, -5, 1, 3, 5]  
  -- providing the NULL sort order  
SELECT list_reverse_sort([1, 3, NULL, 2], 'NULLS LAST')  
----  
[3, 2, 1, NULL]

list支持多种聚合算法:

The following is a list of existing rewrites. Rewrites simplify the use of the list aggregate function by only taking the list (column) as their argument.

  • list_avg, list_var_samp, list_var_pop, list_stddev_pop, list_stddev_samp, list_sem, list_approx_count_distinct, list_bit_xor, list_bit_or, list_bit_and, list_bool_and, list_bool_or, list_count, list_entropy, list_last, list_first, list_kurtosis, list_min, list_max, list_product, list_skewness, list_sum, list_string_agg, list_mode, list_median, list_mad and list_histogram.

SELECT list_aggregate([1, 2, -4, NULL], 'min');  
-- -4  
  SELECT list_aggregate([2, 4, 8, 42], 'sum');  
-- 56  
  SELECT list_aggregate([[1, 2], [NULL], [2, 10, 3]], 'last');  
-- [2, 10, 3]  
  SELECT list_min([1, 2, -4, NULL]);  
-- -4  
  SELECT list_sum([2, 4, 8, 42]);  
-- 56  
  SELECT list_last([[1, 2], [NULL], [2, 10, 3]]);  
-- [2, 10, 3]

list还支持lambda函数计算, 用于转换list的value, 或者过滤list的value.

list_transform(list, lambda)

Returns a list that is the result of applying the lambda function to each element of the input list. See the Lambda Functions section for more details.

In the descriptions, l is the three element list [4, 5, 6].

list_transform(l, x -> x + 1)  
  [5, 6, 7]

list_filter(list, lambda)

Constructs a list from those elements of the input list for which the lambda function returns true. See the Lambda Functions section for more details.

list_filter(l, x -> x > 4)	  
  [5, 6]

(parameter1, parameter2, ...) -> expression. If the lambda function has only one parameter, then the brackets can be omitted. The parameters can have any names.

lambda函数的变量名可以任意取, 计算时变量被替换为list的元素value.

param -> param > 1  
duck -> CONTAINS(CONCAT(duck, 'DB'), 'duck')  
(x, y) -> x + y

list元素转换

list_transform(list, lambda)

Returns a list that is the result of applying the lambda function to each element of the input list. The lambda function must have exactly one left-hand side parameter. The return type of the lambda function defines the type of the list elements.

-- incrementing each list element by one  
SELECT list_transform([1, 2, NULL, 3], x -> x + 1)  
----  
[2, 3, NULL, 4]  
  -- transforming strings  
SELECT list_transform(['duck', 'a', 'b'], duck -> CONCAT(duck, 'DB'))  
----  
[duckDB, aDB, bDB]  
  -- combining lambda functions with other functions  
SELECT list_transform([5, NULL, 6], x -> COALESCE(x, 0) + 1)  
----  
[6, 1, 7]

list元素过滤

list_filter(list, lambda)

Constructs a list from those elements of the input list for which the lambda function returns true. The lambda function must have exactly one left-hand side parameter and its return type must be of type BOOLEAN.

-- filter out negative values, 留下大于0的元素  
SELECT list_filter([5, -6, NULL, 7], x -> x > 0)  
----  
[5, 7]  
  -- divisible by 2 and 5, 2和5的公倍数  
SELECT list_filter(list_filter([2, 4, 3, 1, 20, 10, 3, 30], x -> x % 2 == 0), y -> y % 5 == 0)  
----  
[20, 10, 30]  
  -- in combination with range(...) to construct lists , #1表示range返回的第一列.    
SELECT list_filter([1, 2, 3, 4], x -> x > #1) FROM range(4)  
----  
[1, 2, 3, 4]  
[2, 3, 4]  
[3, 4]  
[4]  
[]  
    D select #1 > 1, #1 from range(10);  
| #1 > 1 | range |  
|--------|-------|  
| false  | 0     |  
| false  | 1     |  
| true   | 2     |  
| true   | 3     |  
| true   | 4     |  
| true   | 5     |  
| true   | 6     |  
| true   | 7     |  
| true   | 8     |  
| true   | 9     |

Lambda functions can be arbitrarily nested.

-- nested lambda functions to get all squares of even list elements  
SELECT list_transform(list_filter([0, 1, 2, 3, 4, 5], x -> x % 2 = 0), y -> y * y)  
----  
[0, 4, 16]

DuckDB list,struct,map类型, PG对应array, row_type|record, 无(或者jsonb比较接近). 但是PG的array没有这么多的算法, 还有待增强. PG array支持GIN倒排索引倒是一个亮点, 特别适合标签匹配、相似度查询. (例如用户画像、一对多的数据模型等场景)

https://pgxn.org/dist/aggs_for_arrays/

欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.  
近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号:
Image
文章中的参考文档请点击阅读原文获得. 

KEEP THE THREAD GOING

更多来源文章