-- サンプルテーブル
CREATE TABLE sales (
    id          SERIAL PRIMARY KEY,
    product     VARCHAR(50),
    category    VARCHAR(30),
    amount      DECIMAL(10, 2),
    quantity    INTEGER,
    sale_date   DATE,
    region      VARCHAR(20),
    salesperson VARCHAR(50)
);
 
-- サンプルデータ挿入
INSERT INTO sales (product, category, amount, quantity, sale_date, region, salesperson) VALUES
    ('ノートPC', '家電', 89000, 2, '2024-01-15', '東京', '田中'),
    ('マウス', '周辺機器', 3500, 10, '2024-01-20', '東京', '佐藤'),
    ('モニター', '家電', 45000, 3, '2024-02-01', '大阪', '鈴木'),
    ('キーボード', '周辺機器', 8000, 5, '2024-02-15', '大阪', '田中'),
    ('プリンター', '家電', 32000, 1, '2024-03-01', '東京', '佐藤'),
    ('USBメモリ', '周辺機器', 1500, 20, '2024-03-10', '福岡', '高橋'),
    ('タブレット', '家電', 55000, 2, '2024-03-20', '東京', '田中'),
    ('ヘッドセット', '周辺機器', 12000, 4, '2024-04-01', '大阪', '鈴木'),
    ('デスクトップPC', '家電', 120000, 1, '2024-04-15', '福岡', '高橋'),
    ('Webカメラ', '周辺機器', 5000, 8, '2024-04-20', '東京', '佐藤');
 
-- 基本集約関数
SELECT
    COUNT(*)          AS total_rows,        -- 全行数: 10
    COUNT(amount)     AS non_null_count,    -- NULL以外の行数: 10
    COUNT(DISTINCT category) AS categories, -- ユニーク値数: 2
    SUM(amount)       AS total_sales,       -- 合計
    AVG(amount)       AS avg_sale,          -- 平均
    MIN(amount)       AS min_sale,          -- 最小
    MAX(amount)       AS max_sale,          -- 最大
    MIN(sale_date)    AS first_sale,        -- 最古日
    MAX(sale_date)    AS last_sale          -- 最新日
FROM sales;
 
-- 条件付き集約（CASE式を使用）
SELECT
    COUNT(*) AS total,
    SUM(CASE WHEN category = '家電' THEN amount ELSE 0 END) AS electronics_total,
    SUM(CASE WHEN category = '周辺機器' THEN amount ELSE 0 END) AS peripheral_total,
    AVG(CASE WHEN region = '東京' THEN amount END) AS tokyo_avg,
    -- NULLが返る行はAVGの分母に含まれない
    COUNT(CASE WHEN amount > 50000 THEN 1 END) AS high_value_count
FROM sales;

-- COUNT(*): NULLを含む全行を数える
SELECT COUNT(*) FROM employees;           -- → 100
 
-- COUNT(column): NULLを除いた行を数える
SELECT COUNT(phone) FROM employees;       -- → 85 (15人はNULL)
 
-- COUNT(DISTINCT column): ユニーク値を数える
SELECT COUNT(DISTINCT department_id) FROM employees;  -- → 8
 
-- ★ COUNT(DISTINCT) の複数列版（PostgreSQL）
SELECT COUNT(DISTINCT (department_id, status)) FROM employees;
-- → (department_id, status) の組み合わせのユニーク数
 
-- ★ 注意: 全行NULLの場合の挙動
CREATE TABLE empty_test (val INTEGER);
INSERT INTO empty_test VALUES (NULL), (NULL), (NULL);
 
SELECT
    COUNT(*)    AS star,   -- 3（行数は数える）
    COUNT(val)  AS col,    -- 0（NULLは除外）
    SUM(val)    AS total,  -- NULL（0ではない！）
    AVG(val)    AS average -- NULL
FROM empty_test;
 
-- SUM/AVGがNULLのとき0にしたい場合
SELECT COALESCE(SUM(val), 0) AS safe_sum FROM empty_test;

-- NULLの影響を実演
CREATE TABLE scores (
    student_id INTEGER,
    subject    VARCHAR(20),
    score      INTEGER  -- NULLは未受験を表す
);
 
