Calculating the stddev and avg between the most recent number and all the other numbers in a running list snowflake

user_13315358 0
Viewed 124

I have a dataset that looks something like this:

id     committed      delivered        timestamp     stddev
1             10              8       01-02-2022          ?
2             20             15       01-14-2022          ?                    
3             12             12       01-30-2022          ?
4              2              0       02-14-2022          ?
.
.
99                                                     null

I am trying to calculate the standard deviation between sprint x and all the sprints after sprint x; for example, the standard deviation and avg between sprint 1, 2, 3 & 4, 2, 3 & 4, 3 & 4, and so on. If there are no records after 4, that stddev would be null

With the current snowflake functions, I am generally unable to calculate the stddev in general, let alone do something with a lag/lead function.

Does anyone have any advice? Thanks in advance!

Update: I've figured out how to calculate a moving avg over sprint x and the next sprint, but not for all previous sprints: (delivered + lead(delivered) over (partition by id order by timestamp asc)) / 2

stddev can also be calculated using abs / sqrt (2)

dbt snowflake-cloud-data-platform
3 Answers

You're looking for a frame clause -- this is part of the window function that can specify which rows in the current partition to use in the calculation.

select
    id,
    stddev(delivered) over (
        order by id asc
        rows between current row and unbounded following
    ) as stddev,
    avg(delivered) over (
        order by id asc
        rows between current row and unbounded following
    ) as avg

from my_data
user_108130820

tconbeer is 100% correct, but here is the code and count to "show it working" and you example data (moshed) into a VALUES section to avoid making a table.

I also stripped out timestamp, as it's not used in this demo, but normally I would order by that, but I could not see the pattern, so just dropped it, as it's non material to the example.

SELECT t.*
    ,count(delivered) over ( order by id asc rows between current row and unbounded following ) as _count
    ,stddev(delivered) over ( order by id asc rows between current row and unbounded following ) as stddev
    ,avg(delivered) over ( order by id asc rows between current row and unbounded following ) as avg
FROM VALUES
    (1, 10,  8),  
    (2, 20, 15),              
    (3, 12, 12), 
    (4,  2,  0),
    (5,  2,  0),
    (6,  0,  0)
    t(id, committed, delivered)
ORDER BY 1;

gives:

ID COMMITTED DELIVERED _COUNT STDDEV AVG
1 10 8 6 6.765106577 5.833
2 20 15 5 7.469939759 5.4
3 12 12 4 6 3
4 2 0 3 0 0
5 2 0 2 0 0
6 0 0 1 null 0
user_439920

you can create a dummy table which will have a id generated sequentially using the generator function for a particular range and do a LEFT join with the table. This way you will get rows with NULL values where the id is not present, and then you can use lag / leap to get the average.

--- untested

select seq4()  as id1 , TMP.* from table(generator(rowcount => 10)) v 
LEFT  JOIN (SELECT * FROM (  
 SELECT   1 as id    , 8 As committed  ,  '01-02-2022' as   delivered    UNION ALL
  SELECT   2 as id    , 20 As committed  ,  '01-14-2022' as   delivered   UNION ALL
  SELECT   3 as id    , 12 As committed  ,  '01-30-2022' as   delivered   UNION ALL
  SELECT   5 as id    , 2 As committed  ,  '02-14-2022' as   delivered  
 )) TMP
 ON trim(id1) = trim(tmp.id)
user_112279190
Related