# Native TCP（推荐）
clickhouse-client --host=localhost --port=9000

# HTTP
curl 'http://localhost:8123/?query=SELECT 1'

# MySQL协议
mysql -h localhost -P 9004 -u default

-- 原始查询
SELECT user_id, COUNT(*) 
FROM events 
WHERE date >= '2024-01-01' AND event_type = 'click'
GROUP BY user_id;

-- 优化后的执行计划
1. 分区裁剪：只扫描 2024-01-01 之后的分区
2. 谓词下推：在读取时过滤 event_type = 'click'
3. 投影下推：只读取 user_id 和 event_type 列
4. 并行聚合：多线程并行执行 GROUP BY
5. 两阶段聚合：先局部聚合，再全局聚合

-- 查看表的Parts结构
SELECT 
    table,
    partition,
    name,
    rows,
    bytes_on_disk,
    level,              -- LSM层级
    is_frozen,
    modification_time
FROM system.parts 
WHERE table = 'events' 
ORDER BY level, modification_time;

CREATE TABLE events_optimized (
    event_date Date,
    event_time DateTime,
    user_id UInt32,
    event_data String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id)
SETTINGS 
    -- LSM相关配置
    parts_to_delay_insert = 150,           -- 延迟插入的Part数量阈值
    parts_to_throw_insert = 300,           -- 拒绝插入的Part数量阈值
    max_parts_in_total = 1000,             -- 最大Part总数
    
    -- 合并策略配置
    merge_selecting_sleep_ms = 5000,       -- 合并选择间隔
    merge_max_block_size = 8192,           -- 合并块大小
    max_bytes_to_merge_at_max_space_in_pool = 1073741824, -- 最大合并字节数
    
    -- 压缩优化
    min_merge_bytes_to_use_direct_io = 10737418240, -- 直接IO阈值
    index_granularity = 8192;                       -- 索引粒度

-- 监控正在进行的合并操作
SELECT 
    table,
    elapsed,
    progress,
    num_parts,
    result_part_name,
    bytes_read_uncompressed,
    bytes_written_uncompressed,
    rows_read,
    rows_written
FROM system.merges;

-- 查看合并历史
SELECT 
    event_time,
    table,
    part_name,
    bytes_uncompressed,
    rows,
    duration_ms
FROM system.part_log 
WHERE event_type = 'MergeParts'
ORDER BY event_time DESC
LIMIT 10;

-- 批量写入配置
SET max_insert_block_size = 1048576;      -- 增大批量大小
SET max_insert_threads = 4;               -- 并行写入线程
SET max_partitions_per_insert_block = 100; -- 限制分区数

-- 异步写入优化
SET async_insert = 1;                     -- 启用异步插入
SET async_insert_max_data_size = 10485760; -- 异步缓冲区10MB
SET wait_for_async_insert = 0;            -- 不等待写入完成

-- 利用分区裁剪
SELECT count() FROM events 
WHERE event_date >= '2024-01-01' 
  AND event_date <= '2024-01-31';

-- 利用排序键优化范围查询
SELECT * FROM events 
WHERE event_date = '2024-01-15'
  AND user_id BETWEEN 1000 AND 2000
ORDER BY user_id, event_time;

-- 手动触发合并
OPTIMIZE TABLE events PARTITION '202401' FINAL;

-- 设置合并调度参数
SET background_pool_size = 16;            -- 后台任务线程数
SET background_merges_mutations_concurrency_ratio = 2;
SET max_bytes_to_merge_at_min_space_in_pool = 1048576;

-- 好的设计：ORDER BY与查询模式匹配
CREATE TABLE user_events (
    user_id UInt32,
    event_date Date,
    event_time DateTime,
    event_type String,
    event_data String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)     -- 按月分区便于管理
ORDER BY (user_id, event_date, event_time) -- 用户维度查询优化
SETTINGS index_granularity = 8192;

-- 监控Part数量，防止过多小文件
SELECT 
    table,
    count() as part_count,
    sum(rows) as total_rows,
    formatReadableSize(sum(bytes_on_disk)) as total_size
FROM system.parts 
WHERE active = 1
GROUP BY table
HAVING part_count > 100  -- 告警阈值
ORDER BY part_count DESC;

-- 监控写入延迟
SELECT 
    event_time,
    ProfileEvents['DelayedInserts'] as delayed_inserts,
    ProfileEvents['RejectedInserts'] as rejected_inserts
FROM system.metric_log 
WHERE event_time > now() - INTERVAL 1 HOUR
  AND (delayed_inserts > 0 OR rejected_inserts > 0);

-- 标准MergeTree表结构
CREATE TABLE events (
    event_date Date,                -- 分区键
    event_time DateTime,            -- 排序键
    user_id UInt32,                -- 排序键  
    event_type LowCardinality(String), -- 优化的字符串类型
    event_data String,
    properties Map(String, String), -- 键值对属性
    tags Array(String),            -- 标签数组
    created_at DateTime DEFAULT now() -- 默认值
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)   -- 按年月分区
ORDER BY (event_date, user_id, event_time) -- 排序键定义
PRIMARY KEY (event_date, user_id)   -- 主键（ORDER BY的前缀）
SETTINGS index_granularity = 8192, -- 索引粒度
         index_granularity_bytes = 10485760, -- 索引粒度字节数
         enable_mixed_granularity_parts = 1, -- 启用混合粒度
         merge_with_ttl_timeout = 86400;     -- TTL合并超时

-- ReplacingMergeTree基本用法
CREATE TABLE user_profiles (
    user_id UInt32,
    user_name String,
    email String,
    phone String,
    updated_at DateTime,
    version UInt64 DEFAULT 1  -- 版本号，用于确定最新记录
) ENGINE = ReplacingMergeTree(version) -- 指定版本字段
PARTITION BY intDiv(user_id, 1000000)  -- 按用户ID范围分区
ORDER BY user_id
SETTINGS index_granularity = 8192;

-- 数据插入示例（模拟用户信息更新）
INSERT INTO user_profiles VALUES
(1001, 'Alice', '[email protected]', '13800138001', now(), 1),
(1002, 'Bob', '[email protected]', '13800138002', now(), 1);

-- 更新用户信息（插入新版本）
INSERT INTO user_profiles VALUES
(1001, 'Alice Updated', '[email protected]', '13800138001', now(), 2);

-- 查询最新数据（使用FINAL确保去重）
SELECT user_id, user_name, email, updated_at
FROM user_profiles FINAL
WHERE user_id = 1001;

-- SummingMergeTree预聚合表
CREATE TABLE user_metrics (
    date Date,
    user_id UInt32,
    event_type String,
    event_count UInt64,           -- 会自动求和的字段
    duration_seconds UInt64,      -- 会自动求和的字段
    unique_sessions UInt32        -- 会自动求和的字段
) ENGINE = SummingMergeTree((event_count, duration_seconds, unique_sessions))
PARTITION BY toYYYYMM(date)
ORDER BY (date, user_id, event_type)
SETTINGS index_granularity = 8192;

-- 插入原始数据
INSERT INTO user_metrics VALUES
('2024-01-01', 1001, 'click', 10, 300, 2),
('2024-01-01', 1001, 'click', 5, 150, 1);  -- 相同键的记录