INSERT INTO scores VALUES
    (1, '数学', 80), (1, '英語', 90), (1, '国語', NULL),
    (2, '数学', 70), (2, '英語', NULL), (2, '国語', 60),
    (3, '数学', NULL), (3, '英語', NULL), (3, '国語', NULL);
 
-- 生徒別の集計
SELECT
    student_id,
    COUNT(*) AS total_subjects,           -- 全科目数
    COUNT(score) AS taken_subjects,       -- 受験科目数
    SUM(score) AS total_score,            -- 合計点（NULLは無視）
    AVG(score) AS avg_score,              -- 平均点（受験科目のみ）
    AVG(COALESCE(score, 0)) AS avg_with_zero  -- 未受験を0点として計算
FROM scores
GROUP BY student_id
ORDER BY student_id;
 
-- 結果:
-- student_id | total | taken | sum | avg  | avg_with_zero
-- 1          | 3     | 2     | 170 | 85.0 | 56.67
-- 2          | 3     | 2     | 130 | 65.0 | 43.33
-- 3          | 3     | 0     | NULL| NULL | 0.00

-- 基本: カテゴリ別の売上集計
SELECT
    category,
    COUNT(*) AS sales_count,
    SUM(amount) AS total_amount,
    ROUND(AVG(amount), 2) AS avg_amount,
    MIN(amount) AS min_amount,
    MAX(amount) AS max_amount
FROM sales
GROUP BY category
ORDER BY total_amount DESC;
 
-- 複数列でグループ化
SELECT
    category,
    region,
    COUNT(*) AS sales_count,
    SUM(amount) AS total_amount
FROM sales
GROUP BY category, region
ORDER BY category, total_amount DESC;
 
-- 式でグループ化（月別集計）
SELECT
    DATE_TRUNC('month', sale_date) AS month,
    SUM(amount) AS monthly_total,
    COUNT(*) AS transaction_count,
    ROUND(AVG(amount), 2) AS avg_per_transaction
FROM sales
GROUP BY DATE_TRUNC('month', sale_date)
ORDER BY month;
 
-- 年月 + カテゴリのクロス集計
SELECT
    TO_CHAR(sale_date, 'YYYY-MM') AS year_month,
    category,
    SUM(amount) AS total,
    COUNT(*) AS cnt
FROM sales
GROUP BY TO_CHAR(sale_date, 'YYYY-MM'), category
ORDER BY year_month, category;
 
-- CASE式でグループ化（金額帯別）
SELECT
    CASE
        WHEN amount < 5000 THEN '低額（5千未満）'
        WHEN amount < 50000 THEN '中額（5千-5万）'
        ELSE '高額（5万以上）'
    END AS price_range,
    COUNT(*) AS count,
    SUM(amount) AS total,
    ROUND(AVG(amount), 2) AS avg
FROM sales
GROUP BY
    CASE
        WHEN amount < 5000 THEN '低額（5千未満）'
        WHEN amount < 50000 THEN '中額（5千-5万）'
        ELSE '高額（5万以上）'
    END
ORDER BY avg;

-- WHEREは行をフィルタ、HAVINGはグループをフィルタ
SELECT
    category,
    COUNT(*) AS sales_count,
    SUM(amount) AS total_amount
FROM sales
WHERE sale_date >= '2024-01-01'     -- ① 行レベルのフィルタ（集約前）
GROUP BY category
HAVING SUM(amount) >= 10000         -- ② グループレベルのフィルタ（集約後）
ORDER BY total_amount DESC;
 
-- 実用例: 注文回数が5回以上の顧客
SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS lifetime_value
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 5
ORDER BY lifetime_value DESC;
 
-- 実用例: 重複データの検出
SELECT
    email,
    COUNT(*) AS duplicate_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
 
-- 実用例: 集約結果の複数条件フィルタ
SELECT
    category,
    region,
    COUNT(*) AS cnt,
    SUM(amount) AS total,
    AVG(amount) AS avg
FROM sales
GROUP BY category, region
HAVING COUNT(*) >= 2
   AND SUM(amount) > 10000
   AND AVG(amount) > 3000
ORDER BY total DESC;
 
-- 実用例: HAVINGでサブクエリを使用
SELECT
    category,
    AVG(amount) AS avg_amount
FROM sales
GROUP BY category
HAVING AVG(amount) > (SELECT AVG(amount) FROM sales);
-- → 全体平均より高い平均金額のカテゴリのみ

