【SQL】前日のデータを取得する方法完全ガイド｜MySQL/PostgreSQL/SQL Server/Oracle・SARGable・タイムゾーン・実務8パターン

-- ❌ 遅い：DATE()がカラムに適用されるため、インデックス無効
SELECT * FROM orders
WHERE DATE(order_date) = CURDATE() - INTERVAL 1 DAY;

-- ❌ 遅い：Oracleで TRUNC(col) を使うパターン
SELECT * FROM orders
WHERE TRUNC(order_date) = TRUNC(SYSDATE) - 1;

-- ❌ 遅い：CAST で列を変換
SELECT * FROM orders
WHERE CAST(order_date AS DATE) = CAST(DATEADD(day,-1,GETDATE()) AS DATE);

-- ⭕ 速い：カラムは素のまま、比較対象を計算
SELECT * FROM orders
WHERE order_date >= CURDATE() - INTERVAL 1 DAY
  AND order_date <  CURDATE();

-- ⭕ 速い：Oracle
SELECT * FROM orders
WHERE order_date >= TRUNC(SYSDATE) - 1
  AND order_date <  TRUNC(SYSDATE);

-- ⭕ 速い：SQL Server
SELECT * FROM orders
WHERE order_date >= CAST(DATEADD(day,-1,GETDATE()) AS DATE)
  AND order_date <  CAST(GETDATE() AS DATE);

-- ❌ ミリ秒切れで取り逃がす
SELECT * FROM events
WHERE event_time BETWEEN '2026-04-21 00:00:00' AND '2026-04-21 23:59:59';
-- → 2026-04-21 23:59:59.500 のレコードは取得されるが
-- → 2026-04-21 23:59:59.999 のレコードは取得される（境界だが微妙）
-- → DBによってはミリ秒まで含めると1秒の端数処理でバグる

-- ❌ より頻発するバグ：当日を含めちゃう（00:00:00含む）
SELECT * FROM events
WHERE event_time BETWEEN '2026-04-20 00:00:00' AND '2026-04-21 00:00:00';
-- → 2026-04-21 00:00:00 の境界レコードも含まれてしまう
-- → 前日だけ取得したいのに当日の先頭も混ざる

-- ⭕ 半開区間：開始含む、終了含まない
SELECT * FROM events
WHERE event_time >= '2026-04-21 00:00:00'
  AND event_time <  '2026-04-22 00:00:00';

-- ⭕ 動的計算版（MySQL）
SELECT * FROM events
WHERE event_time >= CURDATE() - INTERVAL 1 DAY
  AND event_time <  CURDATE();

-- ⭕ SARGable推奨（半開区間）
SELECT *
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 1 DAY
  AND order_date <  CURDATE();

-- DATE_SUB()派（好みで）
SELECT *
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
  AND order_date <  CURDATE();

-- カラムがDATE型しか入っていないケース（時刻なし）
-- これならシンプルに「= 前日」で良い
SELECT *
FROM daily_reports
WHERE report_date = CURDATE() - INTERVAL 1 DAY;

-- 前日の総件数
SELECT COUNT(*) AS yesterday_count
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 1 DAY
  AND order_date <  CURDATE();

-- 前日の売上合計
SELECT SUM(amount) AS yesterday_sales
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 1 DAY
  AND order_date <  CURDATE();

-- 時間帯別件数（前日の1時間ごと集計）
SELECT HOUR(order_date) AS hr, COUNT(*) AS cnt
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 1 DAY
  AND order_date <  CURDATE()
GROUP BY HOUR(order_date)
ORDER BY hr;

-- ⭕ SARGable推奨
SELECT *
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 day'
  AND order_date <  CURRENT_DATE;

-- INTEGER演算でも可（PostgreSQL独自）
SELECT *
FROM orders
WHERE order_date >= CURRENT_DATE - 1
  AND order_date <  CURRENT_DATE;

-- TIMESTAMP型との混在が気になる時はtimestampキャスト
SELECT *
FROM orders
WHERE order_date >= (CURRENT_DATE - 1)::timestamp
  AND order_date <  CURRENT_DATE::timestamp;

-- timestamptz カラムの場合はタイムゾーン意識が必要
-- 日本の前日（UTCではなくJSTで）取得
SELECT *
FROM orders
WHERE order_date AT TIME ZONE 'Asia/Tokyo' >= (CURRENT_DATE - 1)
  AND order_date AT TIME ZONE 'Asia/Tokyo' <  CURRENT_DATE;

-- SARGableにしたい場合は右辺を変換する（左辺は素のまま）
SELECT *
FROM orders
WHERE order_date >= ((CURRENT_DATE - 1) AT TIME ZONE 'Asia/Tokyo')
  AND order_date <  (CURRENT_DATE AT TIME ZONE 'Asia/Tokyo');

-- ⭕ SARGable推奨
SELECT *
FROM orders
WHERE order_date >= CAST(DATEADD(day, -1, GETDATE()) AS DATE)
  AND order_date <  CAST(GETDATE() AS DATE);