-- 强制合并以触发聚合
OPTIMIZE TABLE user_metrics FINAL;

-- 查询聚合结果
SELECT 
    date, user_id, event_type,
    sum(event_count) as total_events,
    sum(duration_seconds) as total_duration,
    sum(unique_sessions) as total_sessions
FROM user_metrics
WHERE date = '2024-01-01' AND user_id = 1001
GROUP BY date, user_id, event_type;

-- 使用物化视图自动聚合
CREATE MATERIALIZED VIEW user_metrics_mv
TO user_metrics
AS SELECT 
    toDate(event_time) as date,
    user_id,
    event_type,
    count() as event_count,
    sum(duration) as duration_seconds,
    uniq(session_id) as unique_sessions
FROM raw_events
GROUP BY date, user_id, event_type;

-- AggregatingMergeTree复杂聚合表
CREATE TABLE user_analytics (
    date Date,
    site_id UInt32,
    user_count AggregateFunction(uniq, UInt32),     -- 去重用户数
    page_views AggregateFunction(count, UInt32),    -- 页面浏览量
    session_duration AggregateFunction(avg, UInt32), -- 平均会话时长
    bounce_rate AggregateFunction(quantile(0.5), Float32) -- 跳出率中位数
) ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, site_id)
SETTINGS index_granularity = 8192;

-- 使用AggregateFunction需要特殊的插入语法
INSERT INTO user_analytics 
SELECT 
    toDate(event_time) as date,
    site_id,
    uniqState(user_id) as user_count,
    countState(*) as page_views,
    avgState(session_duration) as session_duration,
    quantileState(0.5)(bounce_rate) as bounce_rate
FROM raw_page_views
WHERE toDate(event_time) = today()
GROUP BY date, site_id;

-- 查询聚合结果
SELECT 
    date,
    site_id,
    uniqMerge(user_count) as unique_users,
    countMerge(page_views) as total_page_views,
    avgMerge(session_duration) as avg_session_duration,
    quantileMerge(0.5)(bounce_rate) as median_bounce_rate
FROM user_analytics
WHERE date >= today() - 7
GROUP BY date, site_id
ORDER BY date, site_id;

-- CollapsingMergeTree变更日志表
CREATE TABLE user_balance_log (
    user_id UInt32,
    balance Decimal(18, 2),
    transaction_id String,
    transaction_time DateTime,
    sign Int8  -- 1表示插入/更新，-1表示删除/撤销
) ENGINE = CollapsingMergeTree(sign)
PARTITION BY toYYYYMM(transaction_time)
ORDER BY (user_id, transaction_id, sign)
SETTINGS index_granularity = 8192;

-- 插入初始记录
INSERT INTO user_balance_log VALUES
(1001, 1000.00, 'tx_001', '2024-01-01 10:00:00', 1);

-- 更新记录（先插入取消记录，再插入新记录）
INSERT INTO user_balance_log VALUES
(1001, 1000.00, 'tx_001', '2024-01-01 10:00:00', -1), -- 取消原记录
(1001, 1500.00, 'tx_002', '2024-01-01 11:00:00', 1);  -- 插入新记录

-- 查询当前有效记录
SELECT 
    user_id,
    sum(balance * sign) as current_balance,
    max(transaction_time) as last_update
FROM user_balance_log
WHERE user_id = 1001
GROUP BY user_id
HAVING sum(sign) > 0; -- 只显示有效记录

-- VersionedCollapsingMergeTree版本化表
CREATE TABLE user_status_history (
    user_id UInt32,
    status String,
    level UInt32,
    last_login DateTime,
    version UInt64,  -- 版本号
    sign Int8        -- 变更标记
) ENGINE = VersionedCollapsingMergeTree(sign, version)
PARTITION BY intDiv(user_id, 100000)
ORDER BY (user_id, version, sign)
SETTINGS index_granularity = 8192;

-- 版本化数据更新流程
-- 1. 插入初始版本
INSERT INTO user_status_history VALUES
(1001, 'active', 1, '2024-01-01 10:00:00', 1, 1);

-- 2. 更新用户状态（版本2）
INSERT INTO user_status_history VALUES
(1001, 'active', 1, '2024-01-01 10:00:00', 1, -1), -- 撤销版本1
(1001, 'premium', 2, '2024-01-01 12:00:00', 2, 1); -- 插入版本2

-- 3. 查询最新状态
SELECT 
    user_id, status, level, last_login
FROM user_status_history FINAL
WHERE user_id = 1001;

-- GraphiteMergeTree监控数据表
CREATE TABLE metrics_data (
    metric_name String,
    value Float64,
    timestamp UInt32,
    date Date,
    updated UInt32
) ENGINE = GraphiteMergeTree('graphite_rollup')
PARTITION BY toYYYYMM(date)
ORDER BY (metric_name, timestamp)
SETTINGS index_granularity = 8192;

-- Graphite数据汇总配置（在config.xml中配置）
-- <graphite_rollup>
--     <pattern>
--         <regexp>click_cost</regexp>
--         <function>sum</function>
--         <retention>
--             <age>0</age>
--             <precision>60</precision>
--         </retention>
--     </pattern>
-- </graphite_rollup>

-- 添加跳表索引
ALTER TABLE events ADD INDEX idx_event_type event_type TYPE bloom_filter(0.01) GRANULARITY 1;
ALTER TABLE events ADD INDEX idx_user_range user_id TYPE minmax GRANULARITY 1;

-- 添加投影索引（预聚合）
ALTER TABLE events ADD PROJECTION user_daily_stats (
    SELECT 
        user_id,
        toDate(event_time) as date,
        event_type,
        count() as event_count,
        uniq(session_id) as session_count
    GROUP BY user_id, date, event_type
);

-- 物化投影（立即构建）
ALTER TABLE events MATERIALIZE PROJECTION user_daily_stats;

-- 复合TTL策略
CREATE TABLE events_with_lifecycle (
    event_date Date,
    event_time DateTime,
    user_id UInt32,
    event_type String,
    event_data String,
    sensitive_info Nullable(String)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id, event_time)
TTL 
    -- 列级TTL：30天后删除敏感信息
    sensitive_info + INTERVAL 30 DAY,
    -- 表级TTL：数据分层存储和删除策略
    event_date + INTERVAL 7 DAY TO DISK 'hot',      -- 7天内热存储
    event_date + INTERVAL 30 DAY TO DISK 'cold',    -- 30天内冷存储  
    event_date + INTERVAL 365 DAY DELETE,           -- 365天后删除
    -- 聚合TTL：90天后数据聚合
    event_date + INTERVAL 90 DAY GROUP BY user_id, event_type SET
        event_count = count(),
        last_event_time = max(event_time),
        event_data = any(event_data);

-- 存储策略配置
CREATE TABLE events_multi_disk (
    event_date Date,
    event_time DateTime,
    user_id UInt32,
    event_data String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id)
SETTINGS 
    storage_policy = 'tiered_storage',  -- 使用分层存储策略
    min_bytes_for_wide_part = 0,        -- 所有分区使用Wide格式
    min_rows_for_wide_part = 0;

