I have an issue to pull this kind of data. So I need to pull weekly data with these specifications:
- The data pull will be scheduled, hence it will involve multiple months
- The very first week will start from the first date (1 in every month) -- Green in the pic
- The last week doesn't involve dates from the next month -- Red in the pic
The raw data and the desirable output(s) will more or less look like this:
Is there any workaround to do this in BigQuery? Thanks (attached below the data)
------------- -------
| 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 |
------------- -------
CodePudding user response：
Consider below approach
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
if to apply to sample data (as is) in your question - output is