-- GROUPING SETS: 複数の集約レベルを一度に取得
SELECT
    category,
    region,
    SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS (
    (category, region),  -- カテゴリ × 地域
    (category),          -- カテゴリ別小計
    (region),            -- 地域別小計
    ()                   -- 総計
)
ORDER BY category NULLS LAST, region NULLS LAST;
 
-- ROLLUP: 階層的な小計 + 総計
-- ROLLUP(A, B) = GROUPING SETS((A,B), (A), ())
SELECT
    COALESCE(category, '【総計】') AS category,
    COALESCE(region, '【小計】') AS region,
    SUM(amount) AS total,
    COUNT(*) AS cnt
FROM sales
GROUP BY ROLLUP (category, region);
 
-- CUBE: 全組み合わせの集約
-- CUBE(A, B) = GROUPING SETS((A,B), (A), (B), ())
SELECT
    category,
    region,
    SUM(amount) AS total
FROM sales
GROUP BY CUBE (category, region);
 
-- GROUPING関数: NULLが「集約によるもの」か「データのNULL」かを区別
SELECT
    CASE WHEN GROUPING(category) = 1 THEN '【全カテゴリ】'
         ELSE category END AS category,
    CASE WHEN GROUPING(region) = 1 THEN '【全地域】'
         ELSE region END AS region,
    SUM(amount) AS total,
    GROUPING(category) AS is_cat_total,
    GROUPING(region) AS is_reg_total,
    GROUPING(category, region) AS grouping_id
FROM sales
GROUP BY CUBE (category, region)
ORDER BY GROUPING(category, region), category, region;
 
-- 部分ROLLUP: 一部の列のみROLLUP
SELECT
    category,
    region,
    TO_CHAR(sale_date, 'YYYY-MM') AS month,
    SUM(amount) AS total
FROM sales
GROUP BY category, ROLLUP(region, TO_CHAR(sale_date, 'YYYY-MM'));
-- → categoryは常に存在し、region→monthの階層でROLLUP

-- FILTER句: 条件付き集約をCASE式より簡潔に書ける
SELECT
    category,
    COUNT(*) AS total_count,
    COUNT(*) FILTER (WHERE region = '東京') AS tokyo_count,
    COUNT(*) FILTER (WHERE region = '大阪') AS osaka_count,
    COUNT(*) FILTER (WHERE region = '福岡') AS fukuoka_count,
    SUM(amount) FILTER (WHERE sale_date >= '2024-01-01'
                        AND sale_date < '2024-04-01') AS q1_amount,
    SUM(amount) FILTER (WHERE sale_date >= '2024-04-01'
                        AND sale_date < '2024-07-01') AS q2_amount,
    AVG(amount) FILTER (WHERE quantity >= 5) AS avg_bulk_amount
FROM sales
GROUP BY category;
 
-- FILTER句とCASE式の比較
-- FILTER（PostgreSQL推奨）:
SELECT COUNT(*) FILTER (WHERE status = 'active') AS active_count FROM users;
 
-- CASE（全RDBMS対応）:
SELECT COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count FROM users;
-- または
SELECT SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count FROM users;

-- 分散と標準偏差
SELECT
    department_id,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary,
    STDDEV_SAMP(salary) AS salary_stddev,      -- 標本標準偏差（N-1）
    STDDEV_POP(salary) AS salary_stddev_pop,   -- 母標準偏差（N）
    VAR_SAMP(salary) AS salary_variance,       -- 標本分散
    VAR_POP(salary) AS salary_variance_pop     -- 母分散
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 3;  -- 統計値は3件以上で意味がある
 
-- 中央値（PERCENTILE_CONT）
SELECT
    department_id,
    AVG(salary) AS mean_salary,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) AS q1_salary,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) AS q3_salary,
    -- 四分位範囲（IQR）
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary)
    - PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) AS iqr,
    -- 最頻値（モード）はMODE()で取得（PostgreSQL）
    MODE() WITHIN GROUP (ORDER BY salary) AS mode_salary
FROM employees
GROUP BY department_id;
 
-- PERCENTILE_DISC: 離散値（実際に存在する値を返す）
SELECT
    department_id,
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) AS median_exact
FROM employees
GROUP BY department_id;
-- PERCENTILE_CONTは補間値を返すが、PERCENTILE_DISCは実在する値を返す
 
