代码之家 › 专栏 › 技术社区 › phaigeim

BigQuery中的领导和分析功能

state google-bigquery join performance sql

phaigeim · 技术社区 · 6 年前

假设我的桌子是这个

我正试图用这个信息修改我的表

我添加了两列,其中column whenwaslastbasicsubjectdone 将告诉您学生在哪个学期完成了最新的基础课程(按学期排序)。另一列 totalbasicSubjectsDonetillNow 说明学生到目前为止完成基础课程(主题)的次数(按学期排序)?


   
    
     
      
       我认为使用联接和UDF很容易解决这一问题,但我希望使用bigquery中现有分析函数的强大功能,并在不使用联接的情况下解决它。
       
       
      
      
       我正试图用这个信息修改我的表
      
      
       
        
       
      
      
      
      
       我添加了两列,其中列
       
        WhenWasLastBasicSubjectDone
       
       会让你知道学生在哪个学期完成了最新的基础课程(按学期排序)。另一列
       
        TotalBasicSubjectsDoneTillNow
       
       解释到目前为止学生完成基础课程(科目)的次数(按学期排序)?
      
      
       我认为使用连接和UDF都很容易解决这个问题,但是我想在BigQuery中使用现有分析函数的强大功能,并在不使用连接的情况下解决它。

2 回复 | 直到 6 年前

Gordon Linoff 6 年前

您可以为此使用窗口函数——假设您有一个指定排序的列。我假设这个专栏是 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

Mikhail Berlyant 6 年前

以下是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

推荐文章

Hal · 如何在bigquery中从长数据创建列表?

2 年前

Danne Paredes · 如何:对于每个唯一id,对于每个唯一版本,获取最佳分数并将其组织到一个表中

2 年前

Justin · 从字符串中提取日期的大查询

2 年前

Evans Gunawan · 如何使用sql bigquery中的前一列值将数据重塑为新列?

2 年前

Fernando Gomes · 这是本月的第一周。一周从周一开始,周日结束

2 年前

Bill LaLonde · 此错误意味着什么:从位置0开始的行中缺少列索引8所需的列值

2 年前

vinay lokwani · 在大查询中的嵌套字段中插入用于输入数据的语句

2 年前

anat · 在BigQuery中的特定字符串后查找n个字符

2 年前

SaravanaKumar Prithiviraj · 如何在bigquery资源中检测权限删除

2 年前

Anil · 在Google Big\u查询中显示每月的第一天

6 年前