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

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

)
)
)
)

CodePudding user response：

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:

vs.

SELECT STDDEV(i) OVER() FROM t;

Output:

The ORDER BY when applied to