ExcelでやってたことをSQLでもできるか試してみた

2025年2月25日 18:00

こんにちは、野口です。
今回はSQL(BigQuery)についての記事です。

先日、案件を通して初めてBigQueryを触る機会があり、SQLは数年前の研修で少しやった程度で実務未経験でのスタートでした。
調べながらプログラミングをしていったのですが、Excelに慣れ親しんだ時間が長いこともありついつい「これExcelでの実装はすぐ思いつくのにな...」とぼやきながら作業していました。
紆余曲折しながら思い通りのコーディングができ、基礎は抑えることができたのかなと実感しています。

Excelはある程度知っているけどSQLはまだまだこれから、そんな方に向けて記事を書きました。

その０：データセットをBigQueryにアップロードする

そこから？笑
と思った方もいると思いますが、自分はここが難所でした。
何故ならBigQueryではデータセット作成時に型が決まり、互換性のない型への変換が必要になり、それが自分にとっては色々大変でした。。

登録時点で全てのカラムの型（BigQuery上では種類と表記される）の確認をする癖をつけておくと後々の手戻り防止に繋がります。

フィールド名を設定し忘れたのでついでにこれも解説します。BigQueryでは対象のテーブルを指定する際は、’{プロジェクト名}.{データセット名}.{テーブル名}’で指定します。ALTER TABLEは既存テーブルの変更を意味するので指定したテーブルの変更をするコマンドになります。

ALTER TABLE `striped-torus-449706-a6.BigQueryPractice202502.BaseTable`
 RENAME COLUMN string_field_0 TO TicketNumber,
 RENAME COLUMN date_field_1 TO DateofBirth,
 RENAME COLUMN int64_field_2 TO Age,
 RENAME COLUMN int64_field_3 TO TicketPrice,
 RENAME COLUMN int64_field_4 TO FoodandBeveragePrice

コードを実行した結果がこちらになります。

そして軽く今回のデータセットについて説明します。
TicketNumber：アルファベットと数字を組み合わせたチケットの整理番号（ユニークキー）
DateofBirth：チケット購入者の生年月日、yyyy-mm-dd形式
Age：チケット購入者の年齢
TicketPrice：チケット代金
FoodandBeveragePrice：当日の飲食物の支払代金

その１：四則演算

Excelの四則演算は’＋’、’-’、’*’、’/’、で表記しますが、SQLもこの表記で行けます。
今回はチケット代金と飲食物代金の合算を新規列で算出しようと思います。
なのでコードとしては以下の一行で新規作成できます。

TicketPrice + FoodandBeveragePrice AS TotalPrice

注意点としては他のプログラミング言語同様に、既存列の加工によって新規列を算出する際はテーブル指定だけでは不十分で、既存列の宣言が必要になります。

その２：IF（IFERROR）

今回使用したデータセットには欠損値が含まれていませんが、実務では欠損値はつきものです。そこで欠損値があった場合の例外処理を含めた加工も紹介します。

