知道bigquery中每月的周数

下面是一个解决方案(定义可以在查询中使用的UDF)和一个示例。

CREATE TEMP FUNCTION DateWithWeekOfMonth(date DATE) AS (
  CONCAT(
    FORMAT_DATE('%Y-%m-', date),
    CAST(DIV(EXTRACT(DAY FROM date), 7) + 1 AS STRING)
  )
);

SELECT date, DateWithWeekOfMonth(date)
FROM (
  SELECT DATE '2018-04-01' AS date UNION ALL
  SELECT DATE '2018-04-07' UNION ALL
  SELECT DATE '2018-04-08' UNION ALL
  SELECT DATE '2018-04-30'
);