CODEWITHSUNDEEP
google-bigquery
Simon Breton
Simon Breton

Reputation: 2876

How to use two different custom dimension with BigQuery Legacy SQL

I'm trying to build a query with legacy SQL where I need to use two distinct custom dimension. Here is what I've written until now :

SELECT
  count_distinct(hits.customDimensions.value) AS Articles,
  DATE(timestamp(hits.customDimensions.value)) AS date_, 
FROM
  [storied-toolbox-113108:112162023.ga_sessions_20161104]
WHERE
 hits.customDimensions.index=4 AND
 DATE(timestamp(hits.customDimensions.value)) > DATE(DATE_ADD(TIMESTAMP(CURRENT_DATE()), -7, "DAY"))
GROUP BY
  date_

however I would like to apply count_distinct function on the customDimensions index 10 and apply date function on the customDimensions index 4.

How can I do this ?

thanks !

Upvotes: 0

Views: 710

Answers (2)

AngryGael
AngryGael

Reputation: 11

Try This :

SELECT date,
  (IF(hits.customDimensions.index=3,hits.customDimensions.value,NULL)),
  (IF(hits.customDimensions.index=9,hits.customDimensions.value,NULL))
FROM TABLE_DATE_RANGE(
  [YourTable.ga_sessions_], 
  DATE_ADD(CURRENT_TIMESTAMP(), -30, 'DAY'), 
  CURRENT_TIMESTAMP())
LIMIT 1000

Upvotes: 1

Vlad Grachev
Vlad Grachev

Reputation: 146

You can do that by splitting your SELECT statement into two:

SELECT count_distinct(hits.customDimensions.value) AS Articles
FROM [storied-toolbox-113108:112162023.ga_sessions_20161104]
WHERE hits.customDimensions.index=10

DATE(timestamp(hits.customDimensions.value)) AS date_
FROM [storied-toolbox-113108:112162023.ga_sessions_20161104]
WHERE hits.customDimensions.index=4 AND
DATE(timestamp(hits.customDimensions.value)) > DATE(DATE_ADD(TIMESTAMP(CURRENT_DATE()), -7, "DAY"))

Then you can union the results into two separate columns:

SELECT * FROM (SELECT ...), (SELECT ...)

or a single one (needs to be of the same type though, hence string cast)

SELECT COALESCE(date_, Articles)
FROM (SELECT STRING(...) as date_ ...)
, (SELECT STRING(...) as Articles ...)

Upvotes: 3

Related Questions