How do I fill in data in a current row with data from a preceding row with a non zero value?

All we need is an easy explanation of the problem, so here it is.

Suppose I have the following table:

CREATE TEMPORARY TABLE stocks (
    my_date date,
    my_shares int,
    stock_price decimal,
    new_shares int
);

INSERT INTO stocks VALUES('2021-01-01', 100, 10.50, 100);
INSERT INTO stocks VALUES('2021-01-02', 100, 11.00, 0);
INSERT INTO stocks VALUES('2021-01-03', 100, 11.25, 0);
INSERT INTO stocks VALUES('2021-01-04', 150, 12.00, 50);
INSERT INTO stocks VALUES('2021-01-05', 175, 13.00, 25);

select * from stocks

How do I fill in data in a current row with data from a preceding row with a non zero value?

In the table above, my_shares is the total number of shares that I own and new_shares are the number of shares that I would purchase on my_date.

I want to create another column called my_cost that shows the total cost of the shares that I most recently purchased (not total). my_cost = stock_price * new_shares when new_shares > 0.

The final table should look like this:

How do I fill in data in a current row with data from a preceding row with a non zero value?

Notice how even though I don’t purchase new stocks on 1/2 and 1/3, the cost from my most recent purchase is still shown.

I cannot figure out how to do this. I’ve mainly tried CASE with different variations of rows between unbounded preceding and preceding 1 row but I’m just guessing. Can anyone help?

PostgreSQL 13

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

WITH cte AS (
    SELECT *, SUM(new_shares>0) OVER (PARTITION BY my_shares ORDER BY my_date) grp
    FROM stocks
)
SELECT *, FIRST_VALUE(stock_price * new_shares) OVER (PARTITION BY my_shares, grp ORDER BY my_date) my_cost
FROM cte

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5af6e1eb3f6e747e47eb8763c2b79f63

Method 2

After you have fixed the current data, get rid of that code. Then modify your code so that future INSERTs grab the previous row in case there are new purchases today. That is, it is a lot cheaper to avoid GIGO.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply