毎時の累積データ、毎日の累積データを作る

分析関数の PARTITION の使い方を忘れないため

毎時の累積データ

SELECT
    date 
    ,hour
    ,sum(amount) as amount
    ,sum(sum(amount)) OVER (PARTITION BY date ORDER BY hour ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as _sum   
FROM
    (
    SELECT
        DATETIME_TRUNC(a.datetime, hour) as hour
        ,a.date
        ,a.amount
      FROM amount AS a
    ORDER BY hour desc        
    ) as t1
GROUP BY
  hour 
  , date

毎日の累積データ

SELECT
    month 
    ,date
    ,sum(amount) as amount
    ,sum(sum(amount)) OVER (PARTITION BY month ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as _sum   
FROM
    (
    SELECT
        DATE_TRUNC(a.date, MONTH) as month
        ,a.date
        ,a.amount
      FROM amount AS a
    ORDER BY date desc        
    ) as t1
GROUP BY
  date 
  , month