LoginSignup
0
0

More than 1 year has passed since last update.

@kenji123

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

Last updated at Posted at 2023-07-17

比率をSQLやLaravelにて計算し出力した。
LaravelでのDB操作
ところで以下一部記載であるが、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を記載する。

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

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

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で１になるように辻褄を合わせている。

        $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する。

        $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

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の状態

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"
  }
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0