我想得到加速度的标准偏差，除以reg，date和hour.因此，我希望使用以下代码根据这些分区得到一个统一的值，但每行得到的值不同:
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
)
)
)
)
