group_concat
description
Syntax
VARCHAR GROUP_CONCAT([DISTINCT] VARCHAR str[, VARCHAR sep] [ORDER BY { col_name | expr} [ASC | DESC])
該函數是類似于 sum() 的聚合函數，group_concat 將結果集中的多行結果連接成一個字符串。第二個參數 sep 為字符串之間的連接符號，該參數可以省略。該函數通常需要和 group by 語句一起使用。
mysql> select value from test;
+-------+
| value |
+-------+
| a |
| b |
| c |
| c |
+-------+mysql> select GROUP_CONCAT(value) from test;
+-----------------------+
| GROUP_CONCAT(`value`) |
+-----------------------+
| a, b, c, c |
+-----------------------+mysql> select GROUP_CONCAT(DISTINCT value) from test;
+-----------------------+
| GROUP_CONCAT(`value`) |
+-----------------------+
| a, b, c |
+-----------------------+mysql> select GROUP_CONCAT(value, " ") from test;
+----------------------------+
| GROUP_CONCAT(`value`, ' ') |
+----------------------------+
| a b c c |
+----------------------------+mysql> select GROUP_CONCAT(value, NULL) from test;
+----------------------------+
| GROUP_CONCAT(`value`, NULL)|
+----------------------------+
| NULL |
+----------------------------+SELECT abs(k3), group_concat(distinct cast(abs(k2) as varchar) order by abs(k1), k5) FROM bigtable group by abs(k3) order by abs(k3); +------------+-------------------------------------------------------------------------------+
| abs(`k3`) | group_concat(DISTINCT CAST(abs(`k2`) AS CHARACTER), ORDER BY abs(`k1`), `k5`) |
+------------+-------------------------------------------------------------------------------+
| 103 | 255 |
| 1001 | 1989, 1986 |
| 1002 | 1989, 32767 |
| 3021 | 1991, 32767, 1992 |
| 5014 | 1985, 1991 |
| 25699 | 1989 |
| 2147483647 | 255, 1991, 32767, 32767 |
+------------+-------------------------------------------------------------------------------+
row_number
description
為每個 Partition 的每一行返回一個從1開始連續遞增的整數。與 RANK() 和 DENSE_RANK() 不同的是，ROW_NUMBER() 返回的值不會重復也不會出現空缺，是連續遞增的。
ROW_NUMBER() OVER(partition_by_clause order_by_clause)
select x, y, row_number() over(partition by x order by y) as rank from int_t;| x | y | rank |
|---|------|----------|
| 1 | 1 | 1 |
| 1 | 2 | 2 |
| 1 | 2 | 3 |
| 2 | 1 | 1 |
| 2 | 2 | 2 |
| 2 | 3 | 3 |
| 3 | 1 | 1 |
| 3 | 1 | 2 |
| 3 | 2 | 3 |
timestampdiff
description
Syntax
INT TIMESTAMPDIFF(unit, DATETIME datetime_expr1, DATETIME datetime_expr2)
返回datetime_expr2?datetime_expr1，其中datetime_expr1和datetime_expr2是日期或日期時間表達式。
結果(整數)的單位由unit參數給出。interval的單位由unit參數給出，它應該是下列值之一:
SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, or YEAR。
MySQL> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
+--------------------------------------------------------------------+
| timestampdiff(MONTH, '2003-02-01 00:00:00', '2003-05-01 00:00:00') |
+--------------------------------------------------------------------+
| 3 |
+--------------------------------------------------------------------+MySQL> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
+-------------------------------------------------------------------+
| timestampdiff(YEAR, '2002-05-01 00:00:00', '2001-01-01 00:00:00') |
+-------------------------------------------------------------------+
| -1 |
+-------------------------------------------------------------------+MySQL> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
+---------------------------------------------------------------------+
| timestampdiff(MINUTE, '2003-02-01 00:00:00', '2003-05-01 12:05:55') |
+---------------------------------------------------------------------+
| 128885 |
+---------------------------------------------------------------------+
array_distinct
description
Syntax
ARRAY<T> array_distinct(ARRAY<T> arr)
返回去除了重復元素的數組，如果輸入數組為NULL，則返回NULL。
notice
僅支持向量化引擎中使用
mysql> select k1, k2, array_distinct(k2) from array_test;
+------+-----------------------------+---------------------------+
| k1 | k2 | array_distinct(k2) |
+------+-----------------------------+---------------------------+
| 1 | [1, 2, 3, 4, 5] | [1, 2, 3, 4, 5] |
| 2 | [6, 7, 8] | [6, 7, 8] |
| 3 | [] | [] |
| 4 | NULL | NULL |
| 5 | [1, 2, 3, 4, 5, 4, 3, 2, 1] | [1, 2, 3, 4, 5] |
| 6 | [1, 2, 3, NULL] | [1, 2, 3, NULL] |
| 7 | [1, 2, 3, NULL, NULL] | [1, 2, 3, NULL] |
+------+-----------------------------+---------------------------+mysql> select k1, k2, array_distinct(k2) from array_test01;
+------+------------------------------------------+---------------------------+
| k1 | k2 | array_distinct(`k2`) |
+------+------------------------------------------+---------------------------+
| 1 | ['a', 'b', 'c', 'd', 'e'] | ['a', 'b', 'c', 'd', 'e'] |
| 2 | ['f', 'g', 'h'] | ['f', 'g', 'h'] |
| 3 | [''] | [''] |
| 3 | [NULL] | [NULL] |
| 5 | ['a', 'b', 'c', 'd', 'e', 'a', 'b', 'c'] | ['a', 'b', 'c', 'd', 'e'] |
| 6 | NULL | NULL |
| 7 | ['a', 'b', NULL] | ['a', 'b', NULL] |
| 8 | ['a', 'b', NULL, NULL] | ['a', 'b', NULL] |
+------+------------------------------------------+---------------------------+
split_by_string
description
Syntax
ARRAY<STRING> split_by_string(STRING s, STRING separator) 將字符串拆分為由字符串分隔的子字符串。它使用多個字符的常量字符串分隔符作為分隔符。如果字符串分隔符為空，它將字符串拆分為單個字符數組。
Arguments
separator — 分隔符是一個字符串，是用來分割的標志字符. 類型: String
s — 需要分割的字符串. 類型: String
Returned value(s)
返回一個包含子字符串的數組. 以下情況會返回空的子字符串:
需要分割的字符串的首尾是分隔符;
多個分隔符連續出現;
需要分割的字符串為空，而分隔符不為空.
Type: Array(String)
notice
僅支持向量化引擎中使用
select split_by_string('a1b1c1d','1');
+---------------------------------+
| split_by_string('a1b1c1d', '1') |
+---------------------------------+
| ['a', 'b', 'c', 'd'] |
+---------------------------------+select split_by_string(',,a,b,c,',',');
+----------------------------------+
| split_by_string(',,a,b,c,', ',') |
+----------------------------------+
| ['', '', 'a', 'b', 'c', ''] |
+----------------------------------+SELECT split_by_string(NULL,',');
+----------------------------+
| split_by_string(NULL, ',') |
+----------------------------+
| NULL |
+----------------------------+select split_by_string('a,b,c,abcde',',');
+-------------------------------------+
| split_by_string('a,b,c,abcde', ',') |
+-------------------------------------+
| ['a', 'b', 'c', 'abcde'] |
+-------------------------------------+select split_by_string('1,,2,3,,4,5,,abcde', ',,');
+---------------------------------------------+
| split_by_string('1,,2,3,,4,5,,abcde', ',,') |
+---------------------------------------------+
| ['1', '2,3', '4,5', 'abcde'] |
+---------------------------------------------+select split_by_string(',,,,',',,');
+-------------------------------+
| split_by_string(',,,,', ',,') |
+-------------------------------+
| ['', '', ''] |
+-------------------------------+select split_by_string(',,a,,b,,c,,',',,');
+--------------------------------------+
| split_by_string(',,a,,b,,c,,', ',,') |
+--------------------------------------+
| ['', 'a', 'b', 'c', ''] |
+--------------------------------------+
