sql - 大查询中每等周的访问次数

Question

我正在尝试从大查询中提取每周等周的访问量。但是我在日期转换方面失败了。你能支持吗？

StandardSQL
SELECT count  (visitid) as Sessions, date,
EXTRACT (ISOYEAR FROM date) AS isoyear
FROM `xxx_*`
WHERE _TABLE_SUFFIX BETWEEN '201806020' AND '20180630'
GROUP BY date
order by date DESC

标签： sqlgoogle-bigquery

Answer 1

你试过这样的查询吗？

SELECT EXTRACT(ISOYEAR FROM date) as yyyy,
       EXTRACT(ISOWEEK FROM DATE) as ww,
       COUNT(*) as Sessions
FROM `xxx_*`
WHERE _TABLE_SUFFIX BETWEEN '201806020' AND '20180630'
GROUP BY yyyy, ww
ORDER BY MIN(date) DESC;