BigQuery中的领导和分析功能

您可以为此使用窗口函数——假设您有一个指定排序的列。我假设这个专栏是 semester :

select t.*,
       max( case when subject = 'Basic' then semester end ) over (partition by student order by semester end) as lastbasic,
       sum( case when subject = 'Basic' then 1 else 0 end ) over (partition by student order by semester end) as numbasictillnow    
from t

以下是BigQuery标准SQL

#standardSQL
SELECT *,
  LAST_VALUE(IF(subject='Basic',semester,NULL) IGNORE NULLS) OVER(win) AS WhenWasLastBasicSubjectDone ,
  COUNTIF(subject='Basic') OVER(win) AS TotalBasicSubjectsDoneTillNow     
FROM `project.dataset.table`
WINDOW win AS (PARTITION BY student ORDER BY semester)

您可以使用下面问题中的虚拟数据进行测试、玩上面的游戏

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 Student, 'Sub1' Subject, 'Sem1' Semester UNION ALL
  SELECT 1, 'Sub2', 'Sem2' UNION ALL
  SELECT 1, 'Basic', 'Sem3' UNION ALL
  SELECT 1, 'Basic', 'Sem4' UNION ALL
  SELECT 1, 'Sub3', 'Sem5' UNION ALL
  SELECT 1, 'Sub2', 'Sem6' UNION ALL
  SELECT 1, 'Sub3', 'Sem7' UNION ALL
  SELECT 1, 'Sub4', 'Sem8' 
)
SELECT *,
  LAST_VALUE(IF(subject='Basic',semester,NULL) IGNORE NULLS) OVER(win) AS WhenWasLastBasicSubjectDone ,
  COUNTIF(subject='Basic') OVER(win) AS TotalBasicSubjectsDoneTillNow     
FROM `project.dataset.table`
WINDOW win AS (PARTITION BY student ORDER BY semester)
-- ORDER BY Semester