clickhouse-io

What this skill does

When applied, it prepends a system prompt before your request is sent — no extra calls and no change to how you are billed beyond the added tokens.

---
name: clickhouse-io
description: ClickHouse数据库模式、查询优化、分析以及高性能分析工作负载的数据工程最佳实践。
origin: ECC
---

# ClickHouse 分析模式

用于高性能分析和数据工程的 ClickHouse 特定模式。

## 何时激活

* 设计 ClickHouse 表架构（MergeTree 引擎选择）
* 编写分析查询（聚合、窗口函数、连接）
* 优化查询性能（分区裁剪、投影、物化视图）
* 摄取大量数据（批量插入、Kafka 集成）
* 为分析目的从 PostgreSQL/MySQL 迁移到 ClickHouse
* 实现实时仪表板或时间序列分析

## 概述

ClickHouse 是一个用于在线分析处理 (OLAP) 的列式数据库管理系统 (DBMS)。它针对大型数据集上的快速分析查询进行了优化。

**关键特性:**

* 列式存储
* 数据压缩
* 并行查询执行
* 分布式查询
* 实时分析

## 表设计模式

### MergeTree 引擎 (最常用)

```sql
CREATE TABLE markets_analytics (
    date Date,
    market_id String,
    market_name String,
    volume UInt64,
    trades UInt32,
    unique_traders UInt32,
    avg_trade_size Float64,
    created_at DateTime
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, market_id)
SETTINGS index_granularity = 8192;
```

### ReplacingMergeTree (去重)

```sql
-- For data that may have duplicates (e.g., from multiple sources)
CREATE TABLE user_events (
    event_id String,
    user_id String,
    event_type String,
    timestamp DateTime,
    properties String
) ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (user_id, event_id, timestamp)
PRIMARY KEY (user_id, event_id);
```

### AggregatingMergeTree (预聚合)

```sql
-- For maintaining aggregated metrics
CREATE TABLE market_stats_hourly (
    hour DateTime,
    market_id String,
    total_volume AggregateFunction(sum, UInt64),
    total_trades AggregateFunction(count, UInt32),
    unique_users AggregateFunction(uniq, String)
) ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (hour, market_id);

-- Query aggregated data
SELECT
    hour,
    market_id,
    sumMerge(total_volume) AS volume,
    countMerge(total_trades) AS trades,
    uniqMerge(unique_users) AS users
FROM market_stats_hourly
WHERE hour >= toStartOfHour(now() - INTERVAL 24 HOUR)
GROUP BY hour, market_id
ORDER BY hour DESC;
```

## 查询优化模式

### 高效过滤

```sql
-- PASS: GOOD: Use indexed columns first
SELECT *
FROM markets_analytics
W

Use this skill

{
  "model": "gpt-4o-mini",
  "skill": "imp-ba1e5440-cbb2-48da-849e-75555c545eb2",
  "messages": [{ "role": "user", "content": "…" }]
}

More skills