pythonのpandasにある各列の要約統計量（平均、標準偏差など）を取得するdescribeメソッドを知っているだろうか。
このメソッドを使うことで、下記の要約統計量を取得することができ、データの雰囲気を掴むことができます。
- count: 要素の個数
- unique: ユニークな（一意な）値の要素の個数
- top: 最頻値（mode）
- freq: 最頻値の頻度（出現回数）
- mean: 算術平均
- std: 標準偏差
- min: 最小値
- max: 最大値
- 50%: 中央値（median）
- 25%, 75%: 1/4分位数、3/4分位数
このメソッドの感じでBigQueryのテーブルの雰囲気を掴みたいと思いました！
完成したのが下記のクエリです。
このクエリが出力した、queryを実行することで、指定したテーブルに対して下記の要約統計量を取得することができます。
本当はEXECUTE IMMEDIATEを使うことでqueryを実行することができるのですが、
データの量次第でクラウド破産する可能性があるので怖くて使えませんでしたorz
- ordinal_position: 順序
- column_name: カラム名
- data_type: 型
- is_nullable: nullable
- lines: 行数
- null_lines: null行数
- null_rate: null率
- count_distinct: 一意な行数
- max: 最大値
- min: 最小値
- avg: 平均値(数値のみ)
- stddev: 標準偏差(数値のみ)
- percentiles_25: 第一四分位数
- percentiles_50: 第二四分位数
- percentiles_75: 第三四分位数
- top: 出現数上位の値と出現回数
- enumeration: とりうる値を列挙
DECLARE target_table_schema STRING; # 検査対象のスキーマ名を設定します。
DECLARE target_table_name STRING; # 検査対象のテーブル名を設定します。
DECLARE where_clause STRING; # 追加のWHERE句を設定します。
DECLARE show_detail BOOL; # 詳細(top,enumeration)を表示するかを指定します。
DECLARE top_count INT64; # topの最大件数を指定します。
SET target_table_schema = "aaaa";
SET target_table_name = "bbbb";
SET where_clause = "WHERE date BETWEEN '2020-01-01' AND '2020-01-31'";
SET show_detail = true;
SET top_count = 10;
WITH data_source AS (
SELECT * FROM (
SELECT * FROM `aaaa.bbbb.INFORMATION_SCHEMA.COLUMNS`
UNION ALL SELECT * FROM `aaaa.cccc.INFORMATION_SCHEMA.COLUMNS`
)
), target AS (
SELECT
FORMAT("`%s.%s.%s`", table_catalog, table_schema, table_name) AS table_name,
table_schema,
column_name,
data_type,
ordinal_position,
is_nullable
FROM data_source
WHERE
table_name = target_table_name
AND table_schema = target_table_schema
AND data_type NOT LIKE 'STRUCT%'
)
SELECT
STRING_AGG(
CONCAT(
FORMAT("SELECT "),
FORMAT("%d as ordinal_position, ", ordinal_position),
FORMAT("'%s' as column_name, ", column_name),
FORMAT("'%s' as data_type, ", data_type),
FORMAT("'%s' as is_nullable, ", is_nullable),
FORMAT("COUNT(*) as lines,"),
FORMAT("COUNTIF( %s IS NULL) as null_lines, ", column_name),
FORMAT("ROUND(COUNTIF(%s IS NULL) / COUNT(*), 3) as null_rate, ", column_name),
FORMAT("COUNT(DISTINCT %s) as count_distinct, ", column_name),
FORMAT("CAST(MAX(%s) as string) as max, ", column_name),
FORMAT("CAST(MIN(%s) as string) as min, ", column_name),
IF(data_type IN ("FLOAT64", "INT64", "NUMERIC", "BIGNUMERIC"),
FORMAT("CAST(ROUND(AVG(%s), 3) as string) as avg, ", column_name),
"'' AS avg, "
),
IF(data_type IN ("FLOAT64", "INT64", "NUMERIC", "BIGNUMERIC"),
FORMAT("CAST(ROUND(STDDEV(%s), 3) as string) as stddev, ", column_name),
"'' AS stddev, "
),
FORMAT("CAST(APPROX_QUANTILES(%s, 100)[offset(25)] as string) as percentiles_25, ", column_name),
FORMAT("CAST(APPROX_QUANTILES(%s, 100)[offset(50)] as string) as percentiles_50, ", column_name),
FORMAT("CAST(APPROX_QUANTILES(%s, 100)[offset(75)] as string) as percentiles_75, ", column_name),
IF(show_detail, FORMAT("TO_JSON_STRING(APPROX_TOP_COUNT(%s, %d), true) as top, ", column_name, top_count), ''),
IF(show_detail, FORMAT("TO_JSON_STRING(ARRAY_AGG( DISTINCT %s ORDER BY %s), true) as enumeration, ", column_name, column_name), ''),
FORMAT("FROM "),
table_name,
COALESCE(where_clause, '')
) ,
'\nUNION ALL\n'
) || "\nORDER BY ordinal_position" as query
FROM target
INPUT
- targettableschema STRING: 検査対象のスキーマ名を設定します。
- targettablename STRING: 検査対象のテーブル名を設定します。
- where_clause STRING: 追加のWHERE句を設定します。
- show_detail BOOL: 詳細(top,enumeration)を表示するかを指定します。
- top_count INT64: topの最大件数を指定します。
OUTPUT
上記のクエリは、queryという一つのクエリを出力として出します。 このクエリを別途実行することで、要約統計量を取得することができます。
- query