CODEWITHSUNDEEP
google-bigquery
DaasityDan
DaasityDan

Reputation: 1

Big Query error: "Partitioning by expressions of type STRUCT is not allowed"

The ROW_NUMBER() window function in Big Query is returning this error "Partitioning by expressions of type STRUCT is not allowed" when I run this query

 SELECT
    calendar_date, 
    ROW_NUMBER() OVER (PARTITION BY retail_month ORDER BY calendar_date) AS retail_day_of_month
FROM
  drp.retail_month
ORDER BY calendar_date

I used the table below with the following code to create the [retail_month] field

    CREATE TABLE drp.retail_month AS
(
  SELECT
    *,
    CASE
        WHEN retail_week >= 1 AND retail_week <= 4 THEN '01-JAN'
        WHEN retail_week >= 5 AND retail_week <= 9 THEN '02-FEB'
        WHEN retail_week >= 10 AND retail_week <= 13 THEN '03-MAR'
        WHEN retail_week >= 14 AND retail_week <= 17 THEN '04-APR'
        WHEN retail_week >= 18 AND retail_week <= 22 THEN '05-MAY'
        WHEN retail_week >= 23 AND retail_week <= 26 THEN '06-JUN'
        WHEN retail_week >= 27 AND retail_week <= 30 THEN '07-JUL'
        WHEN retail_week >= 31 AND retail_week <= 35 THEN '08-AUG'
        WHEN retail_week >= 36 AND retail_week <= 39 THEN '09-SEP'
        WHEN retail_week >= 40 AND retail_week <= 43 THEN '10-OCT'
        WHEN retail_week >= 44 AND retail_week <= 48 THEN '11-NOV'
        WHEN retail_week >= 49 AND retail_week <= 53 THEN '12-DEC'
      ELSE NULL
    END AS retail_month
  FROM
    calendar.retail_calendar )  
;

What I can't figure out is the column is set as a string but the error seems to think the value is a STRUCT. If I change the PARTITION BY to something else it works

I created the calendar table with the following

CREATE TABLE IF NOT EXISTS calendar.retail_calendar
(
    calendar_date                     TIMESTAMP
  , retail_day_of_year                INT
  , retail_day_of_week                INT
  , retail_week                       INT
  , retail_week_day_number            STRING(64)
  , calendar_month                    INT
  , calendar_quarter                  INT
  , retail_year                       INT
)
;

and inserted with

-- INSERT VALUES INTO CALENDAR TABLE
INSERT INTO calendar.retail_calendar VALUES
('2022-01-01',336,7,48,'48_7',1,1,2021),
('2022-01-02',337,1,49,'49_1',1,1,2021),
('2022-01-03',338,2,49,'49_2',1,1,2021),
('2022-01-04',339,3,49,'49_3',1,1,2021),
('2022-01-05',340,4,49,'49_4',1,1,2021),
('2022-01-06',341,5,49,'49_5',1,1,2021),
('2022-01-07',342,6,49,'49_6',1,1,2021),
('2022-01-08',343,7,49,'49_7',1,1,2021),
('2022-01-09',344,1,50,'50_1',1,1,2021),
('2022-01-10',345,2,50,'50_2',1,1,2021),
('2022-01-11',346,3,50,'50_3',1,1,2021),
('2022-01-12',347,4,50,'50_4',1,1,2021),
('2022-01-13',348,5,50,'50_5',1,1,2021),
('2022-01-14',349,6,50,'50_6',1,1,2021),
('2022-01-15',350,7,50,'50_7',1,1,2021),
('2022-01-16',351,1,51,'51_1',1,1,2021),
('2022-01-17',352,2,51,'51_2',1,1,2021),
('2022-01-18',353,3,51,'51_3',1,1,2021),
('2022-01-19',354,4,51,'51_4',1,1,2021),
('2022-01-20',355,5,51,'51_5',1,1,2021),
('2022-01-21',356,6,51,'51_6',1,1,2021),
('2022-01-22',357,7,51,'51_7',1,1,2021),
('2022-01-23',358,1,52,'52_1',1,1,2021),
('2022-01-24',359,2,52,'52_2',1,1,2021),
('2022-01-25',360,3,52,'52_3',1,1,2021),
('2022-01-26',361,4,52,'52_4',1,1,2021),
('2022-01-27',362,5,52,'52_5',1,1,2021),
('2022-01-28',363,6,52,'52_6',1,1,2021),
('2022-01-29',364,7,52,'52_7',1,1,2021)
;

NRF Retail Calendar

Upvotes: 0

Views: 2894

Answers (1)

Joe R
Joe R

Reputation: 125

I'm not sure if its really an issue with the struct type per se. I see that your table name is drp.retail_month with a column of retail_month. I think there's an issue with the same name for both -- thinking BigQuery is having issues determining which object.

I've changed the create table script:

CREATE TABLE drp.retail_month_tbl AS
(
  SELECT
    *,
    CASE
        WHEN retail_week >= 1 AND retail_week <= 4 THEN '01-JAN'
        WHEN retail_week >= 5 AND retail_week <= 9 THEN '02-FEB'
        WHEN retail_week >= 10 AND retail_week <= 13 THEN '03-MAR'
        WHEN retail_week >= 14 AND retail_week <= 17 THEN '04-APR'
        WHEN retail_week >= 18 AND retail_week <= 22 THEN '05-MAY'
        WHEN retail_week >= 23 AND retail_week <= 26 THEN '06-JUN'
        WHEN retail_week >= 27 AND retail_week <= 30 THEN '07-JUL'
        WHEN retail_week >= 31 AND retail_week <= 35 THEN '08-AUG'
        WHEN retail_week >= 36 AND retail_week <= 39 THEN '09-SEP'
        WHEN retail_week >= 40 AND retail_week <= 43 THEN '10-OCT'
        WHEN retail_week >= 44 AND retail_week <= 48 THEN '11-NOV'
        WHEN retail_week >= 49 AND retail_week <= 53 THEN '12-DEC'
      ELSE NULL
    END AS retail_month
  FROM
    calendar.retail_calendar )  
;

then changed the query:

 SELECT
    calendar_date, 
    ROW_NUMBER() OVER (PARTITION BY retail_month ORDER BY calendar_date) AS retail_day_of_month
FROM
  drp.retail_month_tbl
ORDER BY calendar_date

And it returned data. Give that a try!

Clearly some type of name conflict / resolution issue with BigQuery.

Upvotes: 1

Related Questions