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 cnt
FROM  
  [表名]  
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    
   user_dim.app_info.app_version,count(user_dim.app_info.app_version ) as cnt
FROM    
  [表名]
WHERE  
  event_dim.name = 'battery5'and  event_dim.params.key='action'and event_dim.params.value.string_value ='进入充电报告'
  group by user_dim.app_info.app_version 
  order by cnt desc

查询人数 按照版本发布

SELECT
  user_dim.app_info.app_version,
  COUNT(user_dim.app_info.app_version) AS cnt
FROM (
  SELECT
    user_dim.app_info.app_version,
    user_dim.app_info.app_instance_id
  FROM
    [表名]
  WHERE
    event_dim.name ='ADSDK_广告位'
    AND event_dim.params.value.string_value ='显示_FULL'
  GROUP BY
    user_dim.app_info.app_version,
    user_dim.app_info.app_instance_id )
GROUP BY
  user_dim.app_info.app_version
ORDER BY
  cnt DESC

user_dim.app_info.app_store 应用商店
user_dim.app_info.app_id  仅仅查询次数  
所有语法都支持多表连查  就是把上面的[表名]替换成

[表名],[表名],[表名]

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

SELECT
  event_dim.params.value.string_value,
  COUNT(*) AS cnt
FROM
  [表名]
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_value
ORDER 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

某版本人数

SELECT  
   count(user_dim.app_info.app_instance_id) as cnt
FROM  
  [oneforfour-17568:com_tools_batterydoctor_ANDROID.app_events_20180310]  
WHERE
  user_dim.app_info.app_version='1.0.9'
  group by user_dim.app_info.app_instance_id 
  order by cnt desc