数组与嵌套结构

SELECT
    [1, 2, 3]                     AS a,
    length(a)                     AS len,
    a[1]                          AS first,       -- 下标从 1 开始！
    a[-1]                         AS last,        -- 负下标从末尾数
    arrayConcat(a, [4, 5])        AS concat,
    arraySlice(a, 2, 2)           AS slice,       -- [2,3]
    has(a, 2)                     AS contains,    -- 1
    indexOf(a, 3)                 AS idx,         -- 3
    arrayReverse(a)               AS rev,
    arraySort([3, 1, 2])          AS sorted,
    arrayReverseSort([3, 1, 2])   AS desc_sorted,
    arrayDistinct([1, 1, 2, 2])   AS distinct_,
    arrayFlatten([[1,2],[3]])     AS flat,
    arrayEnumerate([10, 20, 30])  AS positions;   -- [1,2,3]

┌─a───────┬─len─┬─first─┬─last─┬─concat────┬─slice─┬─contains─┬─idx─┐
│ [1,2,3] │   3 │     1 │    3 │ [1,2,3,4,5]│ [2,3] │        1 │   3 │
└─────────┴─────┴───────┴──────┴───────────┴───────┴──────────┴─────┘

SELECT
    arrayIntersect([1,2,3], [2,3,4])      AS inter,     -- [2,3]
    arrayDifference([1, 3, 7])            AS diff,      -- [0,2,4] 相邻差值
    arrayCumSum([1, 2, 3])                AS cumsum,    -- [1,3,6]
    arrayCompact([1,1,2,2,1])             AS compact,   -- [1,2,1] 去掉连续重复
    arrayZip([1,2], ['a','b'])            AS zipped,    -- [(1,'a'),(2,'b')]
    arrayJoin([1,2,3])                    AS unnested;  -- 展开成 3 行，见下节

SELECT
    arraySum([1,2,3])       AS s,     -- 6
    arrayAvg([1,2,3])       AS a,     -- 2
    arrayMax([1,2,3])       AS mx,
    arrayMin([1,2,3])       AS mn,
    arrayProduct([2,3,4])   AS p;     -- 24

SELECT
    arrayMap(x -> x * 2, [1, 2, 3])                     AS doubled,      -- [2,4,6]
    arrayFilter(x -> x > 1, [1, 2, 3])                  AS filtered,     -- [2,3]
    arrayCount(x -> x > 1, [1, 2, 3])                   AS cnt,          -- 2
    arrayExists(x -> x > 2, [1, 2, 3])                  AS any_gt2,      -- 1
    arrayAll(x -> x > 0, [1, 2, 3])                     AS all_pos,      -- 1
    arraySum(x -> x * x, [1, 2, 3])                     AS sum_squares,  -- 14
    arraySort(x -> -x, [1, 3, 2])                       AS sort_desc,    -- [3,2,1]
    arrayFirst(x -> x > 1, [1, 2, 3])                   AS first_gt1,    -- 2
    arrayFirstIndex(x -> x > 1, [1, 2, 3])              AS first_idx;    -- 2

SELECT
    arrayMap((x, y) -> x + y, [1, 2, 3], [10, 20, 30])  AS summed,   -- [11,22,33]
    arrayFilter((x, y) -> y > 15, [1,2,3], [10,20,30])  AS filtered; -- [2,3]

SELECT
    arrayReduce('max', [1, 5, 3])                     AS mx,      -- 5
    arrayReduce('uniq', ['a','b','a'])                AS u,       -- 2
    arrayReduce('quantile(0.9)', [1,2,3,4,5,6,7,8,9,10]) AS p90;  -- 9.1

SELECT
    user_id,
    tag
FROM (
    SELECT 1001 AS user_id, ['vip', 'active', 'mobile'] AS tags
    UNION ALL
    SELECT 1002 AS user_id, ['new'] AS tags
)
ARRAY JOIN tags AS tag;

┌─user_id─┬─tag────┐
│    1001 │ vip    │
│    1001 │ active │
│    1001 │ mobile │
│    1002 │ new    │
└─────────┴────────┘

SELECT user_id, tag
FROM (
    SELECT 1001 AS user_id, ['vip'] AS tags
    UNION ALL
    SELECT 1002 AS user_id, [] AS tags
)
LEFT ARRAY JOIN tags AS tag;

┌─user_id─┬─tag─┐
│    1001 │ vip │
│    1002 │     │   ← 保留了，tag 为空串
└─────────┴─────┘

-- 两个等长数组按位置对齐展开
SELECT user_id, tag, score
FROM (
    SELECT 1001 AS user_id, ['a','b','c'] AS tags, [10, 20, 30] AS scores
)
ARRAY JOIN tags AS tag, scores AS score;

