Array関数

SELECT array(toInt32(1), toUInt16(2), toInt8(3)) AS a, toTypeName(a)

┌─a───────┬─toTypeName(a)─┐
│ [1,2,3] │ Array(Int32)  │
└─────────┴───────────────┘

SELECT array(toInt32(5), toDateTime('1998-06-16'), toInt8(5)) AS a, toTypeName(a)

┌─a───────────────────────────┬─toTypeName(a)─────────────────────────┐
│ [5,'1998-06-16 00:00:00',5] │ Array(Variant(DateTime, Int32, Int8)) │
└─────────────────────────────┴───────────────────────────────────────┘

SELECT arrayAUCPR([0.1, 0.4, 0.35, 0.8], [0, 0, 1, 1]);

┌─arrayAUCPR([0.1, 0.4, 0.35, 0.8], [0, 0, 1, 1])─┐
│                              0.8333333333333333 │
└─────────────────────────────────────────────────┘

SELECT arrayAll(x, y -> x=y, [1, 2, 3], [1, 2, 3])

1

SELECT arrayAll(x, y -> x=y, [1, 2, 3], [1, 1, 1])

0

SELECT arrayAutocorrelation([1, 2, 3, 4, 5]);

[1,0.4,-0.1,-0.4,-0.4]

SELECT arrayAutocorrelation([10, 20, 10]);

[1,-0.6666666666666669,0.16666666666666674]

SELECT arrayAutocorrelation([5, 5, 5]);

[nan,nan,nan]

SELECT arrayAutocorrelation([1, 2, 3, 4, 5], 2);

[1,0.4]

SELECT arrayAvg([1, 2, 3, 4]);

2.5

SELECT arrayAvg(x, y -> x*y, [2, 3], [2, 3]) AS res;

6.5

SELECT arrayBottomK(3, [1, 5, 2, 7, 3])

[1,2,3]

SELECT arrayBottomK(3, [1, NULL, 5, 2, NULL, 7])

[1,2,5]

SELECT arrayBottomK(5, [1, NULL, 2])

[1,2]

SELECT arrayBottomK((x) -> -x, 2, [5, 9, 1, 3])

[9,5]

SELECT arrayBottomK((x, y) -> y, 2, ['a', 'b', 'c'], [3, 1, 2])

['b','c']

SELECT arrayCompact([1, 1, nan, nan, 2, 3, 3, 3]);

[1,nan,2,3]

SELECT arrayConcat([1, 2], [3, 4], [5, 6]) AS res

[1,2,3,4,5,6]

SELECT arrayCount(x -> (x % 2), groupArray(number)) FROM numbers(10)

5

SELECT arrayCumSum([1, 1, 1, 1]) AS res

[1,2,3,4]

SELECT arrayCumSum(x -> x * 2, [1, 2, 3]) AS res

[2,6,12]

SELECT arrayCumSumNonNegative([1, 1, -4, 1]) AS res

[1,2,0,1]

SELECT arrayCumSumNonNegative(x -> x * 2, [1, -2, 3]) AS res

[2,0,6]

SELECT arrayDifference([1, 2, 3, 4]);

[0,1,1,1]

SELECT arrayDifference([0, 10000000000000000000]);

┌─arrayDifference([0, 10000000000000000000])─┐
│ [0,-8446744073709551616]                   │
└────────────────────────────────────────────┘

SELECT arrayDistinct([1, 2, 2, 3, 1]);

[1,2,3]

SELECT arrayDotProduct([1, 2, 3], [4, 5, 6]) AS res, toTypeName(res);

32	UInt16

SELECT dotProduct((1::UInt16, 2::UInt8, 3::Float32),(4::Int16, 5::Float32, 6::UInt8)) AS res, toTypeName(res);

32	Float64

SELECT arrayElement(arr, 2) FROM (SELECT [1, 2, 3] AS arr)

2

SELECT arrayElement(arr, -1) FROM (SELECT [1, 2, 3] AS arr)

3

SELECT arr[2] FROM (SELECT [1, 2, 3] AS arr)

2

SELECT arrayElement(arr, 4) FROM (SELECT [1, 2, 3] AS arr)

0

SELECT [10, 20, 30, 40][[2, 4, 1]]

[20,40,10]

SELECT map('a', 1, 'b', 2)['b']

2

SELECT json['a'] FROM (SELECT '{"a" : 42}'::JSON AS json)

42

SELECT arrayElementOrNull(arr, 2) FROM (SELECT [1, 2, 3] AS arr)

2

SELECT arrayElementOrNull(arr, -1) FROM (SELECT [1, 2, 3] AS arr)

3

SELECT arrayElementOrNull(arr, 4) FROM (SELECT [1, 2, 3] AS arr)