まずはExcel上での関数から、作成したいのは生年月日から年代を算出する関数
=IFERROR((ROUNDDOWN((DATE(B2,E12,"Y")/10,0)*10)&"代","不明")
これをSQL上で実装すると以下のようになります

CASE
    WHEN DateofBirth IS NULL THEN '不明'
    ELSE CONCAT(CAST(FLOOR(DATE_DIFF(CURRENT_DATE(), DateofBirth, YEAR) / 10) * 10 AS STRING), '代')
  END AS YearPeriod,

IFはSQLではCASE式と呼ばれるフォーマットで表記し、
CASE
　WHEN　A　{条件式} B　THEN “CCC”
　ELSE “DDD”
END
で１セットのように扱うイメージです。
今回は条件式にIS NULLを使用しており、「もしDateofBirthが欠損値ならば」という意味になっています。
ELSE以降に使用したSQL関数をさくっと解説します。

CONCAT(A, B）
　 AとBの2つ以上の文字列型の値を結合でき、B以降に値を追加すれば3つ以上も可能。
　Excelにおける’＆’の役割を持つ。

CAST(A AS B）
　AをBの型に変換できる。
　今回はFLOOR関数で算出された値 × 10の値を文字列に変換している。

FLOOR(A)
　AをFLOOR型に変換できる。主にINTEGRER型の数値をFLOOR型に変換する関数。
　今回は計算の都合上、計算結果に小数点が発生する場合があったため、FLOOR型にして対応した。

DATE_DIFF(A, B, {単位})
　AとBの年月日を比較して、指定した単位（YEAR, DAY, MONTH等）で値を算出する関数
　Excelにおける’DATEDIF’関数と同等の役割を持つ。

CURRENT_DATE()
　コード実行時点の現在の日時をyyyy-mm-dd形式で取得する関数
　Excelにおける’TODAY（）’と同等の役割を持つ。

これらをまとめて以下のコードで実行します。

CREATE TABLE `striped-torus-449706-a6.BigQueryPractice202502.ProcessingTable` AS
SELECT
  TicketNumber,
  DateofBirth,
  Age,
  TicketPrice,
  FoodandBeveragePrice,
  TicketPrice + FoodandBeveragePrice AS TotalPrice,
  CASE
    WHEN DateofBirth IS NULL THEN '不明'
    ELSE CONCAT(CAST(FLOOR(DATE_DIFF(CURRENT_DATE(), DateofBirth, YEAR) / 10) * 10 AS STRING), '代')
  END AS YearPeriod,

FROM `striped-torus-449706-a6.BigQueryPractice202502.BaseTable`;

確認すると、新しいテーブルができ、2つのフィールドが追加されていますね。

※データベース等を触れる際のマナーとして生データ（今回のBaseTableというテーブル）には手を加えてはいけないという原則があるためCREATE TABLEというコマンドで新たに作成したテーブルに列追加をしています。

その３：フィルター

Excelでは指定の列を基準に昇順降順の並び替えもボタンポチポチでできます。
SQLではExcelのような切り替えはできませんが、並び替えることは可能です。

SELECT *
FROM `striped-torus-449706-a6.BigQueryPractice202502.ProcessingTable`
ORDER BY DateofBirth DESC

ORDER　BY　AAA　DESC　→降順（新しい日付、小さい数字が先頭）
ORDER　BY　AAA　ASC　　→昇順（古い日付、大きい数字が先頭）

一行で実装できるのはありがたいですね。ちなみにフィルターには特定の値を除く機能もありますが、その場合はDELETE関数とWHERE関数を組み合わせれば対応可能です。

DELETE FROM `striped-torus-449706-a6.BigQueryPractice202502.ProcessingTable`WHERE TicketNumber LIKE 'TT%';

実行結果が以下の画像です。
（DateofBirthが1979-12-22 の値の列に着目すると分かりやすいかもしれません）

まとめ

SQLは古くから今日まで使われているデータベースを操作するプログラミング言語なので技術習得をしておくと何かと便利だと改めて感じました。
複数の関数の組合せがスムーズにできれば、もっと複雑な加工もSQLでは実現できると思います。

ほかにも解説してほしい関数等ありましたらコメントください！

ここまでお読みいただき、ありがとうございました！
この記事が少しでも参考になりましたら「スキ」を押していただけると幸いです！

株式会社分析屋について

弊社が作成を行いました分析レポートを、鎌倉市観光協会様HPに掲載いただきました。

ホームページはこちら。

noteでの会社紹介記事はこちら。

【データ分析で日本を豊かに】
分析屋はシステム分野・ライフサイエンス分野・マーケティング分野の知見を生かし、多種多様な分野の企業様のデータ分析のご支援をさせていただいております。「あなたの問題解決をする」をモットーに、お客様の抱える課題にあわせた解析・分析手法を用いて、問題解決へのお手伝いをいたします！

【マーケティング】
マーケティング戦略上の目的に向けて、各種のデータ統合及び加工ならびにPDCAサイクル運用全般を支援や高度なデータ分析技術により複雑な課題解決に向けての分析サービスを提供いたします。

【システム】
アプリケーション開発やデータベース構築、WEBサイト構築、運用保守業務などお客様の問題やご要望に沿ってご支援いたします。

【ライフサイエンス】
機械学習や各種アルゴリズムなどの解析アルゴリズム開発サービスを提供いたします。過去には医療系のバイタルデータを扱った解析が主でしたが、今後はそれらで培った経験・技術を工業など他の分野の企業様の問題解決にも役立てていく方針です。

【SES】
SESサービスも行っております。