解答

203

SQL文(クエリ)

WITH master AS (
  SELECT
    FORMAT_DATETIME("%Y-%Q", date_time) AS quarter
    , SUM(revenue) AS sum_rev
  FROM
    sample.sales
  WHERE
    FORMAT_DATETIME("%Y", date_time) = "2017"
  GROUP BY
    quarter
)
SELECT
  quarter
  , MAX(sum_rev1) AS sum_rev
  , SUM(sum_rev2) AS accum_sum_rev
FROM
  (
    SELECT
      m1.quarter AS quarter
      , m1.sum_rev AS sum_rev1
      , m2.sum_rev AS sum_rev2
    FROM
      master AS m1
      INNER JOIN master AS m2
      ON m1.quarter >= m2.quarter
    ORDER BY
      m1.quarter
  )
GROUP BY
  quarter
ORDER BY
  1

結果テーブル

結果テーブル

サポートページに戻る