-- 在config.xml中配置存储策略
-- <storage_configuration>
--     <disks>
--         <hot_disk>
--             <path>/mnt/fast_ssd/clickhouse/</path>
--         </hot_disk>
--         <cold_disk>
--             <path>/mnt/slow_hdd/clickhouse/</path>
--         </cold_disk>
--     </disks>
--     <policies>
--         <tiered_storage>
--             <volumes>
--                 <hot>
--                     <disk>hot_disk</disk>
--                     <max_data_part_size_bytes>1073741824</max_data_part_size_bytes>
--                 </hot>
--                 <cold>
--                     <disk>cold_disk</disk>
--                 </cold>
--             </volumes>
--         </tiered_storage>
--     </policies>
-- </storage_configuration>

-- 好的设计：高频查询字段在前
ORDER BY (user_id, event_date, event_time)  -- 用户维度查询优化

-- 不好的设计：时间字段在前，用户查询低效  
ORDER BY (event_time, user_id, event_date)

-- 推荐：按时间分区（便于管理和删除）
PARTITION BY toYYYYMM(event_date)

-- 适用：按业务维度分区（数据倾斜较少时）
PARTITION BY (toYYYYMM(event_date), city_id)

-- 避免：过细分区（导致大量小文件）
PARTITION BY (event_date, user_id)  -- 不推荐

-- 批量写入优化配置
SET max_insert_block_size = 1048576;          -- 1M行批处理
SET max_insert_threads = 4;                   -- 4线程并行插入
SET max_partitions_per_insert_block = 100;    -- 限制单次插入分区数

-- 异步写入优化
SET async_insert = 1;                         -- 启用异步插入
SET wait_for_async_insert = 0;                -- 不等待插入完成
SET async_insert_max_data_size = 1000000;     -- 异步缓冲区大小

-- TinyLog引擎（最简单）
CREATE TABLE tiny_log (
    timestamp DateTime,
    message String
) ENGINE = TinyLog;

-- StripeLog引擎（支持并行读取）
CREATE TABLE stripe_log (
    timestamp DateTime,
    message String
) ENGINE = StripeLog;

-- Log引擎（支持索引）
CREATE TABLE log_table (
    timestamp DateTime,
    level String,
    message String
) ENGINE = Log;

-- MySQL引擎
CREATE TABLE mysql_table (
    id UInt32,
    name String
) ENGINE = MySQL('mysql_host:3306', 'database', 'table', 'user', 'password');

-- HDFS引擎
CREATE TABLE hdfs_table (
    id UInt32,
    data String
) ENGINE = HDFS('hdfs://namenode:9000/path/to/file', 'Parquet');

-- Kafka引擎
CREATE TABLE kafka_table (
    timestamp DateTime,
    message String
) ENGINE = Kafka('kafka_host:9092', 'topic', 'group_id', 'JSONEachRow');

-- 有符号整数
Int8, Int16, Int32, Int64, Int128, Int256

-- 无符号整数
UInt8, UInt16, UInt32, UInt64, UInt128, UInt256

-- 示例
CREATE TABLE integers (
    id UInt32,
    count Int64,
    flag UInt8
);

-- 浮点数
Float32, Float64

-- 示例
CREATE TABLE floats (
    price Float32,
    ratio Float64
);

-- 字符串
String, FixedString(N)

-- 示例
CREATE TABLE strings (
    name String,
    code FixedString(10)
);

-- 日期时间
Date, DateTime, DateTime64

-- 示例
CREATE TABLE dates (
    event_date Date,
    event_time DateTime,
    precise_time DateTime64(3)
);

-- 数组
Array(T)

-- 示例
CREATE TABLE arrays (
    tags Array(String),
    scores Array(Float32),
    ids Array(UInt32)
);

-- 元组
Tuple(T1, T2, ...)

-- 示例
CREATE TABLE tuples (
    point Tuple(Float32, Float32),
    user_info Tuple(String, UInt32, DateTime)
);
-- 根据索引获取元素，第一位索引是1
select (1,2,3).2;
   ┌─tupleElement((1, 2, 3), 2)─┐
1. │                          2 │
   └────────────────────────────┘

-- Map
Map(KeyType, ValueType)

-- 示例
CREATE TABLE maps (
    user_attributes Map(String, String),
    metrics Map(String, Float64)
);

-- 可空类型
Nullable(T)

-- 示例
CREATE TABLE nullable_data (
    id UInt32,
    name Nullable(String),
    age Nullable(UInt8)
);

-- 低基数类型（优化存储）
LowCardinality(T)

-- 示例
CREATE TABLE low_cardinality (
    country LowCardinality(String),
    status LowCardinality(String)
);

-- 高精度小数
Decimal(P, S)

-- 示例
CREATE TABLE decimals (
    amount Decimal(18, 2),
    rate Decimal(10, 4)
);

# 指定数据库连接
clickhouse-client --host 192.168.1.100 --port 9000 --user admin --password 'secret' --database analytics

# 表格格式（默认）
clickhouse-client -q "SELECT name, engine FROM system.tables LIMIT 5" --format Pretty

 SELECT count()  FROM remote('qa-hadoop1:9000', alibb.part1) limit 3;

# 从文件执行SQL
clickhouse-client --queries-file queries.sql

# 从标准输入执行
echo "SELECT 1" | clickhouse-client

# 执行复杂查询文件
cat << 'EOF' > complex_query.sql
WITH user_stats AS (
    SELECT 
        user_id,
        COUNT(*) as event_count,
        MAX(timestamp) as last_activity
    FROM user_events 
    WHERE date >= today() - 7
    GROUP BY user_id
)
SELECT 
    user_id,
    event_count,
    last_activity,
    CASE 
        WHEN event_count > 100 THEN 'high_activity'
        WHEN event_count > 10 THEN 'medium_activity'
        ELSE 'low_activity'
    END as activity_level
FROM user_stats
ORDER BY event_count DESC
LIMIT 100;
EOF

clickhouse-client --queries-file complex_query.sql --format Pretty

# 使用参数化查询
clickhouse-client --param_user_id=12345 --param_start_date='2024-01-01' \
  --query "SELECT * FROM user_events WHERE user_id = {user_id:UInt64} AND date >= {start_date:Date}"

# 多参数查询
clickhouse-client \
  --param_min_amount=100 \
  --param_max_amount=1000 \
  --param_currency='USD' \
  --query "
    SELECT 
        transaction_id,
        amount,
        currency,
        timestamp
    FROM transactions 
    WHERE amount BETWEEN {min_amount:Float64} AND {max_amount:Float64}
      AND currency = {currency:String}
    ORDER BY timestamp DESC
    LIMIT 50
  " --format JSONEachRow

# 大数据量查询优化
clickhouse-client --max_memory_usage=8000000000 \
  --query "SELECT * FROM huge_table WHERE date >= '2024-01-01'" \
  --format CSVWithNames > large_export.csv

# 分页查询
clickhouse-client --query "
  SELECT * FROM user_events 
  ORDER BY timestamp DESC 
  LIMIT 1000 OFFSET 0
" --format JSONEachRow

# 流式查询（避免内存溢出）
clickhouse-client --query "
  SELECT * FROM user_events 
  WHERE date >= today() - 30
" --format JSONEachRow --stream

