铅& BigQuery中的分析功能 [英] Lead & Analytical Functions in BigQuery

问题描述

假设我的桌子是这个

我正在尝试使用此信息修改我的表

I am trying to modify my table with this information

我添加了两列，其中列WhenWasLastBasicSubjectDone将让您知道学生在哪个学期完成了最新的基础课程(按学期排序). TotalBasicSubjectsDoneTillNow的另一列说明该学生到目前为止完成了多少次基础课程(学科)(按学期排序)?

I have added two columns where column WhenWasLastBasicSubjectDone will let you know when in which semester the student completed his latest Basic Course (sorted by Semester). The other column TotalBasicSubjectsDoneTillNow explains how many times had the student completed Basic Course(Subject) till now (sorted by Semester) ?

我认为这很容易通过Joins和UDF解决，但是我想利用BigQuery中现有分析功能的强大功能，并在没有joins的情况下解决它.

I think this is easy to solve with Joins as well as with UDFs but I want to use the power of existing analytical functions in BigQuery and solve it without joins.

推荐答案

您可以为此使用窗口函数-假设您有一列指定排序的列.让我假设列是semester:

You can use window functions for this -- assuming you have a column that specifies ordering. Let me assume that column is 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中的分析功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！