-- 相関係数と回帰分析
SELECT
    CORR(age, salary) AS correlation,           -- 相関係数
    REGR_SLOPE(salary, age) AS slope,           -- 回帰直線の傾き
    REGR_INTERCEPT(salary, age) AS intercept,   -- 回帰直線の切片
    REGR_R2(salary, age) AS r_squared,          -- 決定係数
    REGR_COUNT(salary, age) AS valid_pairs      -- 有効なペア数
FROM employees;

-- 文字列集約（STRING_AGG / GROUP_CONCAT）
-- PostgreSQL / SQL Server
SELECT
    department_id,
    STRING_AGG(name, ', ' ORDER BY name) AS member_list,
    STRING_AGG(DISTINCT title, ' / ' ORDER BY title) AS unique_titles
FROM employees
GROUP BY department_id;
 
-- MySQL
-- SELECT department_id, GROUP_CONCAT(name ORDER BY name SEPARATOR ', ')
-- FROM employees GROUP BY department_id;
 
-- 配列集約（PostgreSQL固有）
SELECT
    department_id,
    ARRAY_AGG(name ORDER BY hired_date) AS members_by_tenure,
    ARRAY_AGG(DISTINCT department_id) AS depts,  -- 重複排除
    ARRAY_AGG(salary ORDER BY salary DESC) AS salaries_desc
FROM employees
GROUP BY department_id;
 
-- JSON集約（PostgreSQL 9.5+ / MySQL 5.7+）
-- PostgreSQL
SELECT
    department_id,
    JSON_AGG(
        JSON_BUILD_OBJECT(
            'name', name,
            'salary', salary,
            'hired', hired_date
        ) ORDER BY salary DESC
    ) AS members_json
FROM employees
GROUP BY department_id;
 
-- BOOL集約（PostgreSQL）
SELECT
    department_id,
    BOOL_AND(is_active) AS all_active,   -- 全員active？
    BOOL_OR(is_manager) AS has_manager,  -- マネージャーがいる？
    EVERY(salary > 300000) AS all_above_300k  -- 全員30万超？
FROM employees
GROUP BY department_id;
 
-- BIT集約
SELECT
    department_id,
    BIT_AND(permissions) AS common_perms,  -- 共通権限（AND）
    BIT_OR(permissions) AS union_perms     -- 権限の和（OR）
FROM employees
GROUP BY department_id;

-- ■ インデックスによる最適化
-- GROUP BY列にインデックスがあるとGroupAggregateが使われやすい
CREATE INDEX idx_sales_category ON sales (category);
CREATE INDEX idx_sales_cat_region ON sales (category, region);
 
-- 実行計画の確認
EXPLAIN (ANALYZE, BUFFERS)
SELECT category, SUM(amount)
FROM sales
GROUP BY category;
 
-- ■ WHERE句で先にフィルタ（最も効果的な最適化）
-- NG: 全行を読んでからHAVINGでフィルタ
SELECT region, SUM(amount)
FROM sales
GROUP BY region
HAVING region IN ('東京', '大阪');  -- 全行をGROUP BY後にフィルタ
 
-- OK: WHEREで先に絞り込み
SELECT region, SUM(amount)
FROM sales
WHERE region IN ('東京', '大阪')     -- 先に行を絞る（インデックス利用可）
GROUP BY region;
 
-- ■ 部分インデックスの活用
CREATE INDEX idx_sales_active ON sales (category, amount)
WHERE sale_date >= '2024-01-01';
 
-- ■ マテリアライズドビューによるキャッシュ（PostgreSQL）
CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT
    DATE_TRUNC('month', sale_date) AS month,
    category,
    region,
    SUM(amount) AS total_amount,
    COUNT(*) AS transaction_count,
    AVG(amount) AS avg_amount
FROM sales
GROUP BY DATE_TRUNC('month', sale_date), category, region;
 
CREATE UNIQUE INDEX ON mv_monthly_sales (month, category, region);
 
-- 更新
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;
 
-- ■ 近似集約（大規模データ向け）
-- PostgreSQL拡張: HyperLogLog（概算COUNT DISTINCT）
-- HLLを使えば数十億行のCOUNT DISTINCTを定数メモリで計算可能
-- CREATE EXTENSION hll;
-- SELECT hll_cardinality(hll_add_agg(hll_hash_text(user_id)))
-- FROM huge_events;
 
