MySQL, SQL, PHP

Laravel SQLを実行ヒアドキュメントクロス集計

2020年5月6日2020年5月13日 | by 優

Laravel

手の込んだ集計などはSQLで取った方がシンプルになることも多い

ヒアドキュメントを利用して記述するのが良いですね🐱

<?php


namespace App\Services;

use App\Services\Service;
use Illuminate\Support\Facades\DB;

class GetsectionParentGoalService extends Service
{
    const MY_section_PARENT_INDEX = 0; // 集計部署取得クエリのINDEX

    /**
     * 集計対象部署の半期のKPIを返却
     *
     * @param int $sectionParentId
     * @param int $year
     * @param string $period
     * @return object(stdClass)
     */
    public function execute($sectionParentId, $year, $period)
    {
        $sql = <<< SQL
            SELECT
              SUM(kpi1) AS kpi1,
              SUM(kpi2) AS kpi2,
              SUM(kpi3) AS kpi3,
              SUM(kpi4) AS kpi4,
              SUM(kpi5) AS kpi5,
              SUM(kpi6) AS kpi6,
              SUM(kpi7) AS kpi7,
              SUM(kpi8) AS kpi8,
              SUM(kpi9) AS kpi9,
              SUM(kpi10) AS kpi10,
              SUM(kpi11) AS kpi11,
              SUM(kpi12) AS kpi12
            FROM
              tb_section_kpi
            WHERE tb_section_kpi.section_id IN
            (
              SELECT DISTINCT
                tb_section.section_id
              FROM
                tb_section
                  JOIN
                tb_section_parent
                  ON tb_section.section_parent_id = ?
              WHERE
                tb_section.deleted = 0
              AND
                tb_section.year = ?
              AND
                tb_section.period = ?
            )
SQL;

        $result = DB::select($sql, [
                                    $sectionParentId,    // ON tb_section.section_parent_id = {$sectionParentId}
                                    $year,               // tb_section.year = '{$year}'
                                    $period,             // tb_section.period = '{$period}'
                                ]);

        return $result[self::MY_section_PARENT_INDEX];
    }
}

終端文字の後に１行空行を入れること
この場合だとSQL;
終端文字の行の前後に文字が入らないようにする
→左端にくっつきます。
結果はarray+stdClassで取得されます
ヒアドキュメント内の変数
・int型は{$変数}でくくります。
・string型は”{$変数}”でくくります。

SQLは良いぞ🐱

ORMも良いけど集計処理なんかは
ORMに固執するとforループにifで森になりがち😨
毒も栄養も喰らうのだ😺

【Laravel SQLを実行ヒアドキュメントクロス集計】https://t.co/GMwvJqej4i #progate #駆け出しエンジニアと繋がりたい #Laravel #ORM

— 優さん🌷わくわく開発YouTuber (@yuu13n6) May 7, 2020

もくじ

クロス集計やったった

<?php

namespace App\Services;

use App\Services\Service;
use Illuminate\Support\Facades\DB;

class GetDepartParentProspectService extends Service
{
    const MY_SECTION_PROSPECT_PARENT_INDEX = 0; // 集計部署見込み取得クエリのINDEX

    /**
     * 親部署の売上を返却
     *
     * @param int $sectionParentId
     * @param int $year
     * @param string $period
     * @return object(stdClass)
     */
    public function execute($sectionParentId, $year, $period)
    {
        $CONST_PLAN_STATUS_BEFORE_ESTIMATE = 1; // 見積もり発行前
        $CONST_PLAN_STATUS_LOW_ACCURACY = 2;    // 受注前低確度
        $CONST_PLAN_STATUS_HIGH_ACCURACY = 3;   // 受注前高確度(内示メール添付必須)
        $CONST_PLAN_STATUS_ORDER_COMPLETE = 4;  // 受注確定
        $CONST_PLAN_STATUS_MAINTENANCE = 5;     // 保守

        $yearJanuary = "{$year}-01";
        $yearFebruary = "{$year}-02";
        $yearMarch = "{$year}-03";
        $yearApril = "{$year}-04";
        $yearMay = "{$year}-05";
        $yearJune = "{$year}-06";
        $yearJuly = "{$year}-07";
        $yearAugust = "{$year}-08";
        $yearSeptember = "{$year}-09";
        $yearOctober = "{$year}-10";
        $yearNovember = "{$year}-11";
        $yearDecember = "{$year}-12";


        $sql = <<< SQL
        SELECT
          SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS January,
          SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS February,
          SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS March,
          SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS April,
          SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS May,
          SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS June,
          SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS July,
          SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS August,
          SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS September,
          SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS October,
          SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS November,
          SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS December
        FROM
          (
            SELECT
              tb_plan.plan_total_price,
              tb_plan.section_id,
              tb_plan.end_time
            FROM
              tb_plan
                JOIN
              tb_plan_source
                ON tb_plan.plan_id = tb_plan_source.plan_id
            WHERE
              tb_plan.plan_status_id = ?
            OR
              tb_plan.plan_status_id = ?
            OR
              tb_plan.plan_status_id = ?
            OR
              tb_plan.plan_status_id = ?
            OR
              tb_plan.plan_status_id = ?
          ) AS d
        WHERE
          d.section_id IN
          (
              SELECT DISTINCT
                tb_section.section_id
              FROM
                tb_section
                  JOIN
                tb_section_parent
                  ON tb_section.section_parent_id = ?
              WHERE
                tb_section.deleted = 0
              AND
                tb_section.year = ?
              AND
                tb_section.period = ?
          )
SQL;

        $result = DB::select($sql, [
                                $yearJanuary,
                                $yearFebruary,
                                $yearMarch,
                                $yearApril,
                                $yearMay,
                                $yearJune,
                                $yearJuly,
                                $yearAugust,
                                $yearSeptember,
                                $yearOctober,
                                $yearNovember,
                                $yearDecember,
                                $CONST_PLAN_STATUS_BEFORE_ESTIMATE,
                                $CONST_PLAN_STATUS_LOW_ACCURACY,
                                $CONST_PLAN_STATUS_HIGH_ACCURACY,
                                $CONST_PLAN_STATUS_ORDER_COMPLETE,
                                $CONST_PLAN_STATUS_MAINTENANCE,
                                $sectionParentId,
                                $year,
                                $period,
                            ]);

        return $result[self::MY_SECTION_PROSPECT_PARENT_INDEX];
    }
}

DATE型の丸め方

SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS January,

DATE_FORMAT(d.end_time,’%Y-%m’)を使って年-月で丸めることができて集計しやすくなる。
例) 2020-01-24 → 2020-01になる

クエリの確認

下記のようにすると発行されるクエリを確認できます。

DB::enableQueryLog();
        $result = DB::select($sql, [
                                $yearJanuary, 

・・・
                                $yearDecember,
                            ]);
dd(DB::getQueryLog());
exit();

Amazonおすすめ

iPad 9世代 2021年最新作

iPad 9世代出たから買い替え。安いぞ！🐱 初めてならiPad。Kindleを外で見るならiPad mini。ほとんどの人には通常のiPadをおすすめします＞＜

コメントを残すコメントをキャンセル