総合 月次利用レポート
師範 / 目安 45分
年度の報告書に載せる月次の利用レポートを作ります。理事会に出す数字なので、月が飛んでも読めるように、貸出のあった月だけを並べます。
必要なのは月ごとの貸出件数と、その月に実際に借りた人の数です。同じ人が月に3回借りても、利用者数としては1人と数えます。ここを間違えると数字が水増しされます。
完成条件
月ごとに、貸出件数と利用者数を集計してください。
- 出す列は
month、loan_count、member_countの3つ monthは2026-01のような文字列member_countはその月に借りた人を重複して数えない- 貸出のあった月だけを出す。返却済みかどうかは問わない
- 並びは
monthの昇順 - 途中の集計は
WITHで名前を付けて組み立てる
日付を年月にする
to_char(日付, 書式) は日付を指定した書式の文字列に変えます。YYYY は4桁の年、MM は2桁の月で、間のハイフンもそのまま出ます。
SQL クエリ
SELECT to_char(DATE '2025-09-18', 'YYYY-MM') AS event_month;結果は文字列の 2025-09 です。9月2日も9月18日も同じ文字列になるので、月単位でまとめられます。
件数と重複を除いた人数
来場記録 attendance に、既に年月の列 event_month がある例で違いを確認します。
| event_month | visitor_id |
|---|---|
| 2025-09 | 7 |
| 2025-09 | 7 |
| 2025-09 | 8 |
SQL クエリ
SELECT event_month,
count(*) AS visit_count,
count(DISTINCT visitor_id) AS visitor_count
FROM attendance
GROUP BY event_month
ORDER BY event_month;count(*) は3件の来場を数えます。count(DISTINCT visitor_id) は同じ番号7を1人として扱うので、来場者は2人です。GROUP BY で月ごとに分けてから数えるため、別の月に同じ人が来ても、その月では改めて1人と数えます。集計結果の列名は AS で付けます。
演習の WITH は第7回と同じく中間結果に名前を付ける仕組みです。ひな形の monthly に年月と会員番号を持たせ、その中間結果を月ごとに集計します。
進め方
WITH の枠、データを読む FROM、出す列の並び、最後の並べ替えはひな形にあります。年月を作る1行、件数と人数を求める2行、月でまとめる1行を足してください。手が止まったらヒントを開けます。ヒントは3段階で、①方針、②使う構文、③部分解の順に出ます。1つも開けずに通せたら、その問題は自分のものです。
開かずに10分粘って進まないなら、①だけ開けてください。粘る時間そのものには価値がありません。価値があるのは、自分で書いた行が動いた瞬間です。
日付をそのまま出すと表記が環境で変わります。文字列に直してから出してください。
テーブル構造
CREATE TABLE members (
id integer PRIMARY KEY,
name text NOT NULL,
joined_on date NOT NULL,
plan text NOT NULL
);
CREATE TABLE books (
id integer PRIMARY KEY,
title text NOT NULL,
author text NOT NULL,
genre text NOT NULL,
published_year integer NOT NULL,
price integer NOT NULL
);
CREATE TABLE loans (
id integer PRIMARY KEY,
book_id integer NOT NULL REFERENCES books (id),
member_id integer NOT NULL REFERENCES members (id),
borrowed_on date NOT NULL,
returned_on date
);
INSERT INTO members VALUES
(1, '田中あかり', DATE '2024-04-01', '一般'),
(2, '佐藤けんじ', DATE '2024-06-15', '一般'),
(3, '鈴木みゆき', DATE '2025-01-20', '学生'),
(4, '高橋なおと', DATE '2025-03-05', '学生'),
(5, '伊藤さくら', DATE '2025-08-11', '一般'),
(6, '渡辺ゆうた', DATE '2026-02-01', '一般');
INSERT INTO books VALUES
(1, '銀河の設計図', '星野りく', 'SF', 2019, 1800),
(2, '沈黙の実験室', '星野りく', 'SF', 2021, 2200),
(3, '夜明けのレシピ', '森田はるか', '料理', 2020, 1500),
(4, '家庭料理の教科書', '森田はるか', '料理', 2018, 2800),
(5, 'やさしい統計入門', '大村しんじ', '技術', 2022, 3200),
(6, 'データベース実践', '大村しんじ', '技術', 2023, 3600),
(7, '山と湖の記録', '川瀬あおい', '生活', 2017, 1600),
(8, '北の島へ', '川瀬あおい', '生活', 2021, 1900),
(9, '猫と暮らす', '林ももこ', '生活', 2024, 1400),
(10, '小さな庭の作り方', '林ももこ', '生活', 2022, 1700),
(11, '消えた手紙', '星野りく', 'SF', 2016, 1300),
(12, '三日月の約束', '藤田けい', 'ミステリ', 2025, 2000),
(13, '軌道の彼方', '星野りく', 'SF', 2024, 2400),
(14, 'ネットワーク入門', '大村しんじ', '技術', 2020, 2900),
(15, '朝のパン作り', '林ももこ', '生活', 2023, 1600);
INSERT INTO loans VALUES
(1, 1, 1, DATE '2026-01-05', DATE '2026-01-19'),
(2, 1, 2, DATE '2026-01-12', DATE '2026-01-26'),
(3, 1, 3, DATE '2026-02-03', DATE '2026-02-17'),
(4, 1, 4, DATE '2026-03-09', DATE '2026-03-23'),
(5, 1, 5, DATE '2026-04-06', DATE '2026-04-20'),
(6, 1, 6, DATE '2026-05-11', NULL),
(7, 9, 1, DATE '2026-01-08', DATE '2026-01-22'),
(8, 9, 2, DATE '2026-02-10', DATE '2026-02-24'),
(9, 9, 3, DATE '2026-03-02', DATE '2026-03-16'),
(10, 9, 4, DATE '2026-04-13', DATE '2026-04-27'),
(11, 9, 5, DATE '2026-05-18', NULL),
(12, 5, 1, DATE '2026-02-16', DATE '2026-03-02'),
(13, 5, 3, DATE '2026-03-23', DATE '2026-04-06'),
(14, 5, 4, DATE '2026-04-20', DATE '2026-05-04'),
(15, 5, 6, DATE '2026-06-01', NULL),
(16, 3, 2, DATE '2026-01-19', DATE '2026-02-02'),
(17, 3, 5, DATE '2026-03-16', DATE '2026-03-30'),
(18, 3, 6, DATE '2026-05-25', NULL),
(19, 12, 1, DATE '2026-04-27', DATE '2026-05-11'),
(20, 12, 3, DATE '2026-06-08', NULL),
(21, 2, 2, DATE '2026-02-23', DATE '2026-03-09'),
(22, 7, 4, DATE '2026-05-04', DATE '2026-05-18'),
(23, 13, 5, DATE '2026-06-15', DATE '2026-06-29');
期待される出力
| month | loan_count | member_count |
|---|---|---|
| 2026-01 | 4 | 2 |
| 2026-02 | 4 | 3 |
| 2026-03 | 4 | 3 |
| 2026-04 | 4 | 3 |
| 2026-05 | 4 | 3 |
| 2026-06 | 3 | 3 |
ヒント
前のヒントを開くと次が開きます。開かずに解けると未開封クリアです