比率を計算するSQL例

プロジェクト毎の従業員の工数を月ごとに見たい場合を例に挙げる。
本来は欲しいデータを取得する時、SQL１回が望ましいので、１回で取得を試みる。

以下のテーブルを考える。
(前回、環境準備で、migrateで下記テーブルは作成済み)

CREATE TABLE `project_works` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `employee_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `project_code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `work_date` date NOT NULL,
  `work_hours` decimal(5,2) NOT NULL,
  `budget_actual_flag` tinyint(4) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

sqlで行うこと

特定の月の特定の従業員がプロジェクトに費やした時間と、その従業員がその月に全体として費やした時間との比率を計算

各従業員のプロジェクト毎の月毎の作業時間を求める(sum_hours)

各従業員の月毎の作業時間を求める(total_hours)

2つの結果を結合して比率を計算する。

予定と実績を取得するにあたってUNION ALLを使用する

CTEでサブクエリーをわかりやすくする(今回はMYSQL5.7を使用しているため簡略化)

（注意)
group by句には、selectでつけた別名は使えない
よって、サブクエリで別名を指定して使用する。

上記を１回で取得するSQL例として以下のようになる。

-- 実績値
(
    SELECT
        -- sum_hoursサブクエリから各フィールドを選択
        sum_hours.employee_id,
        sum_hours.Month as month,
        sum_hours.project_code,
        sum_hours.budget_actual_flag,
        sum_hours.total_work_hours,
        -- 各プロジェクトの作業時間を、その月の従業員の総作業時間で割ることで比率を算出
        ROUND(sum_hours.total_work_hours / total_hours.total_work_hours, 2) as ratio
    FROM
        (
            -- sum_hoursサブクエリでは、各従業員、プロジェクト、budget_actual_flag、月ごとの作業時間の合計を算出
            SELECT
                employee_id,
                project_code,
                budget_actual_flag,
                DATE_FORMAT(work_date, '%Y%m') as Month,
                SUM(work_hours) as total_work_hours
            FROM
                project_works
            WHERE
                work_date >= '2023-06-01' AND work_date <= '2023-12-30'
                and budget_actual_flag = 1
            GROUP BY
                employee_id,
                project_code,
                budget_actual_flag,
                DATE_FORMAT(work_date, '%Y%m')
        ) as sum_hours
    INNER JOIN
        (
            -- total_hoursサブクエリでは、各従業員、月ごとの総作業時間の合計を算出
            SELECT
                employee_id,
                DATE_FORMAT(work_date, '%Y%m') as Month,
                SUM(work_hours) as total_work_hours
            FROM
                project_works
            WHERE
                work_date >= '2023-06-01' AND work_date <= '2023-12-30'
                and budget_actual_flag = 1
            GROUP BY
                employee_id,
                DATE_FORMAT(work_date, '%Y%m')
        ) as total_hours
    ON
        -- 2つのサブクエリをemployee_idと月で結合
        sum_hours.employee_id = total_hours.employee_id AND
        sum_hours.Month = total_hours.Month
)
UNION ALL
-- 予定値
(
    SELECT
        -- sum_hoursサブクエリから各フィールドを選択
        sum_hours.employee_id,
        sum_hours.Month as month,
        sum_hours.project_code,
        sum_hours.budget_actual_flag,
        sum_hours.total_work_hours,
        -- 各プロジェクトの作業時間を、その月の従業員の総作業時間で割ることで比率を算出
        ROUND(sum_hours.total_work_hours / total_hours.total_work_hours, 2) as ratio
    FROM
        (
            -- sum_hoursサブクエリでは、各従業員、プロジェクト、budget_actual_flag、月ごとの作業時間の合計を算出
            SELECT
                employee_id,
                project_code,
                budget_actual_flag,
                DATE_FORMAT(work_date, '%Y%m') as Month,
                SUM(work_hours) as total_work_hours
            FROM
                project_works
            WHERE
                work_date >= '2023-06-01' AND work_date <= '2023-12-30'
                and budget_actual_flag = 0
            GROUP BY
                employee_id,
                project_code,
                budget_actual_flag,
                DATE_FORMAT(work_date, '%Y%m')
        ) as sum_hours
    INNER JOIN
        (
            -- total_hoursサブクエリでは、各従業員、月ごとの総作業時間の合計を算出
            SELECT
                employee_id,
                DATE_FORMAT(work_date, '%Y%m') as Month,
                SUM(work_hours) as total_work_hours
            FROM
                project_works
            WHERE
                work_date >= '2023-06-01' AND work_date <= '2023-12-30'
                and budget_actual_flag = 0
            GROUP BY
                employee_id,
                DATE_FORMAT(work_date, '%Y%m')
        ) as total_hours
    ON
        -- 2つのサブクエリをemployee_idと月で結合
        sum_hours.employee_id = total_hours.employee_id AND
        sum_hours.Month = total_hours.Month
)
ORDER BY
    employee_id,
    Month,
    budget_actual_flag,
    project_code;

・order by
UNIONで結合した結果セットに対してORDER BY句を使用する場合、それぞれのSELECT文で共通でなければならならない。

CTEを使用した場合

sum_hoursと、total_hoursで共通化でき、可読性がかなり高まる。
ただし、CTEはMySQLの場合8以上が必要になる。

WITH sum_hours AS (
    SELECT
        employee_id,
        project_code,
        budget_actual_flag,
        DATE_FORMAT(work_date, '%Y%m') as Month,
        SUM(work_hours) as total_work_hours
    FROM
        project_works
    WHERE
        work_date >= '2023-06-01' AND work_date <= '2023-12-30'
    GROUP BY
        employee_id,
        project_code,
        budget_actual_flag,
        DATE_FORMAT(work_date, '%Y%m')
),
total_hours AS (
    SELECT
        employee_id,
        DATE_FORMAT(work_date, '%Y%m') as Month,
        SUM(work_hours) as total_work_hours
    FROM
        project_works
    WHERE
        work_date >= '2023-06-01' AND work_date <= '2023-12-30'
    GROUP BY
        employee_id,
        DATE_FORMAT(work_date, '%Y%m')
),
actual AS (
    SELECT
        sum_hours.employee_id,
        sum_hours.Month,
        sum_hours.project_code,
        sum_hours.budget_actual_flag,
        sum_hours.total_work_hours,
        ROUND(sum_hours.total_work_hours / total_hours.total_work_hours, 2) as ratio
    FROM
        sum_hours
    INNER JOIN
        total_hours
    ON
        sum_hours.employee_id = total_hours.employee_id AND
        sum_hours.Month = total_hours.Month
    WHERE
        sum_hours.budget_actual_flag = 1
),
budget AS (
    SELECT
        sum_hours.employee_id,
        sum_hours.Month,
        sum_hours.project_code,
        sum_hours.budget_actual_flag,
        sum_hours.total_work_hours,
        ROUND(sum_hours.total_work_hours / total_hours.total_work_hours, 2) as ratio
    FROM
        sum_hours
    INNER JOIN
        total_hours
    ON
        sum_hours.employee_id = total_hours.employee_id AND
        sum_hours.Month = total_hours.Month
    WHERE
        sum_hours.budget_actual_flag = 0
)
SELECT * FROM actual
UNION ALL
SELECT * FROM budget
ORDER BY
    employee_id,
    Month,
    budget_actual_flag,
    project_code;