🥖

[Snowflake] SQL100本ノックのコードをSnowflakeで書き換えてみた (50問目まで)

2023/07/12に公開

SQL

SQL100本ノックの問題のうち50問目までをSnowflakeで書き換えるとどうなるか見てみました。50問目までだと、大きく書き方が変化する問題はあまりなかったのですが、縦持ち横持ちの変換が柔軟にできる PIVOT, UNPIVOT はとても便利に感じました (S-044の問題がそれです)。

SQL100本ノックの全問題はこちらを御覧ください
 SQL100本ノックの公式の模範解答はこちらを御覧ください

なお、100本ノック用のデータをSnowflakeに入れるのはTroccoを使用しています。
Troccoを使用してデータをSnowflakeのTableに入れるための設定方法はこちらの記事を御覧ください。

[Snowflake] Troccoを使ってS3にあるデータをSnowflakeに転送してみた

S-028

レシート明細データ（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の中央値を計算し、降順でTOP5を表示せよ。

Snowflakeでは中央値を計算するための関数 MEDIAN があるため、それを使用。

SELECT 
  store_cd, 
  MEDIAN(amount) AS amount_50per
FROM
  receipt
GROUP BY 
  ALL
ORDER BY
  amount_50per DESC
LIMIT 5

S-044

043で作成した売上サマリデータ（sales_summary）は性別の売上を横持ちさせたものであった。このデータから性別を縦持ちさせ、年代、性別コード、売上金額の3項目に変換せよ。ただし、性別コードは男性を"00"、女性を"01"、不明を"99"とする。

問題がわかりにくいですが、このようなデータ構造のものを

こういう構造に変換する問題です

Snowflakeでは UNPIVOT という、列を行に入れ替える関数があるのでそれを使用。 (参考)

SELECT
  era,
  CASE gender 
    WHEN 'MALE' THEN '00'
    WHEN 'FEMALE' THEN '01'
    WHEN 'UNKNOWN' THEN '02'
  END AS gender_cd,
  amount
FROM
  sales_summary
  UNPIVOT (amount FOR gender IN (MALE, FEMALE, UNKNOWN))
ORDER BY 
  2, 1

S-047

レシート明細データ（receipt）の売上日（sales_ymd）はYYYYMMDD形式の数値型でデータを保有している。これを日付型に変換し、レシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに10件表示せよ。

Snowflakeでは 列名::VARCHAR という記法でCASTできる。

SELECT  
  receipt_no, 
  receipt_sub_no,
  TO_DATE(sales_ymd::VARCHAR, 'YYYYMMDD') AS sales_ymd
FROM
  receipt 
LIMIT
  10

S-049

レシート明細データ（receipt）の売上エポック秒（sales_epoch）を日付型に変換し、「年」だけ取り出してレシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに10件表示せよ。

Snowflakeでは EXTRACT を使わずとも YEAR で年を取得できる。
YEAR に限らず MONTH, DAY なども同じ様に計算できる (参考)

SELECT 
  receipt_no, 
  receipt_sub_no,
  YEAR(TO_TIMESTAMP(sales_epoch)) AS sales_year
FROM receipt 
LIMIT 10;

S-028

S-044

S-047

S-049

Discussion