-- 変数に入れて可読性UP
DECLARE @yesterday DATE = CAST(DATEADD(day, -1, GETDATE()) AS DATE);
DECLARE @today     DATE = CAST(GETDATE() AS DATE);

SELECT *
FROM orders
WHERE order_date >= @yesterday
  AND order_date <  @today;

-- UTC保存→JSTで前日取得（SQL Server 2016+）
SELECT *
FROM orders
WHERE order_date >= CAST(DATEADD(day, -1,
    SYSDATETIMEOFFSET() AT TIME ZONE 'Tokyo Standard Time') AS DATE)
  AND order_date <  CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Tokyo Standard Time' AS DATE);

-- ⭕ SARGable推奨
SELECT *
FROM orders
WHERE order_date >= TRUNC(SYSDATE) - 1
  AND order_date <  TRUNC(SYSDATE);

-- CURRENT_DATE（セッションタイムゾーンに依存）
SELECT *
FROM orders
WHERE order_date >= TRUNC(CURRENT_DATE) - 1
  AND order_date <  TRUNC(CURRENT_DATE);

-- UTC保存→JSTで前日取得
SELECT *
FROM orders
WHERE order_date >= (TRUNC(CAST(SYSTIMESTAMP AT TIME ZONE 'Asia/Tokyo' AS DATE)) - 1)
  AND order_date <  TRUNC(CAST(SYSTIMESTAMP AT TIME ZONE 'Asia/Tokyo' AS DATE));

-- パーティション列と組み合わせ（業務RDBで超高速）
SELECT /*+ FULL(o) PARALLEL(o 4) */ *
FROM orders PARTITION FOR (TRUNC(SYSDATE) - 1) o;

-- UTC保存データをJSTで前日取得
SELECT *
FROM logs
WHERE log_time >= CONVERT_TZ(
    CONCAT(CURDATE() - INTERVAL 1 DAY, ' 00:00:00'),
    '+09:00', '+00:00'
  )
  AND log_time <  CONVERT_TZ(
    CONCAT(CURDATE(), ' 00:00:00'),
    '+09:00', '+00:00'
  );
-- JST 2026-04-21 00:00:00 は UTC 2026-04-20 15:00:00

-- シンプルに範囲を構築
SELECT *
FROM logs
WHERE log_time >= ((CURRENT_DATE - 1) AT TIME ZONE 'Asia/Tokyo')
  AND log_time <  (CURRENT_DATE AT TIME ZONE 'Asia/Tokyo');

SELECT *
FROM logs
WHERE log_time >= CAST(DATEADD(day, -1,
    SYSDATETIMEOFFSET() AT TIME ZONE 'Tokyo Standard Time') AS DATETIME)
  AND log_time <  CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Tokyo Standard Time' AS DATETIME);

SELECT *
FROM logs
WHERE log_time >= (FROM_TZ(CAST(TRUNC(SYSTIMESTAMP AT TIME ZONE 'Asia/Tokyo') - 1 AS TIMESTAMP), 'Asia/Tokyo')
    AT TIME ZONE 'UTC')
  AND log_time <  (FROM_TZ(CAST(TRUNC(SYSTIMESTAMP AT TIME ZONE 'Asia/Tokyo') AS TIMESTAMP), 'Asia/Tokyo')
    AT TIME ZONE 'UTC');

-- MySQL：前日の商品カテゴリ別売上
SELECT
  category,
  COUNT(*) AS order_count,
  SUM(amount) AS total_sales,
  AVG(amount) AS avg_sales
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 1 DAY
  AND order_date <  CURDATE()
GROUP BY category
ORDER BY total_sales DESC;

-- 前々日と前日を比較
SELECT
  SUM(CASE WHEN order_date >= CURDATE() - INTERVAL 1 DAY THEN amount END) AS yesterday,
  SUM(CASE WHEN order_date >= CURDATE() - INTERVAL 2 DAY
            AND order_date <  CURDATE() - INTERVAL 1 DAY THEN amount END) AS day_before,
  (SUM(CASE WHEN order_date >= CURDATE() - INTERVAL 1 DAY THEN amount END)
    - SUM(CASE WHEN order_date >= CURDATE() - INTERVAL 2 DAY
               AND order_date <  CURDATE() - INTERVAL 1 DAY THEN amount END))
    / SUM(CASE WHEN order_date >= CURDATE() - INTERVAL 2 DAY
               AND order_date <  CURDATE() - INTERVAL 1 DAY THEN amount END) * 100 AS dod_percent
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 2 DAY
  AND order_date <  CURDATE();

-- Step1: 前日分をアーカイブテーブルへ
INSERT INTO access_logs_archive
SELECT * FROM access_logs
WHERE log_time >= CURDATE() - INTERVAL 1 DAY
  AND log_time <  CURDATE();

-- Step2: 元テーブルから前日分削除（バッチサイズ制限推奨）
DELETE FROM access_logs
WHERE log_time >= CURDATE() - INTERVAL 1 DAY
  AND log_time <  CURDATE()
LIMIT 10000;
-- LIMITで分割実行してロック範囲を狭める

SELECT u.id, u.email, u.name, u.created_at
FROM users u
WHERE u.created_at >= CURDATE() - INTERVAL 1 DAY
  AND u.created_at <  CURDATE()
  AND u.verified = 0;