-- ■ work_memの調整
SET work_mem = '256MB';  -- HashAggregateのメモリ上限を引き上げ
EXPLAIN (ANALYZE, BUFFERS)
SELECT category, region, COUNT(*)
FROM large_table
GROUP BY category, region;

-- ■ MySQL: GROUP_CONCATの注意
-- デフォルト最大長は1024バイト（切り詰められる！）
SET GROUP_CONCAT_MAX_LEN = 1000000;
SELECT department_id,
       GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS members
FROM employees
GROUP BY department_id;
 
-- ■ MySQL: WITH ROLLUP構文
SELECT category, region, SUM(amount)
FROM sales
GROUP BY category, region WITH ROLLUP;
-- ※ CUBE, GROUPING SETSは非対応
 
-- ■ SQL Server: STRING_AGGの注意
-- SQL Server 2017+で対応。それ以前はFOR XML PATHを使用
SELECT
    department_id,
    STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name) AS members
FROM employees
GROUP BY department_id;
 
-- ■ Oracle: LISTAGG（文字列集約）
SELECT
    department_id,
    LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS members
FROM employees
GROUP BY department_id;
 
-- Oracle 19c+: LISTAGG DISTINCT
SELECT
    department_id,
    LISTAGG(DISTINCT title, ', ') WITHIN GROUP (ORDER BY title) AS titles
FROM employees
GROUP BY department_id;

-- 空テーブル（行が0件）に対する集約
CREATE TABLE empty_table (amount INTEGER);
 
SELECT
    COUNT(*)   AS cnt,      -- 0（空でも1行返る）
    SUM(amount) AS total,   -- NULL（0ではない！）
    AVG(amount) AS avg,     -- NULL
    MIN(amount) AS mn,      -- NULL
    MAX(amount) AS mx       -- NULL
FROM empty_table;
-- → 結果: 1行が返る（0行ではない）
 
-- GROUP BYを付けた場合
SELECT amount, COUNT(*) FROM empty_table GROUP BY amount;
-- → 結果: 0行（グループ自体が存在しない）
 
-- ★ 安全な書き方
SELECT COALESCE(SUM(amount), 0) AS safe_total FROM empty_table;

-- NULLは1つのグループとして扱われる
INSERT INTO sales (product, category, amount, quantity, sale_date, region) VALUES
    ('不明商品', NULL, 1000, 1, '2024-05-01', NULL);
 
SELECT category, COUNT(*), SUM(amount)
FROM sales
GROUP BY category;
-- → NULLも1つのグループとして集約される
-- → (NULL, 1, 1000) が結果に含まれる
 
-- COALESCE で明示的に扱う
SELECT COALESCE(category, '未分類') AS category, COUNT(*)
FROM sales
GROUP BY COALESCE(category, '未分類');

-- COUNT(DISTINCT)のメモリ消費
-- 大テーブルでCOUNT(DISTINCT)を使うとハッシュテーブルのメモリ消費が大きい
SELECT COUNT(DISTINCT user_id) FROM huge_events;  -- 数百万ユニーク値
 
-- 近似でよい場合の代替手段
-- PostgreSQL: HyperLogLog拡張
-- BigQuery: APPROX_COUNT_DISTINCT(user_id)
-- Redshift: APPROXIMATE COUNT(DISTINCT user_id)
 
-- 複数DISTINCT集約は非効率
-- NG: 2つのDISTINCT（2つのハッシュテーブルが必要）
SELECT
    COUNT(DISTINCT user_id) AS unique_users,
    COUNT(DISTINCT product_id) AS unique_products
FROM orders;
 
-- OK: サブクエリで分割（大テーブルの場合）
SELECT
    (SELECT COUNT(DISTINCT user_id) FROM orders) AS unique_users,
    (SELECT COUNT(DISTINCT product_id) FROM orders) AS unique_products;

-- DECIMAL型とFLOAT型のSUM精度の違い
CREATE TABLE float_test (
    val_decimal DECIMAL(10,2),
    val_float   FLOAT
);
 
INSERT INTO float_test VALUES (0.1, 0.1), (0.2, 0.2), (0.3, 0.3);
 
SELECT
    SUM(val_decimal) AS decimal_sum,  -- 0.60（正確）
    SUM(val_float) AS float_sum       -- 0.6000000000000001（誤差あり）
