Appearance
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は指定できません
- SQL本文(
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_STATEMENT | select(列)、where、orderBy、limit |
| 集計表(行グループと集計値) | SELECT_STATEMENT | select(行グループの列と集計値)、groupBy、where、orderBy、limit |
| ピボット(行グループ・列グループと集計値) | PIVOT_STATEMENT | rows、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を直してください