初級ハンズオン [B. 集計]（ECログ 問題集編）

データセットについて

本ハンズオンで使用するデータセットはECログを想定しています。ここではこのデータセットの中身について少し説明していきます。

データセット

データベース名「template_ec」，テーブル名「sales_slip」には1600万レコード以上の以下の様なレコードを含んでいるテーブルになります。

カラム

このテーブルには以下のカラムが存在しており，クエリ内ではこのカラム名を指定する事でそのカラム以下の値に対してオペレーションを実行することができます。

B. 集計

B-1. [集計] 登録されているレコード件数が何件あるか

問題

• template_ec データベースの「sales_slip」テーブルを指定します。
• このテーブルのレコード件数を，COUNT(1), COUNT(*), COUNT(member_id), COUNT('hoge'), COUNT(TRUE), COUNT(FALSE), COUNT(0), COUNT(NULL) で求め，結果に違いがあるのかを確認しましょう。
• 実行時間短縮のため，「WHERE TD_TIME_RANGE(time, '2006-05-01', '2006-06-01','JST')」で取得範囲を限定します。

クエリ

考察

• COUNT(0), COUNT(FALSE)，つまりカラムの値が 0 や FALSE であっても数え上げてしまう事に注意が必要です。
• 一方，COUNT(NULL)，つまりカラムの値が NULL であった場合には数え上げられません。

 

B-2. [集計] 特定の条件を満たすレコードが何件あるか集計する

問題1

• template_ec データベースの「sales_slip」テーブルを指定します。
• 以下の WHERE で指定した条件のレコードが何件あるか COUNT します。
• TD_TIME_RANGE を用いて，取得範囲を JST で'2006-05-01'〜'2006-06-01' に限定して下さい。

クエリ

B-3. [集計] グッズごとに何件あるか集計する

問題1

• template_ec データベースの「sales_slip」テーブルを指定します。
• goods_id を GROUP BY キーにして，グッズ毎のレコード件数を調べて下さい。
• レコード件数が多い順に並べ替え，さらに上位10件のみを取得して下さい。
• TD_TIME_RANGE を用いて，取得範囲を JST で'2006-05-01'〜'2006-06-01' に限定して下さい。

クエリ

B-4. [集計] グッズが100件以上あるページのみを数える

問題1

• template_ec データベースの「sales_slip」テーブルを指定します。
• goods_id を GROUP BY キーにして，グッズ毎のレコード件数を調べて下さい。
• レコード件数が多い順に並べ替え，さらに上位10件のみを取得して下さい。
• TD_TIME_RANGE を用いて，取得範囲を JST で'2006-05-01'〜'2006-06-01' に限定して下さい。
• さらに HAVING 句を使って，レコード件数が100件以上あるグッズだけに制約してく下さい。

クエリ

問題2

• template_ec データベースの「sales_slip」テーブルを指定します。
• goods_id を GROUP BY キーにして，グッズ毎のレコード件数を調べて下さい。
• レコード件数が多い順に並べ替え，さらに上位10件のみを取得して下さい。
• TD_TIME_RANGE を用いて，取得範囲を JST で'2006-05-01'〜'2006-06-01' に限定して下さい。
• さらに WHERE 句を使って，レコード件数が100件以上あるグッズだけに制約してく下さい。

クエリ

考察

• 問題1 と 問題2 は同じ結果をもたらすクエリですが，パフォーマンスには違いがあるのでしょうか？TD_TIME_RANGE，ORDER BY，LIMIT 句を除いたクエリで実行時間を比較してみましょう。

結果：「HAVING：7分12秒」 v.s. 「WHERE：4分32秒」

　　

B-5. [集計] 複数のキー（カテゴリ，サブカテゴリ）で何件あるか集計する

問題1

• template_ec データベースの「sales_slip」テーブルを指定します。
• category, sub_category を GROUP BY キーにして，レコード件数を調べて下さい。
• レコード件数が多い順に並べ替え，さらに上位10件のみを取得して下さい。
• TD_TIME_RANGE を用いて，取得範囲を JST で'2006-05-01'〜'2006-06-01' に限定して下さい。

クエリ

B-7. [集計] ユニークなユーザー数を数える

問題1

• template_ec データベースの「sales_slip」テーブルを指定します。
• ユニークな member_id を COUNT と APPROX_DISTINCT の2種類の方法で求めて下さい。
• TD_TIME_RANGE を用いて，取得範囲を JST で'2006-05-01'〜'2006-06-01' に限定して下さい。

