CODEWITHSUNDEEP
group-bygoogle-bigquerydayofweekweek-number
user12345
user12345

Reputation: 509

In Bigquery, How to get Start Day of the week from available table list?

Input: Have got a table in bigquery, in below format

Store   Date        WeekNumber
11      2019-11-14  201953
11      2019-11-12  201953
11      2019-11-17  201953
11      2019-11-15  201953
11      2019-11-11  201953
11      2019-11-13  201953
11      2019-11-16  201953
11      2019-11-19  201954
11      2019-11-21  201954
11      2019-11-22  201954

Scenario: From the above sample table, have to group by week number and fetch week's start date as mentioned in sample output.

Sample Output:

Store   StartDate       WeekNumber
11      2019-11-11      201953
11      2019-11-19      201954

Query Snippet:

SELECT
Store,
#StartDate
WeekNumber 
FROM tablename 
GROUP BY WeekNumber,Store

Thanks in Advance!

Upvotes: 0

Views: 2293

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Below is for BigQuery Standard SQL

#standardSQL
SELECT strore, 
  DATE_TRUNC(date, WEEK(MONDAY)) StartDate,
  EXTRACT(WEEK(MONDAY) FROM date) WeekNumber,
  COUNT(*) cnt
FROM `project.dataset.table`
GROUP BY strore, StartDate, WeekNumber

If to apply to sample data from your question as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 11 store, DATE '2019-11-14' date UNION ALL
  SELECT 11, '2019-11-12' UNION ALL
  SELECT 11, '2019-11-17' UNION ALL
  SELECT 11, '2019-11-15' UNION ALL
  SELECT 11, '2019-11-11' UNION ALL
  SELECT 11, '2019-11-13' UNION ALL
  SELECT 11, '2019-11-16' UNION ALL
  SELECT 11, '2019-11-19' UNION ALL
  SELECT 11, '2019-11-21' UNION ALL
  SELECT 11, '2019-11-22' 
)
SELECT store, 
  DATE_TRUNC(date, WEEK(MONDAY)) StartDate,
  EXTRACT(WEEK(MONDAY) FROM date) WeekNumber,
  COUNT(*) cnt
FROM `project.dataset.table`
GROUP BY store, StartDate, WeekNumber

output is

Row store   StartDate   WeekNumber  cnt  
1   11      2019-11-11  45          7    
2   11      2019-11-18  46          3

UPDATE: in initial answer I've missed requirement: start date [should be taken] from the available dates for that week from table

Below query does exactly this:

#standardSQL
SELECT Store, 
  MIN(Date) StartDate,
  WeekNumber,
  COUNT(*) cnt
FROM `project.dataset.table`
GROUP BY Store, WeekNumber

if to apply to [updated] sample data from your question

WITH `project.dataset.table` AS (
  SELECT 11 Store, '2019-11-14' Date, 201953 WeekNumber UNION ALL
  SELECT 11, '2019-11-12', 201953 UNION ALL
  SELECT 11, '2019-11-17', 201953 UNION ALL
  SELECT 11, '2019-11-15', 201953 UNION ALL
  SELECT 11, '2019-11-11', 201953 UNION ALL
  SELECT 11, '2019-11-13', 201953 UNION ALL
  SELECT 11, '2019-11-16', 201953 UNION ALL
  SELECT 11, '2019-11-19', 201954 UNION ALL
  SELECT 11, '2019-11-21', 201954 UNION ALL
  SELECT 11, '2019-11-22', 201954 
)

result is

Row Store   StartDate   WeekNumber  cnt  
1   11      2019-11-11  201953      7    
2   11      2019-11-19  201954      3

Upvotes: 3

Related Questions