【SQL】前月のデータを取得する完全ガイド｜前月初〜月末の正確な計算・DATETIME落とし穴・前月比・N ヶ月前・RDBMS別構文まで解説

-- sales テーブル（売上）
-- id | shop_id | amount | category | sold_at
--  1 |       1 |  12000 | food     | 2024-02-05 09:30:00
--  2 |       1 |   3500 | drink    | 2024-02-14 15:45:00
--  3 |       2 |  85000 | food     | 2024-02-20 11:00:00
--  4 |       1 |   2800 | food     | 2024-03-05 08:00:00
--  5 |       2 |  45000 | drink    | 2024-03-10 17:30:00
--  6 |       1 |   9500 | food     | 2024-03-28 10:00:00
--  7 |       2 |  32000 | food     | 2024-04-01 14:00:00
--  8 |       1 |   7800 | drink    | 2024-04-15 09:00:00
--  9 |       2 |  55000 | food     | 2024-04-30 16:30:00

-- 基本パターン（DATE 型列に BETWEEN）
SELECT *
FROM sales
WHERE DATE(sold_at) BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')
                        AND LAST_DAY(NOW() - INTERVAL 1 MONTH);
-- 現在が 2024-04-08 → 2024-03-01 ～ 2024-03-31 を取得

-- DATETIME 型列には推奨: 翌月初未満（< 今月初）で指定
SELECT *
FROM sales
WHERE sold_at >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')
  AND sold_at <  DATE_FORMAT(NOW(), '%Y-%m-01');
-- 2024-03-01 00:00:00 以上 2024-04-01 00:00:00 未満
-- → 2024-03-31 23:59:59 のデータも確実に含む

-- PostgreSQL: DATE_TRUNC を使って前月初〜今月初未満
SELECT *
FROM sales
WHERE sold_at >= DATE_TRUNC('month', NOW() - INTERVAL '1 month')
  AND sold_at <  DATE_TRUNC('month', NOW());
-- 2024-03-01 00:00:00 以上 2024-04-01 00:00:00 未満

-- DATE_TRUNC の確認
SELECT
    DATE_TRUNC('month', NOW() - INTERVAL '1 month') AS prev_month_start,
    DATE_TRUNC('month', NOW()) AS this_month_start;
-- prev_month_start: 2024-03-01 00:00:00
-- this_month_start: 2024-04-01 00:00:00

-- SQL Server: DATEADD + DATEDIFF で月初を計算
DECLARE @prev_start DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0);
DECLARE @this_start DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);

SELECT *
FROM sales
WHERE sold_at >= @prev_start
  AND sold_at <  @this_start;

-- 変数なし（インライン）
SELECT *
FROM sales
WHERE sold_at >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
  AND sold_at <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);

-- Oracle: ADD_MONTHS + TRUNC で月初を計算
SELECT *
FROM sales
WHERE sold_at >= TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM')
  AND sold_at <  TRUNC(SYSDATE, 'MM');
-- TRUNC(SYSDATE, 'MM') = 今月初日の 00:00:00

-- Oracle の LAST_DAY（前月末日 = 今月初日 - 1）
SELECT
    TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM') AS prev_month_start,
    TRUNC(SYSDATE, 'MM') - 1             AS prev_month_end
FROM dual;

-- 現在日時: 2024-04-08 とする

-- NG①: LAST_DAY で DATE 型の末日を使う → 時刻が 00:00:00 扱いになる
SELECT *
FROM sales
WHERE sold_at BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')
                  AND LAST_DAY(NOW() - INTERVAL 1 MONTH);
-- LAST_DAY = '2024-03-31'（= '2024-03-31 00:00:00'）
-- → 2024-03-31 08:00:00 の id=4 が取れない

-- NG②: YEAR + MONTH 関数（インデックスが効かない）
SELECT *
FROM sales
WHERE YEAR(sold_at) = YEAR(NOW() - INTERVAL 1 MONTH)
  AND MONTH(sold_at) = MONTH(NOW() - INTERVAL 1 MONTH);
