Cumulative sum

[Roboroads]

Hi! It would be nice if there was a way to get a cumulative sum per time unit;

Lets say I'm building a bank app and need a trend of how full someones account is, which is calculated by the sum of their mutations. The 3rd time unit in the trend should be a sum of timeunit 1,2 and 3.

[avr]

@Roboroads - I was interested in this, too.

I made a new collection from loop over the data returned from a Trend's count.

Here's an example:

$data = Trend::model(User::class)
    ->between(
        start: $dates['startDate'],
        end: $dates['endDate'],
    )
    ->perDay()
    ->count();

$start = "some function to set the starting amount";

$totals = $data->map(function ($item, $key) use ($start, $data) {
    $item->total = $key === 0 ? $start + $item->aggregate : $data[$key - 1]->total + $item->aggregate;

    return $item;
});

This should give you a new attribute called "total" to use when you loop through $totals;

[Roboroads]

Currenty - I am not using Trend for this specific one, just created my own mysql query since mysql is faster at aggregating;

Mutation::query()
  ->toBase()
  ->select([
      DB::raw('EXTRACT(YEAR_MONTH FROM created_at) AS month'),
      DB::raw('SUM(SUM(amount)) OVER (ORDER BY EXTRACT(YEAR_MONTH FROM created_at)) AS running_sum'),
  ])
  ->groupBy('month')
  ->orderBy('month')
  ->get()

[avr]

@Roboroads - how do you create the starting amount?

Like - if I'm looking at users for the last week, I want to start with the 1000 users who signed up before the start of this week. Using the DB, I've unioned in an extra row.

How would you handle the starting number?