一种方法使用相关子查询：

select id, count(*)
from t
where visitnumber < (select min(t2.visitnumber) from t t2 where t2.id = t.id and type = 'shop')
group by id;

但是，在bigquery中，我更喜欢使用窗口函数的方法：

select id, countif(visitnumber < visitnumber_shop)
from (select t.*,
             min(case when type = 'shop' then visitnumber end) over (partition by id) as visitnumber_shop
      from t
     ) t
group by id;

这样做的好处是 id 甚至是那些没有“商店”类型的。

一个选项使用子查询进行筛选：

select id, count(*) number_of_visits
from mytable t
where t.visit_number < (
    select min(t1.visit_number)
    from mytable t
    where t1.id = t.id and t1.type = 'shop'
)
group by id

也可以使用窗口功能：

select id, count(*) number_of_visits
from (
    select 
        t.*, 
        countif(type = 'shop') over(partition by id order by visit_number) has_shop
    from mytable t
) t
where has_shop = 0
group by id

下面的选项用于bigquery标准sql

# standardSQL

SELECT id, 
  ARRAY_LENGTH(SPLIT(REGEXP_EXTRACT(',' || STRING_AGG(type ORDER BY visitnumber), r'(.*?),shop'))) - 1 AS number_of_visits_before_first_shop
FROM `project.dataset.table`
GROUP BY id

您可以使用下面的示例中的虚拟数据来测试、播放上面的内容

# standardSQL

WITH `project.dataset.table` AS (
  SELECT '01' id, 1 visitnumber, 'register' type UNION ALL
  SELECT '01', 2, 'visit' UNION ALL
  SELECT '01', 3, 'visit' UNION ALL
  SELECT '01', 4, 'shop' UNION ALL
  SELECT '01', 5, 'visit' UNION ALL
  SELECT '02', 1, 'register' UNION ALL
  SELECT '02', 2, 'visit' UNION ALL
  SELECT '02', 3, 'visit' UNION ALL
  SELECT '03', 1, 'shop' UNION ALL
  SELECT '03', 2, 'shop' UNION ALL
  SELECT '03', 3, 'visit' 
)
SELECT id, 
  ARRAY_LENGTH(SPLIT(REGEXP_EXTRACT(',' || STRING_AGG(type ORDER BY visitnumber), r'(.*?),shop'))) - 1 AS number_of_visits_before_first_shop
FROM `project.dataset.table`
GROUP BY id

有结果的

Row id  number_of_visits_before_first_shop   
1   01  3    
2   02  null     
3   03  0

这是我使用analytics 360测试数据集对大查询运行的查询：

select 
id,
visitnumber,
countif(hit_number < hitnumber_quickviewclick) as hitsprev_quickviewclick
from (
      select 
      a.fullVisitorID as id,
      a.visitnumber as visitnumber,
      h.hitNumber as hit_number,
      MIN (case when h.eventInfo.eventAction = 'Quickview Click' then h.hitNumber end) over (partition by a.fullVisitorID) as hitnumber_quickviewclick
      FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170725` as a
      CROSS JOIN UNNEST(hits) as h
     ) as T
group by 1,2;

我想做一个查询，在那里我可以找到事件操作“快速查看单击”命中之前的总命中数。如果这是错误的或可以改善让我知道！
非常感谢，伙计们！

一般来说，我会这样处理sql：

select count(*)
from yourtable yt
where type = 'visit' and not exists (
    select 1
    from yourtable yt2
    where yt.id > yt2.id and yt2.type = 'shop'
)

然而，当我们想在下一家店之前找到拜访者时，我会考虑很多情况。。。下一家商店。。。还有下一家商店。为此，您可以按时间间隔查找商店和组的ID。