【SQL】日付の範囲指定完全ガイド｜BETWEEN・>= AND <=・月別/四半期/年度パターン・期間集計・インデックスまで解説

-- orders テーブル（注文）
-- id | customer_id | amount | status   | ordered_at
--  1 |           1 |  12000 | shipped  | 2024-01-15 09:30:00
--  2 |           2 |   3500 | pending  | 2024-02-01 15:45:00
--  3 |           3 |  85000 | shipped  | 2024-02-20 11:00:00
--  4 |           1 |   2800 | shipped  | 2024-03-05 08:00:00
--  5 |           4 |  45000 | pending  | 2024-03-10 17:30:00
--  6 |           2 |   9500 | canceled | 2024-04-01 10:00:00
--  7 |           5 |  32000 | shipped  | 2024-04-15 14:00:00
--  8 |           3 |   7800 | shipped  | 2024-05-20 09:00:00
--  9 |           1 |  55000 | shipped  | 2024-06-30 16:30:00

-- campaigns テーブル（キャンペーン期間）
-- id | name              | start_date  | end_date
--  1 | 春のセール        | 2024-03-01  | 2024-03-31
--  2 | ゴールデンウィーク| 2024-04-27  | 2024-05-06
--  3 | 夏のボーナスセール| 2024-06-01  | 2024-06-30

-- 2024年1月〜3月の注文（DATE列に BETWEEN：両端を含む）
SELECT id, customer_id, amount, ordered_at
FROM orders
WHERE ordered_at BETWEEN '2024-01-01' AND '2024-03-31';
-- → id 1〜5 が該当（3月31日 23:59:59 のデータも含まれるか？→後述）

-- 上と等価な書き方
SELECT id, customer_id, amount, ordered_at
FROM orders
WHERE ordered_at >= '2024-01-01'
  AND ordered_at <= '2024-03-31';

-- DATE型の列（時刻なし）→ BETWEEN が自然
-- memberships.expired_at が DATE 型の場合
SELECT * FROM memberships
WHERE expired_at BETWEEN '2024-01-01' AND '2024-12-31';
-- 12月31日のレコードを正確に含む

-- DATETIME型の列（時刻あり）→ 翌日 00:00:00 未満が安全
-- orders.ordered_at が DATETIME 型の場合

-- 非推奨：最終日の時刻部分が漏れる可能性
WHERE ordered_at BETWEEN '2024-01-01' AND '2024-03-31';
-- '2024-03-31 23:59:59'以降のデータは取得できない場合がある

-- 推奨：翌月初（2024-04-01 00:00:00）未満で指定
WHERE ordered_at >= '2024-01-01'
  AND ordered_at <  '2024-04-01';
-- 2024-03-31 のいかなる時刻のデータも完全に含む

-- NG①: 終端が 00:00:00 扱いになり当日データが漏れる
SELECT * FROM orders
WHERE ordered_at BETWEEN '2024-03-31' AND '2024-03-31';
-- 結果: 2024-03-31 00:00:00 のみヒット（id=4 の 08:00:00 はマッチしない）

-- NG②: 23:59:59 指定でも 23:59:59.999... が漏れる場合がある
SELECT * FROM orders
WHERE ordered_at BETWEEN '2024-03-31' AND '2024-03-31 23:59:59';
-- マイクロ秒精度の場合 23:59:59.999999 などが漏れる

-- ★OK①: 翌日未満（最も推奨・インデックスも効く）
SELECT * FROM orders
WHERE ordered_at >= '2024-03-31'
  AND ordered_at <  '2024-04-01';

-- ★OK②: DATE() 関数で日付部分を切り出す（インデックスは効きにくい）
SELECT * FROM orders
WHERE DATE(ordered_at) = '2024-03-31';

-- ★OK③: MySQL で時刻を含む終端を自動計算
SELECT * FROM orders
WHERE ordered_at BETWEEN '2024-03-31 00:00:00' AND '2024-03-31 23:59:59.999999';

