# SQLを使ったデータクエリ EMQX Tablesは、データベースからデータをクエリするために完全なSQLをサポートしています。このページでは、`monitor`テーブルを使ってデータのクエリ方法を説明します。 ## 基本的なクエリ クエリは`SELECT`文で表されます。例えば、以下のクエリは`monitor`テーブルからすべてのデータを選択します。 ```sql SELECT * FROM monitor; ``` クエリ結果は以下のようになります。 ```sql +-----------+---------------------+------+--------+ | host | ts | cpu | memory | +-----------+---------------------+------+--------+ | 127.0.0.1 | 2022-11-03 03:39:57 | 0.1 | 0.4 | | 127.0.0.1 | 2022-11-03 03:39:58 | 0.5 | 0.2 | | 127.0.0.2 | 2022-11-03 03:39:58 | 0.2 | 0.3 | +-----------+---------------------+------+--------+ 3 rows in set (0.00 sec) ``` `SELECT`フィールドリストでは関数も使用可能です。例えば、`count()`関数を使ってテーブル内の行数を取得できます。 ```sql SELECT count(*) FROM monitor; ``` ```sql +-----------------+ | COUNT(UInt8(1)) | +-----------------+ | 3 | +-----------------+ ``` `avg()`関数は特定フィールドの平均値を返します。 ```sql SELECT avg(cpu) FROM monitor; ``` ```sql +---------------------+ | AVG(monitor.cpu) | +---------------------+ | 0.26666666666666666 | +---------------------+ 1 row in set (0.00 sec) ``` 関数の結果だけを選択することも可能です。例えば、タイムスタンプから年の何日目かを抽出できます。SQL文中の`DOY`は`day of the year`の略です。 ```sql SELECT date_part('DOY', '2021-07-01 00:00:00'); ``` 出力例： ```sql +----------------------------------------------------+ | date_part(Utf8("DOY"),Utf8("2021-07-01 00:00:00")) | +----------------------------------------------------+ | 182 | +----------------------------------------------------+ 1 row in set (0.003 sec) ``` 日付関数のパラメータと結果はSQLクライアントのタイムゾーンに合わせて処理されます。例えば、クライアントのタイムゾーンが`+08:00`に設定されている場合、以下の2つのクエリの結果は同じです。 ```sql select to_unixtime('2024-01-02 00:00:00'); select to_unixtime('2024-01-02 00:00:00+08:00'); ``` 詳細は[SELECT](https://docs.greptime.com/reference/sql/select/)および[Functions](https://docs.greptime.com/reference/sql/functions/overview/)を参照してください。 ## 返される行数の制限 時系列データは通常大量です。帯域幅の節約やクエリ性能向上のために、`LIMIT`句を使って`SELECT`文で返される行数を制限できます。 例えば、以下のクエリは返される行数を10行に制限します。 ```sql SELECT * FROM monitor LIMIT 10; ``` ## データのフィルタリング `WHERE`句を使って`SELECT`文で返される行をフィルタリングできます。 タグや時間インデックスによるデータのフィルタリングは、時系列シナリオで効率的かつ一般的です。例えば、以下のクエリはタグ`host`でデータをフィルタリングします。 ```sql SELECT * FROM monitor WHERE host='127.0.0.1'; ``` 以下のクエリは時間インデックス`ts`でフィルタリングし、`2022-11-03 03:39:57`以降のデータを返します。 ```sql SELECT * FROM monitor WHERE ts > '2022-11-03 03:39:57'; ``` また、`AND`キーワードを使って複数の条件を組み合わせることも可能です。 ```sql SELECT * FROM monitor WHERE host='127.0.0.1' AND ts > '2022-11-03 03:39:57'; ``` ### 時間インデックスによるフィルタリング 時間インデックスによるフィルタリングは、時系列データベースの重要な機能です。 Unix時間値を扱う際、データベースはカラムの値の型に基づいて処理します。例えば、`monitor`テーブルの`ts`カラムの値の型が`TimestampMillisecond`の場合、以下のクエリでデータをフィルタリングできます。 Unix時間値`1667446797000`は`TimestampMillisecond`型に対応します。 ```sql SELECT * FROM monitor WHERE ts > 1667446797000; ``` カラムの値の精度と異なるUnix時間値を扱う場合は、`::`構文で時間値の型を指定する必要があります。これにより、データベースが正しく型を認識します。 例えば、`1667446797`は秒単位のタイムスタンプであり、`ts`カラムのデフォルトのミリ秒単位のタイムスタンプとは異なります。`::TimestampSecond`構文で秒単位のタイムスタンプであることを指定します。 ```sql SELECT * FROM monitor WHERE ts > 1667446797::TimestampSecond; ``` 標準の`RFC3339`や`ISO8601`形式の文字列リテラルを使う場合は、精度が明確なためそのままフィルタ条件に使用可能です。 ```sql SELECT * FROM monitor WHERE ts > '2022-07-25 10:32:16.408'; ``` フィルタ条件では日時関数も使用可能です。例えば、`now()`関数と`INTERVAL`キーワードを使って直近5分のデータを取得できます。 ```sql SELECT * FROM monitor WHERE ts >= now() - '5 minutes'::INTERVAL; ``` 日時関数の詳細は[Functions](https://docs.greptime.com/reference/sql/functions/overview/)を参照してください。 ### タイムゾーン タイムゾーン情報を含まない文字列リテラルのタイムスタンプは、SQLクライアントのローカルタイムゾーンに基づいて解釈されます。例えば、クライアントのタイムゾーンが`+08:00`の場合、以下の2つのクエリは同等です。 ```sql SELECT * FROM monitor WHERE ts > '2022-07-25 10:32:16.408'; SELECT * FROM monitor WHERE ts > '2022-07-25 10:32:16.408+08:00'; ``` クエリ結果のすべてのタイムスタンプカラム値はクライアントのタイムゾーンに基づいてフォーマットされます。例えば、以下のコードはクライアントのタイムゾーンが`UTC`と`+08:00`の場合の同じ`ts`値のフォーマット例です。 ```sql +-----------+---------------------+------+--------+ | host | ts | cpu | memory | +-----------+---------------------+------+--------+ | 127.0.0.1 | 2023-12-31 16:00:00 | 0.5 | 0.1 | +-----------+---------------------+------+--------+ ``` ```sql +-----------+---------------------+------+--------+ | host | ts | cpu | memory | +-----------+---------------------+------+--------+ | 127.0.0.1 | 2024-01-01 00:00:00 | 0.5 | 0.1 | +-----------+---------------------+------+--------+ ``` ### 関数 GreptimeDBはデータ分析のニーズに応じた豊富な組み込み関数と集約機能を提供します。特徴は以下の通りです。 - Apache Datafusionクエリエンジンから継承した包括的な関数セット。Postgresの命名規則と挙動に準拠した日付/時間関数を含みます。 - JSON、ジオロケーションなどの特殊なデータ型に対する論理データ型操作。 - 高度な全文検索マッチング機能。 詳細は[Functions reference](https://docs.greptime.com/reference/sql/functions/overview/)を参照してください。 ## ORDER BY 返されるデータの順序は保証されません。返されるデータをソートするには`ORDER BY`句を使用する必要があります。例えば、時系列シナリオでは時間インデックスカラムをソートキーとして使い、時系列順に並べることが一般的です。 ```sql -- tsで昇順ソート SELECT * FROM monitor ORDER BY ts ASC; ``` ```sql -- tsで降順ソート SELECT * FROM monitor ORDER BY ts DESC; ``` ## `CASE`式 クエリ内で条件分岐ロジックを実装するには`CASE`文を使用できます。例えば、`cpu`フィールドの値に基づいてCPUの状態を返すクエリは以下の通りです。 ```sql SELECT host, ts, CASE WHEN cpu > 0.5 THEN 'high' WHEN cpu > 0.3 THEN 'medium' ELSE 'low' END AS cpu_status FROM monitor; ``` 結果は以下のようになります。 ```sql +-----------+---------------------+------------+ | host | ts | cpu_status | +-----------+---------------------+------------+ | 127.0.0.1 | 2022-11-03 03:39:57 | low | | 127.0.0.1 | 2022-11-03 03:39:58 | medium | | 127.0.0.2 | 2022-11-03 03:39:58 | low | +-----------+---------------------+------------+ 3 rows in set (0.01 sec) ``` 詳細は[CASE](../data_integration/rule-sql-syntax.md#case-expressions)を参照してください。 ## タグによるデータ集約 `GROUP BY`句を使って同じ値を持つ行をグループ化し、集約行を作成できます。例えば、`host`ごとのCPU平均使用率は以下の通りです。 ```sql SELECT host, avg(cpu) FROM monitor GROUP BY host; ``` ```sql +-----------+------------------+ | host | AVG(monitor.cpu) | +-----------+------------------+ | 127.0.0.2 | 0.2 | | 127.0.0.1 | 0.3 | +-----------+------------------+ 2 rows in set (0.00 sec) ``` 詳細は[GROUP BY](https://docs.greptime.com/reference/sql/group_by/)を参照してください。 ### 時系列の最新データを取得 各時系列の最新ポイントを取得するには、[ClickHouse](https://clickhouse.com/docs/en/sql-reference/statements/select/distinct)のように`DISTINCT ON`と`ORDER BY`を組み合わせて使用できます。 ```sql SELECT DISTINCT ON (host) * FROM monitor ORDER BY host, ts DESC; ``` ```sql +-----------+---------------------+------+--------+ | host | ts | cpu | memory | +-----------+---------------------+------+--------+ | 127.0.0.1 | 2022-11-03 03:39:58 | 0.5 | 0.2 | | 127.0.0.2 | 2022-11-03 03:39:58 | 0.2 | 0.3 | +-----------+---------------------+------+--------+ 2 rows in set (0.00 sec) ``` ## 時間ウィンドウによるデータ集約 GreptimeDBは[レンジクエリ](https://docs.greptime.com/reference/sql/range/)をサポートし、時間ウィンドウごとにデータを集約できます。 例えば、`monitor`テーブルに以下のデータがあるとします。 ```sql +-----------+---------------------+------+--------+ | host | ts | cpu | memory | +-----------+---------------------+------+--------+ | 127.0.0.1 | 2023-12-13 02:05:41 | 0.5 | 0.2 | | 127.0.0.1 | 2023-12-13 02:05:46 | NULL | NULL | | 127.0.0.1 | 2023-12-13 02:05:51 | 0.4 | 0.3 | | 127.0.0.2 | 2023-12-13 02:05:41 | 0.3 | 0.1 | | 127.0.0.2 | 2023-12-13 02:05:46 | NULL | NULL | | 127.0.0.2 | 2023-12-13 02:05:51 | 0.2 | 0.4 | +-----------+---------------------+------+--------+ ``` 以下のクエリは10秒の時間範囲でCPU使用率の平均を集計し、5秒ごとに計算します。 ```sql SELECT ts, host, avg(cpu) RANGE '10s' FILL LINEAR FROM monitor ALIGN '5s' TO '2023-12-01T00:00:00' BY (host) ORDER BY ts ASC; ``` 1. `avg(cpu) RANGE '10s' FILL LINEAR`はレンジ式です。`RANGE '10s'`は集約の時間範囲が10秒であることを指定し、`FILL LINEAR`は集約時間内にデータがない場合に`LINEAR`方式で補完することを指定します。 2. `ALIGN '5s'`はデータ統計のステップ幅を5秒に指定します。 3. `TO '2023-12-01T00:00:00'`は起点のアライメント時間を指定します。デフォルトはUnix時間の0です。 4. `BY (host)`は集約キーを指定します。`BY`キーワードを省略すると、データテーブルの主キーが集約キーとして使われます。 5. `ORDER BY ts ASC`は結果セットのソート方法を指定します。指定しない場合、結果の順序は保証されません。 レスポンスは以下の通りです。 ```sql +---------------------+-----------+----------------------------------------+ | ts | host | AVG(monitor.cpu) RANGE 10s FILL LINEAR | +---------------------+-----------+----------------------------------------+ | 2023-12-13 02:05:35 | 127.0.0.1 | 0.5 | | 2023-12-13 02:05:40 | 127.0.0.1 | 0.5 | | 2023-12-13 02:05:45 | 127.0.0.1 | 0.4 | | 2023-12-13 02:05:50 | 127.0.0.1 | 0.4 | | 2023-12-13 02:05:35 | 127.0.0.2 | 0.3 | | 2023-12-13 02:05:40 | 127.0.0.2 | 0.3 | | 2023-12-13 02:05:45 | 127.0.0.2 | 0.2 | | 2023-12-13 02:05:50 | 127.0.0.2 | 0.2 | +---------------------+-----------+----------------------------------------+ ``` ### 時間範囲ウィンドウ 起点の時間範囲ウィンドウは時系列上で前後にステップし、すべての時間範囲ウィンドウを生成します。上記の例では、起点アライメント時間が`2023-12-01T00:00:00`に設定されており、これは起点時間ウィンドウの終了時間でもあります。 `RANGE`オプションは起点アライメント時間とともに、`origin alignment timestamp`から`origin alignment timestamp + range`までの起点時間範囲ウィンドウを定義します。 `ALIGN`オプションはクエリの解像度ステップを定義します。起点時間ウィンドウから他の時間ウィンドウへの計算ステップを決定します。例えば、起点アライメント時間が`2023-12-01T00:00:00`で`ALIGN '5s'`の場合、アライメント時間は`2023-11-30T23:59:55`、`2023-12-01T00:00:00`、`2023-12-01T00:00:05`、`2023-12-01T00:00:10`…と続きます。 これらの時間ウィンドウは左閉右開区間であり、条件`[alignment timestamp, alignment timestamp + range)`を満たします。 以下の画像は時間範囲ウィンドウの理解を助けます。 クエリ解像度が時間範囲ウィンドウより大きい場合、メトリクスデータは1つの時間範囲ウィンドウのみで計算されます。 ![align_greater_than_range](./_assets/align_greater_than_range.png) クエリ解像度が時間範囲ウィンドウより小さい場合、メトリクスデータは複数の時間範囲ウィンドウで計算されます。 ![align_less_than_range](./_assets/align_less_than_range.png) ### 特定のタイムスタンプにアライメント アライメント時間はデフォルトで現在のSQLクライアントセッションのタイムゾーンに基づきます。起点アライメント時間は任意のタイムスタンプに変更可能です。例えば、`NOW`を使って現在時刻にアライメントする場合： ```sql SELECT ts, host, avg(cpu) RANGE '1w' FROM monitor ALIGN '1d' TO NOW BY (host); ``` または、`ISO 8601`形式のタイムスタンプを使って指定時刻にアライメントする場合： ```sql SELECT ts, host, avg(cpu) RANGE '1w' FROM monitor ALIGN '1d' TO '2023-12-01T00:00:00+08:00' BY (host); ``` ### NULL値の補完 `FILL`オプションはデータのNULL値を補完するために使用します。上記の例では`LINEAR`方式で補完しています。他にも`PREV`や定数値`X`などの方法がサポートされています。詳細は[FILL OPTION](https://docs.greptime.com/reference/sql/range/#fill-option)を参照してください。 ### 構文 詳細は[Range Query](https://docs.greptime.com/reference/sql/range/)を参照してください。 ## テーブル名の制約 テーブル名に特殊文字や大文字が含まれる場合は、テーブル名をバッククォートで囲む必要があります。詳細はテーブル作成ドキュメントの[Table name constraints](https://docs.greptime.com/user-guide/deployments-administration/manage-data/basic-table-operations/#table-name-constraints)を参照してください。