特定の年月を指定してSQLでデータを抽出する方法

特定の年月を抽出する基本形は「月初以上、翌月初未満」です。2023年1月なら次の半開区間で指定します。DATE型だけでなく時刻付き列でも月末の端数秒を取りこぼしにくく、月末日やうるう年を手で数える必要がありません。以下の詳細な関数・DDL例はMySQLを対象とします。

SELECT id, product_name, amount, sale_date
FROM sales
WHERE sale_date >= '2023-01-01'
  AND sale_date <  '2023-02-01'
ORDER BY sale_date, id;

1月1日00:00:00は含み、1月31日23:59:59.999999も含み、2月1日00:00:00は含みません。NULLのsale_dateはこの条件では対象外です。アプリから年月を受け取る場合は月が1〜12かを検証し、月初と翌月初を日付値としてパラメーターへ渡します。入力をSQL文字列へ連結しないでください。

SQLで特定の年月に基づいてデータを抽出する方法を学びます。この方法を使うことで、特定の月や年のデータ分析が容易になります。SQLのDATE関数や他の便利な関数を活用して、効率的にデータを取り出す方法を解説します。

データベースのセットアップ

以下の準備例はMySQL向けで、CREATE DATABASE、USE、AUTO_INCREMENTは他製品へそのまま流用できません。新規の検証用DBだけで使い、本番DBや同名の既存テーブルには実行しません。以下は、サンプルデータを含むテーブルを作成するためのSQLスクリプトです。

データベースの作成

データベースを作成します。すでにデータベースが存在する場合は、このステップをスキップできます。

CREATE DATABASE sample_db;
USE sample_db;

テーブルの作成

次に、データを格納するためのテーブルを作成します。ここでは、salesというテーブルを例にします。

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100),
    amount DECIMAL(10, 2),
    sale_date DATE
);

サンプルデータの挿入

最後に、サンプルデータをテーブルに挿入します。これにより、後のクエリ例で使用するデータが準備されます。

INSERT INTO sales (product_name, amount, sale_date) VALUES
('Product A', 100.00, '2023-01-15'),
('Product B', 150.00, '2023-02-20'),
('Product C', 200.00, '2023-03-25'),
('Product A', 250.00, '2023-01-30'),
('Product B', 300.00, '2023-02-15');

基本的なSQLクエリの書き方

基本的なSQLクエリの構造を理解することは、特定の年月を指定してデータを抽出するための第一歩です。ここでは、基本的なSELECT文の書き方を解説します。

SELECT文の基本

SELECT文は、データベースからデータを取得するために使用されるSQLのコマンドです。以下は、salesテーブルからすべてのデータを取得する基本的なクエリの例です。

SELECT * FROM sales;

特定の列を選択する

テーブルのすべての列ではなく、特定の列のみを選択することも可能です。例えば、product_nameとamount列を選択する場合のクエリは以下のようになります。

SELECT product_name, amount FROM sales;

WHERE句を使用して条件を指定する

WHERE句を使用すると、特定の条件に一致するデータのみを抽出できます。例えば、amountが200以上のデータを取得する場合のクエリは以下の通りです。

SELECT * FROM sales WHERE amount >= 200;

これらの基本を押さえることで、次に特定の年月を指定してデータを抽出するためのクエリを理解しやすくなります。

MySQLの日付関数で年月を取り出す

MySQLのDATE_FORMAT、YEAR、MONTHは年月の表示や集計に使えます。ただし、月の範囲検索では冒頭の半開区間を優先すると、日付列そのものの索引を利用しやすい形になります。関数名や書式文字列はDB製品ごとに異なります。

DATE_FORMAT関数を使用する

DATE_FORMAT関数を使うと、日付の形式を変更することができます。特定の年や月を抽出するためにこの関数を利用します。

SELECT * FROM sales 
WHERE DATE_FORMAT(sale_date, '%Y-%m') = '2023-01';

このクエリは、sale_dateが2023年1月のレコードをすべて取得します。

YEAR関数とMONTH関数を使用する

YEAR関数とMONTH関数を使用すると、日付の年や月の部分を抽出することができます。

SELECT * FROM sales 
WHERE YEAR(sale_date) = 2023 AND MONTH(sale_date) = 1;

このクエリは、同様に2023年1月のレコードを取得します。

BETWEEN句を使用する

BETWEEN句を使って、特定の年月の範囲を指定することもできます。

SELECT * FROM sales 
WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31';

sale_dateがDATE型であるこの例では1月31日も含みます。DATETIME/TIMESTAMP型へ流用すると上限が1月31日の午前0時として扱われ、月末の日中を落とし得ます。時刻付き列では翌月1日未満の条件を使ってください。

これらの方法を使えば、特定の年月に基づいてデータを効果的に抽出することができます。

実際のクエリの例

ここでは、具体的な例を用いて特定の年月を指定してデータを抽出するSQLクエリを示します。

例1: 特定の年月のデータを抽出

