比率を計算するSQL例(複数回で取得)

前回で1回のsqlに分けて行うパターンを行った。
ただ、メンテナンス等考えると複数回の方がわかりやすい場面もある。
以下は、比率の計算のところで、実績と予定を結合してから比率を計算する方法を記載する。
今回はDBファザードのクエリビルダを使用している。
コントローラで前回箇所を以下に書き換えている。

ProjectWorkController.php

        // 実績（budget_actual_flag = 1）の取得
        $actualSumHoursQuery = DB::table('project_works')
            ->select(DB::raw("employee_id,
            DATE_FORMAT(work_date, '%Y%m') as Month, project_code, budget_actual_flag, SUM(work_hours) as total_work_hours"))
            ->whereBetween('work_date', ['2023-06-01', '2023-12-30'])
            ->where('budget_actual_flag', 1)
            ->groupBy('employee_id', 'project_code', 'budget_actual_flag', DB::raw("DATE_FORMAT(work_date, '%Y%m')"))
            ->orderBy('employee_id')
            ->orderBy(DB::raw("DATE_FORMAT(work_date, '%Y%m')"))
            ->orderBy('project_code')
            ->orderBy('budget_actual_flag', 'asc')
            ->get();

        // total_hoursの結果を取得（実績）
        $actualTotalHoursQuery = DB::table('project_works')
            ->select(DB::raw("employee_id,
            DATE_FORMAT(work_date, '%Y%m') as Month,budget_actual_flag,  SUM(work_hours) as total_work_hours"))
            ->whereBetween('work_date', ['2023-06-01', '2023-12-30'])
            ->where('budget_actual_flag', 1)
            ->groupBy('employee_id', 'budget_actual_flag', DB::raw("DATE_FORMAT(work_date, '%Y%m')"))
            ->orderBy('employee_id')
            ->orderBy(DB::raw("DATE_FORMAT(work_date, '%Y%m')"))
            ->get();

        // 予定（budget_actual_flag = 0）の取得
        $plannedSumHoursQuery = DB::table('project_works')
            ->select(DB::raw("employee_id,
            DATE_FORMAT(work_date, '%Y%m') as Month, project_code, budget_actual_flag, SUM(work_hours) as total_work_hours"))
            ->whereBetween('work_date', ['2023-06-01', '2023-12-30'])
            ->where('budget_actual_flag', 0)
            ->groupBy('employee_id', 'project_code', 'budget_actual_flag', DB::raw("DATE_FORMAT(work_date, '%Y%m')"))
            ->orderBy('employee_id')
            ->orderBy(DB::raw("DATE_FORMAT(work_date, '%Y%m')"))
            ->orderBy('project_code')
            ->orderBy('budget_actual_flag', 'asc')
            ->get();

        // total_hoursの結果を取得（予定）
        $plannedTotalHoursQuery = DB::table('project_works')
            ->select(DB::raw("employee_id,
            DATE_FORMAT(work_date, '%Y%m') as Month,budget_actual_flag, SUM(work_hours) as total_work_hours"))
            ->whereBetween('work_date', ['2023-06-01', '2023-12-30'])
            ->where('budget_actual_flag', 0)
            ->groupBy('employee_id', 'budget_actual_flag', DB::raw("DATE_FORMAT(work_date, '%Y%m')"))
            ->orderBy('employee_id')
            ->orderBy(DB::raw("DATE_FORMAT(work_date, '%Y%m')"))
            ->get();


        //以下並び替えはできているが、ソートするならば以下のように記載する
        $actualSumHoursQuerySorted = $actualSumHoursQuery->sortBy(['employee_id', 'Month', 'project_code']);
        $plannedSumHoursQuerySorted = $plannedSumHoursQuery->sortBy(['employee_id', 'Month', 'project_code']);

        // Concatenate and re-index
        $merged = $actualSumHoursQuerySorted->concat($plannedSumHoursQuerySorted)->values();


        // 全従業員の各月の合計労働時間を格納する配列を初期化
        $totalHours = [];

        foreach ($actualTotalHoursQuery as $row) {
            // 集約のキーを作成
            $key = $row->employee_id . '-' . $row->Month . '-' . $row->budget_actual_flag;
            $totalHours[$key] = $row->total_work_hours;
        }

        foreach ($plannedTotalHoursQuery as $row) {
            // 集約のキーを作成
            $key = $row->employee_id . '-' . $row->Month . '-' . $row->budget_actual_flag;
            $totalHours[$key] = $row->total_work_hours;
        }

        // 比率を計算
        foreach ($merged as $row) {
            $key = $row->employee_id . '-' . $row->Month . '-' . $row->budget_actual_flag;
            if (isset($totalHours[$key])) {
                $row->ratio = round($row->total_work_hours / $totalHours[$key], 2);
            } else {
                $row->ratio = 0;
            }
        }

        dd($merged);

今後活用できる点は、
1.２つの結果を結合する。
2.集約する場合キーを作成する。
3.比率を集約キーをもとに計算し、1の配列に追加し計算する。

ただ、計算過程をプログラムで対応する分面倒くさい。

(結果例)

Illuminate\Support\Collection {#1234 ▼ // app/Http/Controllers/ProjectWorkController.php:98
  #items: array:777 [▶
    0 => {#303 ▶
      +"employee_id": "EMP001"
      +"Month": "202306"
      +"project_code": "PRJ001"
      +"budget_actual_flag": 1
      +"total_work_hours": "9.00"
      +"ratio": 0.41
    }
    1 => {#305 ▶
      +"employee_id": "EMP001"
      +"Month": "202306"
      +"project_code": "PRJ002"
      +"budget_actual_flag": 1
      +"total_work_hours": "10.00"
      +"ratio": 0.45
    }
    2 => {#306 ▶
      +"employee_id": "EMP001"
      +"Month": "202306"
      +"project_code": "PRJ003"
      +"budget_actual_flag": 1
      +"total_work_hours": "2.00"
      +"ratio": 0.09
    }
    3 => {#304 ▶
      +"employee_id": "EMP001"
      +"Month": "202306"
      +"project_code": "PRJ004"
      +"budget_actual_flag": 1
      +"total_work_hours": "1.00"
      +"ratio": 0.05
    }
    4 => {#307 ▶
      +"employee_id": "EMP001"
      +"Month": "202307"
      +"project_code": "PRJ001"
      +"budget_actual_flag": 1
      +"total_work_hours": "4.00"
      +"ratio": 0.2
    }
    5 => {#308 ▶
      +"employee_id": "EMP001"
      +"Month": "202307"
      +"project_code": "PRJ002"
      +"budget_actual_flag": 1
      +"total_work_hours": "14.00"
      +"ratio": 0.7
    }

DBファザード、クエリビルダ、Eloquentの違い

以下で一旦確認。