0

How can I dynamically multiply the previous row's results:

row A col_3 = if (row A col_1 <= 1 then 1), else  (1 *  col_2)
row B col_3 = if (row B col_1 <= 1 then 1), else  (row A *  col_2)
row C col_3 = if (row C col_1 <= 1 then 1), else  (row B *  col_2)

I've attempted this in Postgres. Here's

sum (
    case
        when col_1 <= 1 then 1
        else (lag(col_3) over (...)) * col_2 <-- I'm aware you cannot use a lag function within a sum/window function
    end
) over (order by ...) as col_3

Note: I've asked a similar question here (thanks @Bergi!), but I'm not sure how to implement that answer for this purpose.

EDIT:

Logic:

if (previous_interval is null) previous_interval = 1

if (curr_repetition = 1) interval = 1
else if (curr_repetition = 2) interval = 6
else interval = previous_interval * easiness
user3871
  • 12,432
  • 33
  • 128
  • 268
  • You mean you want to reset the aggregate when the previous value was too small? – Bergi Jun 23 '21 at 16:32
  • 1
    You might need a [custom aggregate function](https://www.postgresql.org/docs/current/xaggr.html) that you use a window function – Bergi Jun 23 '21 at 16:34
  • I see. But what are you trying to compute, what is your input data? The algorithm you linked fetches one old record, gets one quality input, and computes one new record. You would not run that on multiple records (for the same question) at once. Please share your database schema (with some explanation where the values would come from) and your exact query. – Bergi Jun 23 '21 at 17:14
  • @Bergi sure thing, but I didn't want to store previous values, rather I wanted to compute them on the fly to get latest date for a row ID. – user3871 Jun 23 '21 at 17:41
  • What is a row id, a flashcard? And where are you getting the quality inputs from? – Bergi Jun 23 '21 at 18:01
  • @Bergi I've added an edit above to clarify what I'm asking. I removed the conditionals because I don't think they're core to the problem. It's more about aggregating products from previous row multiplications – user3871 Jun 23 '21 at 22:20
  • The way I'm doing it is taking previous repetitions of the same word (for all words), checking how many user got incorrect, then using the number incorrect to calculate `quality`. I'm not storing/updating the word's repetition count, quality, etc each time a new repetition happens. – user3871 Jun 23 '21 at 22:30
  • So you *are* storing every repetition (and whether it was answered correctly or not). That's vital information. Btw, regarding your edit, the conditional is very much relevant - without it, it would be just [a `product()` aggregate](https://stackoverflow.com/q/403924/1048572). – Bergi Jun 23 '21 at 22:34
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/234135/discussion-between-growler-and-bergi). – user3871 Jun 23 '21 at 22:35
  • @Bergi Yes I'm storing each repetition for each user and whether it was answered correctly or not. Apologies RE: conditional. The logic is `if (curr_repetition = 1) then interval = 1, else if (curr_repetition = 2) then interval = 6, else interval = previous interval * easiness`. If `previous interval` is not yet set (e.g., we're on row 1), we use default previous interval of `1` – user3871 Jun 23 '21 at 22:40
  • You might be able to do this either with a recursive CTE or the above-mentioned custom aggregate. – Bergi Jun 23 '21 at 22:53

0 Answers0