【SQL】データサイエンス100本ノック、Db2でやってみた（S041-S050の気づき）

かずおじです。データサイエンティスト100本ノックSQL版をDb2 on cloud環境で解いてみました。本記事は問題S041-S050での気づきをまとめました。

【サイトはこちら —> データサイエンス100本ノック（構造化データ加工編）】

【他の記事まとめ —> 【SQL】データサイエンティスト100本ノック、Db2でやってみた（まとめ）】

まとめ（S041-S050）

  • LAG, LEAD関数を使って、行の前後で比較できる
  • TO_CHAR・TO_DATE関数でVARCHAR・日付型へ変換できる
  • DATE関数で日付型へ、TIMESTAMP関数でTIMESTAMP型へ変換できる
  • YEAR・MONTH・DAY関数で年月日を抽出できる
  • LPAD関数で0埋めができる

行の前後で比較

LAG, LEAD関数を使って、行の前後で比較できる。

LAG関数は、ORDER BYでソートされた順で前の行を抽出できる

LAG( 列名１ , 数値 ) OVER( ORDER BY 列名２ )

LEAD関数は、ORDER BYでソートされた順で後の行を抽出できる

LEAD( 列名１ , 数値 ) OVER( ORDER BY 列名２ )

※数値：何行分か、を指定する。

参考：OLAP 仕様

問題S-041

WITH
  DATE_AMOUNT AS (
    SELECT
      SALES_YMD, SUM(AMOUNT) AS SUM_AMOUNT
    FROM
      RECEIPT
    GROUP BY
      SALES_YMD
)
SELECT
  SALES_YMD,
  SUM_AMOUNT-LAG(SUM_AMOUNT,1) OVER(ORDER BY SALES_YMD) AS DIFF_AMOUNT
FROM
  DATE_AMOUNT
ORDER BY 
  SALES_YMD
LIMIT
  10;

VARCHAR・日付型の変換

TO_CHAR・TO_DATE関数を使用することでVARCHAR・日付型に変換できる。

TO_CHAR関数：VARCHAR型へ変換

TO_CHAR( 列名 )

TO_DATE関数：DATE（日付）型へ変換

TO_DATE( 列名 )

どちらとも日付フォーマットを指定できる。

参考：TO_CHAR スカラー関数、TO_DATE スカラー関数

問題S-045

SELECT
  CUSTOMER_ID,
  TO_CHAR(BIRTH_DAY, 'YYYYMMDD')
FROM
  CUSTOMER
LIMIT
  10;

DATE（日付）型・TIMESTAMP型への変換

※日付・TIMESTAMP型について似たような関数が多いので別記事でまたまとめようかと思います。

DATE関数は、数値や文字列を日付型に変換できる。数値を指定する場合、西暦１年１月 1 日からn -1 日後の日付に変換される。

DATE( 列名 )

参考：DATE スカラー関数、TIMESTAMP スカラー関数

TIMESTAMP関数は、文字列をタイムスタンプ型に変換できる。日付に加えて時間まで考慮して変換される。単位をつけた数値（SECONDS, MINUTESなど）を加算することも可能。

TIMESTAMP( 列名 )

問題S-045

SELECT
  RECEIPT_NO,
  RECEIPT_SUB_NO,
  SALES_EPOCH,
  TIMESTAMP('1970-01-01') + SALES_EPOCH SECONDS AS SALES_YMD_DATETYPE
FROM 
  RECEIPT
LIMIT
  10
;

年月日の抽出

YEAR・MONTH・DAY関数を使用することで日付・TIMESTAMP型から年月日を抽出することができる。変換された値は数値型となる。

参考：YEAR スカラー関数、MONTH スカラー関数、DAY スカラー関数

YEAR( 列名 )
MONTH( 列名 )
DAY( 列名 )

問題S-049

SELECT
  RECEIPT_NO,
  RECEIPT_SUB_NO,
  SALES_EPOCH,
  YEAR(TIMESTAMP('1970-01-01') + SALES_EPOCH SECONDS) AS SALES_YEAR
FROM 
  RECEIPT
LIMIT
  10
;

0埋め

LPAD関数を使用することで指定した桁数、指定した穴埋め文字で０埋め（右詰）をすることができる。LPADのLはLEFTのLである。

LPAD( 列名 , 桁数 , 穴埋め文字 )

参考：LPAD スカラー関数

問題S-050

SELECT
  RECEIPT_NO,
  RECEIPT_SUB_NO,
  SALES_EPOCH,
  LPAD(VARCHAR(MONTH(TIMESTAMP('1970-01-01') + SALES_EPOCH SECONDS)),2, '0') AS SALES_MONTH
FROM 
  RECEIPT
LIMIT
  10
;

おわりに

今回は日付系の変換がメイントピックでした。TO_DATEとDATEのように似たような関数があるので別記事でまとめたいと思います。

次は、S051-S060の気づきを書きます。