Skip to content

Explorer ブロック ​

INFO

Explorer ブロックは、プライベートプレビューの機能です。エクスプローラで開き直せるのは、機能が有効なワークスペースだけです。機能が無効なワークスペースでも、結果は表示され、「SQLとして編集」で普通の SQLブロックにできます。また、仕様は予告なく変更される場合があります。

概要 ​

  • Explorer ブロックは、エクスプローラで作った SQLブロックです。SQL本文の代わりに、エクスプローラの設定(元データと statement)を書きます
  • 外側の :::sql-block とSQLブロック属性は、普通の SQLブロックと同じです
  • SQL は読み込むときに、設定から生成されます。集計の条件(行グループ、列グループ、集計値、絞り込み、並び替えなど)を変えるときは、statement を書き換えます
  • 他の SQLブロックからは、普通の SQLブロックと同じように参照できます

記述例 ​

md
:::sql-block
```yaml {.attrs}
connId: 69aa00000000000000000099
id: 69aa00000000000000000002
name: sales_by_region
```

```yaml {.explorer}
engineType: BIGQUERY
source:
  type: SQL_BLOCK
  sqlId: 69aa00000000000000000001
statement:
  type: SELECT_STATEMENT
  select:
    - type: FIELD
      key: region
      alias: region___
    - type: AGGREGATE_FUNCTION
      name: SUM
      alias: amount___sum
      args:
        - type: FIELD
          key: amount
  groupBy:
    - type: FIELD
      key: region___
```
:::

定義 ​

  • yaml {.explorer} のコードブロックに、Explorer ブロックの設定を記述します
    • SQL本文(sql)と同時には記述できません。SQL を直接書く場合は、yaml {.explorer} を削除して sql を記述します(普通の SQLブロックになります)
    • SQLブロック属性の bindings は指定できません
  • engineType には、connId のコネクションのデータベースの種類を指定します。SQL の方言を決めるために使います
  • source には、集計の元になるデータを指定します
    • type: SQL_BLOCK は、同じページの SQLブロックの結果を元にします
    • type: TABLE は、テーブルを元にします。tableResourceId はテーブルリソースIDを指定します
    • type: QUERY は、保存済クエリを元にします。queryJson には、選んだ時点のクエリの内容が入ります(SQLブロックの保存済クエリの参照と同じ形です)
  • statement には、元のデータに対する選択・絞り込み・集計・並び替えを指定します。書き方は statement の書き方を参照してください
ts
type SqlBlockExplorer = {
  engineType: "BIGQUERY" | "SNOWFLAKE" | "DUCKDB" | "REDSHIFT" | "DATABRICKS" | "POSTGRESQL" | "CLICKHOUSE" | "MILA" | "SQLITE";
  source:
    | { type: "SQL_BLOCK"; sqlId: ObjectId }
    | { type: "TABLE"; tableResourceId: TableResourceId }
    | { type: "QUERY"; queryId: ObjectId; queryJson: QueryJson };
  statement: ExplorerSelectStatement | ExplorerPivotStatement;
};

statement の書き方 ​

statement は、エクスプローラの画面の 3 つの形のどれかで書きます。

画面の形type主なキー
表(集計しない)SELECT_STATEMENTselect(列)、where、orderBy、limit
集計表(行グループと集計値)SELECT_STATEMENTselect(行グループの列と集計値)、groupBy、where、orderBy、limit
ピボット(行グループ・列グループと集計値)PIVOT_STATEMENTrows、cols、values、filter、rows_sort、cols_sort
ts
type ExplorerSelectStatement = {
  type: 'SELECT_STATEMENT';
  // 表: 元の列(ExplorerColumn)。集計表: 行グループの列(ExplorerGroup)を先に、集計値(ExplorerValue)を後に並べる
  select: (ExplorerColumn | ExplorerGroup | ExplorerValue)[];
  // 集計表だけに書く。行グループの列の alias を、select と同じ順に FIELD で並べる(行グループが無い全体の集計は [])
  groupBy?: { type: 'FIELD'; key: string }[];
  where?: ExplorerFilter;
  // key には、表なら元の列の名前、集計表なら select の alias を指定する
  orderBy?: { type: 'ORDER_BY'; expr: { type: 'FIELD'; key: string }; dir: 'ASC' | 'DESC' }[];
  limit?: number; // 書かなければ全件
};