FROM float_test;
 
-- 金額計算では必ずDECIMAL/NUMERICを使用すること

-- NG: nameはGROUP BYに含まれていない
SELECT department_id, name, AVG(salary)
FROM employees
GROUP BY department_id;
-- → PostgreSQLではエラー
-- → MySQLでは不定値が返る（ONLY_FULL_GROUP_BY無効時）
 
-- OK: GROUP BYに含めるか、集約関数で包む
SELECT department_id, MIN(name) AS first_name, AVG(salary)
FROM employees
GROUP BY department_id;
 
-- OK: ウィンドウ関数を使う
SELECT DISTINCT
    department_id,
    FIRST_VALUE(name) OVER (PARTITION BY department_id ORDER BY salary DESC),
    AVG(salary) OVER (PARTITION BY department_id)
FROM employees;
 
-- OK: 最高給与者の名前を取得（サブクエリ）
SELECT
    e.department_id,
    e.name AS top_earner,
    d.avg_salary
FROM employees e
INNER JOIN (
    SELECT department_id, AVG(salary) AS avg_salary, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
) d ON e.department_id = d.department_id
   AND e.salary = d.max_salary;

-- NGパターン: NULLの影響を考慮しない
-- データ: [100, NULL, 200, NULL, 300]
SELECT AVG(score) FROM tests;
-- → 200（3件の平均）、NULLを含む5件の平均ではない！
 
-- OK: NULLを0として扱いたい場合は明示する
SELECT AVG(COALESCE(score, 0)) FROM tests;
-- → 120（5件の平均）
 
-- OK: NULL行を除外した上での平均が正しい場合
SELECT AVG(score) FROM tests WHERE score IS NOT NULL;
-- → 200（明示的にNULL除外）

-- NG: 行レベルの条件をHAVINGに書く
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region
HAVING region = '東京';  -- WHERE で書くべき
 
-- OK: WHEREで先にフィルタ
SELECT region, SUM(amount) AS total
FROM sales
WHERE region = '東京'
GROUP BY region;
-- → WHEREの方がインデックスを利用でき高速

-- NG: GROUP BYの結果はすでにユニーク
SELECT DISTINCT category, COUNT(*)
FROM sales
GROUP BY category;
-- DISTINCTは無意味（GROUP BYで既にユニーク化されている）
 
-- OK: DISTINCTを削除
SELECT category, COUNT(*)
FROM sales
GROUP BY category;

-- 問題: salesテーブルに対して以下のクエリを書きなさい。
-- Q1: カテゴリ別の売上件数・合計金額・平均金額・最大金額を求めよ
-- Q2: 地域別で、売上件数が2件以上のものだけを合計金額降順で表示せよ
-- Q3: 月別（YYYY-MM形式）の売上合計と前月からの増減を表示せよ

-- Q1: カテゴリ別集計
SELECT
    category,
    COUNT(*) AS sales_count,
    SUM(amount) AS total_amount,
    ROUND(AVG(amount), 2) AS avg_amount,
    MAX(amount) AS max_amount
FROM sales
GROUP BY category
ORDER BY total_amount DESC;
 
-- Q2: 地域別（2件以上）
SELECT
    region,
    COUNT(*) AS sales_count,
    SUM(amount) AS total_amount
FROM sales
GROUP BY region
HAVING COUNT(*) >= 2
ORDER BY total_amount DESC;
 
-- Q3: 月別推移（ウィンドウ関数との組み合わせ）
WITH monthly AS (
    SELECT
        TO_CHAR(sale_date, 'YYYY-MM') AS month,
        SUM(amount) AS total
    FROM sales
    GROUP BY TO_CHAR(sale_date, 'YYYY-MM')
)
SELECT
    month,
    total,
    LAG(total) OVER (ORDER BY month) AS prev_month,
    total - LAG(total) OVER (ORDER BY month) AS change
FROM monthly
ORDER BY month;

-- 問題:
-- Q1: カテゴリ・地域のROLLUPで階層的小計を含む帳票を作成せよ
--     （小計行と総計行にはラベルを付けること）
-- Q2: 四半期別（Q1-Q4）のカテゴリ別売上をクロス集計（ピボット）せよ
-- Q3: 担当者別に、1万円以上の取引回数と1万円未満の取引回数を横持ちで表示せよ

