可获得数据的最近日期的平均值

本文介绍了可获得数据的最近日期的平均值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个表格(在BigQuery上)，如下所示:

I have a table (on BigQuery) that looks like the following:

日期类型得分

2021-01-04	A	5
2021-01-04	A	4
2021-01-04	A	5
2021-01-02	A	1
2021-01-02	A	1
2021-01-01	A	3
2021-01-04	B	NULL
2021-01-04	B	NULL
2021-01-02	B	NULL
2021-01-02	B	NULL
2021-01-01	B	2
2021-01-01	B	5
2021-01-04	C	NULL
2021-01-04	C	4
2021-01-04	C	NULL
2021-01-01	C	1
2021-01-01	C	2
2021-01-01	C	3

我想得到的是每种类型的平均分数，但该平均值应仅在该类型至少可获得一个分数的最近日期获得.从上面的示例中，目标是获得下表:

What I would like to get is the average score for each type but the average should be taken only on the most recent date for which at least one score is available for the type. From the example above, the aim is to obtain the following table:

类型 AVG得分

A	(5 + 4 + 5)/3
B	(2 + 5)/2
C	(4)/1

如果我希望平均得分(不是针对每种类型，而是针对两列(类型/颜色)的每种组合)的平均得分，仍然需要至少一个得分可用的最新日期，我需要一个可以调整的解决方案组合.

I need a solution that could be adapted if I want the average score, not for each type, but for each combination of two columns (type/color), still on the most recent date for which at least one score is available for the combination.

推荐答案

替代解决方案如下，您可以尝试:-

An alternative solution is as given below, you can try it:-

SELECT type, AVG(score) FROM mytable WHERE score IS NOT NULL and (type, date1) in ( SELECT (type, max(cast (date1 as date))) FROM mytable WHERE score IS NOT NULL GROUP BY type ) GROUP BY type