\N

SELECT arrayElementOrNull([10, 20, 30], [1, 5, 2])

[10,NULL,20]

SELECT arrayElementOrNull(map('a', 1), 'z')

\N

CREATE TABLE test
(
    `id` UInt8,
    `tag` Array(String),
    `version` Array(String)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO test VALUES (1, ['release-stable', 'dev', 'security'], ['2.4.0', '2.6.0-alpha', '2.4.0-sec1']);

SELECT
    id,
    tag,
    version,
    seq
FROM test
ARRAY JOIN
    tag,
    version,
    arrayEnumerate(tag) AS seq

┌─id─┬─tag────────────┬─version─────┬─seq─┐
│  1 │ release-stable │ 2.4.0       │   1 │
│  1 │ dev            │ 2.6.0-alpha │   2 │
│  1 │ security       │ 2.4.0-sec1  │   3 │
└────┴────────────────┴─────────────┴─────┘

SELECT arrayEnumerateDense([10, 20, 10, 30])

[1,2,1,3]

-- With clear_depth=1 and max_array_depth=1, the result is identical to what arrayEnumerateDense would give.

SELECT arrayEnumerateDenseRanked(1,[10, 20, 10, 30],1);

[1,2,1,3]

-- In this example, arrayEnumerateDenseRanked is used to obtain an array indicating, for each element of the
-- multidimensional array, what its position is among elements of the same value.
-- For the first row of the passed array, [10, 10, 30, 20], the corresponding first row of the result is [1, 1, 2, 3],
-- indicating that 10 is the first number encountered in position 1 and 2, 30 the second number encountered in position 3
-- and 20 is the third number encountered in position 4.
-- For the second row, [40, 50, 10, 30], the corresponding second row of the result is [4,5,1,2], indicating that 40
-- and 50 are the fourth and fifth numbers encountered in position 1 and 2 of that row, that another 10
-- (the first encountered number) is in position 3 and 30 (the second number encountered) is in the last position.

SELECT arrayEnumerateDenseRanked(1,[[10,10,30,20],[40,50,10,30]],2);

[[1,1,2,3],[4,5,1,2]]

-- Changing clear_depth=2 results in the enumeration occurring separately for each row anew.

SELECT arrayEnumerateDenseRanked(2,[[10,10,30,20],[40,50,10,30]],2);

[[1,1,2,3],[1,2,3,4]]

SELECT arrayEnumerateUniq([10, 20, 10, 30]);

[1,1,2,1]

SELECT arrayEnumerateUniq([1, 1, 1, 2, 2, 2], [1, 1, 2, 1, 1, 2]);

[1,2,1,1,2,1]

-- Each goal ID has a calculation of the number of conversions (each element in the Goals nested data structure is a goal that was reached, which we refer to as a conversion)
-- and the number of sessions. Without ARRAY JOIN, we would have counted the number of sessions as sum(Sign). But in this particular case,
-- the rows were multiplied by the nested Goals structure, so in order to count each session one time after this, we apply a condition to the
-- value of the arrayEnumerateUniq(Goals.ID) function.

CREATE TABLE visits (CounterID UInt32, Sign Int8, Goals Nested(ID UInt32)) ENGINE = Memory;

INSERT INTO visits VALUES
    (160656, 1, [53225, 53225, 56600]),
    (160656, 1, [53225, 2825062]),
    (160656, 1, [56600, 56600, 2825062]),
    (160657, 1, [53225]);

SELECT
    Goals.ID AS GoalID,
    sum(Sign) AS Reaches,
    sumIf(Sign, num = 1) AS Visits
FROM visits
ARRAY JOIN
    Goals,
    arrayEnumerateUniq(Goals.ID) AS num
WHERE CounterID = 160656
GROUP BY GoalID
ORDER BY Reaches DESC, GoalID
LIMIT 10

┌──GoalID─┬─Reaches─┬─Visits─┐
│   53225 │       3 │      2 │
│   56600 │       3 │      2 │
│ 2825062 │       2 │      2 │
└─────────┴─────────┴────────┘

-- With clear_depth=1 and max_array_depth=1, the result of arrayEnumerateUniqRanked
-- is identical to that which arrayEnumerateUniq would give for the same array.

SELECT arrayEnumerateUniqRanked(1, [1, 2, 1], 1);

[1,1,2]

-- with clear_depth=1 and max_array_depth=1, the result of arrayEnumerateUniqRanked
-- is identical to that which arrayEnumerateUniqwould give for the same array.

SELECT arrayEnumerateUniqRanked(1, [[1, 2, 3], [2, 2, 1], [3]], 2);

[[1,1,1],[2,3,2],[2]]

-- In this example, arrayEnumerateUniqRanked is used to obtain an array indicating,
-- for each element of the multidimensional array, what its position is among elements
-- of the same value. For the first row of the passed array, [1, 2, 3], the corresponding
-- result is [1, 1, 1], indicating that this is the first time 1, 2 and 3 are encountered.
-- For the second row of the provided array, [2, 2, 1], the corresponding result is [2, 3, 3],
-- indicating that 2 is encountered for a second and third time, and 1 is encountered
-- for the second time. Likewise, for the third row of the provided array [3] the
-- corresponding result is [2] indicating that 3 is encountered for the second time.

SELECT arrayEnumerateUniqRanked(1, [[1, 2, 3], [2, 2, 1], [3]], 2);

[[1,1,1],[2,3,2],[2]]

-- Changing clear_depth=2, results in elements being enumerated separately for each row.
SELECT arrayEnumerateUniqRanked(2,[[1, 2, 3],[2, 2, 1],[3]], 2);

[[1,1,1],[1,2,1],[1]]

SELECT arrayExcept([1, 2, 3, 2, 4], [3, 5])

[1,2,2,4]

SELECT arrayExcept([1, NULL, 2, NULL], [2])

[1,NULL,NULL]

SELECT arrayExcept([1, NULL, 2, NULL], [NULL, 2, NULL])

[1]

SELECT arrayExcept(['apple', 'banana', 'cherry'], ['banana', 'date'])

['apple','cherry']

SELECT arrayExists(x, y -> x=y, [1, 2, 3], [0, 0, 0])

0

SELECT arrayFill(x -> not isNull(x), [1, null, 2, null]) AS res

[1,1,2,2]

SELECT arrayFill(x, y, z -> x > y AND x < z, [5, 3, 6, 2], [4, 7, 1, 3], [10, 2, 8, 5]) AS res

[5,5,6,6]

SELECT arrayFilter(x -> x LIKE '%World%', ['Hello', 'abc World']) AS res

['abc World']

SELECT
    arrayFilter(
        (i, x) -> x LIKE '%World%',
        arrayEnumerate(arr),
        ['Hello', 'abc World'] AS arr)
    AS res

[2]

SELECT arrayFirst(x, y -> x=y, ['a', 'b', 'c'], ['c', 'b', 'a'])

b

SELECT arrayFirst(x, y -> x=y, [0, 1, 2], [3, 3, 3]) AS res, toTypeName(res)

0	UInt8

SELECT arrayFirstIndex(x, y -> x=y, ['a', 'b', 'c'], ['c', 'b', 'a'])

2

SELECT arrayFirstIndex(x, y -> x=y, ['a', 'b', 'c'], ['d', 'e', 'f'])

0

SELECT arrayFirstOrNull(x, y -> x=y, ['a', 'b', 'c'], ['c', 'b', 'a'])

b

SELECT arrayFirstOrNull(x, y -> x=y, [0, 1, 2], [3, 3, 3]) AS res, toTypeName(res)

\N	Nullable(UInt8)

SELECT arrayFlatten([[[1]], [[2], [3]]);

[1,2,3]

SELECT arrayFlattenedLength([[1, 2], [3, 4]]);

4

SELECT arrayFlattenedLength([[[1]], [[2], [3]]]);

3

SELECT arrayFlattenedLength([1, 2, 3]);

3

SELECT arrayFlattenedLength([map('a', [1, 2, 3])]);

1

SELECT arrayFold(acc,x -> acc + x*2, [1, 2, 3, 4], 3::Int64) AS res;

23

SELECT arrayFold(acc, x -> (acc.2, acc.2 + acc.1),range(number),(1::Int64, 0::Int64)).1 AS fibonacci FROM numbers(1,10);

┌─fibonacci─┐
│         0 │
│         1 │
│         1 │
│         2 │
│         3 │
│         5 │
│         8 │
│        13 │
│        21 │
│        34 │
└───────────┘

SELECT arrayFold(
(acc, x, y) -> acc + (x * y),
[1, 2, 3, 4],
[10, 20, 30, 40],
0::Int64
) AS res;

300

SELECT
arrayIntersect([1, 2], [1, 3], [2, 3]) AS empty_intersection,
arrayIntersect([1, 2], [1, 3], [1, 4]) AS non_empty_intersection

┌─empty_intersection─┬─non_empty_intersection─┐
│ []                 │ [1]                    │
└────────────────────┴────────────────────────┘

SELECT arrayJaccardIndex([1, 2], [2, 3]) AS res

0.3333333333333333

SELECT arrayJoin([1, 2, 3] AS src) AS dst, 'Hello', src

┌─dst─┬─'Hello'─┬─src─────┐
│   1 │ Hello   │ [1,2,3] │
│   2 │ Hello   │ [1,2,3] │
│   3 │ Hello   │ [1,2,3] │
└─────┴─────────┴─────────┘

-- The arrayJoin function affects all sections of the query, including the WHERE section. Notice the result 2, even though the subquery returned 1 row.

SELECT sum(1) AS impressions
FROM
(
    SELECT ['Istanbul', 'Berlin', 'Bobruisk'] AS cities
)
WHERE arrayJoin(cities) IN ['Istanbul', 'Berlin'];

┌─impressions─┐
│           2 │
└─────────────┘

-- A query can use multiple arrayJoin functions. In this case, the transformation is performed multiple times and the rows are multiplied.

SELECT
    sum(1) AS impressions,
    arrayJoin(cities) AS city,
    arrayJoin(browsers) AS browser
FROM
(
    SELECT
        ['Istanbul', 'Berlin', 'Bobruisk'] AS cities,
        ['Firefox', 'Chrome', 'Chrome'] AS browsers
)
GROUP BY
    2,
    3
ORDER BY
    city,
    browser

┌─impressions─┬─city─────┬─browser─┐
│           2 │ Berlin   │ Chrome  │
│           1 │ Berlin   │ Firefox │
│           2 │ Bobruisk │ Chrome  │
│           1 │ Bobruisk │ Firefox │
│           2 │ Istanbul │ Chrome  │
│           1 │ Istanbul │ Firefox │
└─────────────┴──────────┴─────────┘

-- Using multiple arrayJoin with the same expression may not produce the expected result due to optimizations.
-- For these cases, consider modifying the repeated array expression with extra operations that do not affect join result.
-- e.g. arrayJoin(arraySort(arr)), arrayJoin(arrayConcat(arr, []))

SELECT
    arrayJoin(dice) as first_throw,
    /* arrayJoin(dice) as second_throw */ -- is technically correct, but will annihilate result set
    arrayJoin(arrayConcat(dice, [])) as second_throw -- intentionally changed expression to force re-evaluation
FROM (
    SELECT [1, 2, 3, 4, 5, 6] as dice
);

┌─first_throw─┬─second_throw─┐
│           1 │            1 │
│           1 │            2 │
│           1 │            3 │
│           1 │            4 │
│           1 │            5 │
│           1 │            6 │
│           2 │            1 │
│           2 │            2 │
│           2 │            3 │
│           2 │            4 │
│           2 │            5 │
│           2 │            6 │
│           3 │            1 │
│           3 │            2 │
│           3 │            3 │
│           3 │            4 │
│           3 │            5 │
│           3 │            6 │
│           4 │            1 │
│           4 │            2 │
│           4 │            3 │
│           4 │            4 │
│           4 │            5 │
│           4 │            6 │
│           5 │            1 │
│           5 │            2 │
│           5 │            3 │
│           5 │            4 │
│           5 │            5 │
│           5 │            6 │
│           6 │            1 │
│           6 │            2 │
│           6 │            3 │
│           6 │            4 │
│           6 │            5 │
│           6 │            6 │
└─────────────┴──────────────┘

-- Note the ARRAY JOIN syntax in the `SELECT` query below, which provides broader possibilities.
-- ARRAY JOIN allows you to convert multiple arrays with the same number of elements at a time.

SELECT
    sum(1) AS impressions,
    city,
    browser
FROM
(
    SELECT
        ['Istanbul', 'Berlin', 'Bobruisk'] AS cities,
        ['Firefox', 'Chrome', 'Chrome'] AS browsers
)
ARRAY JOIN
    cities AS city,
    browsers AS browser
GROUP BY
    2,
    3
ORDER BY
    2,
    3

┌─impressions─┬─city─────┬─browser─┐
│           1 │ Berlin   │ Chrome  │
│           1 │ Bobruisk │ Chrome  │
│           1 │ Istanbul │ Firefox │
└─────────────┴──────────┴─────────┘

-- You can also use Tuple

SELECT
    sum(1) AS impressions,
    (arrayJoin(arrayZip(cities, browsers)) AS t).1 AS city,
    t.2 AS browser
FROM
(
    SELECT
        ['Istanbul', 'Berlin', 'Bobruisk'] AS cities,
        ['Firefox', 'Chrome', 'Chrome'] AS browsers
)
GROUP BY
    2,
    3
ORDER BY
    2,
    3

┌─impressions─┬─city─────┬─browser─┐
│           1 │ Berlin   │ Chrome  │
│           1 │ Bobruisk │ Chrome  │
│           1 │ Istanbul │ Firefox │
└─────────────┴──────────┴─────────┘

SELECT arrayLast(x, y -> x=y, ['a', 'b', 'c'], ['a', 'b', 'c'])

c