# 并行查询多个分区
for partition in {20240101..20240131}; do
  clickhouse-client --query "
    SELECT * FROM user_events 
    WHERE toYYYYMMDD(date) = $partition
  " --format CSVWithNames > "data_$partition.csv" &
done
wait

-- 基本插入
INSERT INTO table_name (column1, column2) VALUES (value1, value2);

-- 从HDFS插入数据
INSERT INTO glab_gid_info.appinfo_v2 
SELECT * FROM hdfs('hdfs://path/to/file', 'Parquet');

-- 使用命令行工具插入
clickhouse-client -m --port 9000 --user default --database glab_gid_info --password xxxxx \
 --host hzxs-ga-ck-xs6 --max_insert_block_size=536970912 --max_insert_threads=4 \
 -q 'INSERT INTO table_name SELECT * FROM hdfs("hdfs://xxx:8020/path/to/file", "Parquet");'

# 基础：格式控制（CSV/TSV/JSON/Parquet/ORC 等）
clickhouse-client \
  --host <host> --port 9000 --user <user> --password <pwd> \
  --query "SELECT * FROM db.table WHERE date>=toDate('2025-01-01') FORMAT CSV" \
  > export.csv

# 带表头（CSVWithNames/TSVWithNames）
clickhouse-client -q "SELECT * FROM db.table FORMAT CSVWithNames" > table.csv

# 导出为 JSONEachRow（便于流式处理）
clickhouse-client -q "SELECT * FROM db.table FORMAT JSONEachRow" > table.jsonl

# 并行压缩导出（压缩到本地）
clickhouse-client -q "SELECT * FROM db.table FORMAT CSV" | gzip > table.csv.gz

SET allow_into_outfile = 1;
SELECT * FROM db.table
INTO OUTFILE '/tmp/table.csv'
FORMAT CSVWithNames;

-- 导出到 HDFS ，无需提前创建文件，先创建目录
INSERT INTO FUNCTION hdfs('hdfs://gt-ga-xs/tmp/logs/data/huangg/id_2_wifi/20250917/aaa.parquet', 'Parquet')
select * from glab_relation.id_2_wifi_predict_local limit 1;

-- 使用 file()/hdfs()/s3() 表函数（INSERT/SELECT 互转）
INSERT INTO FUNCTION file('/tmp/export.jsonl', 'JSONEachRow')
SELECT * FROM db.table WHERE dt=toDate('2025-01-01');

INSERT INTO FUNCTION hdfs('hdfs://nn:8020/path/table.parquet', 'Parquet')
SELECT * FROM db.table;

-- 从原始 JSON 字符串中提取字段
SELECT 
    event_time,
    JSONExtractString(properties, 'page') AS page,
    JSONExtractString(properties, 'ua') AS ua,
    JSONExtractFloat(properties, 'amount') AS amount
FROM events_json
WHERE event_date = toDate('2025-08-01');

-- 使用更通用的 JSON_VALUE(JSON, jsonpath) 语法（新版本）
SELECT 
    event_time,
    JSON_VALUE(properties, '$.page') AS page,
    JSON_VALUE(properties, '$.order_id') AS order_id,
    toFloat64OrNull(JSON_VALUE(properties, '$.amount')) AS amount
FROM events_json
WHERE event_type IN ('page_view', 'purchase');

-- Map 列的读取
SELECT 
    event_time,
    props['btn'] AS btn,
    props['q'] AS query,
    mapKeys(props) AS all_keys
FROM events_map
WHERE event_type IN ('click','search');

-- Object('json') 的投影访问（字段自动按需映射并可被索引/投影）
SELECT 
    event_time,
    j.page AS page,
    j.sku::UInt64 AS sku,
    j.price::Float64 AS price
FROM events_obj
WHERE j.page = '/detail';

-- 创建bitmap
SELECT bitmapBuild([1, 2, 3, 4, 5]) AS bitmap;

-- 添加元素
SELECT bitmapAdd(bitmapBuild([1,2,3]), 4) AS bitmap;

-- 删除元素
SELECT bitmapSubtract(bitmapBuild([1,2,3]), 2) AS bitmap;

-- 判断元素是否存在
SELECT bitmapContains(bitmapBuild([1,2,3]), 2) AS exists;

-- 获取基数（元素个数）
SELECT bitmapCardinality(bitmap_column) FROM table;

-- OR操作（并集）
SELECT 
    bitmapOr(bitmap1, bitmap2) AS union_bitmap,
    bitmapOrCardinality(bitmap1, bitmap2) AS union_count
FROM table;

-- AND操作（交集）
SELECT 
    bitmapAnd(bitmap1, bitmap2) AS intersect_bitmap,
    bitmapAndCardinality(bitmap1, bitmap2) AS intersect_count
FROM table;

-- XOR操作（异或）
SELECT 
    bitmapXor(bitmap1, bitmap2) AS xor_bitmap,
    bitmapXorCardinality(bitmap1, bitmap2) AS xor_count
FROM table;

-- 聚合OR操作
SELECT bitmapOrState(bitmap_column)
FROM table
GROUP BY group_column;

-- 聚合AND操作
SELECT bitmapAndState(bitmap_column)
FROM table
GROUP BY group_column;

-- Bitmap转数组
SELECT bitmapToArray(bitmap_column) AS array;

-- 数组转Bitmap
SELECT bitmapBuild([1, 2, 3]) AS bitmap;

-- 获取Bitmap中的指定范围元素
SELECT arraySlice(bitmapToArray(bitmap_column), 1, 5) AS first_five;

-- 实际应用示例
SELECT 
    tag,
    bitmapCardinality(gid_index_bitmap) AS user_count,
    arraySlice(bitmapToArray(gid_index_bitmap), 1, 10) AS sample_users
FROM tag_bitmap 
WHERE tag_md5 = lower(hex(MD5('tag_name')));

-- Bitmap分段统计示例
SELECT 
    bitmap_segment_number,
    bitmapCardinality(gid_index_bitmap) AS segment_count,
    formatReadableSize(length(gid_index_bitmap)) AS bitmap_size
FROM tag_bitmap 
WHERE tag_md5 = lower(hex(MD5('tag_name')))
ORDER BY bitmap_segment_number;

-- 使用 bitmapOrCardinality 替代 bitmapCardinality(bitmapOr())
SELECT bitmapOrCardinality(
    bitmapBuild([1, 2, 3]),
    bitmapBuild([3, 4, 5])
) AS union_count;

-- 批量处理时使用State聚合函数
SELECT 
    tag,
    bitmapCardinality(groupBitmapOrState(gid_index_bitmap)) AS total_users
FROM tag_bitmap
WHERE tag IN ('tag1', 'tag2', 'tag3')
GROUP BY tag;

-- 大规模数据处理优化
SELECT 
    tag,
    bitmapCardinality(gid_index_bitmap) AS user_count,
    formatReadableSize(length(gid_index_bitmap)) AS bitmap_size
FROM tag_bitmap 
WHERE tag_md5 IN (
    SELECT lower(hex(MD5(tag))) 
    FROM (SELECT ['tag1', 'tag2', 'tag3'] AS tags) 
    ARRAY JOIN tags AS tag
)
SETTINGS max_threads = 4;