type ExplorerPivotStatement = {
  type: 'PIVOT_STATEMENT';
  rows: ExplorerGroup[]; // 行グループ
  cols: ExplorerGroup[]; // 列グループ。1 つ以上
  values: ExplorerValue[]; // 集計値
  filter?: ExplorerFilter;
  // ラベル順か値順。値順(VALUE_ASC・VALUE_DESC)にできるのは、values の先頭が SUM・COUNT・MAX・MIN・ANY_VALUE のとき
  rows_sort: AxisSortType;
  cols_sort: AxisSortType;
  rows_sort_custom?: SqlOrderBy[]; // rows_sort が 'CUSTOM' のとき
  cols_sort_custom?: SqlOrderBy[];
  // 行グループ・列グループの値の数の上限(超えた値は「その他」にまとまる)。
  // 両方を省くと 30 ずつ。(rows_limit + 1) × (cols_limit + 1) は 1000 以下にする
  rows_limit?: number;
  cols_limit?: number;
  disable_limit?: boolean; // 画面では設定しない
};

// 表の列。alias は key と同じ
type ExplorerColumn = { type: 'FIELD'; key: string; alias: string };

// 行グループ・列グループの列。区切る・取り出す関数と単位は、列の型で決まる
type ExplorerGroup =
  | { type: 'FIELD'; key: string; alias: string }
  // DATE の列を区切る(月ごとなど)
  | { type: 'SCALAR_FUNCTION'; name: 'DATE_TRUNC'; alias: string;
      args: [{ type: 'FIELD'; key: string }, { type: 'KEYWORD'; name: 'YEAR' | 'MONTH' | 'WEEK' }] }
  // DATETIME の列を区切る
  | { type: 'SCALAR_FUNCTION'; name: 'DATETIME_TRUNC'; alias: string;
      args: [{ type: 'FIELD'; key: string }, { type: 'KEYWORD'; name: 'YEAR' | 'MONTH' | 'WEEK' | 'DAY' | 'HOUR' | 'MINUTE' }] }
  // DATE・DATETIME の列から部分を取り出す(月だけなど)。
  // DATE の列は YEAR・MONTH・DAY、DATETIME の列は YEAR・MONTH・DAY・HOUR・MINUTE
  | { type: 'SCALAR_FUNCTION'; name: 'EXTRACT'; alias: string;
      args: [{ type: 'FIELD'; key: string }, { type: 'KEYWORD'; name: 'YEAR' | 'MONTH' | 'DAY' | 'HOUR' | 'MINUTE' }] }
  // TIMESTAMP の列を区切る。タイムゾーンを 3 番目に渡す
  | { type: 'SCALAR_FUNCTION'; name: 'TIMESTAMP_TRUNC'; alias: string;
      args: [{ type: 'FIELD'; key: string }, { type: 'KEYWORD'; name: 'YEAR' | 'MONTH' | 'WEEK' | 'DAY' | 'HOUR' | 'MINUTE' }, { type: 'CONSTANT'; datatype: 'STRING'; value: string }] }
  // TIMESTAMP の列から部分を取り出す。タイムゾーンを 3 番目に渡す
  | { type: 'SCALAR_FUNCTION'; name: 'TIMESTAMP_EXTRACT'; alias: string;
      args: [{ type: 'FIELD'; key: string }, { type: 'KEYWORD'; name: 'YEAR' | 'MONTH' | 'DAY' | 'HOUR' | 'MINUTE' }, { type: 'CONSTANT'; datatype: 'STRING'; value: string }] }
  // 数値の列を幅で区切る
  | { type: 'SCALAR_FUNCTION'; name: 'STEP_ROUND'; alias: string;
      args: [{ type: 'FIELD'; key: string }, { type: 'CONSTANT'; datatype: 'FLOAT'; value: number }] };

// 集計値
type ExplorerValue = {
  type: 'AGGREGATE_FUNCTION';
  name: 'SUM' | 'AVG' | 'COUNT' | 'COUNT_UNIQUE' | 'MAX' | 'MIN' | 'ANY_VALUE';
  alias: string;
  args: [{ type: 'FIELD'; key: string }];
};

// 絞り込み。チャートの絞り込みの WhereFilter と同じ(一番外側を AND の演算子にする。
// AND でない条件を書いた場合は、読み込むときに AND で包む)
type ExplorerFilter = WhereFilter;
  • AxisSortType・SqlOrderBy は並び替え、WhereFilter は絞り込みを参照してください
  • 上の型に無いキーはエラーになります(statement の直下の alias は書けますが、使われません)
  • SELECT_STATEMENT の select は空にできません

alias の付け方 ​

列の種類alias例
表の列列名region
行グループ・列グループの列<列名>___region___、ordered_on___(月で区切った列も同じ)
部分を取り出した列<列名>___<部分(小文字)>ordered_on___year
集計値<列名>___<集計の種類(小文字)>amount___sum
カスタム SQL の列利用者が決めた名前margin

エクスプローラで開き直せる形 ​