-- ===== 今月のデータ =====

-- 推奨：月初〜翌月初（DATETIME 列でも安全）
SELECT * FROM orders
WHERE ordered_at >= DATE_FORMAT(NOW(), '%Y-%m-01')
  AND ordered_at <  DATE_FORMAT(NOW(), '%Y-%m-01') + INTERVAL 1 MONTH;

-- ===== 先月のデータ =====
SELECT * FROM orders
WHERE ordered_at >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')
  AND ordered_at <  DATE_FORMAT(NOW(), '%Y-%m-01');

-- ===== 特定月のデータ（例：2024年3月）=====
SELECT * FROM orders
WHERE ordered_at >= '2024-03-01'
  AND ordered_at <  '2024-04-01';

-- ===== 特定月（YEAR+MONTH 関数）=====
-- ※ 列に関数を使うためインデックスが効かない場合あり（大量データに注意）
SELECT * FROM orders
WHERE YEAR(ordered_at) = 2024
  AND MONTH(ordered_at) = 3;

-- ===== PostgreSQL =====
-- 今月
WHERE ordered_at >= DATE_TRUNC('month', NOW())
  AND ordered_at <  DATE_TRUNC('month', NOW()) + INTERVAL '1 month';

-- 先月
WHERE ordered_at >= DATE_TRUNC('month', NOW() - INTERVAL '1 month')
  AND ordered_at <  DATE_TRUNC('month', NOW());

-- ===== SQL Server =====
-- 今月
WHERE ordered_at >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
  AND ordered_at <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0);

-- ===== Oracle =====
-- 今月
WHERE ordered_at >= TRUNC(SYSDATE, 'MM')
  AND ordered_at <  ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1);

-- 先月
WHERE ordered_at >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
  AND ordered_at <  TRUNC(SYSDATE, 'MM');

-- ===== Q1（1〜3月）=====
SELECT * FROM orders
WHERE ordered_at >= '2024-01-01'
  AND ordered_at <  '2024-04-01';

-- ===== Q2（4〜6月）=====
WHERE ordered_at >= '2024-04-01'
  AND ordered_at <  '2024-07-01';

-- ===== MySQL: 動的に現在の四半期を計算 =====
SELECT * FROM orders
WHERE ordered_at >= MAKEDATE(YEAR(NOW()), 1)
                  + INTERVAL (QUARTER(NOW()) - 1) * 3 MONTH
  AND ordered_at <  MAKEDATE(YEAR(NOW()), 1)
                  + INTERVAL QUARTER(NOW()) * 3 MONTH;

-- ===== QUARTER 関数で絞り込む（インデックス非効率だが記述が簡潔）=====
SELECT * FROM orders
WHERE YEAR(ordered_at) = 2024
  AND QUARTER(ordered_at) = 1;

-- ===== PostgreSQL: 現在の四半期 =====
WHERE ordered_at >= DATE_TRUNC('quarter', NOW())
  AND ordered_at <  DATE_TRUNC('quarter', NOW()) + INTERVAL '3 months';

-- ===== 今年のデータ（インデックスに優しい書き方）=====
SELECT * FROM orders
WHERE ordered_at >= '2024-01-01'
  AND ordered_at <  '2025-01-01';

-- ===== MySQL: 動的に今年を計算 =====
SELECT * FROM orders
WHERE ordered_at >= DATE_FORMAT(NOW(), '%Y-01-01')
  AND ordered_at <  DATE_FORMAT(NOW() + INTERVAL 1 YEAR, '%Y-01-01');

-- ===== 去年のデータ =====
SELECT * FROM orders
WHERE ordered_at >= DATE_FORMAT(NOW() - INTERVAL 1 YEAR, '%Y-01-01')
  AND ordered_at <  DATE_FORMAT(NOW(), '%Y-01-01');

-- ===== PostgreSQL =====
WHERE ordered_at >= DATE_TRUNC('year', NOW())
  AND ordered_at <  DATE_TRUNC('year', NOW()) + INTERVAL '1 year';

