データサイエンス100本ノック（構造化データ加工編）解説_S021-S026

はじめに

第四回です。前回はこちら。

凡例

• [XXX]：　XXXは省略可能
• (XXX)：　XXXの論理的な名前
• 本来は、%%sqlを答えの1行目に記載する必要があります。

問題

21問目：集計関数① 件数カウント

S-021: レシート明細データ（receipt）に対し、件数をカウントせよ。

S-021答え

SELECT
  COUNT(*)
FROM
  receipt r
;

テーブルのレコード数を算出する際には、COUNT を使います。
構文

SELECT COUNT(*) FROM (テーブル名)

'COUNT(列名)'とすると、列名の数を数えます。
列名を指定すると、列名がNULLの場合は数えません。
'COUNT(*)'はNULLがあってもレコードとして存在していれば数えます。
また、 COUNT(1)としても同じ結果になります。

22問目：集計関数② ユニークな件数カウント

S-022: レシート明細データ（receipt）の顧客ID（customer_id）に対し、ユニーク件数をカウントせよ。

S-022答え

SELECT
  COUNT(DISTINCT customer_id)
FROM
  receipt r
;

重複を排除して件数を算出する場合は DISTINCTを列名の前に入れます。

23問目：集計関数③ 合計、列ごとの集計

S-023: レシート明細データ（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）と売上数量（quantity）を合計せよ。

S-023答え

SELECT
  store_cd
  , SUM(amount) AS amount
  , SUM(quantity) AS quantity
FROM
  receipt r
GROUP BY
  store_cd
;

合計する際には、SUMを使います。
また、テーブルの特定の列の値単位で集計したい際にはGROUP BYを使います。
構文(合計)

SELECT SUM( (列名) ) FROM (テーブル名)

構文(列名単位の集計)

SELECT (列名), SUM( (列名) ) FROM (テーブル名) GROUP BY (集計単位の列名)

集計関数(上記構文ではSUM)がなくてもGROUP BY は使えます。
集計単位の列名は複数指定できます。

24問目：集計関数③ 最大最小

S-024: レシート明細データ（receipt）に対し、顧客ID（customer_id）ごとに最も新しい売上年月日（sales_ymd）を求め、10件表示せよ。

S-024答え

SELECT
  customer_id
  , MAX(sales_ymd) AS sales_ymd
FROM
  receipt r
GROUP BY
  customer_id
;

テーブルのカラムの最大値を算出する際には、MAX を使います。
構文

SELECT MAX((列名)) FROM (テーブル名)

23問目と同様、GROUP BYと併用可能です。
$一番新しい年月日=最大の年月日$ です。

25問目：集計関数④ 最大最小

S-025: レシート明細データ（receipt）に対し、顧客ID（customer_id）ごとに最も古い売上年月日（sales_ymd）を求め、10件表示せよ。

S-025答え

SELECT
  customer_id
  , MIN(sales_ymd) AS sales_ymd
FROM
  receipt r
GROUP BY
  customer_id
;

テーブルのカラムの最大値を算出する際には、MIN を使います。
構文

SELECT MIN((列名)) FROM (テーブル名)

23問目と同様、GROUP BYと併用可能です。
$一番新しい年月日=最大の年月日$ です。

26問目：集計後の条件比較

S-026: レシート明細データ（receipt）に対し、顧客ID（customer_id）ごとに最も新しい売上年月日（sales_ymd）と古い売上年月日を求め、両者が異なるデータを10件表示せよ。

S-026答え

SELECT
  customer_id
  , MIN(sales_ymd) AS sales_ymd_oldest
  , MAX(sales_ymd) AS sales_ymd_newest
FROM
  receipt r
GROUP BY
  customer_id
HAVING
  MIN(sales_ymd) <> MAX(sales_ymd)
LIMIT 10
;

集計関数を基にした処理は、WHERE句では参照できません。
GROU BY で集計した値を用いたり、集計した状態での条件を絞り込みたい場合は HAVINGを利用します。
また、SELECT句でつけた別名は利用できません。

27問目：平均値、GROUP BYとORDER BY

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

S-027答え

SELECT
  customer_id
  , AVG(amount) AS amount_average
FROM
  receipt r
GROUP BY
  store_cd
ORDER BY amount_average DESC
LIMIT 5
;

平均値
平均値は AVG 関数を使います。
ソート処理は SELECT句の後に実行されるため、SELECT句で指定した別名を利用可能です。

参考文献・クレジット

本記事では、以下のライセンスのもと提供されている資料を参考にしています。

MIT License

© 2020 The Japan DataScientist Society
MIT Licenseの詳細

Creative Commons Attribution-NoDerivatives 4.0 International（CC BY-ND 4.0）

ライセンスの詳細

※ 本記事では、元の内容を改変せずに引用・解説を行っています。

その他参考文献