-- 動作するが sold_at 列に関数を適用するためインデックスが使われない

-- ★ OK: 今月初未満で指定（DATETIME 列に最も安全・インデックスも効く）
SELECT *
FROM sales
WHERE sold_at >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')
  AND sold_at <  DATE_FORMAT(NOW(), '%Y-%m-01');
-- 2024-03-01 00:00:00 ≦ sold_at < 2024-04-01 00:00:00
-- → 2024-03-31 の全時刻のデータを確実に取得

-- ★ 1月でも対応: DATE_FORMAT は月をまたいでも正しく動く
-- 現在が 2024-02-15 → prev = 2024-01-01, this = 2024-02-01 ✓
-- 現在が 2024-01-05 → prev = 2023-12-01, this = 2024-01-01 ✓（年またぎも OK）

-- 前月の売上合計・件数・平均
SELECT
    COUNT(*)          AS orders_count,
    SUM(amount)       AS total_amount,
    AVG(amount)       AS avg_amount,
    MAX(amount)       AS max_amount
FROM sales
WHERE sold_at >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')
  AND sold_at <  DATE_FORMAT(NOW(), '%Y-%m-01');

-- 前月をカテゴリ別に集計
SELECT
    category,
    COUNT(*)    AS orders_count,
    SUM(amount) AS total_amount
FROM sales
WHERE sold_at >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')
  AND sold_at <  DATE_FORMAT(NOW(), '%Y-%m-01')
GROUP BY category
ORDER BY total_amount DESC;

-- 1 つのクエリで今月分と前月分を集計
SELECT
    SUM(CASE WHEN sold_at >= DATE_FORMAT(NOW(), '%Y-%m-01')
              AND sold_at <  NOW()
             THEN amount ELSE 0 END) AS this_month_total,
    SUM(CASE WHEN sold_at >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')
              AND sold_at <  DATE_FORMAT(NOW(), '%Y-%m-01')
             THEN amount ELSE 0 END) AS prev_month_total
FROM sales
WHERE sold_at >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01');

-- 前月比（%）を計算
SELECT
    SUM(CASE WHEN sold_at >= DATE_FORMAT(NOW(), '%Y-%m-01') THEN amount ELSE 0 END) AS this_month,
    SUM(CASE WHEN sold_at >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')
              AND sold_at <  DATE_FORMAT(NOW(), '%Y-%m-01')
             THEN amount ELSE 0 END) AS prev_month,
    ROUND(
        SUM(CASE WHEN sold_at >= DATE_FORMAT(NOW(), '%Y-%m-01') THEN amount ELSE 0 END)
        / NULLIF(
            SUM(CASE WHEN sold_at >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')
                      AND sold_at <  DATE_FORMAT(NOW(), '%Y-%m-01')
                     THEN amount ELSE 0 END)
          , 0) * 100, 1
    ) AS growth_pct
FROM sales;

-- ① まず月別集計 CTE を作成
WITH monthly AS (
    SELECT
        DATE_FORMAT(sold_at, '%Y-%m') AS year_month,
        SUM(amount)                    AS total_amount,
        COUNT(*)                       AS orders_count
    FROM sales
    WHERE sold_at >= '2024-01-01'   -- 集計期間の開始
    GROUP BY DATE_FORMAT(sold_at, '%Y-%m')
)
-- ② LAG で前月の値を取得し比較
SELECT
    year_month,
    total_amount,
    orders_count,
    LAG(total_amount) OVER (ORDER BY year_month) AS prev_month_total,
    total_amount - LAG(total_amount) OVER (ORDER BY year_month) AS diff,
    ROUND(
        (total_amount - LAG(total_amount) OVER (ORDER BY year_month))
        / NULLIF(LAG(total_amount) OVER (ORDER BY year_month), 0) * 100,
        1
    ) AS growth_pct
FROM monthly
ORDER BY year_month;

