BigQuery & Firebase - 基于键的事件值求和

Question

在我的安卓应用程序中，我记录了用户选择的项目数，如下所示

public static void trackItemsSelectedCount(Context context, int count) {
    Bundle bundle = new Bundle();
    bundle.putInt("count", count);
    getAnalytics(context).logEvent("items_selected", bundle);
}

在 BigQuery 中，我能够计算具有事件items_selected的条目数，如下所示

SELECT event_dim.name as event,
COUNT(event_dim.name) as event_count
FROM TABLE_DATE_RANGE(package_ANDROID.app_events_intraday_, CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP())
where event_dim.name contains 'items_selected'
GROUP BY event
ORDER BY event DESC LIMIT 1;

我想得到event_dim.params.value.int_value的总和，至于count event_dim.params.key

到目前为止，我正在执行以下操作，但这可能不准确（因为event_dim.params.key没有出现在任何地方）。任何更精确查询的想法将不胜感激：

SELECT event_dim.name as event,
COUNT(event_dim.name) as event_count ,
COUNT(event_dim.params.value.int_value) as value_count
FROM TABLE_DATE_RANGE(package_ANDROID.app_events_intraday_, CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP())
where event_dim.name contains 'items_selected'
GROUP BY event
ORDER BY event DESC LIMIT 1;

答

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'

Answer 1

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'