SQL移动聚合SUM，不包含部分结果

Question

假设我有这个模式(在postgresql上测试)，其中'Scorelines'关系包含体育比赛的结果。(kickoff是TIMESTAMP，但为了可读性被INT替换)

SQLFiddle here: http://sqlfiddle.com/#!12/52475/3

CREATE TABLE Scorelines (
   team TEXT, 
   kickoff INT,
   scored INT,
   conceded INT
);

现在我想生成另一个列' three_matches_scores '，它包含得分的总和超过前三场比赛(由开球决定)的同一队。我有这个:

SELECT team, kickoff, scored, conceded, SUM(scored) OVER three_matches AS three_matches_scored 
FROM Scorelines
WINDOW three_matches AS
      (PARTITION BY team ORDER BY kickoff
       ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING)
ORDER BY kickoff;

到目前为止，这工作得很好，除了我从第二个游戏开始获得值。例子:

| TEAM | KICKOFF | SCORED | CONCEDED | THREE_MATCHES_SCORED |
|------|---------|--------|----------|----------------------|
|    A |       1 |      1 |        0 |               (null) |
|    B |       2 |      1 |        1 |               (null) |
|    A |       3 |      1 |        1 |                    1 |
|    A |       4 |      3 |        0 |                    2 |
|    B |       4 |      1 |        4 |                    1 |
|    A |       6 |      0 |        2 |                    5 |
|    B |       6 |      4 |        2 |                    2 |
|    B |       8 |      1 |        2 |                    6 |
|    B |      10 |      1 |        1 |                    6 |
|    A |      11 |      2 |        1 |                    4 |

我希望列' three_matches_scores '为(null)前3场比赛，因为没有3个结果汇总。我怎样才能做到这一点呢?

我更喜欢简单易懂的解决方案，性能对这种特殊情况并不重要。

我现在唯一的想法是定义一个存储函数SUM3，结果是(null)与少于3个值相加。但是我从来没有在SQL中定义一个函数，似乎不能弄清楚。

Answer 1

您可以使用case语句将少于3个游戏的行设置为空:

SELECT team, kickoff, scored, conceded,
    CASE WHEN COUNT(scored) OVER three_matches = 3 
    THEN SUM(scored) OVER three_matches 
    ELSE NULL 
    END AS three_matches_scored
FROM Scorelines
WINDOW three_matches AS
(PARTITION BY team ORDER BY kickoff
ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING)
ORDER BY kickoff;

输出:

 team | kickoff | scored | conceded | three_matches_scored
------+---------+--------+----------+----------------------
 A    |       1 |      1 |        0 |
 B    |       2 |      1 |        1 |
 A    |       3 |      1 |        1 |
 A    |       4 |      3 |        0 |
 B    |       4 |      1 |        4 |
 A    |       6 |      0 |        2 |                    5
 B    |       6 |      4 |        2 |
 B    |       8 |      1 |        2 |                    6
 B    |      10 |      1 |        1 |                    6
 A    |      11 |      2 |        1 |                    4
(10 rows)

Answer 2

参见上面的答案

(我的第一个解决方案，仅供参考)

用户定义聚合的解决方案:

CREATE TYPE intermediate_sum AS (
   sum INT,
   count INT
);

CREATE FUNCTION sum_sfunc(intermediate_sum, INTEGER) RETURNS intermediate_sum AS
$$ SELECT $2 + $1.sum AS sum, $1.count - 1 AS count $$ LANGUAGE SQL;
CREATE FUNCTION sum_ffunc(intermediate_sum) RETURNS INTEGER AS
$$ SELECT (CASE WHEN $1.count > 1 THEN null
                WHEN $1.count = 0 THEN $1.sum
           END)
$$ LANGUAGE SQL;
CREATE AGGREGATE sum3(INTEGER) (
    sfunc = sum_sfunc,
    finalfunc = sum_ffunc,
    stype = intermediate_sum,
    initcond = '(0,3)'
);

聚合SUM3至少需要3个值，否则返回(null)。可以通过更改initcond来定义其他聚合，如SUM4，例如为'(0,4)'。