誤差を求め、特定の値(filter)を取得する


比率をSQLやLaravelにて計算し出力した。
[LaravelでのDB操作](https://qiita.com/kenji123/items/f61cb1037b10c31de255)
ところで以下一部記載であるが、total_ratioが必ず１になるとは限らない。
合計を必ず１になるように誤差を何らかの基準で対応する。
| employee_id | month | budget_actual_flag | total_ratio |
|---------|---------|---------|---------|
| EMP001  | 202306  | 1  |1.00  |
| EMP001  | 202307  | 1  |1.00  |
| EMP001  | 202308  | 1  |0.99  |


一応上記total_ratioを出力したsqlを記載する。
```sql
SELECT 
    employee_id, 
    month, 
    budget_actual_flag, 
    SUM(ratio) as total_ratio
FROM
    (
        SELECT
            
            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
            (
                
                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 or budget_actual_flag = 1)
                GROUP BY
                    employee_id,
                    project_code,
                    budget_actual_flag,
                    DATE_FORMAT(work_date, '%Y%m')
            ) as sum_hours
        INNER JOIN
            (
                
                SELECT
                    employee_id,
                    DATE_FORMAT(work_date, '%Y%m') as Month,
                    budget_actual_flag,
                    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 or budget_actual_flag = 1)
                GROUP BY
                    employee_id,
                    DATE_FORMAT(work_date, '%Y%m'),
                    budget_actual_flag
            ) as total_hours
        ON
            
            sum_hours.employee_id = total_hours.employee_id AND
            sum_hours.Month = total_hours.Month AND
            sum_hours.budget_actual_flag = total_hours.budget_actual_flag
    ) as inner_query
GROUP BY
    employee_id,
    month,
    budget_actual_flag;
```

ただ、sqlが複雑になってきていることから今回はbudget_actual_flag=1の時に限定し、誤差対応する。


### モデル
LaravelでDBファザードのDB::selectで対応で、budget_actual_flagで実績、予算両方出していたが、今回は実績のみ１(budget_actual_flag)とする。

```php
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class ProjectWork extends Model
{
    use HasFactory;

    protected $table = 'project_works';

    public static function getWorkHoursPerProjectInAMonth($startDate, $endDate)
    {
         return DB::select(
            "
            -- 実績値
            (
                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 >= ? AND work_date <= ?
                            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 >= ? AND work_date <= ?
                            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
                ORDER BY
                    sum_hours.employee_id,
                    sum_hours.Month,
                    sum_hours.project_code
            )
            ",
            [
                $startDate,
                $endDate,
                $startDate,
                $endDate,
            ]

        );
    }
}
```

### コントローラ
```php
<?php

namespace App\Http\Controllers;

use App\Models\Models\ProjectWork;
use Illuminate\Support\Facades\DB;

class ProjectWorkController extends Controller
{
    public function index()
    {

        $results = ProjectWork::getWorkHoursPerProjectInAMonth('2023-06-11  ', '2023-08-30');

    }
}


```
上記$result結果に対し、ratioを見直す。
以下のように誤差に対応する。
Laravelのコレクション機能を使い、employee_id、month毎にまとめる。
mapメソッドを使い、ratioにアクセスし、ratioをまとめて、１になっていないものを最後のratioで１になるように辻褄を合わせている。

```php
        $results = collect($results)->groupBy(['employee_id', 'month']);

        $results->map(function ($items) {
            return $items->map(function ($item) {
                $values = $item->map(function ($i) {
                    return $i->ratio;
                })->all();
                if (array_sum($values) !== 1.0) {
                    $values[count($values) - 1] = round(1 - array_sum(array_slice($values, 0, -1)), 2);
                }
                // dump($values);
                $item->map(function ($i, $key) use ($values) {
                    $i->ratio = $values[$key];
                    return $i;
                });
            });
        });
```

引き続き特定のプロジェクトを取得する。
mapメソッドでネストし、filterメソッドで、特定のプロジェクトのみreturnする。

```php
        $specificPid = 'PRJ001';
        $filteredResults = $results->map(function ($months, $hid) use ($specificPid) {
            return $months->map(function ($items, $month) use ($specificPid) {
                return $items->filter(function ($item) use ($specificPid) {
                    return $item->project_code == $specificPid;
                });
            });
        });

```

コレクションでemployee_id、month毎にまとめたものを元に戻す。
コレクションを多次元にしたものをflattenで戻し、toArrayメソッドでコレクション型を配列にしている。
```
     $flattenedResults = $filteredResults->flatten(2)->toArray();
```


コントローラを整理すると以下になる。
```php
<?php

namespace App\Http\Controllers;

use App\Models\Models\ProjectWork as ModelsProjectWork;
use Illuminate\Http\Request;
use App\Models\Models\ProjectWork;
use Illuminate\Support\Facades\DB;

class ProjectWorkController extends Controller
{
    public function index()
    {

        $results = ProjectWork::getWorkHoursPerProjectInAMonth('2023-06-11  ', '2023-08-30');

      $results = collect($results)->groupBy(['employee_id', 'month']);

        $results->map(function ($items) {
            return $items->map(function ($item) {
                $values = $item->map(function ($i) {
                    return $i->ratio;
                })->all();
                if (array_sum($values) !== 1.0) {
                    $values[count($values) - 1] = round(1 - array_sum(array_slice($values, 0, -1)), 2);
                }
                // dump($values);
                $item->map(function ($i, $key) use ($values) {
                    $i->ratio = $values[$key];
                    return $i;
                });
            });
        });

        $specificPid = 'PRJ001';
        $filteredResults = $results->map(function ($months, $hid) use ($specificPid) {
            return $months->map(function ($items, $month) use ($specificPid) {
                return $items->filter(function ($item) use ($specificPid) {
                    return $item->project_code == $specificPid;
                });
            });
        });

        $flattenedResults = $filteredResults->flatten(2)->toArray();

    dd($flattenedResults);

    }
}

```

$resultの状態
```bash
array:140 [▼ // app/Http/Controllers/ProjectWorkController.php:16
  0 => {#297 ▶
    +"employee_id": "EMP001"
    +"month": "202307"
    +"project_code": "PRJ001"
    +"budget_actual_flag": 1
    +"total_work_hours": "4.00"
    +"ratio": "0.20"
  }
  1 => {#299 ▶
    +"employee_id": "EMP001"
    +"month": "202307"
    +"project_code": "PRJ002"
    +"budget_actual_flag": 1
    +"total_work_hours": "14.00"
    +"ratio": "0.70"
  }
  2 => {#300 ▶
    +"employee_id": "EMP001"
    +"month": "202307"
    +"project_code": "PRJ003"
    +"budget_actual_flag": 1
    +"total_work_hours": "2.00"
    +"ratio": "0.10"
  }
  3 => {#301 ▶
    +"employee_id": "EMP001"
    +"month": "202308"
    +"project_code": "PRJ001"
    +"budget_actual_flag": 1
    +"total_work_hours": "17.00"
    +"ratio": "1.00"
  }
  4 => {#302 ▶
    +"employee_id": "EMP002"
    +"month": "202307"
    +"project_code": "PRJ001"
    +"budget_actual_flag": 1
    +"total_work_hours": "27.00"
    +"ratio": "0.68"
  }
  5 => {#303 ▶
    +"employee_id": "EMP002"
    +"month": "202307"
    +"project_code": "PRJ002"
    +"budget_actual_flag": 1
    +"total_work_hours": "4.00"
    +"ratio": "0.10"
  }
  6 => {#304 ▶
    +"employee_id": "EMP002"
    +"month": "202307"
    +"project_code": "PRJ003"
    +"budget_actual_flag": 1
    +"total_work_hours": "7.00"
    +"ratio": "0.18"
  }
  7 => {#305 ▶
    +"employee_id": "EMP002"
    +"month": "202307"
    +"project_code": "PRJ005"
    +"budget_actual_flag": 1
    +"total_work_hours": "2.00"
    +"ratio": "0.05"
  }
  8 => {#306 ▶
    +"employee_id": "EMP002"
    +"month": "202308"
    +"project_code": "PRJ001"
    +"budget_actual_flag": 1
    +"total_work_hours": "8.00"
    +"ratio": "1.00"
  }

```