STDDEV Windows function in Snowflake: not getting a uniform value according to the partition

user_17686258 0
Viewed 35

I want to get the standard deviation of the acceleration partitioned by reg, date and hour. So, I would expect a uniform value as per these partitions with the following code but I'm getting a different value for each row:

SELECT *,
       STDDEV(ACCELERATION)OVER(PARTITION BY REG, DATE, HOUR ORDER BY TIMESTAMP)
  
FROM 

(
  
SELECT 
       *,
       SPEED_DIFFERENCE/ TIMESTAMP_DIFFERENCE AS ACCELERATION

FROM 

(



SELECT *,
       NEXT_MILES_PER_HOUR - MILES_PER_HOUR AS SPEED_DIFFERENCE,
       TIMESTAMPDIFF(second,timestamp,Next_timestamp) AS TIMESTAMP_DIFFERENCE
       
FROM

(

SELECT *, 
       TO_TIME (DATE_AND_HOUR) AS HOUR,
       LEAD(TIMESTAMP)OVER (PARTITION BY VIN ORDER BY TIMESTAMP) AS NEXT_TIMESTAMP,
       LEAD(MILES_PER_HOUR)OVER (PARTITION BY REG ORDER BY TIMESTAMP) AS NEXT_MILES_PER_HOUR

FROM

(

SELECT Reg, 
       TIMESTAMP,
       Miles_per_hour
       TO_DATE (TIMESTAMP) AS DATE,
       TO_TIME (TIMESTAMP) AS TIME,
       date_trunc('HOUR', TIMESTAMP) as DATE_AND_Hour,
       DATA
       
FROM Motorycle_Data_Refurbished_Models_DuPage_County

)
)
)
)
snowflake-cloud-data-platform sql
1 Answers

To get single value per partition, the ORDER BY should be removed as it switch the function to be cummulative:

SELECT *,
       STDDEV(ACCELERATION)OVER(PARTITION BY REG, DATE, HOUR)
...

Example:

CREATE  OR REPLACE TABLE t(i INTEGER);
INSERT INTO t (i) VALUES  (6), (10), (14);

SELECT STDDEV(i) OVER(ORDER BY i) FROM t;

Output:

enter image description here

vs.

SELECT STDDEV(i) OVER() FROM t;

Output:

enter image description here

The ORDER BY when applied to STDDEV:

If the OVER clause contains an ORDER BY subclause, then:

A window frame is required. If no window frame is specified explicitly, then the ORDER BY implies a cumulative window frame:

   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

The window is moving from begining of the partition to current row.

It is possible to overwrite it:

SELECT STDDEV(i) OVER(ORDER BY i 
                      RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 
FROM t;

Output:

enter image description here

user_50708790
Related