Google bigQuery 查询语句合集

普通查询

SELECT  
   event_dim.*  
FROM  
  [表名]  
WHERE
  event_dim.name = 'battery1' and event_dim.params.value.string_value ='进入充电报告'

普通查询3个参数

SELECT     event_dim.*  FROM    [表名]  WHERE  event_dim.name = '广告'and  event_dim.params.key='广告显示成功' and event_dim.params.value.string_value ='进入充电报告'  

查询使用人数 并且排序

SELECT     user_dim.app_info.app_instance_id,count(user_dim.app_info.app_instance_id) as cntFROM    [表名]  WHERE  event_dim.name = 'battery1' and event_dim.params.value.string_value ='进入充电报告'  group by user_dim.app_info.app_instance_id   order by cnt desc

模糊查询 人数 且按数量排序

SELECT  event_dim.params.value.string_value,  COUNT(*) AS cntFROM  [表名]WHERE  event_dim.name = 'battery1'  AND event_dim.params.value.string_value LIKE '%cpu_accessible_service_need_click_text_%'GROUP BY  event_dim.params.value.string_valueORDER BY  cnt DESC;

当天安装

SELECT  COUNT(user_dim.app_info.app_instance_id)FROM  [表名]WHERE  event_dim.name = 'first_open';

当天安装里面 当天又卸载的人数

SELECT  COUNT(user_dim.app_info.app_instance_id)FROM    [表名]WHERE  user_dim.app_info.app_instance_id IN (  SELECT    user_dim.app_info.app_instance_id  FROM    [表名]  WHERE    event_dim.name = 'first_open' )  AND event_dim.name = 'app_remove'

当天安装里面 次日又卸载的人数

SELECT  COUNT(user_dim.app_info.app_instance_id)FROM    [表名1]WHERE  user_dim.app_info.app_instance_id IN (  SELECT    user_dim.app_info.app_instance_id  FROM    [表名2]  WHERE    event_dim.name = 'first_open' )  AND event_dim.name = 'app_remove'

----表名1  是比表名2 大于1天的表名  如 表名1为  app_events_intraday_20170317 则 表名2为 app_events_intraday_20170316