-- ===== 2024年度（2024/4/1〜2025/3/31）=====
SELECT * FROM orders
WHERE ordered_at >= '2024-04-01'
  AND ordered_at <  '2025-04-01';

-- ===== MySQL: 現在の会計年度を動的に計算 =====
-- 4月以降なら今年度、3月以前なら昨年度を開始年とする
SET @fy_start = IF(
    MONTH(NOW()) >= 4,
    DATE_FORMAT(NOW(), '%Y-04-01'),
    DATE_FORMAT(NOW() - INTERVAL 1 YEAR, '%Y-04-01')
);

SELECT * FROM orders
WHERE ordered_at >= @fy_start
  AND ordered_at <  DATE_ADD(@fy_start, INTERVAL 1 YEAR);

-- ===== 会計年度ラベルを付けて集計 =====
SELECT
    CASE
        WHEN MONTH(ordered_at) >= 4
        THEN CONCAT(YEAR(ordered_at), '年度')
        ELSE CONCAT(YEAR(ordered_at) - 1, '年度')
    END AS fiscal_year,
    COUNT(*) AS orders_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY fiscal_year
ORDER BY fiscal_year;

-- campaigns テーブルの期間内にある注文を取得（JOIN + BETWEEN）
SELECT
    c.name AS campaign_name,
    o.id,
    o.customer_id,
    o.amount,
    o.ordered_at
FROM campaigns c
JOIN orders o
  ON o.ordered_at BETWEEN c.start_date AND c.end_date + INTERVAL 1 DAY - INTERVAL 1 SECOND
ORDER BY c.name, o.ordered_at;
-- ↑ campaigns.end_date が DATE 型なので、終端を 23:59:59 まで拡張

-- より明確な書き方（翌日未満で指定）
SELECT c.name, o.id, o.amount
FROM campaigns c
JOIN orders o
  ON o.ordered_at >= c.start_date
 AND o.ordered_at <  c.end_date + INTERVAL 1 DAY;

-- キャンペーン期間外の注文
SELECT * FROM orders
WHERE ordered_at NOT BETWEEN '2024-03-01' AND '2024-04-30 23:59:59';

-- 上と等価
SELECT * FROM orders
WHERE ordered_at < '2024-03-01'
   OR ordered_at >= '2024-05-01';

-- メンテナンス時間帯（毎日 02:00〜03:00）を除く
-- ※ TIME 型の比較
SELECT * FROM orders
WHERE TIME(ordered_at) NOT BETWEEN '02:00:00' AND '02:59:59';

-- NULL の扱い：NOT BETWEEN でも NULL は除外される
-- ordered_at が NULL の行は NOT BETWEEN でもヒットしない
-- → NULL チェックが必要な場合は OR IS NULL を追加
SELECT * FROM orders
WHERE (ordered_at NOT BETWEEN '2024-03-01' AND '2024-03-31 23:59:59')
   OR ordered_at IS NULL;

-- 2024年の月別注文数・売上合計
SELECT
    DATE_FORMAT(ordered_at, '%Y-%m') AS year_month,
    COUNT(*) AS orders_count,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount
FROM orders
WHERE ordered_at >= '2024-01-01'
  AND ordered_at <  '2025-01-01'
GROUP BY DATE_FORMAT(ordered_at, '%Y-%m')
ORDER BY year_month;

-- 結果例:
-- year_month | orders_count | total_amount | avg_amount
-- 2024-01    |            1 |        12000 |  12000.00
-- 2024-02    |            2 |        88500 |  44250.00
-- 2024-03    |            2 |        47800 |  23900.00

-- ===== MySQL: 週別集計 =====
SELECT
    YEARWEEK(ordered_at, 1) AS year_week,  -- ISO 週番号（月曜始まり）
    MIN(DATE(ordered_at)) AS week_start,
    COUNT(*) AS orders_count,
    SUM(amount) AS total_amount