# Map创建与转换
-- 创建Map
SELECT map('key1', 1, 'key2', 2) AS simple_map;

-- 从两个数组创建Map
SELECT mapFromArrays(['key1', 'key2', 'key3'], [1, 2, 3]) AS map_from_arrays;

-- 从嵌套数组创建Map
SELECT mapFromArrays(['a', 'b'], [[1, 2], [3, 4]]) AS nested_map;

-- 数组转换为Map (key-value对的数组)
SELECT arrayMap(x -> (x, x * x), range(1, 5)) AS array_of_pairs,
       map(arrayMap(x -> (x, x * x), range(1, 5))) AS map_from_pairs;

-- 从JSON字符串创建Map
SELECT mapFromJSON('{"key1": 1, "key2": 2, "key3": 3}') AS map_from_json;

-- Map转换为JSON
SELECT mapToJSON(map('key1', 1, 'key2', 2)) AS json_string;

-- Map转换为两个数组
SELECT mapKeys(map('key1', 1, 'key2', 2)) AS keys,
       mapValues(map('key1', 1, 'key2', 2)) AS values;


# Map查询与操作

-- 获取Map中的值
SELECT map('key1', 1, 'key2', 2)['key1'] AS value;

-- 检查键是否存在
SELECT mapContains(map('key1', 1, 'key2', 2), 'key1') AS contains_key;

-- 获取Map大小
SELECT mapSize(map('key1', 1, 'key2', 2)) AS map_size;

-- 合并两个Map
SELECT mapMerge(
    map('key1', 1, 'key2', 2),
    map('key2', 20, 'key3', 3)
) AS merged_map;

-- 删除Map中的键
SELECT mapRemove(map('key1', 1, 'key2', 2, 'key3', 3), ['key1', 'key3']) AS filtered_map;

-- 提取Map子集
SELECT mapFilter((k, v) -> v > 1, map('key1', 1, 'key2', 2, 'key3', 3)) AS filtered_map;

-- 转换Map值
SELECT mapApply((k, v) -> v * 10, map('key1', 1, 'key2', 2)) AS transformed_map;

-- 展平嵌套Map
SELECT mapFlat(map('nested', map('key1', 1, 'key2', 2), 'key3', 3)) AS flattened_map;


# Map聚合操作

-- 聚合多行数据为单个Map
SELECT mapAgg(key, value) FROM (
    SELECT 'key1' AS key, 1 AS value
    UNION ALL SELECT 'key2' AS key, 2 AS value
    UNION ALL SELECT 'key3' AS key, 3 AS value
);

-- 合并多行Map
SELECT groupArrayMap(m) FROM (
    SELECT map('key1', 1, 'key2', 2) AS m
    UNION ALL SELECT map('key2', 20, 'key3', 3) AS m
    UNION ALL SELECT map('key4', 4) AS m
);

-- 聚合Map并对值进行求和
SELECT 
    date,
    sumMap(page_views) AS total_views
FROM (
    SELECT 
        toDate('2023-01-01') AS date,
        map('page1', 10, 'page2', 15) AS page_views
    UNION ALL
    SELECT 
        toDate('2023-01-01') AS date,
        map('page1', 5, 'page3', 8) AS page_views
)
GROUP BY date;

-- 实际应用示例：用户属性聚合
SELECT 
    user_id,
    mapAgg(attribute, value) AS user_attributes
FROM user_attribute_table
GROUP BY user_id;

-- 实际应用示例：事件计数聚合
SELECT 
    date,
    sumMap(event_counts) AS daily_events
FROM (
    SELECT 
        event_date AS date,
        map(event_type, event_count) AS event_counts
    FROM events_table
)
GROUP BY date
ORDER BY date;

-- 1. 数组字面量创建
SELECT [1, 2, 3, 4, 5] AS simple_array;
SELECT ['apple', 'banana', 'orange'] AS string_array;
SELECT [toDate('2024-01-01'), toDate('2024-01-02')] AS date_array;

-- 2. 从列创建数组
SELECT range(10) AS number_range;              -- [0,1,2,3,4,5,6,7,8,9]
SELECT range(1, 6) AS custom_range;            -- [1,2,3,4,5]
SELECT range(0, 10, 2) AS step_range;          -- [0,2,4,6,8]

-- 3. 空数组和类型指定
SELECT [] AS empty_array;
SELECT CAST([] AS Array(String)) AS typed_empty_array;
SELECT emptyArrayUInt32() AS empty_uint_array;
SELECT emptyArrayString() AS empty_string_array;

-- 4. 数组类型转换
SELECT arrayMap(x -> toString(x), [1, 2, 3]) AS to_string_array;
SELECT arrayMap(x -> toFloat64(x), ['1.1', '2.2', '3.3']) AS to_float_array;
SELECT arrayMap(x -> toDate(x), ['2024-01-01', '2024-01-02']) AS to_date_array;

-- 5. 从字符串解析数组
SELECT splitByChar(',', '1,2,3,4') AS from_string;
SELECT arrayMap(x -> toInt32(x), splitByChar(',', '10,20,30')) AS parsed_int_array;

-- 数组并集：合并两个数组并去重
SELECT arrayDistinct(arrayConcat([1, 2, 3], [2, 3, 4])) AS union_array;
-- 结果: [1, 2, 3, 4]

-- 组合求差集
select arrayFilter(x->not arrayExists(y -> y = x,[1,2,4]),[2,3,5]) as diff;
-- 结果: [3,5]

-- 对多个数组执行交集操作
SELECT arrayIntersect([1, 2, 3], [2, 3, 4], [3, 4, 5]) AS multi_intersection;
-- 结果: [3]

-- 检查一个数组是否是另一个数组的子集
SELECT arrayAll(x -> arrayExists(y -> y = x, [1, 2, 3, 4]), [2, 3]) AS is_subset;
-- 结果: 1 (true)

-- 检查两个数组是否相等（不考虑顺序）
SELECT arraySort(a1) = arraySort(a2) FROM 
(SELECT [1, 2, 3] AS a1, [3, 1, 2] AS a2);
-- 结果: 1 (true)

-- 检查一个元素是否在数组中
SELECT arrayExists(x -> x = 3, [1, 2, 3, 4]) AS contains;
-- 结果: 1 (true)

-- 高效并集实现（去重合并）
SELECT arrayDistinct(arrayConcat([1, 2, 3], [3, 4, 5])) AS union_result;  -- [1, 2, 3, 4, 5]

-- 多数组并集（支持任意数量的数组）
SELECT arrayDistinct(arrayFlatten([
    [1, 2, 3],
    [3, 4, 5], 
    [5, 6, 7],
    [7, 8, 9]
])) AS multi_array_union; -- [1, 2, 3, 4, 5, 6, 7, 8, 9]

-- 条件并集（仅合并满足条件的元素）
WITH 
    arrayFilter(x -> x > 0, [-1, 1, -2, 2, 3]) AS positive_arr1,
    arrayFilter(x -> x > 0, [-2, 2, -3, 3, 4]) AS positive_arr2
SELECT arrayDistinct(arrayConcat(positive_arr1, positive_arr2)) AS positive_union; -- [1, 2, 3, 4]