┌─user_id─┬─tag─┬─score─┐
│    1001 │ a   │    10 │
│    1001 │ b   │    20 │
│    1001 │ c   │    30 │
└─────────┴─────┴───────┘

-- 把每个用户的访问序列折叠成数组，再展开成相邻页面对
WITH user_paths AS (
    SELECT
        user_id,
        groupArray(page) AS pages
    FROM (SELECT user_id, page, event_time FROM events ORDER BY user_id, event_time)
    GROUP BY user_id
)
SELECT
    from_page,
    to_page,
    count() AS transitions
FROM user_paths
ARRAY JOIN
    arraySlice(pages, 1, length(pages) - 1) AS from_page,
    arraySlice(pages, 2)                    AS to_page
GROUP BY from_page, to_page
ORDER BY transitions DESC
LIMIT 5;

┌─from_page─┬─to_page───┬─transitions─┐
│ /p/183    │ /checkout │        1204 │
│ /p/427    │ /p/183    │        1188 │
│ /        │ /p/183    │        1155 │
│ /p/91     │ /checkout │        1142 │
│ /p/183    │ /         │        1098 │
└───────────┴───────────┴─────────────┘

CREATE TABLE events_nested
(
    event_time DateTime,
    user_id    UInt64,
    page       String,
    products Nested
    (
        id       UInt32,
        name     String,
        price    Decimal(10, 2),
        quantity UInt8
    )
)
ENGINE = MergeTree
ORDER BY (user_id, event_time);
 
INSERT INTO events_nested VALUES
(
    '2024-06-15 10:00:00', 1001, '/checkout',
    [101, 205, 388],                       -- products.id
    ['键盘', '鼠标', '显示器'],              -- products.name
    [299.00, 89.00, 1299.00],              -- products.price
    [1, 2, 1]                              -- products.quantity
);

-- 直接查是数组
SELECT user_id, products.id, products.name FROM events_nested;

┌─user_id─┬─products.id───┬─products.name──────────────────┐
│    1001 │ [101,205,388] │ ['键盘','鼠标','显示器']         │
└─────────┴───────────────┴────────────────────────────────┘

-- ARRAY JOIN 整个 Nested，所有子列同时展开
SELECT
    user_id,
    products.id       AS pid,
    products.name     AS pname,
    products.price    AS price,
    products.quantity AS qty,
    products.price * products.quantity AS subtotal
FROM events_nested
ARRAY JOIN products;

┌─user_id─┬─pid─┬─pname────┬───price─┬─qty─┬─subtotal─┐
│    1001 │ 101 │ 键盘     │  299.00 │   1 │   299.00 │
│    1001 │ 205 │ 鼠标     │   89.00 │   2 │   178.00 │
│    1001 │ 388 │ 显示器   │ 1299.00 │   1 │  1299.00 │
└─────────┴─────┴──────────┴─────────┴─────┴──────────┘

SELECT
    user_id,
    arraySum(arrayMap((p, q) -> p * q, products.price, products.quantity)) AS order_total,
    length(products.id) AS item_count
FROM events_nested;

┌─user_id─┬─order_total─┬─item_count─┐
│    1001 │     1776.00 │          3 │
└─────────┴─────────────┴────────────┘

CREATE TABLE events_map
(
    event_time DateTime,
    user_id    UInt64,
    event_type LowCardinality(String),
    props      Map(String, String)
)
ENGINE = MergeTree
ORDER BY (user_id, event_time);
 
INSERT INTO events_map VALUES
    ('2024-06-15 10:00:00', 1001, 'purchase',
     {'order_id': '9527', 'coupon': 'SUMMER20', 'channel': 'wechat'}),
    ('2024-06-15 10:05:00', 1002, 'view',
     {'ref': 'google', 'ab_test': 'B'});

SELECT
    user_id,
    props['coupon']              AS coupon,       -- 不存在返回默认值（空串）
    mapContains(props, 'coupon') AS has_coupon,
    mapKeys(props)               AS keys,
    mapValues(props)             AS vals,
    length(props)                AS prop_count
FROM events_map;

┌─user_id─┬─coupon───┬─has_coupon─┬─keys──────────────────────────────┬─prop_count─┐
│    1001 │ SUMMER20 │          1 │ ['channel','coupon','order_id']   │          3 │
│    1002 │          │          0 │ ['ab_test','ref']                 │          2 │
└─────────┴──────────┴────────────┴───────────────────────────────────┴────────────┘

SELECT user_id, k, v
FROM events_map
ARRAY JOIN mapKeys(props) AS k, mapValues(props) AS v;

SELECT
    (1, 'a', now())                    AS t,
    t.1                                AS first,      -- 1
    t.2                                AS second,     -- 'a'
    tupleElement(t, 3)                 AS third,
    untuple(t)                         AS unpacked;   -- 展开成多列

