google-bigquery - 在 BigQuery 中使用 Where 子句进行切片
问题描述
我的查询有问题,试图计算术语和会话之间的分数变化,每次我尝试按会话切片时,如下面的查询所示,更改列在所有行中返回“null”,但如果我删除会话切片,更改列返回所需的值。
卡表
name id subject session term classroom ca_cat score
one 1 maths 2018/2019 1 level1 1 10
one 1 maths 2018/2019 1 level1 2 6
two 2 maths 2018/2019 1 level1 1 9
two 2 maths 2018/2019 1 level1 2 7
one 1 maths 2018/2019 2 level1 1 9
one 1 maths 2018/2019 2 level1 2 8
two 2 maths 2018/2019 2 level1 1 7
two 2 maths 2018/2019 2 level1 2 5
考试桌
name id course session term classroom score
one 1 maths 2018/2019 1 level1 50
two 2 maths 2018/2019 1 level1 49
one 1 maths 2018/2019 2 level1 50
two 2 maths 2018/2019 2 level1 50
询问
select
studentid,
name,
subject,
classroom,
session,
term,
round(avg_score,0) as average_score,
round(avg_score - lag(avg_score, 1) over(partition by name, subject order by session) / 100*100, 0) as change,
from (
select studentid, name, subject, classroom, session, term, avg(totalscore) over(partition by name, classroom, session, term) avg_score
from (
SELECT name, studentid, subject, classroom, session, term, caone, catwo, exam,
caone + catwo + exam AS totalscore
FROM (
SELECT name, studentid, subject, classroom, session, term,
MAX(IF(cacount = 1, ca.score, NULL)) AS caone,
MAX(IF(cacount = 2, ca.score, NULL)) AS catwo,
ANY_VALUE(ex.score) AS exam
FROM `exam` ex
JOIN `catable` ca
USING (name, studentid, subject, classroom, session, term)
GROUP BY name, studentid, subject, classroom, session, term
)
))
where studentid=1 and session='2018/2019' and term=2
group by studentid, name, subject, classroom, session,term, avg_score
我的预期输出是这样的
name, id, subject, classroom, session, term, totalscore change
one 1 math level1 2018/2019 1 66 null
one 1 math level1 2018/2019 2 67 1
two 2 math level1 2018/2019 1 65 null
two 2 math level1 2018/2019 2 62 -3
分数的变化是术语之间的差异,例如 2nd-1st 和 3rd-2nd
有解决方法吗?我已经尝试了所有我所知道的似乎无法找到为什么它会以这种方式响应或问题可能是什么。
解决方案
以下是 BigQuery 标准 SQL
#standardSQL
SELECT *,
totalscore - LAG(totalscore) OVER(PARTITION BY name, id, subject, classroom, session ORDER BY term) AS change
FROM (
SELECT name, id, subject, classroom, session, term, caone + catwo + exam AS totalscore
FROM (
SELECT name, id, subject, classroom, session, term,
MAX(IF(ca_cat = 1, ca.score, NULL)) AS caone,
MAX(IF(ca_cat = 2, ca.score, NULL)) AS catwo,
ANY_VALUE(ex.score) AS exam,
FROM `project.dataset.exam` ex
JOIN `project.dataset.catable` ca
USING (name, id, subject, classroom, session, term)
GROUP BY name, id, subject, classroom, session, term
)
)
-- ORDER BY name, id, subject, classroom, session, term
当应用于您问题的样本数据时 - 结果是
Row name id subject classroom session term totalscore change
1 one 1 maths level1 2018/2019 1 66 null
2 one 1 maths level1 2018/2019 2 67 1
3 two 2 maths level1 2018/2019 1 65 null
4 two 2 maths level1 2018/2019 2 62 -3
推荐阅读
- angularjs - 嵌套(第一个)自定义指令另一个(第二个)指令将(第一个)自定义指令移动到子作用域而不是作用域
- c# - 如何使用 Castle Windsor 的基本接口注册服务工厂模式
- c# - 以定时间隔开始/停止在 UDP 端口上接收数据
- r - 计算R中数据帧中ID的出现次数
- reactjs - 如何使用它的snake_case名称动态加载图标(React,material-ui)
- python - 如何在此配置中使用外部 python 包
- python - Python:将 csv 文件的内容添加到列表中
- php - 如何为具有多个变量的回声函数设置样式
- javascript - 使用服务帐户的 Google drive api rest 上传文件不起作用
- python - 无法将我的应用程序与 django 中的文本编辑器集成