クエリ

B-8. [集計] グッズごとのユニークなユーザー数を数える

問題1

• template_ec データベースの「sales_slip」テーブルを指定します。
• goods_is ごとのレコード数と，ユニークな member_id を COUNT と APPROX_DISTINCT の2種類の方法で求めて下さい。
• ユニーク数（COUNT の方）が多い順に並べ替え，さらに上位10件のみを取得して下さい。
• TD_TIME_RANGE を用いて，取得範囲を JST で'2006-05-01'〜'2006-06-01' に限定して下さい。

クエリ

B-9. [集計] デイリーでページごとのユニークなユーザー数を数える

問題1

• template_ec データベースの「sales_slip」テーブルを指定します。
• 日付，goods_id ごとのレコード数と，ユニークな member_id を COUNT と APPROX_DISTINCT の2種類の方法で求めて下さい。
• 日付の若い順に，次にユニーク数（COUNT の方）の多い順に並べ替え，さらに上位10件のみを取得して下さい。
• TD_TIME_RANGE を用いて，取得範囲を JST で'2006-05-01'〜'2006-06-01' に限定して下さい。

クエリ

問題2

• 問題1と同じ条件です。
• goods_id の若い順に，次に日付の若い順に並べ替え，さらに上位10件のみを取得して下さい。

クエリ

B-10. [集計] ユーザーの1日あたりの平均購買額を求める

問題1: SUM(amount)

• template_ec データベースの「sales_slip」テーブルを指定します。
• 日付ごと，goods_id ごとに売上点数（amount）を求めましょう。
• 日付ごと，売上点数が多いグッズごとに並び替えましょう。
• TD_TIME_RANGE を用いて，取得範囲を JST で'2006-01-01'〜'2007-01-01' に限定して下さい。

クエリ

問題2: SUM( price * amount )

• template_ec データベースの「sales_slip」テーブルを指定します。
• 日付ごと，member_id ごとに購買総額（price * amount）を求めましょう。
• まずは member_id ごとに並び替え，次に購買総額が大きい日付ごとに並び替えましょう。
• TD_TIME_RANGE を用いて，取得範囲を JST で'2006-01-01'〜'2007-01-01' に限定して下さい。

クエリ

問題3: AVG(sales)

• template_ec データベースの「sales_slip」テーブルを指定します。
• まずは日付ごと，member_id ごとに購買総額（price * amount）を求めましょう。
• 次に member_id ごとの平均購買単価と購入日数を求めしょう。
• 購入日数の多い順に，次に平均購買単価の大きいユーザーの順に並び替えましょう。
• TD_TIME_RANGE を用いて，取得範囲を JST で'2006-01-01'〜'2007-01-01' に限定して下さい。

クエリ

B-11. [集計] グッズごとに1日あたりの最大/最小売上点数を求める

問題1

• template_ec データベースの「sales_slip」テーブルを指定します。
• まずは日付ごと，goods_id ごとに売上点数（amount）を求めましょう。
• 次に各グッズについて，以下の期間の1日の利上げ点数の最大/最小数を求めましょう。
• TD_TIME_RANGE を用いて，取得範囲を JST で'2006-01-01'〜'2007-01-01' に限定して下さい。
• 最大売上点数が多い順に並び替え，上位10グッズを取得しましょう。

クエリ

問題2

• 問題1において，グッズ毎の売上点数が最大/最小となった日付を求めましょう。

クエリ

問題3

• 問題2において，goods_id='174901' の売上点数の最大/最小日が確かなのか確認しましょう。

クエリ

B-12. [集計] 1日あたりの売上の分布を求める

解説

• template_ec データベースの「sales_slip」テーブルを指定します。
• 1日あたりの総売上を求め，日毎の売上分布を求めましょう。
• TD_TIME_RANGE を用いて，取得範囲を JST で'2006-01-01'〜'2007-01-01' に限定して下さい。
• 「SUM(price * amount)/1000000」で 百万円単位の売上に加工し，それ以下の桁を切り落としします。

クエリ

B-13. [集計] ユーザー1日あたりの平均購買額の最大，最小，中央値などを求める

問題

• template_ec データベースの「sales_slip」テーブルを指定します。
• B-10 の 問題3 で求めたユーザーの1日あたりの平均購買額の，全ユーザーでの最大値，最小値，中央値，第一四分位数，第三四分位数を求めます。

クエリ