Блог

Предположим, у меня есть эта схема (протестирована на postgresql), где отношение «Scorelines» содержит результаты спортивных матчей. (kickoff — это ВРЕМЕННАЯ МЕТКА, но заменена на INT для удобства чтения)

SQLFiddle здесь: http://sqlfiddle.com /#!12/52475/3

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

Теперь я хочу создать еще один столбец ‘three_matches_scored’, который содержит сумму очков, набранных
за 3 предыдущих игры (определяется по началу) той же команды. У меня есть это:

 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_scored’ был (нулевым) для первых 3 игр, потому что нет 3 результатов для суммирования. Как я могу этого добиться?

Я бы предпочел простые понятные решения, производительность не критична для данного конкретного случая.

Моя единственная идея прямо сейчас — определить хранимую функцию SUM3, которая приводит к (null) с суммированием менее 3 значений. Но я никогда не определял функцию в SQL и, похоже, не могу ее понять.

Вы можете использовать оператор 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)
  

См. Ответ harmics выше.

(мое первое решение, просто для справки)

Решение с определяемым пользователем агрегатом:

 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)'
);
  

Совокупная СУММА3 требует не менее 3 значений, в противном случае она возвращает (null). Можно определить другие агрегаты, такие как SUM4, изменив initcond, например, на ‘(0,4)’.