FROM orders
WHERE ordered_at >= '2024-01-01'
  AND ordered_at <  '2024-04-01'
GROUP BY YEARWEEK(ordered_at, 1)
ORDER BY year_week;

-- ===== PostgreSQL: 週別集計 =====
SELECT
    DATE_TRUNC('week', ordered_at)::date AS week_start,
    COUNT(*) AS orders_count,
    SUM(amount) AS total_amount
FROM orders
WHERE ordered_at >= '2024-01-01'
  AND ordered_at <  '2024-04-01'
GROUP BY DATE_TRUNC('week', ordered_at)
ORDER BY week_start;

-- MySQL: 四半期別売上
SELECT
    YEAR(ordered_at) AS year,
    QUARTER(ordered_at) AS quarter,
    CONCAT(YEAR(ordered_at), '/Q', QUARTER(ordered_at)) AS label,
    COUNT(*) AS orders_count,
    SUM(amount) AS total_amount
FROM orders
WHERE ordered_at >= '2024-01-01'
  AND ordered_at <  '2025-01-01'
GROUP BY YEAR(ordered_at), QUARTER(ordered_at)
ORDER BY year, quarter;

-- PostgreSQL: 四半期別
SELECT
    DATE_TRUNC('quarter', ordered_at)::date AS quarter_start,
    COUNT(*) AS orders_count,
    SUM(amount) AS total_amount
FROM orders
WHERE ordered_at >= '2024-01-01'
  AND ordered_at <  '2025-01-01'
GROUP BY DATE_TRUNC('quarter', ordered_at)
ORDER BY quarter_start;

-- 月別売上 + 累計売上
SELECT
    DATE_FORMAT(ordered_at, '%Y-%m') AS year_month,
    SUM(amount) AS monthly_total,
    SUM(SUM(amount)) OVER (ORDER BY DATE_FORMAT(ordered_at, '%Y-%m')) AS running_total
FROM orders
WHERE ordered_at >= '2024-01-01'
  AND ordered_at <  '2025-01-01'
GROUP BY DATE_FORMAT(ordered_at, '%Y-%m')
ORDER BY year_month;

-- 前月比較（LAG を使った月次集計）
WITH monthly AS (
    SELECT
        DATE_FORMAT(ordered_at, '%Y-%m') AS ym,
        SUM(amount) AS total
    FROM orders
    WHERE ordered_at >= '2024-01-01'
      AND ordered_at <  '2025-01-01'
    GROUP BY DATE_FORMAT(ordered_at, '%Y-%m')
)
SELECT
    ym,
    total,
    LAG(total) OVER (ORDER BY ym) AS prev_month,
    ROUND((total - LAG(total) OVER (ORDER BY ym))
          / LAG(total) OVER (ORDER BY ym) * 100, 1) AS growth_pct
FROM monthly;

-- ===== MySQL（Python + PyMySQL）=====
-- ユーザーの入力をそのまま連結するのは SQL インジェクションの原因
-- BAD: 文字列連結（インジェクション危険）
query = f"WHERE ordered_at >= '{start_date}' AND ordered_at < '{end_date}'"

-- GOOD: プレースホルダーを使う（パラメーター化クエリ）
query = """
    SELECT id, customer_id, amount, ordered_at
    FROM orders
    WHERE ordered_at >= %s
      AND ordered_at < %s
    ORDER BY ordered_at
"""
cursor.execute(query, (start_date, end_date))  -- タプルで渡す

-- ===== PHP（PDO）=====
$stmt = $pdo->prepare(
    "SELECT * FROM orders
     WHERE ordered_at >= :start AND ordered_at < :end"
);
$stmt->execute([
    ':start' => $startDate,
    ':end'   => $endDate,
]);

-- Python の例：ユーザーが選んだ終了日の翌日を計算して渡す
from datetime import date, timedelta

start_date = date(2024, 3, 1)   # ユーザーが選んだ開始日
end_date   = date(2024, 3, 31)  # ユーザーが選んだ終了日

