Exclude nulls from postgres window function

user_10190191 0
Viewed 288

For each row, I want to take average of last 20 non-null values using.

The window function is taking 20 rows below including the null ones and calculating average, while I want average of last 20 non null rows.

What I have tried?

WITH adv_calculated AS (
    SELECT security_id                                                                               AS sec_id,
           date                                                                                      AS pd,
           AVG(volume)
           OVER (PARTITION BY (security_id) ORDER BY date ROWS BETWEEN 20 PRECEDING AND CURRENT ROW) AS adv
    FROM tbl_financial_index_data
    WHERE volume IS NOT NULL
    GROUP BY security_id, date
)
UPDATE tbl_financial_index_data
SET adv                  = amc.adv
FROM adv_calculated amc
WHERE amc.sec_id = security_id
  AND amc.pd = date

This solution works good for all rows where volume is NOT null but it does not calculate the adv/average for the rows where volume is NULL. Then for the null adv and volume rows, I have to run this query which is really slow

UPDATE tbl_financial_index_data
SET average_daily_volume =
        (SELECT avg(t.volume)
         FROM (
                  SELECT a.volume
                  FROM tbl_financial_index_data a
                  WHERE a.security_id = tbl_financial_index_data.security_id
                    AND a.date::date <= tbl_financial_index_data.date::date
                    AND a.volume IS NOT NULL
                  ORDER BY a.date DESC
                  LIMIT 21
              ) t)
WHERE volume IS NULL;

I want to avoid using the second query and calculate ADV for all rows using first query (because it is much faster).

postgresql sql
2 Answers

Simply omit the WHERE condition WHERE volume IS NOT NULL, then you should get what you want.

You can nest the query in an outer query to remove the undesired values later:

WITH adv_calculated AS (
   SELECT ...
   FROM (SELECT AVG(volume) OVER (... ROWS BETWEEN ... AND ...),
                ...
         FROM tbl_financial_index_data
         GROUP BY security_id, date
        ) AS subq
   WHERE volume IS NOT NULL
)
SELECT ...
user_64643080

This is just a work around

Okay, so I found the solution. If the volume is null for some row then the adv for that row is going to be equal to the previous non-null volume row's adv, so I had to find some way to carry forward previous non-null adv for rows where it is null.

I was able to find a way to do that from this answer.

Here's the code to carry forward the non-null value:

WITH temp_adv_filled_values AS (
    SELECT security_id,
           date,
           FIRST_VALUE(adv) OVER W                  AS adv
    FROM (
             SELECT security_id,
                    date,
                    adv,
                    SUM(CASE WHEN volume IS NULL THEN 0 ELSE 1 END)
                    OVER (PARTITION BY security_id ORDER BY date ) AS value_partition
             FROM tbl_financial_index_data
         ) AS q
        WINDOW W AS (PARTITION BY security_id, value_partition ORDER BY date)
)
UPDATE tbl_financial_index_data tfid
SET adv                  = tmcfv.adv
FROM temp_adv_filled_values tmcfv
WHERE tmcfv.security_id = tfid.security_id
  AND tmcfv.date = tfid.date;
user_101901910
Related