Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
BigQuery requests
You can use TIMESTAMP or DATE or DATETIME and use corresponding FORMAT_* functions
##DAY OF Week
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#supported-format-elements-for-date
%u The weekday (Monday as the first day of the week) as a decimal number (1-7).
### Option 1
SELECT
FORMAT_DATE("%u", CURRENT_DATE()) as currentDayOfWeek;
### Option 2
SELECT
FORMAT_DATETIME("%u", DATETIME(CURRENT_DATE(), TIME "15:30:00")) AS currentDayOfWeek;
##MONTH OF YEAR
%m The month as a decimal number (01-12).
### Option 1
SELECT FORMAT_DATE("%m", CURRENT_DATE()) as monthInYear;
### Option 2
SELECT
FORMAT_DATETIME("%m", DATETIME(CURRENT_DATE(), TIME "15:30:00")) AS monthOfYear;
You can use TIMESTAMP or DATE or DATETIME and use corresponding FORMAT_* functions
## TIMESTAMP
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#timestamp-functions
Formating today as 20161212 (using TIMESTAMP)
FORMAT_TIMESTAMP("%Y%m%d", CURRENT_TIMESTAMP()) as today
Setting CURRENT_TIMESTAMP as extra column in Query
SELECT COUNT(*) as counter,
CURRENT_TIMESTAMP() as yes
FROM `namespace_ANDROID.app_events_*`
WHERE _TABLE_SUFFIX IN ("20161201");
## select today & 2 days ago
SELECT
FORMAT_TIMESTAMP("%Y%m%d", CURRENT_TIMESTAMP()) as today,
FORMAT_TIMESTAMP("%Y%m%d", TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24*2 HOUR)) AS twoDaysAgo;
## substracting TIMESTAMP
SELECT
TIMESTAMP "2008-12-25 15:30:00 UTC" as original,
TIMESTAMP_SUB(TIMESTAMP "2008-12-25 15:30:00 UTC", INTERVAL 10 MINUTE) AS earlier;
Be carefull
- event_dim.timestamp_micros seems not to always be filled
- event_dim.date seems to be available only since 20161111
#Option 1 (less readable, but quicker)
4.6s elapsed, 3.75 MB processed
SELECT
FORMAT_TIMESTAMP("%Y%m%d",TIMESTAMP_MICROS(h.timestamp_micros )) as date, -- this is not always filled
h.name as event,
COUNT(*) as total,
max(COUNT(*)) OVER(ORDER BY COUNT(*) DESC) as best
FROM
`namespace_ANDROID.app_events_*`, UNNEST(event_dim) as h
WHERE
h.name='in_app_purchase'
AND
_TABLE_SUFFIX
BETWEEN
'20161201' -- first date in time
AND
FORMAT_DATE("%Y%m%d", CURRENT_DATE())
GROUP BY 1,2
ORDER BY 1 DESC LIMIT 1 -- will get yesterday count and best count in one row
#Option 2 (more readable, take more time)
(5.1 sec, 3,75MB)
SELECT
date,
event,
total,
max(total) OVER(ORDER BY total DESC) as best
FROM (
SELECT FORMAT_TIMESTAMP("%Y%m%d",TIMESTAMP_MICROS(h.timestamp_micros )) as date,
h.name as event,
COUNT(*) as total
FROM `namespace_ANDROID.app_events_*`, UNNEST(event_dim) as h
WHERE
h.name='in_app_purchase'
AND
_TABLE_SUFFIX
BETWEEN
'20161201' -- first date in time
AND
FORMAT_DATE("%Y%m%d", CURRENT_DATE())
GROUP BY 1,2)
ORDER BY 1 DESC LIMIT 1 -- will get yesterday count and best count in one row
## option 3 (using event_dim.date instead of event_dime.timestamp_micros), available only since 20161111
SELECT
date,
event,
total,
max(total) OVER(ORDER BY total DESC) as best
FROM (
h.date as date,
h.name as event,
COUNT(*) as total
FROM `namespace_ANDROID.app_events_*`, UNNEST(event_dim) as h
WHERE
h.name='in_app_purchase'
AND
_TABLE_SUFFIX
BETWEEN
'20161201' -- first date in time
AND
FORMAT_DATE("%Y%m%d", CURRENT_DATE())
GROUP BY 1,2)
ORDER BY 1 DESC LIMIT 1 -- will get yesterday count and best count in one row
##Last Week range (find the previous monday to previous sunday) -> This will help to get the not rounding Weekly events
Be carefull, we cast FORMAT_DATE to INT64 (as it returns STRING)
SELECT
FORMAT_DATE("%Y%m%d", CURRENT_DATE()) as today,
FORMAT_DATE("%u", CURRENT_DATE()) as currentDayOfWeek,
FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL CAST ( FORMAT_DATE("%u", CURRENT_DATE()) as INT64 ) DAY)) AS previousSunday,
FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL CAST ( FORMAT_DATE("%u", CURRENT_DATE()) as INT64 ) + 6 DAY)) AS previousMonday;
To use this in a query, one can extract these fields with the WITH command and reuse them in subqueries
WITH dateQuery AS (
SELECT FORMAT_DATE("%Y%m%d", CURRENT_DATE()) as today,
FORMAT_DATE("%u", CURRENT_DATE()) as currentDayOfWeek,
FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL CAST ( FORMAT_DATE("%u", CURRENT_DATE()) as INT64 ) DAY)) AS previousSunday,
FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL CAST ( FORMAT_DATE("%u", CURRENT_DATE()) as INT64 ) + 6 DAY)) AS previousMonday
)
SELECT
today, currentDayOfWeek, previousSunday, previousMonday
FROM dateQuery;
#Range of dates (using BETWEEN)
SELECT COUNT(*) as counter
FROM `namespace_ANDROID.app_events_*`
WHERE _TABLE_SUFFIX BETWEEN '20161201' AND '20161202'
#Seperated dates (using IN)
SELECT COUNT(*) as counter
FROM `namespace_ANDROID.app_events_*`
WHERE _TABLE_SUFFIX IN ("20160901", "20161202");
#Range of dates (using DATE_ADD)
SELECT COUNT(*) as counter,
FORMAT_DATE("%Y%m%d", CURRENT_DATE()) as today,
FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)) AS oneDayAgo
FROM `namespace_ANDROID.app_events_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)) AND FORMAT_DATE("%Y%m%d", CURRENT_DATE())
#Range of dates (using DATE_SUB instead of negative DATE_ADD)
SELECT COUNT(*) as counter,
FORMAT_DATE("%Y%m%d", CURRENT_DATE()) as today,
FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)) AS oneDayAgo
FROM `namespace_ANDROID.app_events_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AND FORMAT_DATE("%Y%m%d", CURRENT_DATE())
http://stackoverflow.com/questions/40125614/bigquery-firebase-querying-events-order-by-user-property
SELECT
user_properties.value.value.string_value as lang,
AVG((SELECT SUM(value.int_value)
FROM UNNEST(event_dim),
UNNEST(params)
WHERE key = "coins_awarded")) as avg_coins
FROM
`firebase-analytics-sample-data.ios_dataset.app_events_20160607` t,
UNNEST(user_dim.user_properties) user_properties
WHERE
user_properties.key = "language"
GROUP BY 1;
### legacy SQL
SELECT
user_dim.user_properties.value.value.string_value as lang,
AVG(event_dim.params.value.int_value) as avg_coins
FROM
FLATTEN([firebase-analytics-sample-data:ios_dataset.app_events_20160607], user_dim.user_properties)
WHERE
user_dim.user_properties.key = "language"
AND event_dim.params.key = "coins_awarded"
GROUP BY 1
http://stackoverflow.com/questions/38860534/flatten-firebase-exports-to-bigquery-into-tables-where-1-row-1-event-nested-d
SELECT
user_dim.app_info.app_instance_id,
timestamp_micros,
(SELECT value.int_value FROM UNNEST(dim.params) WHERE key = "level") AS level,
(SELECT value.int_value FROM UNNEST(dim.params) WHERE key = "coins_awarded") AS coins,
(SELECT value.int_value FROM UNNEST(dim.params) WHERE key = "powerups") AS powerups
FROM `firebase-analytics-sample-data.ios_dataset.app_events_20160607`, UNNEST(event_dim) AS dim
public static void trackItemsSelectedCount(Context context, int count) {
Bundle bundle = new Bundle();
bundle.putInt("count", count);
getAnalytics(context).logEvent("items_selected", bundle);
}
SELECT
SUM((SELECT params.value.int_value FROM x.params WHERE params.key = 'count')) AS count
FROM package_ANDROID.app_events_20170123, UNNEST(event_dim) AS x
WHERE x.name = 'items_selected'
8:15
Select event_param.value.double_value
FROM `namespace.table.app_events_intraday_20161111`
UNEST(event_dim) as event,
UNEST(event.params) as event_param
WHERE event.name = 'adjust_slider' AND event_param.key ='newValue'
LIMIT 1000
8:24
Select avg(event_param.value.double_value)
FROM `namespace.table.app_events_intraday_20161111`
UNEST(event_dim) as event,
UNEST(event.params) as event_param
WHERE event.name = 'adjust_slider' AND event_param.key ='newValue'
LIMIT 1000
8:54
Select userProp.value.value.string_value as dogOrCat, avg(event_param.value.double_value) as newSliderValue
FROM `namespace.table.app_events_intraday_20161111`
UNEST(user_dim.user_properties) as userProp,
UNEST(event_dim) as event,
UNEST(event.params) as event_param
WHERE event.name = 'adjust_slider' AND event_param.key ='newValue' AND userProp.key='dogOrCatPerson'
GROUPY dogOrCat
SELECT
FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL CAST ( FORMAT_DATE("%u", CURRENT_DATE()) as INT64 ) + 6 DAY)) AS previousMonday,
FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL CAST ( FORMAT_DATE("%u", CURRENT_DATE()) as INT64 ) DAY)) AS previousSunday,
COUNT(*) as counter,
h.name as event
FROM `namespace.app_events_*`, UNNEST(event_dim) as h
WHERE _TABLE_SUFFIX
BETWEEN
FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL CAST ( FORMAT_DATE("%u", CURRENT_DATE()) as INT64 ) + 6 DAY))
AND
FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL CAST ( FORMAT_DATE("%u", CURRENT_DATE()) as INT64 ) DAY))
GROUP BY event
ORDER BY counter DESC
####
SELECT
COUNT(*) as counter,
h.name as event
FROM `firebase-analytics-sample-data.android_dataset.app_events_*`, UNNEST(event_dim) as h
WHERE _TABLE_SUFFIX
BETWEEN
'20160601' AND '20160607'
GROUP BY event
ORDER BY counter DESC
SELECT
COUNT(*) as counter,
h.name as event
FROM `firebase-analytics-sample-data.android_dataset.app_events_*`, UNNEST(event_dim) as h
WHERE _TABLE_SUFFIX
BETWEEN
'20160601' AND '20160603'
GROUP BY event
UNION DISTINCT
SELECT
COUNT(*) as counter,
g.name as event
FROM `firebase-analytics-sample-data.android_dataset.app_events_*`, UNNEST(event_dim) as g
WHERE _TABLE_SUFFIX
BETWEEN
'20160604' AND '20160607'
GROUP BY event
ORDER BY counter DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment