SnowflakeでSQLコンテストの問題解いてみた

分析屋

2023年9月8日 15:45

分析屋の中田(ナカタ)です。
SnowflakeでSQLコンテストの問題に挑戦してみました。

SQLコンテストとは

SQLコンテスト公式

正式名は「TOPSIC SQL CONTEST」
お題に対して、SQLで素早く実装するコンテストです。

2022年7月から開催されており、直近は偶数月に開催されることが多いです。
開催中の任意の１時間で４問を解いて得点を競います。
文法はSQLiteが前提になっています。

今回やること

2023年8月開催の4問のうち、第2問と第3問をSnowflake上で試してみます。
また、正解例以外の別解も探っていきます。
※第1問はかなりシンプルな問題で、正解例以外にひねりようがなさそうなのでカット
※第4問は尺の都合でカット

環境

Snowflakeのエディション：エンタープライズ版
クラウド：AWS（東京リージョン）

事前準備

2023年8月開催回のページ

第2問と第3問のテーブルをSnowflake上に作成します。
CREATE TABLE文とINSERT文は長くなるので省略します。
各設問ページに表示されているサンプルデータをコピーしてExcelに貼り付け
TEXTJOIN関数でうまいことINSERT文が量産されるようにしました。

第２問

使用するテーブル

・ORDERS：注文履歴のテーブル
・CUSTOMER：顧客マスタテーブル

問題文

受注テーブル(ORDERS)より、受注日(ORDER_DATE)が、2023年7月1日から2023年7月31日の1ヵ月間で受注した顧客毎の受注件数を表示しなさい。
ただし、同一日の受注は1件とカウントする。

※コンテスト期間は終了していますが、サイト上で問題を解いて正誤判定してもらうことは可能です。
このあと正解例を書いていますので、挑戦してみたい人はいったんサイトに飛んでください。

正解例

SELECT
    OD.CUST_CODE AS CODE
    , CS.CUST_NAME AS NAME
    -- DISTNCTで同一日付を1件とカウントする
    , COUNT(DISTINCT OD.ORDER_DATE) AS CNT 
FROM
    ORDERS AS OD 
    -- 顧客名を取得するため顧客テーブルを内部結合
    INNER JOIN CUSTOMER AS CS 
        ON CS.CUST_CODE = OD.CUST_CODE 
WHERE
    OD.ORDER_DATE BETWEEN '2023-07-01' AND '2023-07-31' 
GROUP BY
    OD.CUST_CODE 
    , CS.CUST_NAME
ORDER BY
    CNT DESC
    , CODE DESC;

正解例の方針は以下の通りです。
・注文履歴のテーブルと、顧客マスタのテーブルを結合しよう
・それから期間を絞って集約しよう

もう少し手を加えて以下のように変えてみます。

別解

WITH temp AS(
    SELECT
        OD.CUST_CODE AS CODE
        -- DISTNCTで同一日付を1件とカウントする
        , COUNT(DISTINCT OD.ORDER_DATE) AS CNT 
    FROM
        ORDERS AS OD 
    WHERE
        OD.ORDER_DATE BETWEEN '2023/07/01' AND '2023/07/31' 
    GROUP BY
        OD.CUST_CODE
    )

SELECT
    temp.CODE
    ,CS.CUST_NAME
    ,temp.CNT
FROM temp
    -- 顧客名を取得するため顧客テーブルを内部結合
    INNER JOIN CUSTOMER AS CS 
        ON temp.CODE = CS.CUST_CODE 
ORDER BY
    temp.CNT DESC
    , temp.CODE DESC
;

先に集約しておいて、後から顧客マスタテーブルを結合しています。
正解例との違いは、こうすることで結合の行数を削減できるということです。

特にSnowflakeならではという解答は浮かばないですが
計算コストの高い結合をできるだけ後回しにするという工夫をしてみました。

第３問

使用するテーブル

・SCHOOL_HEALTH：地区毎の学生の身長・体重の平均値を記録したテーブル

問題文

※長いので要約します。
SCHOOL_HEALTHテーブルから
調査年(SURVEY_YEAR列)が2019年のデータを
年齢・性別ごとに身長・体重の平均値を計算し、その平均値以上の地区の割合を表示しなさい。
カテゴリコード(CATEGORY_CODE列)の意味は、'10'が身長、'20'が体重。
性別コード(GENDER_CODE列)は、'20'が男、'30'が女。
小数点第2位を四捨五入すること。

正解例に移る前に、テーブル構造を説明します。

例えば１行目については
2018年調査の地区番号10000、男子17歳の身長平均165.2cmというデータです。
余談ですが、これっていわゆるEAVアンチパターンっぽいですね。
興味のある方はググってみてください。

正解例

