集計関数
合計、最大、最小、平均
ts
// 集計関数 (SUM / AVG / MAX / MIN) の動作確認
//
// SUM(列) ⇒ 合計
// MAX(列) ⇒ 最大値
// MIN(列) ⇒ 最小値
// AVG(列) ⇒ 平均値
//
// 引数には列名だけでなく SUM(出金額 * 1.5) のように式も渡せる。
//
// 家計簿テーブルに対して、WHERE 出金額 > 0 で支出行だけを対象に集計する。
// テキストの結果表を再現する:
// 合計出金額 = 15740 / 平均出金額 = 3935 / 最も大きな散財 = 7560 / 最も少額の支払い = 380
// ⇒ 対象 4 件: 7560 + 4500 + 3300 + 380 = 15740, 平均 3935
type KakeiboRow = {
費目: string
入金額: number | null
出金額: number | null
}
async function showKakeibo(label: string) {
const rows = await sql<KakeiboRow[]>`
SELECT 費目, 入金額, 出金額
FROM 家計簿
ORDER BY 費目
`
console.log(`\n--- ${label} (${rows.length}件) ---`)
console.table(rows)
}
async function main() {
try {
await sql.unsafe(`DROP TABLE IF EXISTS 家計簿;`)
await sql.unsafe(`
CREATE TABLE 家計簿 (
費目 VARCHAR(20),
入金額 INTEGER,
出金額 INTEGER
);
`)
// 入金行 (出金額 0) と 支出行 を混在させ、WHERE 出金額 > 0 の効きを確認する
await sql`INSERT INTO 家計簿 (費目, 入金額, 出金額) VALUES ('給料', 280000, 0)`
await sql`INSERT INTO 家計簿 (費目, 入金額, 出金額) VALUES ('交際費', 0, 7560)`
await sql`INSERT INTO 家計簿 (費目, 入金額, 出金額) VALUES ('食費', 0, 4500)`
await sql`INSERT INTO 家計簿 (費目, 入金額, 出金額) VALUES ('日用品', 0, 3300)`
await sql`INSERT INTO 家計簿 (費目, 入金額, 出金額) VALUES ('交通費', 0, 380)`
await showKakeibo('家計簿データ')
// ============================================================
// 4 つの集計関数で支出の統計を取る
// WHERE 出金額 > 0 で「実際に支出があった行」だけを集計対象にする
// ============================================================
const summary = await sql<
{
合計出金額: number
平均出金額: number
最も大きな散財: number
最も少額の支払い: number
}[]
>`
SELECT SUM(出金額) AS 合計出金額,
AVG(出金額) AS 平均出金額,
MAX(出金額) AS 最も大きな散財,
MIN(出金額) AS 最も少額の支払い
FROM 家計簿
WHERE 出金額 > 0
`
console.log('\n--- SUM / AVG / MAX / MIN の集計結果 ---')
console.table(summary)
// 参考: 列名だけでなく式も渡せる (SUM(出金額 * 1.5))
const withExpr = await sql<{ 出金額: number; '出金額x1.5': number }[]>`
SELECT SUM(出金額) AS "出金額",
SUM(出金額 * 1.5) AS "出金額x1.5"
FROM 家計簿
WHERE 出金額 > 0
`
console.log('\n--- 参考: SUM(出金額 * 1.5) のように式も渡せる ---')
console.table(withExpr)
} catch (e) {
console.error('❌ エラーが発生しました:', e)
} finally {
await sql.end()
}
}
main()実行結果
sh
--- 家計簿データ (5件) ---
┌─────────┬──────────┬────────┬────────┐
│ (index) │ 費目 │ 入金額 │ 出金額 │
├─────────┼──────────┼────────┼────────┤
│ 0 │ '交通費' │ 0 │ 380 │
│ 1 │ '交際費' │ 0 │ 7560 │
│ 2 │ '日用品' │ 0 │ 3300 │
│ 3 │ '給料' │ 280000 │ 0 │
│ 4 │ '食費' │ 0 │ 4500 │
└─────────┴──────────┴────────┴────────┘
--- SUM / AVG / MAX / MIN の集計結果 ---
┌─────────┬────────────┬─────────────────────────┬────────────────┬──────────────────┐
│ (index) │ 合計出金額 │ 平均出金額 │ 最も大きな散財 │ 最も少額の支払い │
├─────────┼────────────┼─────────────────────────┼────────────────┼──────────────────┤
│ 0 │ '15740' │ '3935.0000000000000000' │ 7560 │ 380 │
└─────────┴────────────┴─────────────────────────┴────────────────┴──────────────────┘
--- 参考: SUM(出金額 * 1.5) のように式も渡せる ---
┌─────────┬─────────┬────────────┐
│ (index) │ 出金額 │ 出金額x1.5 │
├─────────┼─────────┼────────────┤
│ 0 │ '15740' │ '23610.0' │
└─────────┴─────────┴────────────┘行数を数える集計関数
ts
// 行数を数える集計関数 COUNT の動作確認
//
// COUNT(*) ⇒ 検索結果の行数 (NULL の行も含めて数える)
// COUNT(列) ⇒ 指定列が NULL でない行だけを数える
// COUNT(DISTINCT 列) ⇒ 指定列の重複を除いた値の種類数
//
// 家計簿テーブル (費目「食費」を 3 行入れて重複を作る)
// 日付 費目 出金額
// 2024-02-10 給料 NULL ← 出金額が NULL
// 2024-02-14 食費 5000
// 2024-02-18 食費 7560
// 2024-02-20 食費 1200
// 2024-02-22 日用品 3000
//
// 期待される結果:
// COUNT(*) = 5 (全行)
// COUNT(出金額) = 4 (NULL 行を除く)
// 食費の行数 = 3
// COUNT(費目) = 5 (費目が入っている行数)
// COUNT(DISTINCT 費目) = 3 (給料 / 食費 / 日用品 の 3 種類)
type KakeiboRow = {
日付: Date
費目: string
出金額: number | null
}
async function showKakeibo(label: string) {
const rows = await sql<KakeiboRow[]>`
SELECT 日付, 費目, 出金額
FROM 家計簿
ORDER BY 日付
`
console.log(`\n--- ${label} (${rows.length}件) ---`)
console.table(rows)
}
async function main() {
try {
await sql.unsafe(`DROP TABLE IF EXISTS 家計簿;`)
await sql.unsafe(`
CREATE TABLE 家計簿 (
日付 DATE,
費目 VARCHAR(20),
出金額 INTEGER
);
`)
await sql`INSERT INTO 家計簿 (日付, 費目, 出金額) VALUES ('2024-02-10', '給料', NULL)`
await sql`INSERT INTO 家計簿 (日付, 費目, 出金額) VALUES ('2024-02-14', '食費', 5000)`
await sql`INSERT INTO 家計簿 (日付, 費目, 出金額) VALUES ('2024-02-18', '食費', 7560)`
await sql`INSERT INTO 家計簿 (日付, 費目, 出金額) VALUES ('2024-02-20', '食費', 1200)`
await sql`INSERT INTO 家計簿 (日付, 費目, 出金額) VALUES ('2024-02-22', '日用品', 3000)`
await showKakeibo('家計簿データ')
// COUNT(*) で「食費」の行数を数える
const shokuhi = await sql<{ 食費の行数: number }[]>`
SELECT COUNT(*) AS 食費の行数
FROM 家計簿
WHERE 費目 = '食費'
`
console.log('\n--- 食費の行数 (COUNT(*) + WHERE) ---')
console.table(shokuhi)
// COUNT(*) と COUNT(列) の違い
// COUNT(*) → 単純に行数 (NULL 行も含む) = 5
// COUNT(出金額) → 出金額が NULL でない行だけ = 4
const counts = await sql<{ 'COUNT(*)': number; 'COUNT(出金額)': number }[]>`
SELECT COUNT(*) AS "COUNT(*)",
COUNT(出金額) AS "COUNT(出金額)"
FROM 家計簿
`
console.log('\n--- COUNT(*) と COUNT(列) の違い ---')
console.table(counts)
// DISTINCT のあり / なしを並べて比較する
// COUNT(費目) → 費目が入っている行数 = 5 (食費を 3 回数える)
// COUNT(DISTINCT 費目) → 重複を除いた種類数 = 3 (食費は 1 回だけ数える)
const distinct = await sql<{ 'COUNT(費目)': number; 'COUNT(DISTINCT 費目)': number }[]>`
SELECT COUNT(費目) AS "COUNT(費目)",
COUNT(DISTINCT 費目) AS "COUNT(DISTINCT 費目)"
FROM 家計簿
`
console.log('\n--- COUNT(費目) と COUNT(DISTINCT 費目) の違い ---')
console.table(distinct)
// 実際にどんな値が「種類」として残るのかも表示する
const kinds = await sql<{ 費目: string }[]>`
SELECT DISTINCT 費目
FROM 家計簿
ORDER BY 費目
`
console.log('\n--- DISTINCT 費目 (重複を除いた費目の一覧) ---')
console.table(kinds)
} catch (e) {
console.error('❌ エラーが発生しました:', e)
} finally {
await sql.end()
}
}
main()実行結果
sh
--- 家計簿データ (5件) ---
┌─────────┬──────────────────────────┬──────────┬────────┐
│ (index) │ 日付 │ 費目 │ 出金額 │
├─────────┼──────────────────────────┼──────────┼────────┤
│ 0 │ 2024-02-10T00:00:00.000Z │ '給料' │ null │
│ 1 │ 2024-02-14T00:00:00.000Z │ '食費' │ 5000 │
│ 2 │ 2024-02-18T00:00:00.000Z │ '食費' │ 7560 │
│ 3 │ 2024-02-20T00:00:00.000Z │ '食費' │ 1200 │
│ 4 │ 2024-02-22T00:00:00.000Z │ '日用品' │ 3000 │
└─────────┴──────────────────────────┴──────────┴────────┘
--- 食費の行数 (COUNT(*) + WHERE) ---
┌─────────┬────────────┐
│ (index) │ 食費の行数 │
├─────────┼────────────┤
│ 0 │ '3' │
└─────────┴────────────┘
--- COUNT(*) と COUNT(列) の違い ---
┌─────────┬──────────┬───────────────┐
│ (index) │ COUNT(*) │ COUNT(出金額) │
├─────────┼──────────┼───────────────┤
│ 0 │ '5' │ '4' │
└─────────┴──────────┴───────────────┘
--- COUNT(費目) と COUNT(DISTINCT 費目) の違い ---
┌─────────┬─────────────┬──────────────────────┐
│ (index) │ COUNT(費目) │ COUNT(DISTINCT 費目) │
├─────────┼─────────────┼──────────────────────┤
│ 0 │ '5' │ '3' │
└─────────┴─────────────┴──────────────────────┘
--- DISTINCT 費目 (重複を除いた費目の一覧) ---
┌─────────┬──────────┐
│ (index) │ 費目 │
├─────────┼──────────┤
│ 0 │ '日用品' │
│ 1 │ '給料' │
│ 2 │ '食費' │
└─────────┴──────────┘集計に関する注意点
ts
// 集計に関する注意点を実際に試すスクリプト
//
// [1] 集計関数は SELECT の選択列リスト / ORDER BY / HAVING でしか使えない。
// WHERE 句や UPDATE / INSERT / DELETE 文では使えない。
// [2] 結果表はデコボコ (列ごとに行数が違う形) になってはいけない。
// SELECT 日付, SUM(出金額) のような書き方はエラーになる。
// [3] 集計関数が受け取れる引数の型は関数ごとに異なる。
// SUM / AVG ... 数値型のみ
// MAX / MIN ... 数値・文字列・日付など (順序を持つ型)
// COUNT ... どんな型でも可
// [4] NULL の取り扱いは関数ごとに異なる。
// SUM / MAX / MIN / AVG / COUNT(列) ... NULL は無視 (集計に影響しない)
// COUNT(*) ... NULL 行も数える
// 全行が NULL なら SUM/MAX/MIN/AVG は NULL、COUNT(列) は 0
// NULL を 0 として扱いたいときは COALESCE(列, 0) を使う
type KakeiboRow = {
日付: Date
費目: string
出金額: number | null
}
// クエリを実行し、成功なら結果を、失敗ならエラーメッセージを表示するヘルパー
async function runTest(label: string, run: () => Promise<readonly unknown[]>) {
try {
const rows = await run()
console.log(`\n✅ ${label} → 成功`)
console.table(rows)
} catch (e) {
const msg = e instanceof Error ? e.message : String(e)
console.log(`\n❌ ${label} → エラー: ${msg}`)
}
}
async function main() {
try {
await sql.unsafe(`DROP TABLE IF EXISTS 家計簿;`)
await sql.unsafe(`
CREATE TABLE 家計簿 (
日付 DATE,
費目 VARCHAR(20),
出金額 INTEGER
);
`)
await sql`INSERT INTO 家計簿 (日付, 費目, 出金額) VALUES ('2024-02-03', '食費', 2800)`
await sql`INSERT INTO 家計簿 (日付, 費目, 出金額) VALUES ('2024-02-10', '日用品', NULL)`
await sql`INSERT INTO 家計簿 (日付, 費目, 出金額) VALUES ('2024-02-11', '交際費', 7560)`
await sql`INSERT INTO 家計簿 (日付, 費目, 出金額) VALUES ('2024-02-14', '食費', 5000)`
await sql`INSERT INTO 家計簿 (日付, 費目, 出金額) VALUES ('2024-02-18', '交通費', 380)`
const rows = await sql<KakeiboRow[]>`SELECT 日付, 費目, 出金額 FROM 家計簿 ORDER BY 日付`
console.log(`\n=== 家計簿データ (${rows.length}件) ===`)
console.table(rows)
// ============================================================
// [1] 集計関数を書ける場所 / 書けない場所
// ============================================================
console.log('\n========== [1] 集計関数を書ける場所 ==========')
// OK: SELECT の選択列リスト
await runTest(
'SELECT の選択列リストで SUM',
() => sql`
SELECT SUM(出金額) AS 出金額合計 FROM 家計簿
`,
)
// OK: ORDER BY 句 (集計結果で並べ替え)
await runTest(
'ORDER BY 句で COUNT',
() => sql`
SELECT COUNT(*) AS 件数 FROM 家計簿 ORDER BY COUNT(*)
`,
)
// NG: WHERE 句では使えない
await runTest(
'WHERE 句で AVG (使えない)',
() => sql`
SELECT 費目, 出金額 FROM 家計簿 WHERE 出金額 >= AVG(出金額)
`,
)
// NG: UPDATE 文では使えない
await runTest(
'UPDATE 文で MAX (使えない)',
() => sql`
UPDATE 家計簿 SET 出金額 = MAX(出金額)
`,
)
// ============================================================
// [2] 結果表はデコボコになってはいけない
// 日付 (複数行) と SUM(出金額) (1行) を混ぜるとエラー
// ============================================================
console.log('\n========== [2] デコボコな結果表は不可 ==========')
await runTest(
'SELECT 日付, SUM(出金額) (デコボコ)',
() => sql`
SELECT 日付, SUM(出金額) AS 出金額計 FROM 家計簿
`,
)
// ============================================================
// [3] 引数に許される型が関数ごとに異なる
// ============================================================
console.log('\n========== [3] 引数に許される型 ==========')
// OK: SUM / AVG は数値型
await runTest(
'SUM(出金額) / AVG(出金額) … 数値型',
() => sql`
SELECT SUM(出金額) AS 合計, AVG(出金額) AS 平均 FROM 家計簿
`,
)
// NG: SUM に文字列型はNG
await runTest(
'SUM(費目) … 文字列に SUM は不可',
() => sql`
SELECT SUM(費目) FROM 家計簿
`,
)
// OK: MAX / MIN は文字列型もOK (照合順で最初/最後)
await runTest(
'MAX(費目) / MIN(費目) … 文字列の最大/最小',
() => sql`
SELECT MAX(費目) AS 最大, MIN(費目) AS 最小 FROM 家計簿
`,
)
// OK: MAX / MIN は日付型もOK (最も新しい/古い日付)
await runTest(
'MAX(日付) / MIN(日付) … 最新/最古の日付',
() => sql`
SELECT MAX(日付) AS 最新, MIN(日付) AS 最古 FROM 家計簿
`,
)
// OK: COUNT はどんな型でも可
await runTest(
'COUNT(費目) / COUNT(日付) … 何でも可',
() => sql`
SELECT COUNT(費目) AS 費目件数, COUNT(日付) AS 日付件数 FROM 家計簿
`,
)
// ============================================================
// [4] NULL の取り扱い
// 出金額には NULL が 1 件 (日用品) 含まれている。
// ============================================================
console.log('\n========== [4] NULL の取り扱い ==========')
// SUM/AVG/COUNT は NULL を無視する。
// 出金額の非NULLは 4 件 (2800/7560/5000/380, 合計15740)。
// AVG は 15740 / 4 = 3935 (NULL の 1 件は分母に入らない)。
// COUNT(*) は NULL 行も数えて 5、COUNT(出金額) は 4。
await runTest(
'NULL は無視される (SUM/AVG/COUNT の違い)',
() => sql`
SELECT SUM(出金額) AS 合計,
AVG(出金額) AS 平均,
COUNT(*) AS "COUNT(*)",
COUNT(出金額) AS "COUNT(出金額)"
FROM 家計簿
`,
)
// リスト6-5: NULL を 0 と読み替えて平均を求める。
// COALESCE(出金額, 0) で NULL→0 にすると分母が 5 になり、
// 平均は 15740 / 5 = 3148 になる (3935 との違いに注目)。
await runTest(
'AVG(出金額) と AVG(COALESCE(出金額,0)) の違い',
() => sql`
SELECT AVG(出金額) AS "NULL無視の平均",
AVG(COALESCE(出金額, 0)) AS "NULLを0とした平均"
FROM 家計簿
`,
)
// 全行が NULL の場合 (NULL 行だけを WHERE で抜き出す)。
// SUM/MAX/MIN/AVG → NULL、COUNT(出金額) → 0、COUNT(*) → 1
await runTest(
'全行が NULL のとき (日用品の行だけ)',
() => sql`
SELECT SUM(出金額) AS 合計,
MAX(出金額) AS 最大,
MIN(出金額) AS 最小,
AVG(出金額) AS 平均,
COUNT(出金額) AS "COUNT(出金額)",
COUNT(*) AS "COUNT(*)"
FROM 家計簿
WHERE 費目 = '日用品'
`,
)
} catch (e) {
console.error('❌ 想定外のエラーが発生しました:', e)
} finally {
await sql.end()
}
}
main()実行結果
sh
=== 家計簿データ (5件) ===
┌─────────┬──────────────────────────┬──────────┬────────┐
│ (index) │ 日付 │ 費目 │ 出金額 │
├─────────┼──────────────────────────┼──────────┼────────┤
│ 0 │ 2024-02-03T00:00:00.000Z │ '食費' │ 2800 │
│ 1 │ 2024-02-10T00:00:00.000Z │ '日用品' │ null │
│ 2 │ 2024-02-11T00:00:00.000Z │ '交際費' │ 7560 │
│ 3 │ 2024-02-14T00:00:00.000Z │ '食費' │ 5000 │
│ 4 │ 2024-02-18T00:00:00.000Z │ '交通費' │ 380 │
└─────────┴──────────────────────────┴──────────┴────────┘
========== [1] 集計関数を書ける場所 ==========
✅ SELECT の選択列リストで SUM → 成功
┌─────────┬────────────┐
│ (index) │ 出金額合計 │
├─────────┼────────────┤
│ 0 │ '15740' │
└─────────┴────────────┘
✅ ORDER BY 句で COUNT → 成功
┌─────────┬──────┐
│ (index) │ 件数 │
├─────────┼──────┤
│ 0 │ '5' │
└─────────┴──────┘
❌ WHERE 句で AVG (使えない) → エラー: aggregate functions are not allowed in WHERE
❌ UPDATE 文で MAX (使えない) → エラー: aggregate functions are not allowed in UPDATE
========== [2] デコボコな結果表は不可 ==========
❌ SELECT 日付, SUM(出金額) (デコボコ) → エラー: column "家計簿.日付" must appear in the GROUP BY clause or be used in an aggregate function
========== [3] 引数に許される型 ==========
✅ SUM(出金額) / AVG(出金額) … 数値型 → 成功
┌─────────┬─────────┬─────────────────────────┐
│ (index) │ 合計 │ 平均 │
├─────────┼─────────┼─────────────────────────┤
│ 0 │ '15740' │ '3935.0000000000000000' │
└─────────┴─────────┴─────────────────────────┘
❌ SUM(費目) … 文字列に SUM は不可 → エラー: function sum(character varying) does not exist
✅ MAX(費目) / MIN(費目) … 文字列の最大/最小 → 成功
┌─────────┬────────┬──────────┐
│ (index) │ 最大 │ 最小 │
├─────────┼────────┼──────────┤
│ 0 │ '食費' │ '交通費' │
└─────────┴────────┴──────────┘
✅ MAX(日付) / MIN(日付) … 最新/最古の日付 → 成功
┌─────────┬──────────────────────────┬──────────────────────────┐
│ (index) │ 最新 │ 最古 │
├─────────┼──────────────────────────┼──────────────────────────┤
│ 0 │ 2024-02-18T00:00:00.000Z │ 2024-02-03T00:00:00.000Z │
└─────────┴──────────────────────────┴──────────────────────────┘
✅ COUNT(費目) / COUNT(日付) … 何でも可 → 成功
┌─────────┬──────────┬──────────┐
│ (index) │ 費目件数 │ 日付件数 │
├─────────┼──────────┼──────────┤
│ 0 │ '5' │ '5' │
└─────────┴──────────┴──────────┘
========== [4] NULL の取り扱い ==========
✅ NULL は無視される (SUM/AVG/COUNT の違い) → 成功
┌─────────┬─────────┬─────────────────────────┬──────────┬───────────────┐
│ (index) │ 合計 │ 平均 │ COUNT(*) │ COUNT(出金額) │
├─────────┼─────────┼─────────────────────────┼──────────┼───────────────┤
│ 0 │ '15740' │ '3935.0000000000000000' │ '5' │ '4' │
└─────────┴─────────┴─────────────────────────┴──────────┴───────────────┘
✅ AVG(出金額) と AVG(COALESCE(出金額,0)) の違い → 成功
┌─────────┬─────────────────────────┬─────────────────────────┐
│ (index) │ NULL無視の平均 │ NULLを0とした平均 │
├─────────┼─────────────────────────┼─────────────────────────┤
│ 0 │ '3935.0000000000000000' │ '3148.0000000000000000' │
└─────────┴─────────────────────────┴─────────────────────────┘
✅ 全行が NULL のとき (日用品の行だけ) → 成功
┌─────────┬──────┬──────┬──────┬──────┬───────────────┬──────────┐
│ (index) │ 合計 │ 最大 │ 最小 │ 平均 │ COUNT(出金額) │ COUNT(*) │
├─────────┼──────┼──────┼──────┼──────┼───────────────┼──────────┤
│ 0 │ null │ null │ null │ null │ '0' │ '1' │
└─────────┴──────┴──────┴──────┴──────┴───────────────┴──────────┘グループ別の集計
ts
// データをグループに分ける (GROUP BY) と、集計後の絞り込み (HAVING) の解説
//
// [1] グループ化しない集計 … テーブル全体で 1 つの結果になる
// [2] 力業 … 費目ごとに WHERE で絞って集計を繰り返す (カッコ悪い)
// [3] GROUP BY … 指定した列でグループ化し、グループごとに集計する
// [4] WHERE では集計結果で絞り込めない (集計前に処理されるため) → エラー
// [5] HAVING … 集計結果に対する絞り込み
// [6] SELECT 文の全貌 … WHERE / GROUP BY / HAVING / ORDER BY を一度に使う
//
// 家計簿テーブル (費目別に集計すると p.181 の集計表になるデータ)
// 交際費 = 3000 + 1200 + 800 = 5000 / 給料 = 0 / 合計 = 15740
type KakeiboRow = {
費目: string
出金額: number
}
async function runTest(label: string, run: () => Promise<readonly unknown[]>) {
try {
const rows = await run()
console.log(`\n✅ ${label} → 成功`)
console.table(rows)
} catch (e) {
const msg = e instanceof Error ? e.message : String(e)
console.log(`\n❌ ${label} → エラー: ${msg}`)
}
}
async function main() {
try {
await sql.unsafe(`DROP TABLE IF EXISTS 家計簿;`)
await sql.unsafe(`
CREATE TABLE 家計簿 (
費目 VARCHAR(20),
出金額 INTEGER
);
`)
await sql`INSERT INTO 家計簿 (費目, 出金額) VALUES ('食費', 380)`
await sql`INSERT INTO 家計簿 (費目, 出金額) VALUES ('給料', 0)`
await sql`INSERT INTO 家計簿 (費目, 出金額) VALUES ('教養娯楽費', 2800)`
await sql`INSERT INTO 家計簿 (費目, 出金額) VALUES ('交際費', 3000)`
await sql`INSERT INTO 家計簿 (費目, 出金額) VALUES ('交際費', 1200)`
await sql`INSERT INTO 家計簿 (費目, 出金額) VALUES ('交際費', 800)`
await sql`INSERT INTO 家計簿 (費目, 出金額) VALUES ('水道光熱費', 7560)`
const rows = await sql<KakeiboRow[]>`SELECT 費目, 出金額 FROM 家計簿`
console.log(`\n=== 家計簿データ (${rows.length}件) ===`)
console.table(rows)
// ============================================================
// [1] グループ化しない集計 … テーブル全体が 1 グループ扱い
// ============================================================
console.log('\n========== [1] グループ化しない集計 (全体で1行) ==========')
await runTest(
'SELECT SUM(出金額) … 全体の合計',
() => sql`
SELECT SUM(出金額) AS 出金額の合計 FROM 家計簿
`,
)
// ============================================================
// [2] 力業: 費目ごとに WHERE で絞って集計を繰り返す
// (費目の数だけ SELECT を書く必要があり現実的でない)
// ============================================================
console.log('\n========== [2] 力業: 費目ごとに SELECT を繰り返す ==========')
await runTest(
"WHERE 費目 = '食費' で食費だけ集計",
() => sql`
SELECT '食費' AS 費目, SUM(出金額) AS 費目別の出金額合計
FROM 家計簿 WHERE 費目 = '食費'
`,
)
await runTest(
"WHERE 費目 = '交際費' で交際費だけ集計",
() => sql`
SELECT '交際費' AS 費目, SUM(出金額) AS 費目別の出金額合計
FROM 家計簿 WHERE 費目 = '交際費'
`,
)
// ============================================================
// [3] GROUP BY: 1 つの SQL で費目ごとにまとめて集計する
// ============================================================
console.log('\n========== [3] GROUP BY 費目 でまとめて集計 ==========')
await runTest(
'GROUP BY 費目 … 費目別の出金額合計',
() => sql`
SELECT 費目, SUM(出金額) AS 費目別の出金額合計
FROM 家計簿
GROUP BY 費目
ORDER BY 費目
`,
)
// ============================================================
// [4] WHERE では集計結果で絞り込めない
// WHERE は「集計前 (検索時)」に処理されるため SUM がまだ確定しない
// ============================================================
console.log('\n========== [4] WHERE で集計結果は絞り込めない ==========')
await runTest(
'WHERE SUM(出金額) > 0 (エラーになる)',
() => sql`
SELECT 費目, SUM(出金額) AS 費目別の出金額合計
FROM 家計簿
WHERE SUM(出金額) > 0
GROUP BY 費目
`,
)
// ============================================================
// [5] HAVING: 集計後の結果に対して絞り込む
// 合計が 0 円の「給料」グループを除外できる
// ============================================================
console.log('\n========== [5] HAVING で集計結果を絞り込む ==========')
await runTest(
'HAVING SUM(出金額) > 0 … 合計0円の給料を除外',
() => sql`
SELECT 費目, SUM(出金額) AS 費目別の出金額合計
FROM 家計簿
GROUP BY 費目
HAVING SUM(出金額) > 0
ORDER BY 費目
`,
)
// ============================================================
// [6] SELECT 文の全貌
// WHERE (検索時の絞り込み) → GROUP BY (グループ化)
// → HAVING (集計後の絞り込み) → ORDER BY (並べ替え)
// ============================================================
console.log('\n========== [6] SELECT 文の全貌 (WHERE/GROUP BY/HAVING/ORDER BY) ==========')
await runTest(
'全部入り: 給料以外を費目別集計し、合計1000超を多い順に',
() => sql`
SELECT 費目, SUM(出金額) AS 費目別の出金額合計
FROM 家計簿
WHERE 費目 <> '給料'
GROUP BY 費目
HAVING SUM(出金額) > 1000
ORDER BY SUM(出金額) DESC
`,
)
} catch (e) {
console.error('❌ 想定外のエラーが発生しました:', e)
} finally {
await sql.end()
}
}
main()実行結果
sh
=== 家計簿データ (7件) ===
┌─────────┬──────────────┬────────┐
│ (index) │ 費目 │ 出金額 │
├─────────┼──────────────┼────────┤
│ 0 │ '食費' │ 380 │
│ 1 │ '給料' │ 0 │
│ 2 │ '教養娯楽費' │ 2800 │
│ 3 │ '交際費' │ 3000 │
│ 4 │ '交際費' │ 1200 │
│ 5 │ '交際費' │ 800 │
│ 6 │ '水道光熱費' │ 7560 │
└─────────┴──────────────┴────────┘
========== [1] グループ化しない集計 (全体で1行) ==========
✅ SELECT SUM(出金額) … 全体の合計 → 成功
┌─────────┬──────────────┐
│ (index) │ 出金額の合計 │
├─────────┼──────────────┤
│ 0 │ '15740' │
└─────────┴──────────────┘
========== [2] 力業: 費目ごとに SELECT を繰り返す ==========
✅ WHERE 費目 = '食費' で食費だけ集計 → 成功
┌─────────┬────────┬────────────────────┐
│ (index) │ 費目 │ 費目別の出金額合計 │
├─────────┼────────┼────────────────────┤
│ 0 │ '食費' │ '380' │
└─────────┴────────┴────────────────────┘
✅ WHERE 費目 = '交際費' で交際費だけ集計 → 成功
┌─────────┬──────────┬────────────────────┐
│ (index) │ 費目 │ 費目別の出金額合計 │
├─────────┼──────────┼────────────────────┤
│ 0 │ '交際費' │ '5000' │
└─────────┴──────────┴────────────────────┘
========== [3] GROUP BY 費目 でまとめて集計 ==========
✅ GROUP BY 費目 … 費目別の出金額合計 → 成功
┌─────────┬──────────────┬────────────────────┐
│ (index) │ 費目 │ 費目別の出金額合計 │
├─────────┼──────────────┼────────────────────┤
│ 0 │ '交際費' │ '5000' │
│ 1 │ '教養娯楽費' │ '2800' │
│ 2 │ '水道光熱費' │ '7560' │
│ 3 │ '給料' │ '0' │
│ 4 │ '食費' │ '380' │
└─────────┴──────────────┴────────────────────┘
========== [4] WHERE で集計結果は絞り込めない ==========
❌ WHERE SUM(出金額) > 0 (エラーになる) → エラー: aggregate functions are not allowed in WHERE
========== [5] HAVING で集計結果を絞り込む ==========
✅ HAVING SUM(出金額) > 0 … 合計0円の給料を除外 → 成功
┌─────────┬──────────────┬────────────────────┐
│ (index) │ 費目 │ 費目別の出金額合計 │
├─────────┼──────────────┼────────────────────┤
│ 0 │ '交際費' │ '5000' │
│ 1 │ '教養娯楽費' │ '2800' │
│ 2 │ '水道光熱費' │ '7560' │
│ 3 │ '食費' │ '380' │
└─────────┴──────────────┴────────────────────┘
========== [6] SELECT 文の全貌 (WHERE/GROUP BY/HAVING/ORDER BY) ==========
✅ 全部入り: 給料以外を費目別集計し、合計1000超を多い順に → 成功
┌─────────┬──────────────┬────────────────────┐
│ (index) │ 費目 │ 費目別の出金額合計 │
├─────────┼──────────────┼────────────────────┤
│ 0 │ '水道光熱費' │ '7560' │
│ 1 │ '交際費' │ '5000' │
│ 2 │ '教養娯楽費' │ '2800' │
└─────────┴──────────────┴────────────────────┘SELECT文の基本構文
sql
SELECT 選択列リスト
FROM テーブル名
[ WHERE 条件式 ]
[ GROUP BY グループ化列名 ]
[ HAVING 集計結果に対する条件式 ]
[ ORDER BY 並び替え列名 ]集計テーブルの活用
ts
// 集計テーブルの活用
//
// [1] 大量データの集計
// ベーステーブル (家計簿) に対して毎回 SUM/AVG/MAX/MIN/COUNT を
// 計算する。データが膨大だと毎回の計算は重い。
// [2] 集計テーブルの活用
// 集計結果を別テーブル (家計簿集計) に 1 回だけ計算して保存しておく。
// 必要なときは集計済みの値を SELECT するだけで取り出せる。
// [3] 集計テーブルの更新
// 元データが増えると集計テーブルの内容は古くなる (ズレる)。
// 定期的に再集計して内容を更新する必要がある。
type KakeiboRow = { 費目: string; 金額: number }
type ShukeiRow = {
費目: string
合計: number
平均: number
最大: number
最小: number
回数: number
}
async function showShukei(label: string) {
const rows = await sql<ShukeiRow[]>`
SELECT 費目, 合計, 平均, 最大, 最小, 回数
FROM 家計簿集計
ORDER BY 費目
`
console.log(`\n--- 家計簿集計テーブルの中身: ${label} ---`)
console.table(rows)
}
// 家計簿 (ベーステーブル) を費目ごとに集計して家計簿集計テーブルを作り直す
async function refreshShukei() {
await sql`DELETE FROM 家計簿集計`
await sql`
INSERT INTO 家計簿集計 (費目, 合計, 平均, 最大, 最小, 回数)
SELECT 費目,
SUM(金額) AS 合計,
AVG(金額) AS 平均,
MAX(金額) AS 最大,
MIN(金額) AS 最小,
COUNT(*) AS 回数
FROM 家計簿
GROUP BY 費目
`
}
async function main() {
try {
await sql.unsafe(`DROP TABLE IF EXISTS 家計簿;`)
await sql.unsafe(`DROP TABLE IF EXISTS 家計簿集計;`)
await sql.unsafe(`
CREATE TABLE 家計簿 (
費目 VARCHAR(20),
金額 INTEGER
);
`)
// 費目ごとに複数行を用意する (集計すると費目別の合計などが得られる)
// 居住費 : 80000 × 3 → 合計240000 平均80000 最大/最小80000 回数3
// 水道光熱費 : 7560, 4200 → 合計11760 平均5880 最大7560 最小4200 回数2
// 食費 : 5000, 5000, 380 → 合計10380 平均3460 最大5000 最小380 回数3
// 教養娯楽費 : 2800, 1800 → 合計4600 平均2300 最大2800 最小1800 回数2
// 給料 : 280000 × 3 → 合計840000 平均280000 最大/最小280000 回数3
await sql`INSERT INTO 家計簿 (費目, 金額) VALUES
('居住費', 80000), ('居住費', 80000), ('居住費', 80000),
('水道光熱費', 7560), ('水道光熱費', 4200),
('食費', 5000), ('食費', 5000), ('食費', 380),
('教養娯楽費', 2800), ('教養娯楽費', 1800),
('給料', 280000), ('給料', 280000), ('給料', 280000)
`
const base = await sql<KakeiboRow[]>`SELECT 費目, 金額 FROM 家計簿 ORDER BY 費目`
console.log(`\n=== 家計簿 (ベーステーブル) ${base.length}件 ===`)
console.table(base)
// ============================================================
// [1] 大量データの集計: 毎回ベーステーブルを集計する
// 行数が膨大だと、この計算を必要なたびに走らせるのは非効率。
// ============================================================
console.log('\n========== [1] ベーステーブルを毎回集計する ==========')
const aggregated = await sql<ShukeiRow[]>`
SELECT 費目,
SUM(金額) AS 合計,
AVG(金額) AS 平均,
MAX(金額) AS 最大,
MIN(金額) AS 最小,
COUNT(*) AS 回数
FROM 家計簿
GROUP BY 費目
ORDER BY 費目
`
console.table(aggregated)
// ============================================================
// [2] 集計テーブルの活用: 集計結果を 1 回だけ計算して保存する
// ============================================================
console.log('\n========== [2] 集計テーブルに結果を保存する ==========')
await sql.unsafe(`
CREATE TABLE 家計簿集計 (
費目 VARCHAR(20) PRIMARY KEY,
合計 INTEGER,
平均 INTEGER,
最大 INTEGER,
最小 INTEGER,
回数 INTEGER
);
`)
// 集計を 1 回だけ行い、その結果を集計テーブルに INSERT して保存
await refreshShukei()
await showShukei('作成直後')
// 以降は重い集計をせず、集計テーブルを SELECT するだけで取り出せる
const fast = await sql<ShukeiRow[]>`
SELECT 費目, 合計, 平均, 回数 FROM 家計簿集計 WHERE 費目 = '食費'
`
console.log('\n--- 集計テーブルから食費の集計を取り出す (SELECT だけで高速) ---')
console.table(fast)
// ============================================================
// [3] 集計テーブルの更新: 元データが増えると古くなる
// ============================================================
console.log('\n========== [3] 元データが増えて集計テーブルが古くなる ==========')
// 家計簿に新しい食費を追加する (例: 2000 円)
await sql`INSERT INTO 家計簿 (費目, 金額) VALUES ('食費', 2000)`
console.log('\n→ 家計簿に「食費 2000」を追加した')
// 集計テーブルは古いまま (合計10380 / 回数3 のまま)
const stale = await sql<ShukeiRow[]>`
SELECT 費目, 合計, 平均, 回数 FROM 家計簿集計 WHERE 費目 = '食費'
`
console.log('\n--- 集計テーブルの食費 (古い値のまま) ---')
console.table(stale)
// ベーステーブルを今あらためて集計すると正しい値 (合計12380 / 回数4)
const fresh = await sql<ShukeiRow[]>`
SELECT 費目, SUM(金額) AS 合計, AVG(金額) AS 平均, COUNT(*) AS 回数
FROM 家計簿 WHERE 費目 = '食費' GROUP BY 費目
`
console.log('\n--- ベーステーブルを今集計した食費 (本当の最新値) ---')
console.table(fresh)
// 定期的な更新: 再集計して集計テーブルを最新に更新する
await refreshShukei()
await showShukei('再集計して更新した後')
} catch (e) {
console.error('❌ エラーが発生しました:', e)
} finally {
await sql.end()
}
}
main()実行結果
sh
=== 家計簿 (ベーステーブル) 13件 ===
┌─────────┬──────────────┬────────┐
│ (index) │ 費目 │ 金額 │
├─────────┼──────────────┼────────┤
│ 0 │ '居住費' │ 80000 │
│ 1 │ '居住費' │ 80000 │
│ 2 │ '居住費' │ 80000 │
│ 3 │ '教養娯楽費' │ 1800 │
│ 4 │ '教養娯楽費' │ 2800 │
│ 5 │ '水道光熱費' │ 7560 │
│ 6 │ '水道光熱費' │ 4200 │
│ 7 │ '給料' │ 280000 │
│ 8 │ '給料' │ 280000 │
│ 9 │ '給料' │ 280000 │
│ 10 │ '食費' │ 5000 │
│ 11 │ '食費' │ 380 │
│ 12 │ '食費' │ 5000 │
└─────────┴──────────────┴────────┘
========== [1] ベーステーブルを毎回集計する ==========
┌─────────┬──────────────┬──────────┬─────────────────────────┬────────┬────────┬──────┐
│ (index) │ 費目 │ 合計 │ 平均 │ 最大 │ 最小 │ 回数 │
├─────────┼──────────────┼──────────┼─────────────────────────┼────────┼────────┼──────┤
│ 0 │ '居住費' │ '240000' │ '80000.000000000000' │ 80000 │ 80000 │ '3' │
│ 1 │ '教養娯楽費' │ '4600' │ '2300.0000000000000000' │ 2800 │ 1800 │ '2' │
│ 2 │ '水道光熱費' │ '11760' │ '5880.0000000000000000' │ 7560 │ 4200 │ '2' │
│ 3 │ '給料' │ '840000' │ '280000.000000000000' │ 280000 │ 280000 │ '3' │
│ 4 │ '食費' │ '10380' │ '3460.0000000000000000' │ 5000 │ 380 │ '3' │
└─────────┴──────────────┴──────────┴─────────────────────────┴────────┴────────┴──────┘
========== [2] 集計テーブルに結果を保存する ==========
--- 家計簿集計テーブルの中身: 作成直後 ---
┌─────────┬──────────────┬────────┬────────┬────────┬────────┬──────┐
│ (index) │ 費目 │ 合計 │ 平均 │ 最大 │ 最小 │ 回数 │
├─────────┼──────────────┼────────┼────────┼────────┼────────┼──────┤
│ 0 │ '居住費' │ 240000 │ 80000 │ 80000 │ 80000 │ 3 │
│ 1 │ '教養娯楽費' │ 4600 │ 2300 │ 2800 │ 1800 │ 2 │
│ 2 │ '水道光熱費' │ 11760 │ 5880 │ 7560 │ 4200 │ 2 │
│ 3 │ '給料' │ 840000 │ 280000 │ 280000 │ 280000 │ 3 │
│ 4 │ '食費' │ 10380 │ 3460 │ 5000 │ 380 │ 3 │
└─────────┴──────────────┴────────┴────────┴────────┴────────┴──────┘
--- 集計テーブルから食費の集計を取り出す (SELECT だけで高速) ---
┌─────────┬────────┬───────┬──────┬──────┐
│ (index) │ 費目 │ 合計 │ 平均 │ 回数 │
├─────────┼────────┼───────┼──────┼──────┤
│ 0 │ '食費' │ 10380 │ 3460 │ 3 │
└─────────┴────────┴───────┴──────┴──────┘
========== [3] 元データが増えて集計テーブルが古くなる ==========
→ 家計簿に「食費 2000」を追加した
--- 集計テーブルの食費 (古い値のまま) ---
┌─────────┬────────┬───────┬──────┬──────┐
│ (index) │ 費目 │ 合計 │ 平均 │ 回数 │
├─────────┼────────┼───────┼──────┼──────┤
│ 0 │ '食費' │ 10380 │ 3460 │ 3 │
└─────────┴────────┴───────┴──────┴──────┘
--- ベーステーブルを今集計した食費 (本当の最新値) ---
┌─────────┬────────┬─────────┬─────────────────────────┬──────┐
│ (index) │ 費目 │ 合計 │ 平均 │ 回数 │
├─────────┼────────┼─────────┼─────────────────────────┼──────┤
│ 0 │ '食費' │ '12380' │ '3095.0000000000000000' │ '4' │
└─────────┴────────┴─────────┴─────────────────────────┴──────┘
--- 家計簿集計テーブルの中身: 再集計して更新した後 ---
┌─────────┬──────────────┬────────┬────────┬────────┬────────┬──────┐
│ (index) │ 費目 │ 合計 │ 平均 │ 最大 │ 最小 │ 回数 │
├─────────┼──────────────┼────────┼────────┼────────┼────────┼──────┤
│ 0 │ '居住費' │ 240000 │ 80000 │ 80000 │ 80000 │ 3 │
│ 1 │ '教養娯楽費' │ 4600 │ 2300 │ 2800 │ 1800 │ 2 │
│ 2 │ '水道光熱費' │ 11760 │ 5880 │ 7560 │ 4200 │ 2 │
│ 3 │ '給料' │ 840000 │ 280000 │ 280000 │ 280000 │ 3 │
│ 4 │ '食費' │ 12380 │ 3095 │ 5000 │ 380 │ 4 │
└─────────┴──────────────┴────────┴────────┴────────┴────────┴──────┘集計とグループ化
集計
- 集計関数を用いてデータを集計することができる。
- 集計関数は、まとめたグループごとに1つの結果を算出する。
- 集計関数はSELECT文でのみ使用できる。
グループ化
- GROUP BY句にグループ分けの基準となる列を指定して、グループ別に集計を行うことができる。
- GROUP BY句を用いない集計では、検索結果の全件を1つのグループとして扱う。
- 集計値をもとにして特定のグループのみを抽出するには、HAVING句を用いる。
集計関数
主な集計関数
| 関数名 | 集計の内容 | 集計できるデータ型 |
|---|---|---|
| SUM | データを合計する | 数値 |
| MAX | 最も大きい値を求める | 数値、日付と時刻、文字列 |
| MIN | 最も小さい値を求める | 数値、日付と時刻、文字列 |
| AVG | データを平均する | 数値 |
| COUNT | 行数をカウントする | すべてのデータ型 |
問題1
ある年の日本各地の気象データを記録した、次のような都市別気象観測テーブルがあります。このテーブルについて、以下の設問で求められているデータを取得するSQL文を作成してください。その際、観測データのない都市や月の影響を受けないように集計してください。
都市別気象観測テーブル
| 列名 | データ型 | 備考 |
|---|---|---|
| 都市名 | VARCHAR(20) | 「熊谷」「奈良」「博多」など |
| 月 | INTEGER | 1〜12 のいずれかの数値 |
| 降水量 | INTEGER | 観測データがないものは NULL |
| 最高気温 | INTEGER | 観測データがないものは NULL |
| 最低気温 | INTEGER | 観測データがないものは NULL |
sh
=== 都市別気象観測テーブル (9件) ===
┌─────────┬────────┬────┬────────┬──────────┬──────────┐
│ (index) │ 都市名 │ 月 │ 降水量 │ 最高気温 │ 最低気温 │
├─────────┼────────┼────┼────────┼──────────┼──────────┤
│ 0 │ '奈良' │ 1 │ 40 │ 8 │ -11 │
│ 1 │ '奈良' │ 2 │ 20 │ null │ -12 │
│ 2 │ '奈良' │ 8 │ null │ 37 │ 26 │
│ 3 │ '東京' │ 1 │ 50 │ 10 │ 2 │
│ 4 │ '東京' │ 7 │ 150 │ 35 │ 25 │
│ 5 │ '東京' │ 8 │ 120 │ 38 │ 27 │
│ 6 │ '熊谷' │ 1 │ 30 │ 9 │ -2 │
│ 7 │ '熊谷' │ 8 │ 100 │ 39 │ 28 │
│ 8 │ '熊谷' │ 12 │ null │ 8 │ -3 │
└─────────┴────────┴────┴────────┴──────────┴──────────┘設問
- 日本全体としての年間降水量の合計と、年間の最高気温・最低気温の平均
ts
await sql`
SELECT SUM(降水量) AS 年間降水量合計,
AVG(最高気温) AS 最高気温の平均,
AVG(最低気温) AS 最低気温の平均
FROM 都市別気象観測
`実行結果
sh
┌─────────┬────────────────┬───────────────────────┬──────────────────────┐
│ (index) │ 年間降水量合計 │ 最高気温の平均 │ 最低気温の平均 │
├─────────┼────────────────┼───────────────────────┼──────────────────────┤
│ 0 │ '510' │ '23.0000000000000000' │ '8.8888888888888889' │
└─────────┴────────────────┴───────────────────────┴──────────────────────┘- 都市名「東京」の年間降水量と、各月の最高気温、最低気温の平均
ts
await sql`
SELECT SUM(降水量) AS 年間降水量,
AVG(最高気温) AS 最高気温の平均,
AVG(最低気温) AS 最低気温の平均
FROM 都市別気象観測
WHERE 都市名 = '東京'
`実行結果
sh
┌─────────┬────────────┬───────────────────────┬───────────────────────┐
│ (index) │ 年間降水量 │ 最高気温の平均 │ 最低気温の平均 │
├─────────┼────────────┼───────────────────────┼───────────────────────┤
│ 0 │ '320' │ '27.6666666666666667' │ '18.0000000000000000' │
└─────────┴────────────┴───────────────────────┴───────────────────────┘- 各都市の降水量の平均と、最も低かった最高気温、最も高かった最低気温
ts
await sql`
SELECT 都市名,
AVG(降水量) AS 降水量の平均,
MIN(最高気温) AS 最も低い最高気温,
MAX(最低気温) AS 最も高い最低気温
FROM 都市別気象観測
GROUP BY 都市名
`実行結果
sh
┌─────────┬────────┬────────────────────────┬──────────────────┬──────────────────┐
│ (index) │ 都市名 │ 降水量の平均 │ 最も低い最高気温 │ 最も高い最低気温 │
├─────────┼────────┼────────────────────────┼──────────────────┼──────────────────┤
│ 0 │ '東京' │ '106.6666666666666667' │ 10 │ 27 │
│ 1 │ '熊谷' │ '65.0000000000000000' │ 8 │ 28 │
│ 2 │ '奈良' │ '30.0000000000000000' │ 8 │ 26 │
└─────────┴────────┴────────────────────────┴──────────────────┴──────────────────┘- 月別の降水量、最高気温、最低気温の平均
ts
await sql`
SELECT 月,
AVG(降水量) AS 降水量の平均,
AVG(最高気温) AS 最高気温の平均,
AVG(最低気温) AS 最低気温の平均
FROM 都市別気象観測
GROUP BY 月
ORDER BY 月
`実行結果
sh
┌─────────┬────┬────────────────────────┬───────────────────────┬────────────────────────┐
│ (index) │ 月 │ 降水量の平均 │ 最高気温の平均 │ 最低気温の平均 │
├─────────┼────┼────────────────────────┼───────────────────────┼────────────────────────┤
│ 0 │ 1 │ '40.0000000000000000' │ '9.0000000000000000' │ '-3.6666666666666667' │
│ 1 │ 2 │ '20.0000000000000000' │ null │ '-12.0000000000000000' │
│ 2 │ 7 │ '150.0000000000000000' │ '35.0000000000000000' │ '25.0000000000000000' │
│ 3 │ 8 │ '110.0000000000000000' │ '38.0000000000000000' │ '27.0000000000000000' │
│ 4 │ 12 │ null │ '8.0000000000000000' │ '-3.0000000000000000' │
└─────────┴────┴────────────────────────┴───────────────────────┴────────────────────────┘- 1年間で最も高い最高気温が38度以上を記録した月のある都市名とその気温
ts
await sql`
SELECT 都市名,
MAX(最高気温) AS 最も高い最高気温
FROM 都市別気象観測
GROUP BY 都市名
HAVING MAX(最高気温) >= 38
`実行結果
sh
┌─────────┬────────┬──────────────────┐
│ (index) │ 都市名 │ 最も高い最高気温 │
├─────────┼────────┼──────────────────┤
│ 0 │ '東京' │ 38 │
│ 1 │ '熊谷' │ 39 │
└─────────┴────────┴──────────────────┘- 1年間で最も低い最低気温が-10度以下を記録した月のある都市名とその気温
ts
await sql`
SELECT 都市名,
MIN(最低気温) AS 最も低い最低気温
FROM 都市別気象観測
GROUP BY 都市名
HAVING MIN(最低気温) <= -10
`実行結果
sh
┌─────────┬────────┬──────────────────┐
│ (index) │ 都市名 │ 最も低い最低気温 │
├─────────┼────────┼──────────────────┤
│ 0 │ '奈良' │ -12 │
└─────────┴────────┴──────────────────┘設問2
サーバールームへの入退室を記録した、次のような入退室管理テーブルがあります。このテーブルについて、以下の設問で求められているデータを取得するSQL文を作成してください。なお、同姓同名の社員はいないものとします。
sh
=== 入退室管理テーブル (17件) ===
┌─────────┬──────────────────────────┬──────┬────────┬──────────┐
│ (index) │ 日付 │ 退室 │ 社員名 │ 事由区分 │
├─────────┼──────────────────────────┼──────┼────────┼──────────┤
│ 0 │ 2024-03-01T00:00:00.000Z │ '1' │ '佐藤' │ '1' │
│ 1 │ 2024-03-02T00:00:00.000Z │ '1' │ '佐藤' │ '2' │
│ 2 │ 2024-03-03T00:00:00.000Z │ '1' │ '佐藤' │ '3' │
│ 3 │ 2024-03-04T00:00:00.000Z │ '1' │ '佐藤' │ '2' │
│ 4 │ 2024-03-05T00:00:00.000Z │ '1' │ '佐藤' │ '1' │
│ 5 │ 2024-03-06T00:00:00.000Z │ '1' │ '佐藤' │ '3' │
│ 6 │ 2024-03-07T00:00:00.000Z │ '1' │ '佐藤' │ '1' │
│ 7 │ 2024-03-08T00:00:00.000Z │ '1' │ '佐藤' │ '2' │
│ 8 │ 2024-03-09T00:00:00.000Z │ '1' │ '佐藤' │ '3' │
│ 9 │ 2024-03-10T00:00:00.000Z │ '1' │ '佐藤' │ '2' │
│ 10 │ 2024-03-11T00:00:00.000Z │ null │ '佐藤' │ '1' │
│ 11 │ 2024-03-15T00:00:00.000Z │ '1' │ '田中' │ '3' │
│ 12 │ 2024-03-02T00:00:00.000Z │ '1' │ '鈴木' │ '2' │
│ 13 │ 2024-03-05T00:00:00.000Z │ '1' │ '鈴木' │ '3' │
│ 14 │ 2024-03-09T00:00:00.000Z │ null │ '鈴木' │ '1' │
│ 15 │ 2024-03-05T00:00:00.000Z │ '1' │ '高橋' │ '3' │
│ 16 │ 2024-03-12T00:00:00.000Z │ null │ '高橋' │ '9' │
└─────────┴──────────────────────────┴──────┴────────┴──────────┘入退室管理テーブル
| 列名 | データ型 | 備考 |
|---|---|---|
| 日付 | DATE | 入室した日付 |
| 退室 | CHAR(1) | NULL：入室中 / 1：退室済み |
| 社員名 | VARCHAR(20) | 入室した社員名 |
| 事由区分 | CHAR(1) | 入室事由を表すコード 1：メンテナンス 2：リリース作業 3：障害対応 9：その他 |
設問
- 現在入室中の社員数を取得する。
ts
await sql`
SELECT COUNT(*) AS 入室中の社員数
FROM 入退室管理
WHERE 退室 IS NULL
`実行結果
sh
┌─────────┬────────────────┐
│ (index) │ 入室中の社員数 │
├─────────┼────────────────┤
│ 0 │ '3' │
└─────────┴────────────────┘- 社員ごとの入室回数を、回数の多い順に取得する。
ts
await sql`
SELECT 社員名, COUNT(*) AS 入室回数
FROM 入退室管理
GROUP BY 社員名
ORDER BY 入室回数 DESC
`実行結果
sh
┌─────────┬────────┬──────────┐
│ (index) │ 社員名 │ 入室回数 │
├─────────┼────────┼──────────┤
│ 0 │ '佐藤' │ '11' │
│ 1 │ '鈴木' │ '3' │
│ 2 │ '高橋' │ '2' │
│ 3 │ '田中' │ '1' │
└─────────┴────────┴──────────┘- 事由区分ごとの入室回数を取得する（事由区分はわかりやすく表示する）。
ts
await sql`
SELECT CASE 事由区分
WHEN '1' THEN 'メンテナンス'
WHEN '2' THEN 'リリース作業'
WHEN '3' THEN '障害対応'
WHEN '9' THEN 'その他'
END AS 事由,
COUNT(*) AS 入室回数
FROM 入退室管理
GROUP BY 事由区分
ORDER BY 事由区分
`実行結果
sh
┌─────────┬────────────────┬──────────┐
│ (index) │ 事由 │ 入室回数 │
├─────────┼────────────────┼──────────┤
│ 0 │ 'リリース作業' │ '5' │
│ 1 │ 'その他' │ '1' │
│ 2 │ '障害対応' │ '6' │
│ 3 │ 'メンテナンス' │ '5' │
└─────────┴────────────────┴──────────┘- 入室回数が10回を超過する社員について、社員名と入室回数を取得する。
ts
await sql`
SELECT 社員名, COUNT(*) AS 入室回数
FROM 入退室管理
GROUP BY 社員名
HAVING COUNT(*) > 10
`実行結果
sh
┌─────────┬────────┬──────────┐
│ (index) │ 社員名 │ 入室回数 │
├─────────┼────────┼──────────┤
│ 0 │ '佐藤' │ '11' │
└─────────┴────────┴──────────┘- これまでに障害対応が発生した日付と、それに対応した社員数を取得する。
ts
await sql`
SELECT 日付, COUNT(*) AS 対応社員数
FROM 入退室管理
WHERE 事由区分 = '3'
GROUP BY 日付
`実行結果
sh
┌─────────┬──────────────────────────┬────────────┐
│ (index) │ 日付 │ 対応社員数 │
├─────────┼──────────────────────────┼────────────┤
│ 0 │ 2024-03-03T00:00:00.000Z │ '1' │
│ 1 │ 2024-03-05T00:00:00.000Z │ '2' │
│ 2 │ 2024-03-06T00:00:00.000Z │ '1' │
│ 3 │ 2024-03-09T00:00:00.000Z │ '1' │
│ 4 │ 2024-03-15T00:00:00.000Z │ '1' │
└─────────┴──────────────────────────┴────────────┘