BigQuery - Aggregate by 2 months window - IT technology developer community

So currently im currently doing a project on BQ to obtain monthly level data using the codes below.

WITH agg as (
  SELECT
  DATE_TRUNC(CAST(date as DATE), MONTH) AS year_month,
  market,
  SUM(Rev) as Rev,
FROM `data_table` 
group by 1,2)

However now i am required to get it at a 2 month level. Expected results would be like this.

Date	Market	Rev
Jan 2021	JP	(sum of Jan & Feb Rev)
Mar 2021	JP	(sum of Mar & Apr Rev)
May 2021	JP	(sum of May & Jun Rev)
Jan 2021	MY	(sum of Jan & Feb Rev)
Mar 2021	MY	(sum of Mar & Apr Rev)
May 2021	MY	(sum of May & Jun Rev)

Anyone got any ideas?

SELECT FORMAT_DATE('%b %Y', DATE(EXTRACT(YEAR FROM CAST(date AS DATE)), 2 * DIV(EXTRACT(MONTH FROM CAST(date AS DATE)) - 1, 2) + 1, 1) ) AS Date, Market, SUM(Rev) AS Rev, FROM `data_table` GROUP BY 1, 2

BigQuery - Aggregate by 2 months window

BigQuery - Aggregate by 2 months window

Rev

0 Comment

0 Comment

About the Author

Question Info

Related Question

为什么我不能解码byteReadChannel到字符串

How to retrieve the host name by IP with dnsjava library?

我可以在Django ORM中写RIGHT JOIN和*ORDER BY queryset吗?(复制)

如何验证一个字符串是否包含有效的Ruby符号文字

adb push 错误；adb: error: 65536-byte write failed: Invalid argument

如何使用react或Gatsby在url中使用查询字符串(get, set)(没有窗口||文档)

Ruby jekyll主题无法发现错误问题

带有导出配置的BigQueryInsertJobOperator

在Ruby on Rails中编写i18n(国际化)yml文件

使用BigQuery匹配网络子网