-- 加权并集（保留权重信息）
SELECT arrayDistinct(arrayConcat(
    arrayMap(x -> (x, 'source_A'), [1, 2, 3]),
    arrayMap(x -> (x, 'source_B'), [3, 4, 5])
)) AS weighted_union;

-- 分组并集聚合
SELECT 
    group_id,
    arrayDistinct(arrayFlatten(groupArray(elements))) AS group_union
FROM (
    SELECT 1 AS group_id, [1, 2, 3] AS elements
    UNION ALL SELECT 1, [3, 4, 5]
    UNION ALL SELECT 2, [6, 7, 8]
    UNION ALL SELECT 2, [8, 9, 10]
)
GROUP BY group_id;

-- 基本arrayJoin用法：将数组展开为多行
SELECT arrayJoin([1, 2, 3]) AS value;
-- 结果:
-- 1
-- 2  
-- 3
-- 数组去重
SELECT arrayDistinct([1, 2, 2, 3, 3, 4]) AS unique_array;
-- 结果: [1, 2, 3, 4]

-- 数组合并
SELECT arrayConcat([1, 2], [3, 4], [5, 6]) AS merged_array;
-- 结果: [1, 2, 3, 4, 5, 6]

-- 数组映射转换
SELECT arrayMap(x -> x * 2, [1, 2, 3, 4, 5]) AS doubled;
-- 结果: [2, 4, 6, 8, 10]

-- 数组过滤
SELECT arrayFilter(x -> x > 3, [1, 2, 3, 4, 5, 6]) AS filtered;
-- 结果: [4, 5, 6]

-- 数组打平（多维数组展开为一维）
SELECT arrayFlatten([[1, 2], [3, 4], [5]]) AS flattened;
-- 结果: [1, 2, 3, 4, 5]

-- 数组元素操作
SELECT arrayPushBack([1, 2, 3], 4) AS with_new_element;     -- 末尾添加
SELECT arrayPushFront([1, 2, 3], 0) AS with_front_element;  -- 开头添加
SELECT arrayPopBack([1, 2, 3, 4]) AS without_last;          -- 移除末尾
SELECT arrayPopFront([1, 2, 3, 4]) AS without_first;        -- 移除开头

-- 数组切片和索引
SELECT arraySlice([1, 2, 3, 4, 5], 2, 3) AS sliced;        -- 从位置2开始取3个元素
SELECT arrayReverse([1, 2, 3, 4]) AS reversed;              -- 反转数组
SELECT arrayElement([1, 2, 3, 4], 2) AS second_element;     -- 获取第2个元素

-- 数组长度和大小
SELECT length([1, 2, 3, 4]) AS array_length;
SELECT empty([]) AS is_empty;
SELECT notEmpty([1, 2, 3]) AS is_not_empty;

-- 数组类型检查
SELECT toTypeName([1, 2, 3]) AS array_type;
SELECT toTypeName(['a', 'b', 'c']) AS string_array_type;

-- 数组内容检查
SELECT arrayExists(x -> x > 3, [1, 2, 3, 4, 5]) AS has_element_gt_3;
SELECT arrayAll(x -> x > 0, [1, 2, 3, 4]) AS all_positive;
SELECT arrayCount(x -> x % 2 = 0, [1, 2, 3, 4, 5, 6]) AS even_count;

-- 数组元素索引查找
SELECT indexOf([10, 20, 30, 40], 30) AS index_of_30;  -- 返回 3
SELECT arrayFirstIndex(x -> x > 25, [10, 20, 30, 40]) AS first_gt_25_index;

-- 元素访问（1基索引）
SELECT [1, 2, 3, 4, 5][3] AS third_element;    -- 返回 3
SELECT arrayElement([1, 2, 3, 4, 5], 2) AS second_element;  -- 返回 2

-- 负索引访问（从末尾开始）
SELECT [1, 2, 3, 4, 5][-1] AS last_element;    -- 返回 5
SELECT [1, 2, 3, 4, 5][-2] AS second_last;     -- 返回 4

-- 安全访问（越界返回默认值）
SELECT arrayElement([1, 2, 3], 10, 0) AS safe_access;  -- 返回 0

-- 数组切片
SELECT arraySlice([1, 2, 3, 4, 5, 6], 2, 3) AS middle_slice;  -- [2,3,4]
SELECT arraySlice([1, 2, 3, 4, 5], -3) AS last_three;         -- [3,4,5]

-- 数组修改操作
SELECT arrayPushBack([1, 2, 3], 4) AS append_element;
SELECT arrayPushFront([2, 3, 4], 1) AS prepend_element;
SELECT arrayPopBack([1, 2, 3, 4]) AS remove_last;
SELECT arrayPopFront([1, 2, 3, 4]) AS remove_first;
SELECT arrayResize([1, 2, 3], 5, 0) AS resize_array;  -- [1,2,3,0,0]

-- 基本排序
SELECT arraySort([3, 1, 4, 1, 5, 9]) AS sorted_asc;        -- [1,1,3,4,5,9]
SELECT arrayReverseSort([3, 1, 4, 1, 5, 9]) AS sorted_desc; -- [9,5,4,3,1,1]

-- 字符串数组排序
SELECT arraySort(['banana', 'apple', 'cherry']) AS sorted_strings;

-- 自定义排序（Lambda表达式）
SELECT arraySort(x -> -x, [3, 1, 4, 1, 5]) AS custom_sort;  -- 按负值排序

-- 复杂对象排序
SELECT arraySort(x -> tupleElement(x, 2), [('a', 3), ('b', 1), ('c', 2)]) AS sort_by_second;

-- 并行数组排序（多个数组按照第一个数组的顺序排序）
SELECT 
    arraySort([3, 1, 4, 2]) AS sorted_keys,
    arraySort((x, y) -> x, [3, 1, 4, 2], ['c', 'a', 'd', 'b']) AS sorted_values;
-- 结果: [1,2,3,4] 和 ['a','b','c','d']

-- 元素查找
SELECT arrayFirst(x -> x > 3, [1, 2, 3, 4, 5]) AS first_gt_3;     -- 4
SELECT arrayLast(x -> x < 3, [1, 2, 3, 4, 5]) AS last_lt_3;       -- 2
SELECT arrayFirstIndex(x -> x > 3, [1, 2, 3, 4, 5]) AS first_index; -- 4

-- 条件计数和存在性检查
SELECT arrayCount(x -> x > 3, [1, 2, 3, 4, 5, 6]) AS count_gt_3;
SELECT arrayExists(x -> x = 5, [1, 2, 3, 4, 5]) AS contains_5;
SELECT arrayAll(x -> x > 0, [1, 2, 3, 4, 5]) AS all_positive;

-- 模式匹配查找
SELECT arrayFirst(x -> match(x, '^a'), ['apple', 'banana', 'avocado']) AS first_a_word;
SELECT arrayFilter(x -> match(x, 'an'), ['apple', 'banana', 'orange', 'mango']) AS contains_an;

-- 最值查找
SELECT arrayMin([5, 2, 8, 1, 9]) AS min_value;
SELECT arrayMax([5, 2, 8, 1, 9]) AS max_value;
SELECT arrayArgMin((x, y) -> y, ['a', 'b', 'c'], [3, 1, 2]) AS arg_min;  -- 'b'
SELECT arrayArgMax((x, y) -> y, ['a', 'b', 'c'], [3, 1, 2]) AS arg_max;  -- 'a'