# DATETIME 列に BETWEEN を使うと 23:59:59 以降が漏れる可能性があるため
# アプリ側で「翌日」に変換して "<" で渡す
end_exclusive = end_date + timedelta(days=1)  # 2024-04-01

query = """
    SELECT * FROM orders
    WHERE ordered_at >= %s
      AND ordered_at < %s
"""
cursor.execute(query, (start_date, end_exclusive))
-- → 2024-03-31 のすべての時刻のデータを正確に取得

-- ===== 単一の日付インデックス =====
CREATE INDEX idx_orders_ordered_at ON orders (ordered_at);

-- 有効（rangeスキャン）
EXPLAIN SELECT * FROM orders
WHERE ordered_at >= '2024-01-01' AND ordered_at < '2024-04-01';
-- type: range, key: idx_orders_ordered_at

-- ===== 複合インデックス（status + ordered_at）=====
-- 「shipped のみを月別に集計」などの絞り込みが速くなる
CREATE INDEX idx_orders_status_ordered_at ON orders (status, ordered_at);

-- status の等値絞り込み後に ordered_at のレンジスキャン（効率的）
SELECT SUM(amount) FROM orders
WHERE status = 'shipped'
  AND ordered_at >= '2024-01-01'
  AND ordered_at <  '2024-04-01';

-- ※ 複合インデックスは左端のカラムから順に使われる
-- (ordered_at, status) の順では上のクエリで status が使われないことがある

-- 月別パーティションの定義例（RANGE COLUMNS）
CREATE TABLE orders_partitioned (
    id INT,
    customer_id INT,
    amount DECIMAL(10,2),
    status VARCHAR(20),
    ordered_at DATETIME
)
PARTITION BY RANGE (TO_DAYS(ordered_at)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    PARTITION p202404 VALUES LESS THAN (TO_DAYS('2024-05-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- EXPLAIN PARTITIONS でパーティションプルーニングを確認
EXPLAIN PARTITIONS
SELECT * FROM orders_partitioned
WHERE ordered_at >= '2024-02-01'
  AND ordered_at <  '2024-04-01';
-- partitions: p202402,p202403 のみスキャン → 大幅に I/O を削減

-- memberships.expired_at が DATE 型
SELECT id, name, expired_at,
       DATEDIFF(expired_at, CURDATE()) AS days_left
FROM memberships
WHERE expired_at BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)
ORDER BY expired_at;
-- BETWEEN で「今日〜30日後」を簡潔に表現（DATE 型なので安全）

-- 90〜180日前に注文した顧客（最近は注文なし）
SELECT DISTINCT customer_id
FROM orders
WHERE ordered_at >= DATE_SUB(CURDATE(), INTERVAL 180 DAY)
  AND ordered_at <  DATE_SUB(CURDATE(), INTERVAL 90 DAY)
  AND customer_id NOT IN (
      SELECT customer_id FROM orders
      WHERE ordered_at >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
  );

-- 給与計算期間：2024年3月分（2024/2/21〜2024/3/20）
SELECT * FROM work_logs
WHERE worked_at >= '2024-02-21'
  AND worked_at <  '2024-03-21';

-- MySQL: 動的に今月分を計算
SET @payroll_start = DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-21');
SET @payroll_end   = DATE_FORMAT(NOW(), '%Y-%m-21');

SELECT * FROM work_logs
WHERE worked_at >= @payroll_start
  AND worked_at <  @payroll_end;

-- 各キャンペーン期間に該当する注文を横断的に集計
SELECT
    c.name AS campaign_name,
    COUNT(o.id) AS orders_count,
    COALESCE(SUM(o.amount), 0) AS total_amount
FROM campaigns c
LEFT JOIN orders o
  ON o.ordered_at >= c.start_date
 AND o.ordered_at <  c.end_date + INTERVAL 1 DAY
GROUP BY c.id, c.name
ORDER BY c.start_date;