WITH AVG_TBL AS ( 
   -- 年齢、性別毎に身長と体重の平均値を算出
    SELECT
        AGE
        , GENDER_CODE
          -- ROUND関数で小数点第2位を四捨五入
        , ROUND( 
            AVG( 
                -- カテゴリコードが10(身長)のデータが対象
                CASE CATEGORY_CODE 
                    WHEN 10 THEN AVERAGE_VALUE 
                    END
            ) 
            , 1
        ) H_AVG
        , ROUND( 
            AVG( 
                -- カテゴリコードが20(体重)のデータが対象
                CASE CATEGORY_CODE 
                    WHEN 20 THEN AVERAGE_VALUE 
                    END
            ) 
            , 1
        ) W_AVG 
    FROM
        SCHOOL_HEALTH 
    WHERE
        SURVEY_YEAR = 2019 
    GROUP BY
        AGE
        , GENDER_CODE
) 
SELECT
    AVG_TBL.AGE
      -- CASE句で性別の表示内容を振分け
    , CASE AVG_TBL.GENDER_CODE 
        WHEN 20 THEN 'MALE' 
        ELSE 'FEMALE' 
        END GENDER
    , MAX(H_AVG) H_AVG
    , MAX(W_AVG) W_AVG
      -- 計算結果を四捨五入
    , ROUND( 
        -- 平均値以上の件数をカウント(100.0を掛けて小数点以下の計算を可能とする)
        100.0 * COUNT( 
            CASE 
                WHEN SH.CATEGORY_CODE = 10 
                AND H_AVG <= SH.AVERAGE_VALUE 
                    THEN 1 
                END
       -- カテゴリコードが身長のデータの全件をカウント
        ) / COUNT(CASE WHEN SH.CATEGORY_CODE = 10 THEN 1 END)
        , 1
    -- %を結果に付加
    ) || '%' H_PER
      -- 体重も身長と同様に計算
    , ROUND( 
        100.0 * COUNT( 
            CASE 
                WHEN SH.CATEGORY_CODE = 20 
                AND W_AVG <= SH.AVERAGE_VALUE 
                    THEN 1 
                END
        ) / COUNT(CASE WHEN SH.CATEGORY_CODE = 20 THEN 1 END)
        , 1
    ) || '%' W_PER 
FROM
    AVG_TBL 
    -- 学校保健データを外部結合して比較する平均値を取得
    LEFT OUTER JOIN SCHOOL_HEALTH AS SH 
        ON AVG_TBL.AGE = SH.AGE 
        AND AVG_TBL.GENDER_CODE = SH.GENDER_CODE 
        AND SURVEY_YEAR = 2019 
GROUP BY
    AVG_TBL.AGE
    , AVG_TBL.GENDER_CODE 
ORDER BY
    AVG_TBL.AGE DESC
    , AVG_TBL.GENDER_CODE DESC;

少し補足します。
WITH句のAVG_TBLで、年齢・性別ごとに2019年の全地区の身長と体重の平均値を集計しています。
CASE式を上手く使うことで、身長と体重を分けて平均値を集計しています。

一時テーブルのAVG_TBLは以下のようになっています。

このAVG_TBLに対して、元のSCHOOL_HEALTHテーブルをLEFT JOINします。
これで、各エリアの集計結果に対して、全体平均が付与されたことになります。

正解例の以下の部分は、カテゴリーコードが10（身長）の行が、全体平均を超えているかどうかのフラグ列（1かNULL）を作ってカウントしています。
COUNTではなくSUMでも同じ結果になります。

COUNT( 
            CASE 
                WHEN SH.CATEGORY_CODE = 10 
                AND H_AVG <= SH.AVERAGE_VALUE 
                    THEN 1 
                END
        )

以下のコードは、カテゴリーコードが10（身長）の行数をカウントしています。
こちらもカッコの中身は1かNULLなので、COUNTでもSUMでも同じです。

COUNT(CASE WHEN SH.CATEGORY_CODE = 10 THEN 1 END)

別解

WITH句の中身は以下のように書き換えてみました。

    SELECT
        p.AGE
        ,p.GENDER_CODE
        ,p."10" AS H_AVG
        ,p."20" AS W_AVG
    FROM (SELECT * EXCLUDE(PF_CODE) FROM SCHOOL_HEALTH) SH
        PIVOT(AVG(SH.AVERAGE_VALUE) FOR SH.category_code IN (10,20)) p
    WHERE p.SURVEY_YEAR = 2019

PIVOT関数を使用しています。
PIVOT | Snowflake Documentation

Excelのピボットテーブル的な考え方でクロス集計表の形式を作ることができます。
使い方は以下の通りです。

まずはFROM句に、集計で使用するテーブルを指定します。
不要なカラムは除外しないと、全カラムでGROUP BYみたいなことになるので
今回はEXCLUDE(PF_CODE)と書いて、地区コード列を除外します。
FROM (SELECT * EXCLUDE(PF_CODE) FROM SCHOOL_HEALTH) SH

