如何获得每周数据，但从第一日期的月份和做总和计算，因此在贝?

Question 1

我有一个问题，把这种数据。所以我需要拔周数据与这些规格：

数据拉将排定的，因此它将涉及多个月
第一个星期将开始从第一日期(1月)-绿色的图片
最后一个星期不涉及日期是从下个月--红色的图片

原始数据和期望输出的(s)将或多或少是这样的：

是否有任何解决办法要做到这一点，在BigQuery? 谢谢(下面的数据)

+-------------+-------+
| date        | sales |
+-------------+-------+
|  1 Oct 2021 |     5 |
+-------------+-------+
|  2 Oct 2021 |    13 |
+-------------+-------+
|  3 Oct 2021 |    75 |
+-------------+-------+
|  4 Oct 2021 |     3 |
+-------------+-------+
|  5 Oct 2021 |    70 |
+-------------+-------+
|  6 Oct 2021 |    85 |
+-------------+-------+
|  7 Oct 2021 |    99 |
+-------------+-------+
|  8 Oct 2021 |    90 |
+-------------+-------+
|  9 Oct 2021 |    68 |
+-------------+-------+
| 10 Oct 2021 |    97 |
+-------------+-------+
| 11 Oct 2021 |    87 |
+-------------+-------+
| 12 Oct 2021 |    56 |
+-------------+-------+
| 13 Oct 2021 |    99 |
+-------------+-------+
| 14 Oct 2021 |    38 |
+-------------+-------+
| 15 Oct 2021 |     6 |
+-------------+-------+
| 16 Oct 2021 |    43 |
+-------------+-------+
| 17 Oct 2021 |    45 |
+-------------+-------+
| 18 Oct 2021 |    90 |
+-------------+-------+
| 19 Oct 2021 |    64 |
+-------------+-------+
| 20 Oct 2021 |    26 |
+-------------+-------+
| 21 Oct 2021 |    24 |
+-------------+-------+
| 22 Oct 2021 |     4 |
+-------------+-------+
| 23 Oct 2021 |    36 |
+-------------+-------+
| 24 Oct 2021 |    68 |
+-------------+-------+
| 25 Oct 2021 |     4 |
+-------------+-------+
| 26 Oct 2021 |    16 |
+-------------+-------+
| 27 Oct 2021 |    30 |
+-------------+-------+
| 28 Oct 2021 |    89 |
+-------------+-------+
| 29 Oct 2021 |    46 |
+-------------+-------+
| 30 Oct 2021 |    28 |
+-------------+-------+
| 31 Oct 2021 |    28 |
+-------------+-------+
|  1 Nov 2021 |    47 |
+-------------+-------+
|  2 Nov 2021 |    75 |
+-------------+-------+
|  3 Nov 2021 |     1 |
+-------------+-------+
|  4 Nov 2021 |    26 |
+-------------+-------+
|  5 Nov 2021 |    26 |
+-------------+-------+
|  6 Nov 2021 |    38 |
+-------------+-------+
|  7 Nov 2021 |    79 |
+-------------+-------+
|  8 Nov 2021 |    37 |
+-------------+-------+
|  9 Nov 2021 |    83 |
+-------------+-------+
| 10 Nov 2021 |    97 |
+-------------+-------+
| 11 Nov 2021 |    56 |
+-------------+-------+
| 12 Nov 2021 |    83 |
+-------------+-------+
| 13 Nov 2021 |    14 |
+-------------+-------+
| 14 Nov 2021 |    25 |
+-------------+-------+
| 15 Nov 2021 |    55 |
+-------------+-------+
| 16 Nov 2021 |    16 |
+-------------+-------+
| 17 Nov 2021 |    80 |
+-------------+-------+
| 18 Nov 2021 |    66 |
+-------------+-------+
| 19 Nov 2021 |    25 |
+-------------+-------+
| 20 Nov 2021 |    62 |
+-------------+-------+
| 21 Nov 2021 |    36 |
+-------------+-------+
| 22 Nov 2021 |    33 |
+-------------+-------+
| 23 Nov 2021 |    19 |
+-------------+-------+
| 24 Nov 2021 |    47 |
+-------------+-------+
| 25 Nov 2021 |    14 |
+-------------+-------+
| 26 Nov 2021 |    22 |
+-------------+-------+
| 27 Nov 2021 |    66 |
+-------------+-------+
| 28 Nov 2021 |    15 |
+-------------+-------+
| 29 Nov 2021 |    96 |
+-------------+-------+
| 30 Nov 2021 |     4 |
+-------------+-------+

Question 2

考虑下文的方法

with temp as (
  select parse_date('%d %B %Y', date) date, sales
  from your_table
)
select format_date('%d %B %Y', weeks[ordinal(num)]) start_week, sum(sales) total_sales 
from (
  select sales, weeks, range_bucket(date, weeks) num
  from temp, unnest([struct(generate_date_array(date_trunc(date, month), last_day(date, month), interval 7 day ) as weeks)])
)
group by start_week

如果申请样本数据(是)在您的问题出是

Mikhail Berlyant · Answer 1 · 2021-11-24T04:12:50

考虑下文的方法

with temp as (
  select parse_date('%d %B %Y', date) date, sales
  from your_table
)
select format_date('%d %B %Y', weeks[ordinal(num)]) start_week, sum(sales) total_sales 
from (
  select sales, weeks, range_bucket(date, weeks) num
  from temp, unnest([struct(generate_date_array(date_trunc(date, month), last_day(date, month), interval 7 day ) as weeks)])
)
group by start_week

如果申请样本数据(是)在您的问题出是

如何获得每周数据，但从第一日期的月份和做总和计算，因此在贝?

的问题

最好的答案

其他语言

此页面有其他语言版本

受欢迎的此类别

流行的问题，在这个类别