SQL文（クエリ）

WITH plan AS (
  SELECT
    2017 AS year
    , 800000 AS target
  UNION ALL
  SELECT
    2018
    , 1000000
  UNION ALL
  SELECT
    2019
    , 1200000
)
, result AS (
  SELECT
    EXTRACT(YEAR FROM date_time) AS year
    , SUM(revenue) AS sum_rev
  FROM
    sample.sales
  GROUP BY
    year
)
SELECT
  *
  , (result_rev - target) / target AS achivement_rate
FROM
  (
    SELECT
      result.year AS year
      , plan.target AS target
      , result.sum_rev AS result_rev
    FROM
      result
      JOIN plan
      USING
        (year)
    ORDER BY
      year
  )

結果テーブル

サポートページに戻る