CODEWITHSUNDEEP
google-bigqueryflatten
Rachel
Rachel

Reputation: 181

Google Big Query Flatten table and use table_range function

Does anyone know how to use multiple FLATTEN function and Table_date_range at the same time? Now I can only get one day data, but I would like to have all the data that are available. Is there a way to do it?

select 
Date,COUNT(DISTINCT FULLVISITORID),hits.product.v2ProductCategory
FROM FLATTEN((FLATTEN (table, hits.product.v2ProductCategory)) ,customDimensions.value)
group by Date
,hits.product.v2ProductCategory

Thank you

Upvotes: 1

Views: 2182

Answers (2)

Elliott Brossard
Elliott Brossard

Reputation: 33765

You should use standard SQL instead. For example, 

#standardSQL
SELECT
  Date,
  COUNT(DISTINCT FULLVISITORID),
  product.v2ProductCategory,
  customDimension.value
FROM `aaprod-20160309.112099209.ga_sessions_*` AS t
  CROSS JOIN UNNEST(hits) AS hit
  CROSS JOIN UNNEST(t.customDimensions) AS customDimension
  CROSS JOIN UNNEST(hit.product) AS product
GROUP BY 1, 3, 4;

The differences between legacy and standard SQL are described in the migration guide.

Upvotes: 6

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Try below (not tested) 

SELECT 
  DATE,
  COUNT(DISTINCT FULLVISITORID),
  hits.product.v2ProductCategory
FROM FLATTEN(FLATTEN (
  (SELECT * 
  FROM TABLE_DATE_RANGE([aaprod-20160309:112099209.ga_sessions_],
        TIMESTAMP('2016-07-25'),
        TIMESTAMP('2016-07-27'))
  ), hits.product.v2ProductCategory), customDimensions.value
)
GROUP BY 
  DATE, 
  hits.product.v2ProductCategory

Upvotes: 2

Related Questions