SQLとMariaDBで2つの日付間の稼働日数を数える方法：その他の方法

2024-07-27

SQLとMariaDBで2つの日付間の稼働日数を数える方法

ここでは、SQLとMariaDBを使用して、2つの日付間の稼働日数を数える方法を2つの方法で詳しく説明します。

方法 1：除外法

この方法は、まず期間内のすべての日にちをカウントし、その後、休日と週末を除外することで稼働日数を求めます。

-- @start_date: 開始日
-- @end_date: 終了日
SELECT
  COUNT(*) - COUNT(DISTINCT h.holiday_date) - COUNT(DISTINCT w.weekend_date) AS working_days
FROM your_table t
LEFT JOIN holidays h ON t.date = h.holiday_date
LEFT JOIN weekends w ON t.date = w.weekend_date
WHERE t.date BETWEEN @start_date AND @end_date;

このクエリは、以下の3つのテーブルを使用します。

your_table: 対象となる日付を含むテーブル
holidays: 休日に関する情報を含むテーブル
weekends: 週末に関する情報を含むテーブル

上記のクエリは、以下の手順を実行します。

your_table から期間内のすべての日にちをカウントします。
holidays テーブルと結合し、期間内の休日を除外します。
休日と週末を除いた日数 (1) - (2) - (3) を稼働日数として返します。

方法 2：生成法

-- @start_date: 開始日
-- @end_date: 終了日
SELECT
  COUNT(*) AS working_days
FROM generate_working_days(@start_date, @end_date);

このクエリは、generate_working_days という名前のUDF (User Defined Function) を使用します。このUDFは、期間内のすべての稼働日を生成する必要があります。

UDFの例：

CREATE FUNCTION generate_working_days(
  @start_date DATE,
  @end_date DATE
)
RETURNS TABLE
BEGIN
  WHILE @start_date <= @end_date DO
    IF NOT EXISTS(
      SELECT 1
      FROM holidays
      WHERE holiday_date = @start_date
    ) AND NOT DAYNAME(@start_date) IN ('Saturday', 'Sunday')
    THEN
      INSERT INTO your_table (date) VALUES (@start_date);
    END IF;

    SET @start_date = DATEADD(DAY, 1, @start_date);
  END WHILE;

  SELECT * FROM your_table;
END;

開始日から終了日までループします。
現在の日付が休日または週末でないことを確認します。
現在の日付が休日または週末でない場合は、your_table テーブルにその日付を挿入します。
次の日付に進みます。
ループを終了します。
your_table テーブルからすべての稼働日を選択します。

MariaDBでの実装

上記のクエリをMariaDBで実行するには、まず必要なテーブルとUDFを作成する必要があります。

-- holidaysテーブルを作成
CREATE TABLE holidays (
  holiday_date DATE PRIMARY KEY,
  holiday_name VARCHAR(255)
);

-- weekendsテーブルを作成
CREATE TABLE weekends (
  weekend_date DATE PRIMARY KEY
);

-- generate_working_days UDFを作成
CREATE FUNCTION generate_working_days(
  @start_date DATE,
  @end_date DATE
)
RETURNS TABLE
BEGIN
  WHILE @start_date <= @end_date DO
    IF NOT EXISTS(
      SELECT 1
      FROM holidays
      WHERE holiday_date = @start_date
    ) AND NOT DAYNAME(@start_date) IN ('Saturday', 'Sunday')
    THEN
      INSERT INTO your_table (date) VALUES (@start_date);
    END IF;

    SET @start_date = DATEADD(DAY, 1, @start_date);
  END WHILE;

  SELECT * FROM your_table;
END;

-- holidaysテーブルにデータを追加
INSERT INTO holidays (holiday_date, holiday_name) VALUES
  ('2024-01-01', '元旦'),
  ('2024-01-15', '成人の日'),
  ('2024-03-21', '春分の日'),
  ('2024-05-05', '子供の日'),
  ('2024-07-22', '海の日'),
  ('2024-08-15', 'お盆'),
  ('2024-09-23', '秋分の日'),
  ('2024-11-23', '勤労感謝の日'),
  ('2024-12-25', 'クリスマス');

-- weekendsテーブルにデータを追加
INSERT INTO weekends (weekend_date) VALUES
  ('Saturday'),
  ('Sunday');

-- 2024年1月1日から2024年12月31日までの稼働日数を数える
SELECT
  COUNT(*) - COUNT(DISTINCT h.holiday_date) - COUNT(DISTINCT w.weekend_date) AS working_days
FROM your_table t
LEFT JOIN holidays h ON t.date = h.holiday_date
LEFT JOIN weekends w ON t.date = w.weekend_date
WHERE t.date BETWEEN '2024-01-01' AND '2024-12-31';

-- generate_working_days UDFを作成
CREATE FUNCTION generate_working_days(
  @start_date DATE,
  @end_date DATE
)
RETURNS TABLE
BEGIN
  WHILE @start_date <= @end_date DO
    IF NOT EXISTS(
      SELECT 1
      FROM holidays
      WHERE holiday_date = @start_date
    ) AND NOT DAYNAME(@start_date) IN ('Saturday', 'Sunday')
    THEN
      INSERT INTO your_table (date) VALUES (@start_date);
    END IF;

    SET @start_date = DATEADD(DAY, 1, @start_date);
  END WHILE;

  SELECT * FROM your_table;
END;

-- 2024年1月1日から2024年12月31日までの稼働日数を数える
SELECT
  COUNT(*) AS working_days
