Zenn
💡

【SQL】期間別に抽出して集計（契約期間が４ヶ月以上、１ヶ月以上４ヶ月未満）

2022/04/03に公開
MySQL
SQL
tech

概要

職業紹介事業報告書を提出する際に、
職種、期間毎にデータを抽出する必要があったので
メモとして残しておく。

前提条件

  • DB：MySQL
  • テーブル：jobs(募集)
    • カラム：contract_start_date(契約開始)
    • カラム：contract_end_date(契約終了)
    • カラム：number_of_recruitment(採用人数)

必要なデータ

  • 職種、期間別の募集人数
    • 臨時と日雇いは人日（契約日数✖︎採用人数）で表示
    • ※職種のグループ化は解説無し

期間区分

  • 常用：契約期間が４ヶ月以上
  • 臨時：契約期間が１ヶ月以上４ヶ月未満
  • 日雇い：契約期間が１ヶ月未満

以下の画像のような形（一部抜粋）

期間別集計の手順

DATE_ADDを使用して、以下の条件を満たすSQLを作成。

  • 常用：契約期間が４ヶ月以上
  • 臨時：契約期間が１ヶ月以上４ヶ月未満
  • 日雇い：契約期間が１ヶ月未満

DATE_ADDを使用すると、
以下のような形で対象の日付に加算することができる。

DATE_ADD使用方法
# 1日後
DATE_ADD('2022-01-01', INTERVAL 1 DAY)
# 2022-01-02

# 2ヶ月後
DATE_ADD('2022-01-01', INTERVAL 2 MONTH)
# 2022-03-01

参考：MySQL 日付および時間関数

常用：契約期間が４ヶ月以上

契約期間が４ヶ月以上　＝　契約開始日＋4ヶ月 <= 契約終了日

4ヶ月以上
DATE_ADD(jobs.contract_start_date, INTERVAL 4 MONTH) <= jobs.contract_end_date

条件に当てはまっている場合は、採用人数（number_of_recruitment）、
当てはまっていない場合は0を取得するSQLを作成

4ヶ月以上
case
    when DATE_ADD(jobs.contract_start_date, INTERVAL 4 MONTH) <= jobs.contract_end_date
    then jobs.number_of_recruitment
    else 0
end

臨時：契約期間が１ヶ月以上４ヶ月未満

契約期間が１ヶ月以上４ヶ月未満　＝　契約開始日+1ヶ月 <= 契約終了日 AND 契約開始日+4ヶ月 > 契約終了日
契約期間が１ヶ月以上４ヶ月未満の採用人数

契約期間が１ヶ月以上４ヶ月未満の採用人数
case
    when DATE_ADD(jobs.contract_start_date, INTERVAL 4 MONTH) > jobs.contract_end_date
AND DATE_ADD(jobs.contract_start_date, INTERVAL 1 MONTH) <= jobs.contract_end_date then jobs.number_of_recruitment
    else 0
end
契約期間が１ヶ月以上４ヶ月未満の契約日数
case
    when DATE_ADD(jobs.contract_start_date, INTERVAL 4 MONTH) > jobs.contract_end_date
AND DATE_ADD(jobs.contract_start_date, INTERVAL 1 MONTH) <= jobs.contract_end_date then DATEDIFF(jobs.contract_end_date, jobs.contract_start_date)
    else 0
end

日雇い：契約期間が１ヶ月未満

臨時と同様に、契約日数と採用人数を取得。

契約期間が１ヶ月未満
case
    when DATE_ADD(jobs.contract_start_date, INTERVAL 1 MONTH) > jobs.contract_end_date then jobs.number_of_recruitment
    else 0
end

case
    when DATE_ADD(jobs.contract_start_date, INTERVAL 1 MONTH) > jobs.contract_end_date then DATEDIFF(jobs.contract_end_date, jobs.contract_start_date)
    else 0
end

職種でグループ化して、SUMで集計。

人日（契約日数✖︎採用人数）に関しては、
契約日数と採用人数をそれぞれ抽出後、
アプリ側で計算する処理にしている。
（SQLでまとめる方法がわからなかったため...orz）

集計
SELECT
    SUM(jobs.number_of_recruitment) as total_recruitment,
    SUM(
        case
            when DATE_ADD(jobs.contract_start_date, INTERVAL 4 MONTH) <= jobs.contract_end_date
        then jobs.number_of_recruitment
            else 0
        end
    ) as permanent_recruitment,
    SUM(
        case
            when DATE_ADD(jobs.contract_start_date, INTERVAL 4 MONTH) > jobs.contract_end_date
        AND DATE_ADD(jobs.contract_start_date, INTERVAL 1 MONTH) <= jobs.contract_end_date then jobs.number_of_recruitment
            else 0
        end
    ) as temporary_recruitment,
    SUM(
        case
            when DATE_ADD(jobs.contract_start_date, INTERVAL 4 MONTH) > jobs.contract_end_date
        AND DATE_ADD(jobs.contract_start_date, INTERVAL 1 MONTH) <= jobs.contract_end_date then DATEDIFF(jobs.contract_end_date, jobs.contract_start_date)
            else 0
        end
    ) as temporary_recruitment_contract_days,
    SUM(
        case
            when DATE_ADD(jobs.contract_start_date, INTERVAL 1 MONTH) > jobs.contract_end_date then jobs.number_of_recruitment
            else 0
        end
    ) as daily_recruitment,
    SUM(
        case
            when DATE_ADD(jobs.contract_start_date, INTERVAL 1 MONTH) > jobs.contract_end_date then DATEDIFF(jobs.contract_end_date, jobs.contract_start_date)
            else 0
        end
    ) as daily_recruitment_contract_days
FROM
    `jobs`
GROUP BY
    `職種コード`

Discussion