SQLの平均より人気の本 - ゆめさくコード

中段 / 目安 30分

「人気」を上位5冊のような固定の数ではなく、平均との比較で定義し直します。館の規模が変わっても意味が変わらない指標にしたいからです。

比べる相手の平均は、貸出履歴のある本だけで計算します。一度も借りられていない本を分母に入れると、平均が下がって基準が甘くなるためです。

完成条件

貸出履歴のある本について、1冊あたりの平均貸出回数を求め、それを上回る本を取り出してください。

出す列は title と loan_count の2つ
並びは loan_count の降順。同数なら books.id の昇順

中間結果を1つの数と比べる

店舗別の売上回数から平均を求める例で流れを確認します。sales が次の5行なら、店舗1の売上は2回、店舗2は3回です。

shop_id	sale_id
1	101
1	102
2	103
2	104
2	105

WITH 名前 AS (...) は、かっこの中の結果に名前を付け、そのSQLの中でテーブルのように使う書き方です。

SQL クエリ

WITH per_shop AS (
  SELECT shop_id, count(*) AS sale_count
  FROM sales
  GROUP BY shop_id
)
SELECT avg(sale_count) AS average_count
FROM per_shop;

per_shop の中間結果は次の2行です。avg(sale_count) は、この2行の件数から平均 (2 + 3) / 2 = 2.5 を求めます。売上の1行ずつではなく、店舗ごとに数えた結果を平均する点が大切です。

shop_id	sale_count
1	2
2	3

同じ WITH に続く最後の SELECT を次にすると、平均より多い店舗2だけを取り出せます。

SQL クエリ

SELECT shop_id, sale_count
FROM per_shop
WHERE sale_count > (
  SELECT avg(sale_count) FROM per_shop
)
ORDER BY sale_count DESC, shop_id ASC;

かっこの中の SELECT は副問い合わせです。ここでは平均という1つの数を返すので、WHERE の比較に使えます。平均と等しい行は > では残りません。

ひな形の結合も確認しておきます。AS は短い別名を付け、JOIN ... ON は条件に合う行同士をつなぎます。

SQL クエリ

SELECT s.shop_name, p.sale_count
FROM per_shop AS p
JOIN shops AS s ON s.shop_id = p.shop_id;

この例は同じ per_shop の後ろに続けるもので、店舗の番号を手掛かりに店舗名を付けます。演習でも同じ方法で本の題名を付けてあります。

進め方

本ごとの貸出回数を作る per_book と、本の題名を結び付ける部分はひな形にあります。その後ろに、平均との比較を3行、並べ替えを1行足してください。手が止まったらヒントを開けます。ヒントは3段階で、①方針、②使う構文、③部分解の順に出ます。1つも開けずに通せたら、その問題は自分のものです。

開かずに10分粘って進まないなら、①だけ開けてください。粘る時間そのものには価値がありません。価値があるのは、自分で書いた行が動いた瞬間です。

平均そのものは1つの数です。まとめた結果を、その数と比べます。

title	loan_count
銀河の設計図	6
猫と暮らす	5
やさしい統計入門	4
夜明けのレシピ	3

中段 / 目安 30分

「人気」を上位5冊のような固定の数ではなく、平均との比較で定義し直します。館の規模が変わっても意味が変わらない指標にしたいからです。

完成条件

貸出履歴のある本について、1冊あたりの平均貸出回数を求め、それを上回る本を取り出してください。

出す列は title と loan_count の2つ
並びは loan_count の降順。同数なら books.id の昇順

中間結果を1つの数と比べる

店舗別の売上回数から平均を求める例で流れを確認します。sales が次の5行なら、店舗1の売上は2回、店舗2は3回です。

shop_id	sale_id
1	101
1	102
2	103
2	104
2	105

WITH 名前 AS (...) は、かっこの中の結果に名前を付け、そのSQLの中でテーブルのように使う書き方です。

SQL クエリ

WITH per_shop AS (
  SELECT shop_id, count(*) AS sale_count
  FROM sales
  GROUP BY shop_id
)
SELECT avg(sale_count) AS average_count
FROM per_shop;

shop_id	sale_count
1	2
2	3

同じ WITH に続く最後の SELECT を次にすると、平均より多い店舗2だけを取り出せます。

SQL クエリ

SELECT shop_id, sale_count
FROM per_shop
WHERE sale_count > (
  SELECT avg(sale_count) FROM per_shop
)
ORDER BY sale_count DESC, shop_id ASC;

ひな形の結合も確認しておきます。AS は短い別名を付け、JOIN ... ON は条件に合う行同士をつなぎます。

SQL クエリ

SELECT s.shop_name, p.sale_count
FROM per_shop AS p
JOIN shops AS s ON s.shop_id = p.shop_id;

この例は同じ per_shop の後ろに続けるもので、店舗の番号を手掛かりに店舗名を付けます。演習でも同じ方法で本の題名を付けてあります。

進め方

平均そのものは1つの数です。まとめた結果を、その数と比べます。

平均より人気の本

中段 / 目安 30分

完成条件

中間結果を1つの数と比べる

進め方

テーブル構造

期待される出力

ヒント

平均より人気の本

中段 / 目安 30分

完成条件

中間結果を1つの数と比べる

進め方

テーブル構造

期待される出力

ヒント