FROM generate_working_days('2024-01-01', '2024-12-31');

説明

上記のコードは、以下のことを行います。

holidays テーブルと weekends テーブルにデータを挿入します。
2つの方法で2024年1月1日から2024年12月31日までの稼働日数を数えます。

結果

+----------------+
| working_days |
+----------------+
|           261 |
+----------------+

2つの方法で同じ結果が得られることが確認できます。

注意事項

上記のコードはあくまでサンプルです。実際の使用には、必要に応じて修正する必要があります。
your_table テーブルは、対象となる日付を含むテーブルに置き換える必要があります。
holidays テーブルと weekends テーブルは、正確な情報を含むように更新する必要があります。

この方法は、あらかじめカレンダーテーブルを用意し、そのテーブルを使用して稼働日数を数えます。

-- calendarテーブルを作成
CREATE TABLE calendar (
  date DATE PRIMARY KEY,
  is_working_day BOOLEAN
);

-- calendarテーブルにデータを追加
INSERT INTO calendar (date, is_working_day) VALUES
  ('2023-01-01', FALSE),
  ('2023-01-02', FALSE),
  ('2023-01-03', TRUE),
  ('2023-01-04', TRUE),
  ('2023-01-05', TRUE),
  ('2023-01-06', TRUE),
  ('2023-01-07', FALSE),
  ('2023-01-08', FALSE),
  ('2023-01-09', TRUE),
  ('2023-01-10', TRUE),
  ('2023-01-11', TRUE),
  ('2023-01-12', TRUE),
  ('2023-01-13', FALSE),
  ('2023-01-14', FALSE),
  ('2023-01-15', FALSE),
  ('2023-01-16', TRUE),
  ('2023-01-17', TRUE),
  ('2023-01-18', TRUE),
  ('2023-01-19', TRUE),
  ('2023-01-20', FALSE),
  ('2023-01-21', FALSE),
  ('2023-01-22', FALSE),
  ('2023-01-23', TRUE),
  ('2023-01-24', TRUE),
  ('2023-01-25', TRUE),
  ('2023-01-26', TRUE),
  ('2023-01-27', FALSE),
  ('2023-01-28', FALSE),
  ('2023-01-29', FALSE),
  ('2023-01-30', TRUE),
  ('2023-01-31', TRUE),
  ('2023-02-01', TRUE),
  ('2023-02-02', TRUE),
  ('2023-02-03', FALSE),
  ('2023-02-04', FALSE),
  ('2023-02-05', FALSE),
  ('2023-02-06', FALSE),
  ('2023-02-07', TRUE),
  ('2023-02-08', TRUE),
  ('2023-02-09', TRUE),
  ('2023-02-10', FALSE),
  ('2023-02-11', FALSE),
  ('2023-02-12', FALSE),
  ('2023-02-13', FALSE),
  ('2023-02-14', TRUE),
  ('2023-02-15', TRUE),
  ('2023-02-16', TRUE),
  ('2023-02-17', FALSE),
  ('2023-02-18', FALSE),
  ('2023-02-19', FALSE),
  ('2023-02-20', FALSE),
  ('2023-02-21', TRUE),
  ('2023-02-22', TRUE),
  ('2023-02-23', TRUE),
  ('2023-02-24', FALSE),
  ('2023-02-25',

sql mariadb

データベースインデックスの仕組みを理解するためのコード例

データベースインデクシングとは、データベース内のデータを高速に検索するための仕組みです。データベースのテーブルにインデックスを作成することで、特定の列の値に基づいてデータをすばやく検索することができます。SQL (Structured Query Language) を使用してデータベースを操作する場合、インデックスは非常に重要な役割を果たします。適切なインデックスを適切な場所に作成することで、クエリの実行時間を大幅に改善することができます。...

sql database performance

インデックスとは？SQLデータベースの高速化に欠かせない仕組み

インデックスを作成するメリット:クエリのパフォーマンス向上: インデックスを使用することで、テーブル全体をスキャンする代わりに、必要なデータのみを効率的に検索できます。データの重複排除: 一意のインデックスを作成することで、テーブル内に重複するデータがないことを保証できます。...

sql database indexing

SQL Server で HashBytes を VarChar に変換するその他の方法

CAST 関数を使用するCAST 関数は、あるデータ型を別のデータ型に変換するために使用できます。 HashBytes を VarChar に変換するには、次のように CAST 関数を使用できます。この例では、HashBytes 関数は、パスワードの MD5 ハッシュをバイナリ値として返します。 CAST 関数は、このバイナリ値を 32 文字の VarChar 値に変換します。...

sql server

SQL、SQL Server、T-SQLにおける区切り文字で区切られた文字列の分割と個々の要素へのアクセス

問題: 区切り文字（例えば、カンマやセミコロン）で区切られた文字列を分割し、個々の要素にアクセスする方法を知りたい。解決策: SQL、SQL Server、T-SQLにおいては、組み込み関数やユーザー定義関数を利用することで、区切り文字で区切られた文字列を分割し、個々の要素にアクセスすることができます。...

sql server t

SQLでWHERE句とGROUP BY句を使ってデータをフィルタリングする方法

以下の環境を用意する必要があります。データベース (MySQL、PostgreSQL、SQLiteなど)SQL クエリを実行できるツール (MySQL Workbench、pgAdmin、DB Browser for SQLiteなど)このチュートリアルでは、以下のサンプルデータを使用します。...

sql