続けて、PIVOT関数を使用します。
PIVOT(AVG(SH.AVERAGE_VALUE) FOR SH.category_code IN (10,20)) p
PIVOT関数の中には集計の種類（AVGやSUMなど）を指定

SELECT句には、元テーブルではなくピボットテーブルの結果を指定します。
    SELECT
        p.AGE
        ,p.GENDER_CODE
        ,p."10" AS H_AVG
        ,p."20"ASW_AVG

結果は以下の通りです。

さらに、続けてWITH句以降のメインクエリもウィンドウ関数で集計してみます。
※身長だけ

WITH temp AS (
    SELECT
        p.AGE
        ,p.GENDER_CODE
        ,p."10" AS H_AVG
        ,p."20" AS W_AVG
    FROM (SELECT * EXCLUDE(PF_CODE) FROM SCHOOL_HEALTH) SH
        PIVOT(AVG(SH.AVERAGE_VALUE) FOR SH.category_code IN (10,20)) p
    WHERE p.SURVEY_YEAR = 2019
    -- ORDER BY p.AGE,p.GENDER_CODE
)
-- 年齢・性別ごとのウィンドウ関数
SELECT DISTINCT
    temp.AGE
    ,temp.GENDER_CODE
    ,temp.H_AVG
    ,SUM(CASE WHEN SH.CATEGORY_CODE = 10 THEN (SH.AVERAGE_VALUE >= temp.H_AVG)::NUMBER END) OVER(PARTITION BY SH.AGE, SH.GENDER_CODE) AS "分子"
    ,COUNT(CASE WHEN SH.CATEGORY_CODE = 10 THEN 1 END) OVER(PARTITION BY SH.AGE, SH.GENDER_CODE) AS "分母"
    ,ROUND(100 * "分子" / "分母", 1) || '%' AS H_PER
FROM
    temp
INNER JOIN
    SCHOOL_HEALTH SH
ON temp.AGE = SH.AGE
AND temp.GENDER_CODE = SH.GENDER_CODE
AND SH.SURVEY_YEAR = 2019
ORDER BY AGE DESC,GENDER_CODE DESC
;

ウィンドウ関数の部分（分子）を抜粋します。
SUM(CASE WHEN SH.CATEGORY_CODE = 10
THEN (SH.AVERAGE_VALUE >= temp.H_AVG)::NUMBER END)
OVER(PARTITION BY SH.AGE, SH.GENDER_CODE)

カテゴリーコードが10（身長）であれば、全体平均以上かどうかを評価しています。
(SH.AVERAGE_VALUE >= temp.H_AVG) がTRUEかFALSEになるので
::NUMBERで1か0になるよう変換しています。

身長が全体平均以上なら1、未満なら0、そもそも身長の集計でなければNULL
の合計なので、身長の全体平均以上の地区数が求まります。

SnowflakeではSELECT句で作った列を、そのままSELECT句で再利用できるので
今回は途中経過が検証しやすいように分子と分母と割合を並べて表示しています。

他にも、RATIO_TO_REPORT関数という関数も使えそうかなと思いましたが
今回の集計用途には合わなそうでした。。。
RATIO_TO_REPORT | Snowflake Documentation

RATIO_TO_REPORT(列名) OVER() で、各行が全体の何割かが表示されます。
例えば全店舗の売上データテーブルで
RATIO_TO_REPORT(売上金額) OVER() とすると
各店舗の売上の割合が表示されることになります。

まとめ

Snowflakeは、多様な関数や機能をそろえています。
他のDBやDWH製品からの移植性を考えてのことかと思いますが
通常は長くなったり複雑になるSQL文が、工夫次第で短くできるのかなと感じます。

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

これまでの記事はこちら！

株式会社分析屋について

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

ホームページはこちら。

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

【データ分析で日本を豊かに】
分析屋はシステム分野・ライフサイエンス分野・マーケティング分野の知見を生かし、多種多様な分野の企業様のデータ分析のご支援をさせていただいております。「あなたの問題解決をする」をモットーに、お客様の抱える課題にあわせた解析・分析手法を用いて、問題解決へのお手伝いをいたします！
【マーケティング】
マーケティング戦略上の目的に向けて、各種のデータ統合及び加工ならびにPDCAサイクル運用全般を支援や高度なデータ分析技術により複雑な課題解決に向けての分析サービスを提供いたします。
【システム】
アプリケーション開発やデータベース構築、WEBサイト構築、運用保守業務などお客様の問題やご要望に沿ってご支援いたします。
【ライフサイエンス】
機械学習や各種アルゴリズムなどの解析アルゴリズム開発サービスを提供いたします。過去には医療系のバイタルデータを扱った解析が主でしたが、今後はそれらで培った経験・技術を工業など他の分野の企業様の問題解決にも役立てていく方針です。
【SES】
SESサービスも行っております。

この記事が参加している募集

#やってみた

36,993件