例えば、2023年1月の売上データを取得する場合、以下のクエリを使用します。

SELECT * FROM sales 
WHERE DATE_FORMAT(sale_date, '%Y-%m') = '2023-01';

このクエリは、sale_dateが2023年1月に一致するすべてのレコードを返します。

例2: 年と月を別々に指定してデータを抽出

同様に、YEAR関数とMONTH関数を使用して2023年1月のデータを抽出する方法です。

SELECT * FROM sales 
WHERE YEAR(sale_date) = 2023 AND MONTH(sale_date) = 1;

このクエリも2023年1月のすべてのレコードを返します。

例3: 日付範囲を指定してデータを抽出

BETWEEN句を使用して、2023年1月1日から2023年1月31日までのデータを取得する場合です。

SELECT * FROM sales 
WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31';

このクエリは、指定した期間内のすべてのレコードを抽出します。

例4: 月単位の集計データを抽出

特定の年月ごとにデータを集計する場合、以下のクエリを使用します。

SELECT DATE_FORMAT(sale_date, '%Y-%m') AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY month
ORDER BY month;

このクエリは、各月の売上合計を計算し、年月ごとに集計されたデータを返します。

これらのクエリを使えば、特定の年月に関連するデータを効果的に抽出できます。目的に応じて適切なクエリを選択してください。

応用例：特定の年月の範囲でデータを抽出

特定の年月の範囲でデータを抽出する方法について、いくつかの応用例を紹介します。これにより、複数の月や年にわたるデータ分析が可能になります。

例1: 複数の月にわたるデータを抽出

例えば、2023年1月から2023年3月までのデータを抽出する場合、以下のクエリを使用します。

SELECT * FROM sales 
WHERE sale_date >= '2023-01-01'
  AND sale_date < '2023-04-01';

このクエリは、2023年1月1日から2023年3月31日までの期間におけるすべてのレコードを取得します。

例2: 複数の年にわたる特定の月のデータを抽出

例えば、毎年1月のデータを抽出する場合、以下のクエリを使用します。

SELECT * FROM sales 
WHERE MONTH(sale_date) = 1;

このクエリは、すべての年の1月に一致するレコードを抽出します。

例3: 動的な範囲でデータを抽出

動的な範囲を使用して、現在の日付から過去6ヶ月間のデータを抽出する場合、以下のクエリを使用します。

SELECT * FROM sales 
WHERE sale_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
  AND sale_date < DATE_ADD(CURDATE(), INTERVAL 1 DAY);

この例は6か月前の同日から今日までを含み、明日以降のデータを除きます。「直近6つの完了した暦月」ではありません。時刻付き列で現在時刻までに限定する場合は、CURDATEではなくNOWを使うなど境界の定義を変えてください。

例4: 年ごとの特定の月のデータを抽出して集計

例えば、各年の1月の売上合計を抽出して集計する場合、以下のクエリを使用します。

SELECT YEAR(sale_date) AS year, SUM(amount) AS total_sales
FROM sales
WHERE MONTH(sale_date) = 1
GROUP BY year
ORDER BY year;

このクエリは、各年の1月の売上合計を計算し、年ごとに集計されたデータを返します。

これらの応用例を使えば、特定の期間にわたるデータ分析が効率的に行えます。必要に応じて適切なクエリを選択し、データ分析に役立ててください。

まとめ

SQLで特定の年月を指定してデータを抽出する方法について解説しました。DATE関数やWHERE句を活用することで、特定の年や月、さらには範囲を指定してデータを効果的に抽出できます。また、応用例として複数の月や年にわたるデータ分析の方法も紹介しました。これらの技術を駆使して、必要なデータを的確に取得し、データ分析やレポート作成に役立ててください。

索引・タイムゾーン・集計で見落としやすい点

DATE_FORMAT(sale_date, …)などで列側を加工すると、通常のsale_date索引による範囲検索を使いにくくなる場合があります。半開区間でも必ず索引が選ばれるわけではないため、既存索引、件数、選択率とEXPLAINを確認します。集計のSELECTやGROUP BYで日付関数を使うことと、WHEREの列を加工することは分けて考えます。

TIMESTAMPはMySQLのセッションタイムゾーンとUTC変換の影響を受けます。DATETIMEは自動的にUTCへ変換される型ではありません。業務の「1月」を日本時間で定義し、保存がUTCなら、開始・終了境界を同じ時間基準へ変換してから比較します。列全体へ変換関数をかける前に、境界側で変換できないか検討してください。

月別集計のサンプルデータなら、2023-01は350.00、2023-02は450.00、2023-03は200.00になる計算です。売上のない月はGROUP BYだけでは0円の行として出ないため、必要ならカレンダー表とLEFT JOINで補います。これはサンプル値からの期待値で、利用先DBでの実行結果ではありません。

公式資料：MySQLの日付・時刻関数／範囲検索の最適化／DATE・DATETIME・TIMESTAMPの違い。掲載SQLは利用環境で実行検証していません。