SELECT
-- PERCENTILE_CONTにはOVER()が必須な為，必要のない他の関数にもつける
  ROUND (AVG (number) OVER (), 2) AS average,
  MAX (number) OVER () AS max,
  MIN (number) OVER () AS min,
-- PERCENTILE_CONT()内でカラムとデータの位置を指定する
-- 今回はnumberの0.5に位置するデータが知りたい
  PERCENTILE_CONT (number, 0.5) OVER () AS median,
-- 標準偏差を算出する関数
  ROUND (STDDEV (number) OVER (), 2) AS stddeviation,
-- 分散を算出する関数
ROUND (VARIANCE (number) OVER (), 2) AS variance
FROM
  `qiita.number_of_people_monthly` 
-- 何も入れていないOVER()を付けた為，まったく同じ内容の行がデータ個数分出力される
-- 今回は1つあれば十分なので，LIMITで出力される行数を指定
LIMIT 1;

SELECT
  * 
FROM
  ( 
    SELECT
-- BigQueryのEXTRACT関数で週ごとに区切るための番号（その日付がその年の第何週目か）を付与
      EXTRACT (week FROM date) AS week,
-- PARTITION BYで「どのカラムの内容で区切るか」を指定　今回は上述のEXTRACT関数で付与した週番号ごとに区切って処理をしてもらう
      ROUND (AVG (number) OVER (PARTITION BY (EXTRACT (week FROM date))), 2) AS average,
      MAX (number) OVER (PARTITION BY (EXTRACT (week FROM date))) AS max,
      MIN (number) OVER (PARTITION BY (EXTRACT (week FROM date))) AS min,
      PERCENTILE_CONT (number, 0.5) OVER (PARTITION BY (EXTRACT (week FROM date))) AS median,
      ROUND (STDDEV (number) OVER (PARTITION BY (EXTRACT (week FROM date))), 2) AS stddeviation,
      ROUND (VARIANCE (number) OVER (PARTITION BY (EXTRACT (week FROM date))), 2) AS variance 
    FROM
      `qiita.number_of_people_monthly`
  ) AS t1 
-- このクエリでも同じ内容の行が，今度は各週の日数分出力されてしまうので，週につき1つだけ出力されるようにする
GROUP BY
  week,average, max, min, median, stddeviation, variance 
ORDER BY
  week;

SELECT
  * 
FROM
  ( 
    SELECT
      EXTRACT (week FROM date) AS week,
-- OVER以下は()を使わず後述のWINDOW句で設定した名前を指定する
      ROUND (AVG (number) OVER part_week, 2) AS average,
      MAX (number) OVER part_week AS max,
      MIN (number) OVER part_week AS min,
      PERCENTILE_CONT (number, 0.5) OVER part_week AS median,
      ROUND (STDDEV (number) OVER part_week, 2) AS stddeviation,
      ROUND (VARIANCE (number) OVER part_week, 2) AS variance 
    FROM
      `qiita.number_of_people_monthly` 
-- WINDOW句はFROM句の後に
-- 先に別名を設定し，その後分析関数で用いる予定のOVER()内を記述する
-- これで，SELECT句内では別名を記述するだけでOVER()が処理されるようになる
    WINDOW
      part_week AS (PARTITION BY (EXTRACT (week FROM date)))
  ) AS t1 
GROUP BY
  week, average, max, min, median, stddeviation, variance
ORDER BY
  week;