昨年のデータと今年度のデータを取得して計算する場合に、うまく計算できない

Question

昨年のデータと今年度のデータを取得して計算する場合に、うまく計算できない

$lastYearDate

Illuminate\Support\Collection {#1673 ▼
  #items: array:3 [▼
    0 => {#1680 ▼
      +"prev_date": "2020-06-08"
      +"prev_total_amount": "18000"
      +"prev_number_customers": 2
      +"prev_avg_customer_spend": "9000.0000"
      +"prev_score": "115"
    }
    1 => {#1681 ▼
      +"prev_date": "2020-06-21"
      +"prev_total_amount": "12000"
      +"prev_number_customers": 1
      +"prev_avg_customer_spend": "12000.0000"
      +"prev_score": "47"
    }
    2 => {#1712 ▼
      +"prev_date": "2020-07-04"
      +"prev_total_amount": "50000"
      +"prev_number_customers": 1
      +"prev_avg_customer_spend": "50000.0000"
      +"prev_score": "500"
    }
  ]
}

$date

Illuminate\Support\Collection {#1709 ▼
  #items: array:4 [▼
    0 => {#1671 ▼
      +"date": "2020-06-08"
    }
    1 => {#1714 ▼
      +"date": "2020-06-10"
    }
    2 => {#1726 ▼
      +"date": "2020-06-21"
    }
    3 => {#1669 ▼
      +"date": "2020-07-04"
    }
  ]
}

$val->prev_date

"2020-06-08"

$row->date

"2020-06-08"

amtは金額のカラム

foreach ($lastDate as $val) {
  foreach ($date as $row) {
                    
    $result = DB::table('posts')
         ->when(($val->prev_date == $row->date), function ($query) use ($val) {
             return $query->selectRaw('DATE_FORMAT(created_at, "%Y%m%d") as date')
              ->selectRaw('SUM(amt) AS amount')
              ->selectRaw('SUM(amt) / ? AS amount_ratio',[$val->prev_total_amount]);
 
           })
          ->when((!isset($val->prev_date)), function ($query) {
              return $query->selectRaw('DATE_FORMAT(created_at, "%Y%m%d") as date')
                           ->selectRaw('SUM(amt) AS total_amount')
                           ->selectRaw('SUM(amt) / 0 AS amount_ratio');
            )}
          ->get();

自分が考えるに、$val->prev_date == $row->dateで日付が一致したデータは昨年のデータも含めて計算が走り、日付が一致しないばあいは!isset($val->prev_date)の処理がはしると思っています。

しかし、現状は、現在の日付(今年)は正常に計算しているのですが、昨年の計算処理は全て昨年の最初の日付で全て計算してしまいます。
(例：2021-06-08と2020-06-08で計算。2021-06-10と2020-06-08で計算。やりたいことは、2021-06-10は2020-06-10で計算。)

追記

amtは金額
foreach ($lastDate as $val) {
                    
    $result = DB::table('posts')
         ->when(($val->prev_date == $row->date), function ($query) use ($val) {
             return $query->selectRaw('DATE_FORMAT(created_at, "%Y%m%d") as date')
              ->selectRaw('SUM(amt) AS amount')
              ->selectRaw('SUM(amt) / SUBDATE(SUM(amt), SUBDATE(DATE_FORMAT(created_at, "%Y%m%d"), INTERVAL 1 YEAR)) AS amount_ratio');
 
           })
          ->when((!isset($val->prev_date)), function ($query) {
              return $query->selectRaw('DATE_FORMAT(created_at, "%Y%m%d") as date')
                           ->selectRaw('SUM(amt) AS total_amount')
                           ->selectRaw('SUM(amt) / 0 AS amount_ratio');
            )}
          ->get();

Comments

[kai0310]

EloquentとCollectionは使用できないという制約でもあるのですか？

[退会済みユーザー]

いえ特に制約はないですね。

Answer 1

foreachって存在するデータを順番に叩くのに向いているのであって、
本来の目的としては現在の日付から１年分引いた日付と比較するようなクエリーでないといけないのでは？
（MySQLだと SUBDATE(date, INTERVAL 1 YEAR) で、PostgreSQLだと '-1 year'::interval を単に足し込むことで、前年の同じ日付になるはず。）

Comments

[退会済みユーザー]

ご回答ありがとうございます。
今状況を確認できないので大変申し訳ないのですが、明日状況がわかり次第再度結果をご連絡させていただきます。

[退会済みユーザー]

foreachの二重ループはだめ、比較する軸になる方だけを使ったforeachでできると思う、日付の1年引く計算を加えたクエリーで。

[退会済みユーザー]

結果についてですが、結論取得できませんでした。自分で書いたのがうまくひかくできていないせいかもしれませんが...

修正後の内容を追記で書きました。