-- Q1: ROLLUP + GROUPING関数
SELECT
    CASE WHEN GROUPING(category) = 1 THEN '★総計'
         ELSE category END AS category,
    CASE WHEN GROUPING(region) = 1 THEN
        CASE WHEN GROUPING(category) = 1 THEN '' ELSE '☆小計' END
    ELSE region END AS region,
    SUM(amount) AS total_amount,
    COUNT(*) AS cnt
FROM sales
GROUP BY ROLLUP(category, region)
ORDER BY GROUPING(category, region), category, region;
 
-- Q2: クロス集計（ピボットテーブル）
SELECT
    category,
    SUM(amount) FILTER (WHERE EXTRACT(QUARTER FROM sale_date) = 1) AS q1,
    SUM(amount) FILTER (WHERE EXTRACT(QUARTER FROM sale_date) = 2) AS q2,
    SUM(amount) FILTER (WHERE EXTRACT(QUARTER FROM sale_date) = 3) AS q3,
    SUM(amount) FILTER (WHERE EXTRACT(QUARTER FROM sale_date) = 4) AS q4,
    SUM(amount) AS annual_total
FROM sales
GROUP BY category
ORDER BY annual_total DESC;
 
-- FILTER句が使えないRDBMSの場合
SELECT
    category,
    SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 1 THEN amount ELSE 0 END) AS q1,
    SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 2 THEN amount ELSE 0 END) AS q2,
    SUM(amount) AS annual_total
FROM sales
GROUP BY category;
 
-- Q3: 条件別カウント
SELECT
    salesperson,
    COUNT(*) AS total_transactions,
    COUNT(*) FILTER (WHERE amount >= 10000) AS high_value_count,
    COUNT(*) FILTER (WHERE amount < 10000) AS low_value_count,
    ROUND(
        COUNT(*) FILTER (WHERE amount >= 10000)::NUMERIC / COUNT(*) * 100, 1
    ) AS high_value_pct
FROM sales
GROUP BY salesperson
ORDER BY total_transactions DESC;

-- 問題:
-- Q1: 各カテゴリの売上金額の標準偏差と変動係数（CV = 標準偏差/平均）を求めよ
-- Q2: 売上金額の分布を10個のバケット（デシル）に分割し、各バケットの件数と
--     金額範囲を表示せよ
-- Q3: 担当者別の売上推移で、3回移動平均を計算し、トレンド（上昇/下降）を判定せよ

-- Q1: 変動係数
SELECT
    category,
    COUNT(*) AS cnt,
    ROUND(AVG(amount), 2) AS avg_amount,
    ROUND(STDDEV_SAMP(amount), 2) AS stddev_amount,
    ROUND(STDDEV_SAMP(amount) / NULLIF(AVG(amount), 0) * 100, 2) AS cv_pct
FROM sales
GROUP BY category
HAVING COUNT(*) >= 2;
 
-- Q2: デシル分析（NTILE使用）
WITH deciles AS (
    SELECT
        amount,
        NTILE(10) OVER (ORDER BY amount) AS decile
    FROM sales
)
SELECT
    decile,
    COUNT(*) AS cnt,
    MIN(amount) AS min_amount,
    MAX(amount) AS max_amount,
    ROUND(AVG(amount), 2) AS avg_amount,
    SUM(amount) AS total_amount
FROM deciles
GROUP BY decile
ORDER BY decile;
 
-- Q3: 移動平均とトレンド判定
WITH sales_ordered AS (
    SELECT
        salesperson,
        sale_date,
        amount,
        ROW_NUMBER() OVER (PARTITION BY salesperson ORDER BY sale_date) AS rn,
        AVG(amount) OVER (
            PARTITION BY salesperson ORDER BY sale_date
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS ma3
    FROM sales
),
with_prev AS (
    SELECT
        *,
        LAG(ma3) OVER (PARTITION BY salesperson ORDER BY sale_date) AS prev_ma3
    FROM sales_ordered
)
SELECT
    salesperson,
    sale_date,
    amount,
    ROUND(ma3, 2) AS moving_avg_3,
    CASE
        WHEN ma3 > prev_ma3 THEN '上昇↑'
        WHEN ma3 < prev_ma3 THEN '下降↓'
        ELSE '横ばい→'
    END AS trend
FROM with_prev
ORDER BY salesperson, sale_date;