エクスプローラの「編集」で開き直せるのは、エクスプローラが作るのと同じ形の statement だけです。次のどれかに当たると、SQL は生成されて実行できますが、エクスプローラでは開けません。

  • alias がalias の付け方と違う
  • 集計表の select で、集計値が行グループの列より前にある。groupBy の順が select の行グループの順と違う
  • 区切る・取り出す関数や単位が、列の型に合わない(TIMESTAMP の列に DATE_TRUNC など)
  • ピボットの cols が空。rows_sort・cols_sort が無い
  • ピボットで、values の先頭が AVG・COUNT_UNIQUE なのに、値順(VALUE_ASC・VALUE_DESC)で並べている
  • disable_limit を書いている

よくある書き換え ​

したいこと書き換えるところ
列でまとめて集計するselect に行グループの列(<列名>___)と集計値を足し、groupBy に行グループの列の alias を足す
ある列を列側のグループにする(クロス集計)type を PIVOT_STATEMENT にし、集計表の select の行グループを rows、列側にしたい列を cols、集計値を values に移す。groupBy は消し、where は filter に、orderBy は消して rows_sort・cols_sort を書く
月ごと・年ごとにする日付の列を DATE_TRUNC などで区切った行グループにする(DATE の列は DATE_TRUNC、DATETIME の列は DATETIME_TRUNC)
絞り込むwhere(ピボットは filter)の AND の args に条件を足す
並べ替えるorderBy に { type: ORDER_BY, expr: { type: FIELD, key: <alias> }, dir } を足す(ピボットは rows_sort・cols_sort)
集計の種類を変える集計値の name と alias(<列名>___<集計の種類(小文字)>)を変え、orderBy の key も合わせる
件数を絞るlimit を書く(ピボットは rows_limit・cols_limit)

次の例は、地域ごとの売上の合計を、東の地域を除いて合計の大きい順に並べる集計表です。

yaml
statement:
  type: SELECT_STATEMENT
  select:
    - type: FIELD
      key: region
      alias: region___
    - type: AGGREGATE_FUNCTION
      name: SUM
      alias: amount___sum
      args:
        - type: FIELD
          key: amount
  groupBy:
    - type: FIELD
      key: region___
  where:
    type: OPERATOR
    name: AND
    args:
      - type: OPERATOR
        name: NOT_EQUAL
        args:
          - type: FIELD
            key: region
          - type: CONSTANT
            datatype: STRING
            value: east
  orderBy:
    - type: ORDER_BY
      expr:
        type: FIELD
        key: amount___sum
      dir: DESC

次の例は、上の集計表をピボットに書き換え、月(DATE の列 ordered_on を月で区切った値)を列側のグループとして足したものです。

yaml
statement:
  type: PIVOT_STATEMENT
  rows:
    - type: FIELD
      key: region
      alias: region___
  cols:
    - type: SCALAR_FUNCTION
      name: DATE_TRUNC
      alias: ordered_on___
      args:
        - type: FIELD
          key: ordered_on
        - type: KEYWORD
          name: MONTH
  values:
    - type: AGGREGATE_FUNCTION
      name: SUM
      alias: amount___sum
      args:
        - type: FIELD
          key: amount
  filter:
    type: OPERATOR
    name: AND
    args:
      - type: OPERATOR
        name: NOT_EQUAL
        args:
          - type: FIELD
            key: region
          - type: CONSTANT
            datatype: STRING
            value: east
  rows_sort: VALUE_DESC
  cols_sort: LABEL_ASC

生成される SQL の列名 ​

SELECT_STATEMENT で生成される SQL では、alias が ___ を含む列の名前を、次の決まりで付け替えます。それ以外の列は alias のままです。PIVOT_STATEMENT の列名は、付け替えずに alias のままです。後ろの SQLブロックからこのブロックを参照するときは、付け替えた後の列名を使います。

列の種類付け替えた列名例(alias → 列名)
行グループの列、区切った列<列名>region___ → region
部分を取り出した列<列名>_<部分(小文字)>ordered_on___year → ordered_on_year
集計値<集計の種類(小文字)>_<列名>amount___sum → sum_amount

次の場合は、付け替えません。

  • 付け替えた名前が重なる列は、alias のままにします。後から列を足して名前が重なると、それまでの列名も alias に変わります
  • alias の無い列が 1 つでもある場合は、どの列名も付け替えません
  • 元の列の名前が決まらない列(カスタム SQL の列など)は、alias のままにします
  • 出力の列に当たらない式で並べている場合は、並び順を保つために、どの列名も付け替えません

注意 ​

  • source の SQLブロックが見つからない場合、validate は通りますが、run などの SQL を解決するコマンドはノートブック全体で失敗します。yaml {.explorer} を削除して sql を記述するか、source を直してください

Codatum CLI AIエージェントのための分析環境