SELECT
    user_id,
    argMax((page, country, duration_ms), event_time) AS last,
    last.1 AS last_page,
    last.2 AS last_country
FROM events
GROUP BY user_id LIMIT 3;

SELECT count() FROM events WHERE (country, event_type) IN (('CN','purchase'), ('US','signup'));

CREATE TABLE events_json
(
    event_time DateTime,
    user_id    UInt64,
    payload    String          -- 原始 JSON 字符串
)
ENGINE = MergeTree ORDER BY (user_id, event_time);
 
INSERT INTO events_json VALUES
    ('2024-06-15 10:00:00', 1001,
     '{"order":{"id":9527,"amount":299.5},"tags":["vip","new"],"active":true}');

SELECT
    JSONExtractString(payload, 'order', 'id')       AS order_id_str,
    JSONExtractUInt(payload, 'order', 'id')         AS order_id,
    JSONExtractFloat(payload, 'order', 'amount')    AS amount,
    JSONExtractBool(payload, 'active')              AS active,
    JSONExtractArrayRaw(payload, 'tags')            AS tags_raw,
    JSONExtract(payload, 'tags', 'Array(String)')   AS tags,
    JSONHas(payload, 'order', 'amount')             AS has_amount,
    JSONLength(payload, 'tags')                     AS tag_count,
    JSONType(payload, 'order')                      AS order_type
FROM events_json;

┌─order_id_str─┬─order_id─┬─amount─┬─active─┬─tags─────────────┬─has_amount─┬─tag_count─┬─order_type─┐
│ 9527         │     9527 │  299.5 │      1 │ ['vip','new']    │          1 │         2 │ Object     │
└──────────────┴──────────┴────────┴────────┴──────────────────┴────────────┴───────────┴────────────┘

SELECT
    simpleJSONExtractString(payload, 'name')  AS name,
    simpleJSONExtractUInt(payload, 'id')      AS id
FROM events_json;

ALTER TABLE events_json
    ADD COLUMN order_id UInt64  MATERIALIZED JSONExtractUInt(payload, 'order', 'id'),
    ADD COLUMN amount   Float64 MATERIALIZED JSONExtractFloat(payload, 'order', 'amount');
 
-- 新写入的行会自动计算，历史数据需要 MATERIALIZE
ALTER TABLE events_json MATERIALIZE COLUMN order_id;
ALTER TABLE events_json MATERIALIZE COLUMN amount;

-- 让 ClickHouse 自动把 JSON 字段映射到表的列
CREATE TABLE events_typed
(
    event_time DateTime,
    user_id    UInt64,
    order_id   UInt64,
    amount     Float64,
    tags       Array(String)
) ENGINE = MergeTree ORDER BY (user_id, event_time);
 
SET input_format_import_nested_json = 1;
SET input_format_skip_unknown_fields = 1;
-- 然后 INSERT ... FORMAT JSONEachRow

-- 每个用户 6 月的完整行为序列与关键指标
SELECT
    user_id,
    length(seq)                                              AS event_count,
    seq                                                      AS event_sequence,
    arrayCount(x -> x = 'purchase', seq)                     AS purchases,
    arrayExists(x -> x = 'signup', seq)                      AS did_signup,
    arrayFirstIndex(x -> x = 'purchase', seq)                AS first_purchase_step,
    arrayStringConcat(arrayDistinct(seq), ' > ')             AS uniq_path,
    arraySum(durations)                                      AS total_ms,
    arrayMax(durations)                                      AS max_ms,
    -- 相邻事件的时间间隔
    arrayDifference(times)                                   AS gaps
FROM (
    SELECT
        user_id,
        groupArray(event_type)               AS seq,
        groupArray(duration_ms)              AS durations,
        groupArray(toUnixTimestamp(event_time)) AS times
    FROM (
        SELECT user_id, event_type, duration_ms, event_time
        FROM events
        WHERE event_time >= '2024-06-01' AND event_time < '2024-06-02'
        ORDER BY user_id, event_time
    )
    GROUP BY user_id
)
WHERE event_count >= 3
ORDER BY purchases DESC, event_count DESC
LIMIT 3;

┌─user_id─┬─event_count─┬─event_sequence──────────────────────────┬─purchases─┬─did_signup─┬─first_purchase_step─┬─uniq_path──────────────────────┬─total_ms─┬─max_ms─┐
│   18234 │           6 │ ['view','click','purchase','view',...]  │         3 │          0 │                   3 │ view > click > purchase        │    15238 │   4912 │
│    9077 │           5 │ ['view','view','click','purchase',...]  │         2 │          1 │                   4 │ view > click > purchase > signup│   12044 │   4788 │
└─────────┴─────────────┴─────────────────────────────────────────┴───────────┴────────────┴─────────────────────┴────────────────────────────────┴──────────┴────────┘