可获得数据的最近日期的平均值 [英] Average on the most recent date for which data is available

问题描述

我有一个表格(在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

这篇关于可获得数据的最近日期的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！