-- Map操作（元素变换）
SELECT arrayMap(x -> x * x, [1, 2, 3, 4, 5]) AS squares;
SELECT arrayMap(x -> upper(x), ['hello', 'world']) AS uppercase;
SELECT arrayMap(x -> tuple(x, x * 2), [1, 2, 3]) AS tuples;

-- Filter操作（条件过滤）
SELECT arrayFilter(x -> x % 2 = 1, [1, 2, 3, 4, 5, 6]) AS odd_numbers;
SELECT arrayFilter(x -> length(x) > 5, ['cat', 'elephant', 'dog', 'butterfly']) AS long_words;

-- Reduce操作（累积计算）
SELECT arrayReduce('sum', [1, 2, 3, 4, 5]) AS array_sum;
SELECT arrayReduce('avg', [1, 2, 3, 4, 5]) AS array_avg;
SELECT arrayReduce('max', [1, 2, 3, 4, 5]) AS array_max;
SELECT arrayReduce('uniq', [1, 2, 2, 3, 3, 3]) AS unique_count;

-- 自定义Reduce操作
SELECT arrayReduce('groupBitOr', [1, 2, 4, 8]) AS bit_or_result;  -- 按位或
SELECT arrayReduce('groupBitAnd', [7, 3, 1]) AS bit_and_result;   -- 按位与

-- 复杂函数式操作
SELECT arrayMap((x, y) -> x + y, [1, 2, 3], [4, 5, 6]) AS element_wise_sum;
SELECT arrayZip([1, 2, 3], ['a', 'b', 'c']) AS zipped_arrays;

-- 数学统计
SELECT arraySum([1, 2, 3, 4, 5]) AS total_sum;
SELECT arrayAvg([1, 2, 3, 4, 5]) AS average;
SELECT arraySum(arrayMap(x -> x * x, [1, 2, 3, 4])) AS sum_of_squares;

-- 数组差分和累积
SELECT arrayDifference([1, 3, 6, 10, 15]) AS differences;  -- [2,3,4,5]
SELECT arrayCumSum([1, 2, 3, 4, 5]) AS cumulative_sum;     -- [1,3,6,10,15]
SELECT arrayCumSumNonNegative([1, -2, 3, -1, 2]) AS cum_sum_non_neg;

-- 数组旋转和移位
SELECT arrayRotateLeft([1, 2, 3, 4, 5], 2) AS rotate_left;   -- [3,4,5,1,2]
SELECT arrayRotateRight([1, 2, 3, 4, 5], 2) AS rotate_right; -- [4,5,1,2,3]

-- 数组比较和排名
SELECT arrayRank([3, 1, 4, 1, 5]) AS rank_values;
SELECT arrayDotProduct([1, 2, 3], [4, 5, 6]) AS dot_product;  -- 1*4 + 2*5 + 3*6 = 32

-- 序列生成和递推
SELECT arrayMap(i -> i * i, range(1, 6)) AS squares_1_to_5;
SELECT arrayMap(i -> pow(2, i), range(0, 8)) AS powers_of_2;

-- 数组扁平化
SELECT arrayFlatten([[1, 2], [3, 4], [5, 6]]) AS flattened;
SELECT arrayFlatten([[[1, 2]], [[3, 4]]]) AS deep_flattened;

-- 数组重塑（转置）
SELECT 
    arrayMap(i -> arrayMap(j -> arr[j][i], range(1, length(arr) + 1)), 
             range(1, length(arr[1]) + 1)) AS transposed
FROM (
    SELECT [[1, 2, 3], [4, 5, 6]] AS arr
);

-- 数组编码和解码
SELECT arrayEncode([1, 2, 2, 3, 3, 3]) AS rle_encoded;  -- 游程编码
SELECT arrayStringConcat(['a', 'b', 'c'], '') AS concatenated;
SELECT arrayStringConcat(['a', 'b', 'c'], ',') AS csv_format;

-- 数组与字符串互转
SELECT splitByChar(',', '1,2,3,4,5') AS string_to_array;
SELECT arrayStringConcat(['a', 'b', 'c'], '|') AS array_to_string;

-- 1. 避免过大的数组（推荐 < 1万元素）
-- 不好：
SELECT groupArray(large_text_column) FROM huge_table;  -- 可能导致内存问题

-- 好：限制数组大小
SELECT groupArray(100)(large_text_column) FROM huge_table;

-- 2. 使用适当的数据类型
-- 不好：存储数字的字符串数组
SELECT ['1', '2', '3'] AS inefficient;

-- 好：使用数字数组
SELECT [1, 2, 3] AS efficient;

-- 3. 利用数组函数避免arrayJoin
-- 不好：使用arrayJoin处理简单统计
SELECT count() FROM table ARRAY JOIN tags;

-- 好：使用数组函数
SELECT arraySum(arrayMap(x -> 1, tags)) AS tag_count FROM table;

-- 检查数组大小分布
SELECT 
    length(array_column) AS array_size,
    count() AS count,
    avg(length(array_column)) AS avg_size,
    max(length(array_column)) AS max_size
FROM table_with_arrays
GROUP BY length(array_column)
ORDER BY array_size;

-- 数组内存使用估算
SELECT 
    table,
    column,
    sum(length(array_column) * 8) AS estimated_bytes  -- 假设每个元素8字节
FROM system.columns 
WHERE type LIKE 'Array%'
GROUP BY table, column;

-- 性能对比：数组vs多行
-- 方案1：数组存储
SELECT user_id, arraySum(scores) AS total_score
FROM user_scores_array;

-- 方案2：多行存储
SELECT user_id, sum(score) AS total_score  
FROM user_scores_normalized
GROUP BY user_id;

-- 用户行为分析：类似Hive的复杂聚合查询迁移
SELECT 
    user_id,
    -- 类似Hive的collect_set，收集用户所有访问的独特页面
    groupUniqArray(page_url) AS unique_pages_visited,
    -- 类似Hive的collect_list，保持用户访问页面的时间顺序
    groupArray(page_url) AS page_visit_sequence,
    -- 类似Hive的size(collect_set())，计算独特页面数量
    uniq(page_url) AS unique_page_count,
    -- 类似Hive的concat_ws，连接用户访问路径
    arrayStringConcat(groupArray(page_url), ' -> ') AS user_journey,
    -- 类似Hive的percentile，计算用户访问时长分位数
    quantile(0.5)(visit_duration) AS median_visit_time,
    -- 类似Hive的count(distinct)进行条件统计
    uniqIf(session_id, visit_duration > 300) AS long_sessions,
    -- 复杂条件聚合，类似Hive的case when + collect_set
    groupUniqArrayIf(page_category, visit_duration > 60) AS engaged_categories,
    -- 类似Hive的map聚合，页面停留时间统计
    mapAgg(page_category, avg_duration) AS category_avg_durations
