月単位での集計はよしなにDATE_FORMATを使っていけるのですが、週単位のときに、「？」となったのでメモ。
BigqueryのStandardSQL前提で書いてますが、レガシーでも考え方は一緒のはず。（関数はどうかわからない・・・）
| _date | user_name |
|---|---|
| 2018-09-02(日曜) | a |
| 2018-09-03 | b |
| 2018-09-04 | c |
| 2018-09-05 | d |
| 2018-09-06 | e |
| 2018-09-07 | f |
| 2018-09-08 | g |
| 2018-09-09(日曜) | a |
| 2018-09-10 | b |
| 2018-09-11 | c |
| 2018-09-12 | e |
| 2018-09-13 | f |
というテーブルがあったとして、
| _week | _count |
|---|---|
| 2018-09-02(日曜) | 7 |
| 2018-09-09(日曜) | 5 |
という形で集計したいというとき。
※わかりやすくするためにあえて日曜日の日付に（日曜）とつけていますが、実際にはついている必要はないです。
SQL
SELECT
DATE_ADD(_date, INTERVAL - EXTRACT(DAYOFWEEK FROM DATE_ADD(_date, INTERVAL -0 DAY)) +1 DAY) AS _week,
COUNT(user_name) AS _count
FROM
`table`
起算日を月曜にしたい場合
DATE_ADD(_date, INTERVAL - EXTRACT(DAYOFWEEK FROM DATE_ADD(_date, INTERVAL -1 DAY)) +1 DAY) AS _week
で、いけるはず。
以上です
これができると週単位でのPVや、UUとかも工夫すればいけて幸せいっぱいですね。