✍️ 필사 모드: データベース基礎完全ガイド — SQL、正規化、インデックス、トランザクション、NoSQL

-- 基本的なクエリ
SELECT name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 10;

-- エイリアスの活用
SELECT
    u.name AS user_name,
    COUNT(o.id) AS order_count,
    SUM(o.total_amount) AS total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

-- INNER JOIN: 両方に一致する行のみ
SELECT u.name, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN: 左テーブル全体 + 一致する右側
SELECT u.name, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN: 右テーブル全体 + 一致する左側
SELECT u.name, o.order_date
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- FULL OUTER JOIN: 両方全て含む
SELECT u.name, o.order_date
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- 平均給与が500万円以上の部署
SELECT
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) >= 5000000
ORDER BY avg_salary DESC;

-- 平均給与より高い給与の社員
SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

-- EXISTSを使ったサブクエリ
SELECT u.name
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
    AND o.created_at >= '2026-01-01'
);

-- INを使ったサブクエリ
SELECT name, department
FROM employees
WHERE department IN (
    SELECT department
    FROM employees
    GROUP BY department
    HAVING COUNT(*) > 10
);

-- 1NF違反: phone_numbersに複数値を格納
-- name: '田中太郎', phone_numbers: '090-1234-5678, 090-9876-5432'

-- 1NF準拠: 別テーブルに分離
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE phone_numbers (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    phone VARCHAR(20) NOT NULL
);

-- 2NF違反: student_nameはstudent_idにのみ従属
-- (student_id, course_id) -> grade  (OK)
-- student_id -> student_name  (部分従属!)

-- 2NF準拠
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);

CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(student_id),
    course_id INTEGER REFERENCES courses(course_id),
    grade CHAR(2),
    PRIMARY KEY (student_id, course_id)
);

-- 3NF違反: department_nameはdepartment_idを経由して推移的に従属
-- employee_id -> department_id -> department_name

-- 3NF準拠
CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INTEGER REFERENCES departments(department_id)
);

-- 非正規化の例: 注文テーブルにユーザー名を重複格納
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    user_name VARCHAR(100),  -- 非正規化: 読み取り時のJOINを排除
    total_amount DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 単一カラムインデックス
CREATE INDEX idx_users_email ON users(email);

-- インデックスの使用例
SELECT * FROM users WHERE email = 'test@example.com';  -- インデックス使用
SELECT * FROM users WHERE email LIKE 'test%';           -- インデックス使用
SELECT * FROM users WHERE email LIKE '%test';           -- インデックス未使用（前方ワイルドカード）

CREATE INDEX idx_users_email_hash ON users USING HASH (email);

-- JSONBカラムにGINインデックス
CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- 全文検索用GINインデックス
CREATE INDEX idx_articles_search ON articles USING GIN (
    to_tsvector('japanese', title || ' ' || content)
);

-- 空間データインデックス
CREATE INDEX idx_locations_coords ON locations USING GIST (coordinates);

-- 複合インデックス: 先頭カラムからマッチング
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- このインデックスが効果的なクエリ:
SELECT * FROM orders WHERE user_id = 1;                          -- 使用
SELECT * FROM orders WHERE user_id = 1 AND created_at > '2026-01-01'; -- 使用
SELECT * FROM orders WHERE created_at > '2026-01-01';            -- 未使用（先頭カラム欠落）

-- カバリングインデックス
CREATE INDEX idx_orders_covering ON orders(user_id, created_at, total_amount);

-- インデックスのみで処理可能（Index Only Scan）
SELECT user_id, created_at, total_amount
FROM orders
WHERE user_id = 1 AND created_at > '2026-01-01';

-- 口座振替トランザクションの例
BEGIN;

UPDATE accounts SET balance = balance - 100000
WHERE account_id = 'A001';

UPDATE accounts SET balance = balance + 100000
WHERE account_id = 'B001';

-- 残高確認
SELECT balance FROM accounts WHERE account_id = 'A001';

COMMIT;  -- または問題発生時は ROLLBACK;

-- 分離レベルの設定
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;
SELECT balance FROM accounts WHERE account_id = 'A001';
-- このトランザクション中、他のトランザクションの変更は見えない
COMMIT;

-- デッドロックシナリオ
-- トランザクション1: A -> B の順でロック
-- トランザクション2: B -> A の順でロック

-- 解決方法:
-- 1. ロック順序を統一する（常にIDが小さい方から）
-- 2. タイムアウトを設定する
-- 3. トランザクション範囲を最小化する

SET lock_timeout = '5s';

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2026-01-01'
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 10;

-- N+1問題の発生
-- 1回: SELECT * FROM users LIMIT 100;
-- 100回: SELECT * FROM orders WHERE user_id = ?;  (各ユーザーごと)

-- 解決: JOINで一度に取得
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id IN (SELECT id FROM users LIMIT 100);

-- またはサブクエリを活用
SELECT u.*, (
    SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id
) AS order_count
FROM users u
LIMIT 100;

-- 悪い例: インデックス無効化
SELECT * FROM users WHERE YEAR(created_at) = 2026;

-- 良い例: インデックス活用可能
SELECT * FROM users
WHERE created_at >= '2026-01-01'
  AND created_at < '2027-01-01';

-- JSONBカラムの作成と活用
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    attributes JSONB
);

