SELECT user_dim.app_info.app_instance_id, user_dim.device_info.device_category, user_dim.device_info.user_default_language, user_dim.device_info.platform_version, user_dim.device_info.device_model, user_dim.geo_info.country, user_dim.geo_info.city, user_dim.app_info.app_version, user_dim.app_info.app_store, user_dim.app_info.app_platform FROM [firebase-analytics-sample-data:ios_dataset.app_events_20160601]
user_dim.app_info.app_instance_id
SELECT user_dim.geo_info.country as country, EXACT_COUNT_DISTINCT( user_dim.app_info.app_instance_id ) as users FROM [firebase-analytics-sample-data:android_dataset.app_events_20160601], [firebase-analytics-sample-data:ios_dataset.app_events_20160601] GROUP BY country ORDER BY users DESC
SELECT user_dim.user_properties.value.value.string_value as language_code, EXACT_COUNT_DISTINCT(user_dim.app_info.app_instance_id) as users, FROM [firebase-analytics-sample-data:android_dataset.app_events_20160601], [firebase-analytics-sample-data:ios_dataset.app_events_20160601] WHERE user_dim.user_properties.key = "language" GROUP BY language_code ORDER BY users DESC
SELECT event_dim.name, COUNT(event_dim.name) as event_count FROM [firebase-analytics-sample-data:android_dataset.app_events_20160601] GROUP BY event_dim.name ORDER BY event_count DESC
SELECT event_dim.params.value.int_value as virtual_currency_amt, COUNT(*) as num_times_spent FROM [firebase-analytics-sample-data:android_dataset.app_events_20160601] WHERE event_dim.name = "spend_virtual_currency" AND event_dim.params.key = "value" GROUP BY 1 ORDER BY num_times_spent DESC
SELECT user_dim.geo_info.city, COUNT(user_dim.geo_info.city) as city_count FROM TABLE_DATE_RANGE([firebase-analytics-sample-data:android_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()), TABLE_DATE_RANGE([firebase-analytics-sample-data:ios_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()) GROUP BY user_dim.geo_info.city ORDER BY city_count DESC
SELECT user_dim.app_info.app_platform as appPlatform, user_dim.device_info.device_category as deviceType, COUNT(user_dim.device_info.device_category) AS device_type_count FROM TABLE_DATE_RANGE([firebase-analytics-sample-data:android_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()), TABLE_DATE_RANGE([firebase-analytics-sample-data:ios_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()) GROUP BY 1,2 ORDER BY device_type_count DESC
SELECT STRFTIME_UTC_USEC(eventTime,"%Y%m%d") as date, appPlatform, eventName, COUNT(*) totalEvents, EXACT_COUNT_DISTINCT(IF(userId IS NOT NULL, userId, fullVisitorid)) as users FROM ( SELECT fullVisitorid, openTimestamp, FORMAT_UTC_USEC(openTimestamp) firstOpenedTime, userIdSet, MAX(userIdSet) OVER(PARTITION BY fullVisitorid) userId, appPlatform, eventTimestamp, FORMAT_UTC_USEC(eventTimestamp) as eventTime, eventName FROM FLATTEN( ( SELECT user_dim.app_info.app_instance_id as fullVisitorid, user_dim.first_open_timestamp_micros as openTimestamp, user_dim.user_properties.value.value.string_value, IF(user_dim.user_properties.key = 'user_id',user_dim.user_properties.value.value.string_value, null) as userIdSet, user_dim.app_info.app_platform as appPlatform, event_dim.timestamp_micros as eventTimestamp, event_dim.name AS eventName, event_dim.params.key, event_dim.params.value.string_value FROM TABLE_DATE_RANGE([firebase-analytics-sample-data:android_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()), TABLE_DATE_RANGE([firebase-analytics-sample-data:ios_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()) ), user_dim.user_properties) ) GROUP BY date, appPlatform, eventName
public class ImmutableDatastoreEntity {@IdLong versionId;@ParentKey<T> firstEntityInChain;protected Long consistentId;protected boolean isTip;Key<User> savedByUser;}
ImmutableDatastoreEntity entity = new ImmutableDatastoreEntity();entity.setVersionId(DAO.allocateId(this.getClass()));entity.setConsistentId(entity.getVersionId());entity.setFirstEntityInChain((Key<T>) Key.create(entity.getClass(), entity.versionId));entity.setTip(true);
// start transactionImmutableDatastoreEntity oldVersion = getImmutableEntity(immutableId) oldVersion.setTip(false);ImmutableDatastoreEntity newVersion = oldVersion.clone(); // make the user edits needed newVersion.setVersionId(null);newVersion.setConsistentId(this.getConsistentId());newVersion.setFirstEntityInChain(oldVersion.getFirstEntityInChain()); // .clone also performs the last two lines but just to be explicit this, just fyi newVersion.setTip(true); ofy().save(oldVersion, newVersion).now(); // end transaction
consistentId
Key ancestorKey = KeyFactory.createKey(ImmutableDatastoreEntity.class, consistentId);ImmutableDatastoreEntity e = ofy().load().kind(ImmutableDatastoreEntity.class).filter("consistentId", consistentId).filter("isTip", true).ancestor(ancestorKey) // this limits our query to just the 1 entity group.list() .first();
// wrap block in transactionImmutableDatastoreEntity oldVersion = getImmutableEntity(immutableId);oldVersion.setTip(false);ofy().save(oldVersion, newVersion).now();
List<ImmutableDatastoreEntity> results = ofy().load().kind(ImmutableDatastoreEntity.class).filter("isTip", true).filter(/** apply other filters here */) .list();
Key ancestorKey = KeyFactory.createKey(ImmutableDatastoreEntity.class, consistentId);List<ImmutableDatastoreEntity> versions = ofy().load().kind(ImmutableDatastoreEntity.class).filter("consistentId", consistentId).ancestor(ancestorKey) .list();