ClickHouseのコマンドとSQL構文

ClickHouseのコマンドの一般的な使用法

  1. SELECTクエリ

1.1 構造


[WITH expr_list|(subquery)]
SELECT [DISTINCT] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN ...]
[GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table (ON <expr_list>)|(USING <column_list>)
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH TOTALS]
[HAVING expr]
[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr]
[LIMIT [offset_value, ]n BY columns]
[LIMIT [n, ]m] [WITH TIES]
[UNION ALL ...]
[INTO OUTFILE filename]
[FORMAT format]
</column_list></expr_list>

1.2 WITH句

  • 定数式を変数として使用

WITH '2019-08-01 15:23:00' AS ts_upper_bound
SELECT *
FROM hits
WHERE
    EventDate = toDate(ts_upper_bound) AND
    EventTime <= ts_upper_bound
  • SELECTリストからsum(bytes)の結果を排出

WITH sum(bytes) AS s
SELECT
    formatReadableSize(s),
    table
FROM system.parts
GROUP BY table
ORDER BY s
  • サブクエリの結果をスカラ値として使用

WITH
    (
        SELECT sum(bytes)
        FROM system.parts
        WHERE active
    ) AS total_disk_usage
SELECT
    (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
    table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10
  • サブクエリ内で式を再利用

WITH ['hello'] AS hello
SELECT
    hello,
    *
FROM
(
    WITH ['hello'] AS hello
    SELECT hello
)

1.3 JOIN句

  • 構文：

SELECT <expr_list>
FROM <left_table>
[GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI|ANY|ASOF] JOIN <right_table>
(ON <expr_list>)|(USING <column_list>) ...
</column_list></expr_list></right_table></left_table></expr_list>

  • JOINの種類

  • INNER JOIN: マッチする行のみを返す

  • LEFT OUTER JOIN: マッチする行と左テーブルの非マッチ行を返す

  • RIGHT OUTER JOIN: マッチする行と右テーブルの非マッチ行を返す

  • FULL OUTER JOIN: マッチする行と両方のテーブルの非マッチ行を返す

  • CROSS JOIN: すべてのテーブルのデカルト積を生成

  • 使用上の推奨事項

  • 空のセルの処理: join_use_nullsを使用

  • USINGの使用: 指定された列は両方のサブクエリで同じ名前を持たなければならない

  • 複数のJOIN: 単一のSELECTクエリでは、すべての列(*)を使用して結合テーブルにアクセスできる


SELECT
    CounterID,
    hits,
    visits
FROM
(
    SELECT
        CounterID,
        count() AS hits
    FROM test.hits
    GROUP BY CounterID
) ANY LEFT JOIN
(
    SELECT
        CounterID,
        sum(Sign) AS visits
    FROM test.visits
    GROUP BY CounterID
) USING CounterID
ORDER BY hits DESC
LIMIT 10

1.4 LIMIT句

  • LIMIT m: 結果の最初のm行を選択

1.5 ORDER BY句

  • 降順: DESC
  • 昇順: ASC (デフォルト)
  • NaN, NULLとのソート: 値が最初、次にNaN、最後にNULL

1.6 WHERE句

  • UInt8型の式を含む必要がある

1.7 DISTINCT句

  • 重複を削除
  • GROUP BY, ORDER BYと一緒に使用可能

SELECT DISTINCT a FROM t1 ORDER BY b ASC

1.8 FORMAT句

  • 格式: FORMATの後ろに具体的な形式を指定
  • 入出力形式のドキュメント: 入出力形式

SELECT * FROM nestedt FORMAT TSV

SQL構文

2.1 キーワードの大文字小文字の区別

2.2 数字

  • まず64ビット符号付き整数として解釈し、stroull関数を使用
  • 失敗した場合、64ビット符号なし整数として解釈し、stroull関数を使用
  • それでも失敗した場合、浮動小数点数として解釈し、strod関数を使用
  • データタイプの詳細: データタイプ

関数

3.1 算術関数

  • UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64, Float32, Float64に対応
  • 加算: plus(a, b)
  • 減算: minus(a, b)
  • 乗算: multiply(a, b)
  • 除算: divide(a, b)
  • 絶対値による除算（下方向への丸め）: intDiv(a, b)
  • 0または-1での除算時、0を返す
  • 剰余: modulo(a, b)
  • 0での除算時、0を返す: moduloOrZero(a, b)
  • 絶対値: abs(a)
  • 最大公約数: lcm(a, b)
  • 2つの値を比較し最大値を返す: max2(value1, value2)
  • 2つの値を比較し最小値を返す: min2(value1, value2)

3.2 比較関数

  • 等しい: a=b または a==b
  • 等しくない: a!=b または a<>b
  • その他: <, >, <=, >=

3.3 論理関数

  • AND: and
  • OR: OR
  • NOT: NOT
  • XOR: XOR

3.4 型変換関数

  • 整数間の変換: toInt8(expr), toInt16(expr), toInt32(expr), toInt64(expr)

  • 整数への変換失敗時に0を返す: toInt(8|16|32|64)OrZero

  • 例: toInt64OrZero('123abc123')

  • 整数への変換失敗時にNULLを返す: toInt(8|16|32|64)OrNull

  • UInt型への変換: toUInt8(expr), toUInt16(expr), toUInt32(expr), toUInt64(expr)

  • 整数への変換失敗時に0を返す: toUInt(8|16|32|64)OrZero

  • 整数への変換失敗時にNULLを返す: toUInt(8|16|32|64)OrNull

  • 浮動小数点数への変換: toFloat(32|64)

  • 浮動小数点数への変換失敗時に0を返す: toFloat(32|64)OrZero

  • 浮動小数点数への変換失敗時にNULLを返す: toFloat(32|64)OrNull

  • 日付への変換: toDate, toDateTime

  • 日付への変換失敗時に0を返す: toDateOrZero, toDateTimeOrZero

  • 日付への変換失敗時にNULLを返す: toDateOrNull, toDateTimeOrNull

  • Decimal(32|64|128)型への変換: toDecimal32(value, S)

  • 変換失敗時に0を返す: toDecimal(32|64|128)OrZero

  • 文字列への変換: toString()

  • String型のパラメータを固定長NのFixedString(N)に変換: toFixedString(s, N)

  • 最初のゼロバイトで内容が切り捨てられる: toStringCutToZero(s)

  • 'x'を't'データ型に変換: CAST(x, T) または CAST(x AS t)

  • 数値型の値をInterval型に変換: toInterval(Year|Quarter|Month|Week|Day|Hour|Minute|Second)

  • 文字列の日時をDateTime型に変換: parseDateTimeBestEffort(time_string [, time_zone])

  • 解析できない場合、NULLを返す: parseDateTimeBestEffortOrNull

  • 解析できない場合、0を返す: parseDateTimeBestEffortOrZero

3.5 条件関数

  • if関数:

  • 構文: SELECT if(cond, then, else)

  • 説明: condが非ゼロ値の場合、thenを返し、condがゼロまたはNULLの場合、elseを返す

  • 例: SELECT if(1, plus(2, 2), plus(2, 6))

  • 三項演算子:

  • 構文: cond ? then : else

  • 説明: cond != 0 の場合、then を返し、cond == 0 の場合、else を返す

3.6 日付時間関数

  • サーバーのタイムゾーンを返す: timeZone() -- String型

  • DateまたはDateTimeを指定のタイムゾーンに変換: toTimeZone(value, timezone) -- DateTime型

  • 例:


SELECT
    toDateTime('2019-01-01 00:00:00', 'UTC') AS time_utc,
    toTypeName(time_utc) AS type_utc,
    toInt32(time_utc) AS int32utc,
    toTimeZone(time_utc, 'Asia/Yekaterinburg') AS time_yekat,
    toTypeName(time_yekat) AS type_yekat,
    toInt32(time_yekat) AS int32yekat,
    toTimeZone(time_utc, 'US/Samoa') AS time_samoa,
    toTypeName(time_samoa) AS type_samoa,
    toInt32(time_samoa) AS int32samoa
FORMAT Vertical;
  • 出力結果:
Row 1:
──────
time_utc:   2019-01-01 00:00:00
type_utc:   DateTime('UTC')
int32utc:   1546300800
time_yekat: 2019-01-01 05:00:00
type_yekat: DateTime('Asia/Yekaterinburg')
int32yekat: 1546300800
time_samoa: 2018-12-31 13:00:00
type_samoa: DateTime('US/Samoa')
int32samoa: 1546300800

  • 年を取得: toYear()

  • 月を取得: toMonth()

  • 時を取得: toHour()

  • 分を取得: toMinute()

  • 秒を取得: toSecond()

  • 例:


SELECT 
    toYear(now()), toQuarter(now()), toMonth(now()), toHour(now()), toMinute(now()), toSecond(now())

  • 年の最初の日に丸める: toStartOfYear()

  • 四半期の最初の日に丸める: toStartOfQuarter()

  • 月の最初の日に丸める: toStartOfMonth()

  • 今日の開始に丸める: toStartOfDay()

  • DateTime内の日付を固定の日付に変換: toTime()

  • 日付や時間を指定の単位で丸める: date_trunc(unit, value[, timezone])

  • unit: second, minute, hour, day, week, month, quarter, year

  • value: DateTime

  • timezone: 戻り値のタイムゾーン

  • 例:


SELECT now(), date_trunc('hour', now());

  • 2つの日付または時刻値を持つ日付の差を返す: date_diff(unit, startdate, enddate, [timezone])

  • unit: second, minute, hour, day, week, month, quarter, year

  • startdate: DateまたはDateTime

  • enddate: DateまたはDateTime

  • timezone: 戻り値のタイムゾーン

  • 例:


SELECT dateDiff('hour', toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00'));

  • 現在の日時を返す: now()

  • 現在の日付を返す: today()

  • 昨日の日付を返す: yesterday()

  • 時間間隔をDate/DateTimeに加算: addYears, addMonths, addWeeks, addDays, addHours, addMinutes, addSeconds, addQuarters

  • 例:


WITH
    toDate('2018-01-01') AS date,
    toDateTime('2018-01-01 00:00:00') AS date_time
SELECT
    addYears(date, 1) AS add_years_with_date,
    addYears(date_time, 1) AS add_years_with_date_time
  • 出力結果:
┌─add_years_with_date─┬─add_years_with_date_time─┐
│          2019-01-01 │      2019-01-01 00:00:00 │
└─────────────────────┴──────────────────────────┘

  • 時間間隔をDate/DateTimeから減算: subtractYears, subtractMonths, subtractWeeks, subtractDays, subtractHours, subtractMinutes, subtractSeconds, subtractQuarters

  • 例:


WITH
    toDate('2019-01-01') AS date,
    toDateTime('2019-01-01 00:00:00') AS date_time
SELECT
    subtractYears(date, 1) AS subtract_years_with_date,
    subtractYears(date_time, 1) AS subtract_years_with_date_time
  • 出力結果:
┌─subtract_years_with_date─┬─subtract_years_with_date_time─┐
│               2018-01-01 │           2018-01-01 00:00:00 │
└──────────────────────────┴───────────────────────────────┘

  • 指定された書式文字列で日時をフォーマット: formatDateTime(Time, Format[, Timezone])

  • %F: %Y-%m-%d, 例: 2018-01-02

  • %G: 4桁の年, 例: 2018

  • %Q: 四半期 (1-4)

  • %Y: 年

  • 例:


SELECT formatDateTime(toDate('2010-01-04'), '%F'), formatDateTime(toDate('2010-01-04'), '%G'), formatDateTime(toDate('2010-01-04'), '%Q'), formatDateTime(toDate('2010-01-04'), '%Y')

  • 出力結果:

  • 日付の指定部分を返す: dateName(date_part, date)

  • date_part: 'year', 'quarter', 'month', 'week', 'dayofyear', 'day', 'weekday', 'hour', 'minute', 'second'

  • date: 日付

  • この関数は使用できません

タグ: ClickHouse SQL query functions Data Types

8月16日 20:18 投稿

ホットタグ

©2026 異端開発室