-- 結果例（2024-04-08時点）
-- year_month | total_amount | prev_month_total | diff   | growth_pct
-- 2024-02    |       100500 |             NULL |   NULL |       NULL
-- 2024-03    |        57300 |           100500 | -43200 |      -43.0
-- 2024-04    |        94800 |            57300 |  37500 |       65.4

-- shop_id ごとに独立した前月比
WITH monthly AS (
    SELECT
        shop_id,
        DATE_FORMAT(sold_at, '%Y-%m') AS year_month,
        SUM(amount)                    AS total_amount
    FROM sales
    WHERE sold_at >= '2024-01-01'
    GROUP BY shop_id, DATE_FORMAT(sold_at, '%Y-%m')
)
SELECT
    shop_id,
    year_month,
    total_amount,
    LAG(total_amount) OVER (PARTITION BY shop_id ORDER BY year_month) AS prev_month,
    ROUND(
        (total_amount - LAG(total_amount) OVER (PARTITION BY shop_id ORDER BY year_month))
        / NULLIF(LAG(total_amount) OVER (PARTITION BY shop_id ORDER BY year_month), 0) * 100,
        1
    ) AS growth_pct
FROM monthly
ORDER BY shop_id, year_month;
-- PARTITION BY shop_id により各ショップの先頭月の prev_month は NULL になる

-- ===== YEAR / MONTH 関数（シンプルだがインデックス非使用になりやすい）=====
SELECT *
FROM sales
WHERE YEAR(sold_at) = YEAR(NOW() - INTERVAL 1 MONTH)
  AND MONTH(sold_at) = MONTH(NOW() - INTERVAL 1 MONTH);
-- → sold_at 列に関数を適用するため EXPLAIN で type=ALL（フルスキャン）になりやすい

-- ===== SARGable な書き方（インデックスのレンジスキャンを使える）=====
SELECT *
FROM sales
WHERE sold_at >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')
  AND sold_at <  DATE_FORMAT(NOW(), '%Y-%m-01');
-- → EXPLAIN で type=range になりインデックスが使われる

-- ===== EXPLAIN で確認 =====
EXPLAIN SELECT *
FROM sales
WHERE sold_at >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')
  AND sold_at <  DATE_FORMAT(NOW(), '%Y-%m-01');
-- type: range, key: idx_sold_at → インデックス使用 ✓

-- N を変えるだけでどの月でも対応可能
SET @n = 1;  -- 1 = 前月、2 = 前々月、3 = 3 ヶ月前...

SELECT *
FROM sales
WHERE sold_at >= DATE_FORMAT(NOW() - INTERVAL @n MONTH, '%Y-%m-01')
  AND sold_at <  DATE_FORMAT(NOW() - INTERVAL (@n - 1) MONTH, '%Y-%m-01');
-- @n=1: 2024-03-01 〜 2024-03-31 （前月）
-- @n=2: 2024-02-01 〜 2024-02-29 （前々月）
-- @n=3: 2024-01-01 〜 2024-01-31 （3 ヶ月前）

-- 直近 3 ヶ月（当月含まず、3 ヶ月分）
SELECT *
FROM sales
WHERE sold_at >= DATE_FORMAT(NOW() - INTERVAL 3 MONTH, '%Y-%m-01')
  AND sold_at <  DATE_FORMAT(NOW(), '%Y-%m-01');
-- 2024-01-01 〜 2024-03-31 のデータ

-- ===== PostgreSQL =====
-- 前月
WHERE sold_at >= DATE_TRUNC('month', NOW() - INTERVAL '1 month')
  AND sold_at <  DATE_TRUNC('month', NOW());

-- N ヶ月前（N=2 の場合）
WHERE sold_at >= DATE_TRUNC('month', NOW() - INTERVAL '2 months')
  AND sold_at <  DATE_TRUNC('month', NOW() - INTERVAL '1 month');

-- ===== SQL Server =====
-- 前月
WHERE sold_at >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
  AND sold_at <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);