FROM (
    SELECT 
        user_id,
        page_url,
        page_category,
        session_id,
        visit_duration,
        avg(visit_duration) OVER (PARTITION BY user_id, page_category) AS avg_duration
    FROM user_page_visits
    WHERE visit_date >= today() - 30  -- 最近30天数据
) 
GROUP BY user_id
ORDER BY unique_page_count DESC
LIMIT 100;

-- 电商分析：商品维度的复杂聚合统计
SELECT 
    category,
    -- 类似Hive collect_set，收集该分类下的所有品牌
    groupUniqArray(brand) AS available_brands,
    -- 类似Hive collect_list，按销量排序的商品列表
    arrayStringConcat(
        arraySort(x -> tupleElement(x, 2), 
                 groupArray(tuple(product_name, sales_count))), 
        ', '
    ) AS top_products_by_sales,
    -- 类似Hive的percentile_approx，价格分布分析
    quantiles(0.25, 0.5, 0.75, 0.95)(price) AS price_quartiles,
    -- 复杂嵌套聚合，类似Hive的复杂case when聚合
    groupArray(tuple(
        brand,
        countIf(price > 1000),                    -- 高价商品数
        avgIf(rating, rating > 0),                -- 平均评分
        groupUniqArray(color),                    -- 可选颜色
        maxIf(sales_count, in_stock = 1)          -- 库存商品最高销量
    )) AS brand_detailed_stats,
    -- 类似Hive的map函数，品牌价格统计
    mapFromArrays(
        groupUniqArray(brand),
        groupArray(avg_price_by_brand)
    ) AS brand_price_map
FROM (
    SELECT 
        category,
        brand,
        product_name,
        price,
        rating,
        color,
        sales_count,
        in_stock,
        avg(price) OVER (PARTITION BY category, brand) AS avg_price_by_brand
    FROM products 
    WHERE status = 'active'
) 
GROUP BY category
HAVING uniq(brand) > 5  -- 至少5个品牌的分类
ORDER BY count() DESC;

-- 时间序列分析：用户留存和转化漏斗
SELECT 
    registration_date,
    -- 新用户注册数
    count() AS new_users,
    -- 类似Hive collect_set，收集用户完成的关键行为
    groupUniqArray(completed_actions) AS conversion_actions,
    -- 各阶段转化率统计
    countIf(day1_active) / count() AS day1_retention,
    countIf(day7_active) / count() AS day7_retention,
    countIf(day30_active) / count() AS day30_retention,
    -- 类似Hive的复杂聚合，用户分层统计
    mapFromArrays(
        ['high_value', 'medium_value', 'low_value'],
        [
            countIf(total_spending > 1000),
            countIf(total_spending BETWEEN 100 AND 1000),
            countIf(total_spending < 100)
        ]
    ) AS user_value_segments,
    -- 用户行为路径分析
    topK(10)(arrayStringConcat(
        groupArray(action_sequence), ' -> '
    )) AS common_user_journeys
FROM (
    SELECT 
        user_id,
        toDate(registration_time) AS registration_date,
        arrayJoin(user_actions) AS completed_actions,
        any(total_spending) AS total_spending,
        any(day1_active) AS day1_active,
        any(day7_active) AS day7_active,
        any(day30_active) AS day30_active,
        groupArray(action_name) AS action_sequence
    FROM user_lifecycle_analysis
    WHERE registration_time >= today() - 90
    GROUP BY user_id, registration_date
)
GROUP BY registration_date
ORDER BY registration_date DESC;

-- 原有的用户标签分析示例
SELECT 
    tag,
    count() AS user_count,
    countDistinct(user_id) AS unique_users
FROM (
    SELECT 
        user_id,
        arrayJoin(splitByChar(',', tags)) AS tag
    FROM user_profiles
    WHERE tags != ''
)
GROUP BY tag
ORDER BY user_count DESC
LIMIT 20;

-- 事件序列分析：展开用户行为路径
SELECT 
    step_number,
    event_name,
    count() AS occurrence_count
FROM (
    SELECT 
        user_id,
        arrayJoin(arrayEnumerate(event_sequence)) AS step_number,
        arrayJoin(event_sequence) AS event_name
    FROM user_behavior_sequence
) 
GROUP BY step_number, event_name
ORDER BY step_number, occurrence_count DESC;

-- 商品分类分析：多级分类展开
SELECT 
    level,
    category,
    count() AS product_count
FROM (
    SELECT 
        product_id,
        arrayJoin(arrayEnumerate(category_path)) AS level,
        arrayJoin(category_path) AS category
    FROM products
    WHERE length(category_path) > 0
)
GROUP BY level, category
ORDER BY level, product_count DESC;

-- 字符串分割
SELECT splitByChar(',', 'a,b,c');
select arrayMap(x->splitByChar('#',x)[1],splitByString('\x01',detail))

-- 字符串匹配,
SELECT match('string', 'pattern');
SELECT match('aftvf', 'a');

-- 字符串包含多个模式
SELECT * FROM table WHERE match(column_name, 'MG|XZ');

-- 字符串替换
SELECT replaceAll('hello world world', 'world', 'ClickHouse') AS result;
-- 输出：hello ClickHouse ClickHouse

SELECT replaceOne('hello world world', 'world', 'ClickHouse') AS result;
-- 输出：hello ClickHouse world

SELECT replaceRegexpAll('abc123xyz456', '\\d+', '_') AS result;
-- 输出：abc_xyz_

-- multiIf语法：multiIf(condition1, result1, condition2, result2, ..., default_result)
SELECT multiIf(
    score >= 90, 'A',
    score >= 80, 'B', 
    score >= 70, 'C',
    score >= 60, 'D',
    'F'
) AS grade
FROM student_scores;

-- 等价的CASE WHEN写法（但性能较差）
SELECT CASE 
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    WHEN score >= 60 THEN 'D'
    ELSE 'F'
END AS grade
FROM student_scores;

-- 用户等级划分
SELECT 
    user_id,
    user_name,
    total_amount,
    multiIf(
        total_amount >= 10000, 'VIP',
        total_amount >= 5000, 'Gold',
        total_amount >= 1000, 'Silver',
        'Bronze'
    ) AS user_level
FROM user_orders;

-- 数据质量评分
SELECT 
    id,
    multiIf(
        name != '' AND email != '' AND phone != '', 'Complete',
        name != '' AND email != '', 'Good',
        name != '', 'Basic',
        'Incomplete'
    ) AS data_quality
FROM user_profiles;

-- 复杂业务逻辑判断
SELECT 
    order_id,
    multiIf(
        status = 'paid' AND delivery_time <= now(), 'Delivered',
        status = 'paid' AND delivery_time > now(), 'In Transit',
        status = 'pending' AND created_time < now() - INTERVAL 1 HOUR, 'Expired',
        status = 'pending', 'Waiting Payment',
        'Unknown'
    ) AS order_status
FROM orders;

-- if语法：if(condition, true_result, false_result)
SELECT 
    user_id,
    if(age >= 18, 'Adult', 'Minor') AS age_group,
    if(gender = 'M', '男', '女') AS gender_cn,
    if(balance > 0, balance, 0) AS available_balance
FROM users;

-- 处理NULL值
SELECT 
    product_id,
    if(discount IS NULL, 0, discount) AS final_discount,
    if(isNull(description), 'No description', description) AS product_desc
FROM products;