Snowflakeで年齢計算してみた
分析屋の中田(ナカタ)です。
Snowflakeで年齢計算を試してみました。
今回やること
生年月日列を元に、本日時点での年齢を算出します。
ただし、生年月日列のフォーマットはぐちゃぐちゃになっています。
環境
Snowflakeのエディション：エンタープライズ版
クラウド：AWS（東京リージョン）
事前準備
検証用のテーブルを作成します。
-- 誕生日リストの空テーブル
CREATE TABLE birthday_list (
birth_day VARCHAR(20)
);
-- 誕生日入力
INSERT INTO birthday_list VALUES('2000-01-01');
INSERT INTO birthday_list VALUES('20000101');
INSERT INTO birthday_list VALUES('2000/01/01');
INSERT INTO birthday_list VALUES('01/01/2000');
INSERT INTO birthday_list VALUES('2000.01.01');
INSERT INTO birthday_list VALUES('平成12年1月1日');
INSERT INTO birthday_list VALUES('2000年01月01日');
INSERT INTO birthday_list VALUES('２０００－０１－０１');結果は以下の通りです。
SELECT * FROM birthday_list;
TO_DATE関数による変換
文字列のデータを日付型に変換するため、TO_DATE関数で変換してみます。
-- 変換失敗
SELECT
birth_day
,TO_DATE(birth_day) AS "変換後の誕生日"
FROM
birthday_list
;
日付型に変換できない行があると、その時点でエラーが発生して停止します。
TRY_TO_DATE関数による変換
TRY_TO_DATE関数を代わりに使用します。
変換できないデータはNULLに置き換える関数です。
ちなみに他の製品だと
SQL ServerではTRY_TO_CAST関数
BigQueryではSAFE_CAST関数
という、CASTに失敗したらNULLに置き換える関数があります。
-- 自動変換
SELECT
birth_day
,TRY_TO_DATE(birth_day,'AUTO') AS "変換後の誕生日"
FROM
birthday_list
;
ほぼほぼ失敗しています・・・
が、エラーで停止することはなくなりました。
代わりに、「20000101」だけ日付が正しく変換できていません。
これは、Snowflakeでは1970年1月1日0時0分0秒を基準の0として
秒単位でシリアル値を管理するためです。
86,400（=24時間*60分*60秒）ごとに1日繰り上げており
20000101はシリアル値20,000,101(秒)と解釈され
1970年1月1日から約231日(=20,000,101 / 86,400)後の日付になっています。
別途なんらかの処理が必要ですが、今回はこのまま進めます。
年齢計算ロジック
よく使われる計算ロジックを使用します。
本日の日付をYYYYMMDD形式の8桁整数に変換します。
誕生日も同様にYYYYMMDD形式の8桁整数に変換します。
その差を取って10,000で割る(小数切り捨て) で年齢になります。
例：本日2023/08/15時点での私(1991/03/16)の年齢
(20,230,815 - 19,910,316) / 10,000 = 32.0499 ≒ 32歳
SELECT
birth_day
,TRY_TO_DATE(birth_day,'AUTO') AS "変換後の誕生日"
,TO_VARCHAR(CURRENT_DATE, 'YYYYMMDD')::NUMBER AS "本日YYYYMMDD"
,TO_VARCHAR("変換後の誕生日", 'YYYYMMDD')::NUMBER AS "誕生日YYYYMMDD"
,FLOOR(("本日YYYYMMDD" - "誕生日YYYYMMDD") / 10000) AS "年齢"
,COALESCE(TO_CHAR("年齢"),'変換失敗') AS "結果"
FROM
birthday_list
;
NULL表記ではなく分かりやすいメッセージにしたい場合は
最終列にCOALESCE関数で、NULLの場合の文言を記載すればOKです。
ただし列全体を文字列型に統一する必要が出てくるため、TO_CHAR関数で年齢を文字列型に変換しています。
また、SnowflakeではSELECT句の別名(エイリアス)を、同じSELECT句内で利用できます。
よって上記のクエリのように、少しずつ加工しながら変遷をまとめて表示することができますが、他製品への移植性は低いクエリになります。
最後に
Snowflakeは他製品の様々な関数や機能を網羅しており
既存のロジックをSnowflakeに移植する際は
今までと違った書き方ができるのではないかと感じました。
ここまでお読みいただき、ありがとうございました！
この記事が少しでも参考になりましたら「スキ」を押していただけると幸いです！
これまでの記事はこちら！
株式会社分析屋について
弊社が作成を行いました分析レポートを、鎌倉市観光協会様HPに掲載いただきました。
ホームページはこちら。
noteでの会社紹介記事はこちら。
【データ分析で日本を豊かに】
分析屋はシステム分野・ライフサイエンス分野・マーケティング分野の知見を生かし、多種多様な分野の企業様のデータ分析のご支援をさせていただいております。 「あなたの問題解決をする」をモットーに、お客様の抱える課題にあわせた解析・分析手法を用いて、問題解決へのお手伝いをいたします！
【マーケティング】
マーケティング戦略上の目的に向けて、各種のデータ統合及び加工ならびにPDCAサイクル運用全般を支援や高度なデータ分析技術により複雑な課題解決に向けての分析サービスを提供いたします。
【システム】
アプリケーション開発やデータベース構築、WEBサイト構築、運用保守業務などお客様の問題やご要望に沿ってご支援いたします。
【ライフサイエンス】
機械学習や各種アルゴリズムなどの解析アルゴリズム開発サービスを提供いたします。過去には医療系のバイタルデータを扱った解析が主でしたが、今後はそれらで培った経験・技術を工業など他の分野の企業様の問題解決にも役立てていく方針です。
【SES】
SESサービスも行っております。