我有以下数据集:
player | score | day
--------+-------+------------
John | 3 | 02-01-2014
John | 5 | 02-02-2014
John | 7 | 02-03-2014
John | 9 | 02-04-2014
John | 11 | 02-05-2014
John | 13 | 02-06-2014
Mark | 2 | 02-01-2014
Mark | 4 | 02-02-2014
Mark | 6 | 02-03-2014
Mark | 8 | 02-04-2014
Mark | 10 | 02-05-2014
Mark | 12 | 02-06-2014
给定两个时间范围:
02-01-2014..02-03-2014
02-04-2014..02-06-2014
我需要获取给定时间范围内每个玩家的平均得分。我想要达到的最终结果是:
player | period_1_score | period_2_score
--------+----------------+----------------
John | 5 | 11
Mark | 4 | 10
我想到的原始算法是:
SELECT
用两个值执行,这是通过将每个时间段的分数集划分为两个而得出的SELECT
,执行另一个,按玩家名称分组。我陷入了第一步:运行以下查询:
SELECT
player,
AVG(score) OVER (PARTITION BY day BETWEEN '02-01-2014' AND '02-03-2014') AS period_1,
AVG(score) OVER (PARTITION BY day BETWEEN '02-04-2014' AND '02-06-2014') AS period_2;
让我得到错误的结果(请注意分数period1
和period2
平均分数的分数相同:
player | period_1_score | period_2_score
--------+----------------+----------------
John | 5 | 5
John | 5 | 5
John | 5 | 5
John | 5 | 5
John | 5 | 5
John | 5 | 5
Mark | 4 | 4
Mark | 4 | 4
Mark | 4 | 4
Mark | 4 | 4
Mark | 4 | 4
Mark | 4 | 4
我想我不完全了解窗口功能的工作原理...我有2个问题:
您不需要为此提供窗口功能。尝试:
select
player
,avg(case when day BETWEEN '02-01-2014' AND '02-03-2014' then score else null end) as period_1_score
,avg(case when day BETWEEN '02-04-2014' AND '02-06-2014' then score else null end) as period_1_score
from <your data>
group by player
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句