There’s a time for generality, and there’s a time for solving the problem actually posed. This is one of the latter times. If you want to make a method that turns a sequence of doubles into a sequence of partial sums, then just do that: public static IEnumerable<double> CumulativeSum(this IEnumerable<double> sequence) { double sum = … Read more
New Answer At first, I didn’t understand you were trying to do a running total. Here is how that would look: SET @runningTotal = 0; SELECT e_date, num_interactions, @runningTotal := @runningTotal + totals.num_interactions AS runningTotal FROM (SELECT DATE(eDate) AS e_date, COUNT(*) AS num_interactions FROM example AS e GROUP BY DATE(e.Date)) totals ORDER BY e_date; Original … Read more
In SQL Server 2005, I would do this using a correlated subquery: select dummy_id, date_registered, item_id, quantity, price, (select sum(quantity) from t t2 where t2.item_id = t.item_id and t2.date_registered <= t.date_registered ) as cumulative from table t; If you actually want to add this into a table, you need to alter the table to add … Read more
In analogy to the cumulative sum of a list I propose this: The cumulative average avg of a vector x would contain the averages from 1st position till position i. One method is just to compute the the mean for each position by summing over all previous values and dividing by their number. By rewriting … Read more
A great resource on calculating running totals in SQL Server is this document by Itzik Ben Gan that was submitted to the SQL Server Team as part of his campaign to have the OVER clause extended further from its initial SQL Server 2005 implementation. In it he shows how once you get into tens of … Read more
The ave function computes a function by group. > id <- c(1,2,3,2,2,1,2,3) > data.frame(id,count=ave(id==id, id, FUN=cumsum)) id count 1 1 1 2 2 1 3 3 1 4 2 2 5 2 3 6 1 2 7 2 4 8 3 2 I use id==id to create a vector of all TRUE values, which get … Read more
Here’s one way with ave: ave(df$a, cumsum(c(F, diff(df$a) < 0)), FUN=seq_along) – 1 [1] 0 1 2 3 0 1 2 3 4 5 6 0 1 0 We can get a running count grouped by diff(df$a) < 0. Which are the positions in the vector that are less than their predecessors. We add c(F, … Read more
UPDATE MySQL 8.0 introduces “window functions”, functionality equivalent to SQL Server “window functions” (with partitioning and ordering provided by Transact-SQL OVER syntax), and Oracle “analytic functions”. MySQL Reference Manual 12.21 Window Functions https://dev.mysql.com/doc/refman/8.0/en/window-functions.html The answer provided here is an approach for MySQL versions prior to 8.0. ORIGINAL ANSWER MySQL doesn’t provide the type analytic function … Read more
# replace the second column for the cumsum of the initial second column data[, 2] <- cumsum(data[, 2])
Basically, you need a window function. That’s a standard feature nowadays. In addition to genuine window functions, you can use any aggregate function as window function in Postgres by appending an OVER clause. The special difficulty here is to get partitions and sort order right: SELECT ea_month, id, amount, ea_year, circle_id , sum(amount) OVER (PARTITION … Read more