概要です。
Excelで実施していたデータ処理をSnowflakeに移設することがありました。移行元のExcelではクロス集計やヒストグラムなどを使っていたのですが、Snowflakeには準備されたSQLが存在し、比較的ラクにSQLで処理を再現することが出来ました。EXCELではデータ量が増えてくると辛くなってきます。そんなときにも良いのかな、と考えております。
公式にSQL構文の解説があります。
- クロス集計・ピボットテーブル
https://docs.snowflake.com/ja/sql-reference/constructs/pivot.html - ヒストグラム作成
https://docs.snowflake.com/ja/sql-reference/functions/width_bucket.html
やってみます。
KaggleのTitanicのデータ、及びチュートリアルのデータ前処理を一部切り取って実装してみました。
- データ https://www.kaggle.com/competitions/titanic/data
- チュートリアル https://www.kaggle.com/code/hatokgl/titanic
上記のURLから
#crosstab : クロス集計表（カテゴリごとの個数をカウント)
pd.crosstab(train_df.Parch, train_df.Pclass).style.background_gradient(cmap="summer_r")
の部分をやります。
まず、テーブルを作成してデータをロードします。同じことをやってみたい方のために
テーブル作成用のSQLは共有しておきます。
CREATE TABLE titanic_train(
PassengerId integer,
Survived integer,
Pclass integer,
Name varchar,
Sex varchar,
Age decimal,
SibSp integer,
Parch integer,
Ticket varchar,
Fare varchar,
Cabin varchar,
Embarked varchar);
titanic_train テーブルをクロス集計します。
SELECT * from ( SELECT PassengerId,Parch,Pclass from titanic_train)
pivot(count(PassengerId) for Pclass in (1, 2, 3))
as p order by Parch
実行結果
結果を 別なテーブルに保管しておくことも一つのやり方だと考えます。titanic_train_cross テーブルを結果表として作る形にすると以下になります。
CREATE or replace table titanic_train_cross AS
SELECT * from ( SELECT PassengerId,Parch,Pclass from titanic_train)
pivot(count(PassengerId) for Pclass in (1, 2, 3))
as p order by Parch
参考までに最も小さいウェアハウスで実行しましたが70msで実行できました。
ヒストグラム
チュートリアルでは年齢を男女別にヒストグラムとしてグラフ表示していますが、ここでは年齢別のヒストグラムを作成してみます。SQLでは、グラフ表示ではなく既存のレコードがヒストグラムのどの場所（バケット）に割り振られるのかを算出する形になります。
※チュートリアルでは年齢の範囲を10のバケットに分けていました。
分析はこれでよいのですが、確認が曖昧になるので年代別に分けてみます。
まずバケット番号を割り当てるための列を追加します。
alter table titanic_train
add age_bucket_number smallint;
次にバケット番号を振ります。
update titanic_train
set age_bucket_number = width_bucket(age, 0, 89, 9)
バケット番号を一部確認してみます。
select top 10 age,age_bucket_number from titanic_train
結果
年代別の件数を確認します。
select age_bucket_number,count(PassengerId) from titanic_train group by age_bucket_number
order by age_bucket_number
結果
以上になります。
記事に不具合。不都合などございましたらご連絡いただけると幸いです。記事内容は会社公式のものではなく、個人として記載するものです。