-- データ挿入
INSERT INTO products (name, attributes)
VALUES ('ノートPC', '{"brand": "Samsung", "ram": 16, "storage": "512GB", "tags": ["electronics", "portable"]}');

-- JSONBクエリ
SELECT name, attributes->>'brand' AS brand
FROM products
WHERE attributes->>'ram' = '16';

-- JSONB包含演算子
SELECT * FROM products
WHERE attributes @> '{"brand": "Samsung"}';

-- JSONB配列検索
SELECT * FROM products
WHERE attributes->'tags' ? 'electronics';

-- 再帰CTE: 組織図の探索
WITH RECURSIVE org_tree AS (
    -- 起点: 最上位管理者
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 再帰: 配下の社員を探索
    SELECT e.id, e.name, e.manager_id, t.depth + 1
    FROM employees e
    INNER JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY depth, name;

-- 部署別給与ランキング
SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept,
    RANK() OVER (ORDER BY salary DESC) AS overall_rank,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
    salary - LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS diff_from_prev
FROM employees;

-- 累計合計
SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM daily_sales;

-- 範囲パーティショニング
CREATE TABLE events (
    id BIGSERIAL,
    event_type VARCHAR(50),
    payload JSONB,
    created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

-- 月別パーティション作成
CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE events_2026_03 PARTITION OF events
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- パーティション別インデックス自動作成
CREATE INDEX idx_events_type ON events(event_type);

// MongoDBクエリ例
db.users.insertOne({
  name: "田中太郎",
  email: "tanaka@example.com",
  address: {
    city: "東京",
    ward: "渋谷区"
  },
  tags: ["premium", "active"]
});

// 集約パイプライン
db.orders.aggregate([
  { $match: { status: "completed" } },
  { $group: {
      _id: "$user_id",
      totalSpent: { $sum: "$amount" },
      orderCount: { $sum: 1 }
  }},
  { $sort: { totalSpent: -1 } },
  { $limit: 10 }
]);

-- Cassandra CQL
CREATE TABLE sensor_data (
    sensor_id UUID,
    event_time TIMESTAMP,
    temperature DOUBLE,
    humidity DOUBLE,
    PRIMARY KEY (sensor_id, event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);

// Neo4j Cypherクエリ
// 友達の友達を探す（2段階関係）
MATCH (me:Person)-[:FRIEND]->(friend)-[:FRIEND]->(fof)
WHERE me.name = '田中太郎'
  AND NOT (me)-[:FRIEND]->(fof)
  AND fof <> me
RETURN DISTINCT fof.name AS recommendation
LIMIT 10;

# キャッシュ設定（TTL 3600秒）
SET user:1001:profile '{"name":"田中太郎","email":"tanaka@example.com"}' EX 3600

# キャッシュ取得
GET user:1001:profile

# キャッシュ無効化
DEL user:1001:profile

# セッション保存（30分期限）
HSET session:abc123 user_id 1001 role admin login_time "2026-04-12T10:00:00"
EXPIRE session:abc123 1800

# セッション取得
HGETALL session:abc123

# スコア追加
ZADD game:leaderboard 1500 "player:001"
ZADD game:leaderboard 2300 "player:002"
ZADD game:leaderboard 1800 "player:003"

# 上位10名取得（高スコア順）
ZREVRANGE game:leaderboard 0 9 WITHSCORES

# 特定プレイヤーの順位取得
ZREVRANK game:leaderboard "player:001"

# チャンネル購読
SUBSCRIBE notifications:user:1001

# メッセージ発行
PUBLISH notifications:user:1001 '{"type":"order","message":"注文が完了しました"}'

# ロック取得（NX: 存在しない場合のみ、EX: 有効期限）
SET lock:order:process "worker-1" NX EX 30

# ロック解放（Luaスクリプトでアトミックに処理）
# 自身が設定したロックのみ解放するため値を確認
EVAL "if redis.call('get', KEYS[1]) == ARGV[1] then return redis.call('del', KEYS[1]) else return 0 end" 1 lock:order:process "worker-1"

-- ユーザーとプロフィール: 1:1
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY REFERENCES users(id),
    bio TEXT,
    avatar_url VARCHAR(500)
);

-- ユーザーと投稿: 1:N
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 投稿とタグ: M:N（中間テーブルが必要）
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE post_tags (
    post_id INTEGER REFERENCES posts(id),
    tag_id INTEGER REFERENCES tags(id),
    PRIMARY KEY (post_id, tag_id)
);

-- V1__create_users_table.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- V2__add_status_to_users.sql
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';
CREATE INDEX idx_users_status ON users(status);

-- V3__create_orders_table.sql
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id),
    total_amount DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

# alembic/versions/001_create_users.py
from alembic import op
import sqlalchemy as sa

def upgrade():
    op.create_table(
        'users',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('email', sa.String(255), nullable=False, unique=True),
        sa.Column('name', sa.String(100), nullable=False),
        sa.Column('created_at', sa.DateTime(), server_default=sa.func.now()),
    )

def downgrade():
    op.drop_table('users')

-- NOT NULLなしで追加（既存行に影響なし）
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

ALTER TABLE users DROP COLUMN phone;

-- CONCURRENTLYオプション: テーブルロックなしでインデックス作成
CREATE INDEX CONCURRENTLY idx_orders_user ON orders(user_id);