-- verified=0 の未認証ユーザーだけに再送信通知を送る等

SELECT
  DATE_FORMAT(log_time, '%Y-%m-%d %H:00:00') AS hour,
  COUNT(*) AS error_count
FROM error_logs
WHERE log_time >= CURDATE() - INTERVAL 1 DAY
  AND log_time <  CURDATE()
  AND severity = "ERROR"
GROUP BY DATE_FORMAT(log_time, '%Y-%m-%d %H:00:00')
HAVING COUNT(*) > 100
ORDER BY hour;
-- 1時間当たり100件超のエラーを検出

SELECT *
FROM jobs
WHERE scheduled_at >= CURDATE() - INTERVAL 1 DAY
  AND scheduled_at <  CURDATE()
  AND status IN ('pending', 'retrying');
-- 前日予定のpending/retrying を検出→再投入対象

SELECT
  product_id,
  product_name,
  SUM(quantity) AS total_qty,
  RANK() OVER (ORDER BY SUM(quantity) DESC) AS daily_rank
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
WHERE o.order_date >= CURDATE() - INTERVAL 1 DAY
  AND o.order_date <  CURDATE()
GROUP BY product_id, product_name
ORDER BY total_qty DESC
LIMIT 10;

-- MySQL/PostgreSQL: RANGE PARTITION BY date
SELECT *
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 1 DAY
  AND order_date <  CURDATE();
-- パーティションプルーニングが効き前日分のみスキャン

-- Oracle: PARTITION FOR でさらに明示
SELECT *
FROM orders PARTITION FOR (TRUNC(SYSDATE) - 1);

# 毎朝6:00に前日の集計バッチ実行（ログ付き）
0 6 * * * /usr/bin/mysql -h db.example.com -u batch -p"$PASS" mydb \
  -e "CALL daily_summary_batch()" >> /var/log/batch.log 2>&1

# Dockerベース（compose経由）
0 6 * * * cd /opt/app && /usr/bin/docker compose exec -T db \
  mysql -uroot -proot mydb -e "CALL daily_summary_batch()" \
  >> /var/log/batch.log 2>&1

import pymysql
import os
from datetime import datetime, timedelta, timezone

def handler(event, context):
    # JST基準の前日を計算（Lambdaタイムゾーンに依存せず明示）
    jst = timezone(timedelta(hours=9))
    today_jst = datetime.now(jst).date()
    yesterday_jst = today_jst - timedelta(days=1)

    conn = pymysql.connect(
        host=os.environ["DB_HOST"],
        user=os.environ["DB_USER"],
        password=os.environ["DB_PASSWORD"],
        db=os.environ["DB_NAME"],
    )
    try:
        with conn.cursor() as cur:
            cur.execute(
                "SELECT COUNT(*), SUM(amount) FROM orders "
                "WHERE order_date >= %s AND order_date < %s",
                (yesterday_jst, today_jst),
            )
            count, total = cur.fetchone()
    finally:
        conn.close()

    return {"count": count, "total": float(total or 0), "date": str(yesterday_jst)}

# cron(分 時 日 月 曜日 年) UTC指定
cron(0 21 * * ? *)    # UTC 21:00 = JST 06:00 毎日

# Terraform例
resource "aws_cloudwatch_event_rule" "daily_batch" {
  name                = "daily-batch"
  schedule_expression = "cron(0 21 * * ? *)"
}

-- EXPLAINを付けるだけ
EXPLAIN SELECT * FROM orders
WHERE order_date >= CURDATE() - INTERVAL 1 DAY
  AND order_date <  CURDATE();

-- 見るべき列
-- type: range（◎）/ ref（◯）/ ALL（✗フルスキャン）
-- key:  使われたインデックス名（NULLは未使用）
-- rows: 想定読み込み行数（少ないほど良い）
-- Extra: "Using index condition" があれば効いている

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 day'
  AND order_date <  CURRENT_DATE;

-- 見るべきキーワード
-- "Index Scan using orders_order_date_idx"（◎）
-- "Seq Scan"（✗フルスキャン）
-- "Bitmap Index Scan"（◯中程度）
-- actual rows vs estimated rows が大きくズレたら統計情報更新

-- SSMS: Ctrl+Mで実行プラン表示を有効化
-- または SET STATISTICS IO ON; SET STATISTICS TIME ON;

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM orders
WHERE order_date >= CAST(DATEADD(day, -1, GETDATE()) AS DATE)
  AND order_date <  CAST(GETDATE() AS DATE);

-- 見るべきキーワード
-- "Index Seek"（◎）
-- "Index Scan"（△）
-- "Table Scan"（✗フルスキャン）

EXPLAIN PLAN FOR
SELECT * FROM orders
WHERE order_date >= TRUNC(SYSDATE) - 1
  AND order_date <  TRUNC(SYSDATE);

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 見るべきキーワード
-- "INDEX RANGE SCAN"（◎）
-- "INDEX FAST FULL SCAN"（△）
-- "TABLE ACCESS FULL"（✗フルスキャン）