-- N ヶ月前（N=2 の場合）
WHERE sold_at >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 2, 0)
  AND sold_at <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0);

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

-- N ヶ月前（N=2 の場合）
WHERE sold_at >= TRUNC(ADD_MONTHS(SYSDATE, -2), 'MM')
  AND sold_at <  TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM');

-- 前月の 1 日〜15 日
SELECT *
FROM sales
WHERE sold_at >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')
  AND sold_at <  DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-16');
-- '%Y-%m-16' で翌日（16日 00:00:00 未満）を指定することで 15 日の全時刻を含む

-- 前月の最終週（最終 7 日間）
SELECT *
FROM sales
WHERE sold_at >= LAST_DAY(NOW() - INTERVAL 1 MONTH) - INTERVAL 6 DAY
  AND sold_at <  DATE_FORMAT(NOW(), '%Y-%m-01');
-- LAST_DAY - 6 = 月末から 7 日前（月末の週）

-- 前月の月曜日始まりの週ごとに集計
SELECT
    DATE_FORMAT(sold_at, '%Y-%u') AS week,
    MIN(DATE(sold_at)) AS week_start,
    COUNT(*) AS cnt,
    SUM(amount) AS total
FROM sales
WHERE sold_at >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')
  AND sold_at <  DATE_FORMAT(NOW(), '%Y-%m-01')
GROUP BY DATE_FORMAT(sold_at, '%Y-%u')
ORDER BY week;

-- Python で前月の開始日・終了日を計算
from datetime import date
from dateutil.relativedelta import relativedelta  # pip install python-dateutil

today = date.today()  # 2024-04-08

# 前月初日
prev_start = (today.replace(day=1) - relativedelta(months=1))  # 2024-03-01
# 今月初日（= 前月の翌月初 = 前月の「<」に渡す値）
this_start = today.replace(day=1)  # 2024-04-01

import pymysql
conn = pymysql.connect(host='localhost', db='mydb')
cur = conn.cursor()

# プレースホルダーで安全に渡す（SQL インジェクション対策）
cur.execute("""
    SELECT category, SUM(amount) AS total
    FROM sales
    WHERE sold_at >= %s
      AND sold_at <  %s
    GROUP BY category
""", (prev_start, this_start))

for row in cur.fetchall():
    print(row)

-- PHP（PDO）での例
$prevStart = (new DateTime('first day of last month'))->format('Y-m-d');
$thisStart = (new DateTime('first day of this month'))->format('Y-m-d');

$stmt = $pdo->prepare(
    "SELECT category, SUM(amount) FROM sales
     WHERE sold_at >= :start AND sold_at < :end
     GROUP BY category"
);
$stmt->execute([':start' => $prevStart, ':end' => $thisStart]);

-- 直近 12 ヶ月分の月一覧を生成して LEFT JOIN
WITH months AS (
    SELECT DATE_FORMAT(
        DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL n MONTH), '%Y-%m'
    ) AS year_month
    FROM (
        SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
        UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
        UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11
    ) t
)
SELECT
    m.year_month,
    COALESCE(SUM(s.amount), 0) AS total_amount,
    COUNT(s.id)                AS orders_count
FROM months m
LEFT JOIN sales s
  ON DATE_FORMAT(s.sold_at, '%Y-%m') = m.year_month
GROUP BY m.year_month
ORDER BY m.year_month;

-- PostgreSQL: generate_series で月一覧を生成
SELECT
    TO_CHAR(months.d, 'YYYY-MM') AS year_month,
    COALESCE(SUM(s.amount), 0)   AS total_amount
FROM generate_series(
    DATE_TRUNC('month', NOW()) - INTERVAL '11 months',
    DATE_TRUNC('month', NOW()),
    '1 month'
) AS months(d)
LEFT JOIN sales s
  ON s.sold_at >= months.d
 AND s.sold_at <  months.d + INTERVAL '1 month'
GROUP BY months.d
ORDER BY months.d;