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

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

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

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

まとめ（S021-S030）

集合関数は使い方が似ているため、覚えやすい

ここでよく使われそうな集合関数と最低限のフォーマットを列挙する。（オプションがあったりするがここでは省く）

	フォーマット
最大値	MAX(列名)
最小値	MIN(列名)
平均値	AVG(列名)
合計値	SUM(列名)
中央値	MEDIAN(列名)
分散	VAR(列名)

これは単純で覚えやすい。英語を知っておけば最低限使える。

問題S023

SELECT 
    STORE_CD, 
    SUM(AMOUNT) 
FROM 
    RECEIPT
GROUP BY
    STORE_CD;

問題S024

SELECT 
    CUSTOMER_ID, 
    MAX(SALES_YMD) 
FROM 
    RECEIPT
GROUP BY
    CUSTOMER_ID
LIMIT
    10;

中央値を求める

MEDIAN関数を使用することで中央値を出力することができる。

MEDIAN( 列名 )

参考：MEDIAN 集約関数

※以下のPERCENTILE_CONT関数を使った表現と同じ結果になる。

PERCENTILE_CONT( 0.5 ) WITHIN GROUP( 列名 )

問題S028

SELECT
    STORE_CD,
    MEDIAN(AMOUNT) AS MDN_AMOUT
FROM
    RECEIPT
GROUP BY
    STORE_CD
ORDER BY
    MDN_AMOUT DESC
LIMIT
    5;

最頻値を求める

私が調べた限りでは、Db2での最頻値を求める関数は無い。そのため、RANK関数を利用して求める。

RANK() OVER(PARTITION BY 列名１ ORDER BY COUNT(列名２) DESC)

PERTITION BY…はその単位でランク付けするか、を指定する。

参考：OLAP 仕様

上記でランク付けして、RANK()..の出力が「1」のものを抽出すればそれが最頻値である。

問題S029

SELECT
    STORE_CD, PRODUCT_CD, CNT
FROM(
    SELECT
        STORE_CD, 
        PRODUCT_CD, 
        COUNT(STORE_CD) as CNT,
        RANK() OVER(PARTITION BY STORE_CD ORDER BY COUNT(STORE_CD) DESC) AS RNK
    FROM
       RECEIPT
    GROUP BY
        STORE_CD, PRODUCT_CD
    )
WHERE
    RNK = 1
ORDER BY
    STORE_CD, PRODUCT_CD;

分散を求める

VAR関数を使用することで中央値を出力することができる。

VAR( 列名 )
or
VARIANCE( 列名 )

参考：VARIANCE 集約関数

問題S030

SELECT 
    STORE_CD, 
    VAR(AMOUNT)
FROM
    RECEIPT
GROUP BY
    STORE_CD;

おわりに

Db2の公式サイトを調べると思ったよりいろんな関数がありますね。ただ記事でも書いているように英単語さえ知っていれば非常に覚えやすいのかなと思います。

次は問題S031-S040についてまとめていきます。