Вычислить текущее соотношение двух сумм

У меня есть база данных PostgreSQL 9.4.1 (данные Retrosheet) с таблицей events, содержащей одну строку для каждой игры в бейсбол. Я хочу вычислить текущее среднее значение для данного игрока: формула (общее количество попаданий на данный момент)/(общее количество действительных летучих мышей на данный момент).

Я могу использовать оконные функции, чтобы получить общее количество попаданий для Дэвида Ортиса, чей код игрока ortid001, используя следующий запрос:

SELECT count(*) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
FROM events WHERE bat_id='ortid001' AND (event_cd='20' OR event_cd='21' 
OR event_cd='22' OR event_cd='23');

(Предложение, включающее event_cd, просто определяет, какие строки считаются совпадениями.)

Используя тот же метод, я могу получить промежуточное количество at-bats (предложение event_cd отклоняет каждую строку, которая не считается at-bat. обратите внимание, что выбранные выше совпадения являются подмножеством at-bats):

SELECT count(*) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
FROM events WHERE bat_id='ortid001' AND (event_cd != '11' AND 
event_cd!='14' AND event_cd!='15' AND event_cd!='16' AND     
event_cd!='17');

Как я могу совместить это? В идеале для каждой строки, описывающей игру с bat_id='some_player_id', я должен вычислить две функции: количество всех предыдущих строк, описывающих биту, и количество всех предыдущих строк, описывающих попадания. Разделив их, вы получите текущее среднее значение в этом ряду.


postgresql sql window-functions aggregate-filter
person Michael Curry    schedule 13.03.2015    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Предполагая (поскольку он не был объявлен) event_cd имеет тип данных integer и может быть NULL.

SELECT *, round(hit::numeric / at_bat, 2) AS rate
FROM  (
   SELECT input_ts
        , count(*) FILTER (WHERE event_cd = ANY ('{20,21,22,23}'::int[]))
                   OVER (ORDER BY input_ts) AS hit
        , count(*) FILTER (WHERE NOT (event_cd = ANY ('{11,14,15,16,17}'::int[]))) 
                   OVER (ORDER BY input_ts) AS at_bat
   FROM   events
   WHERE  bat_id = 'ortid001'
   ) sub
ORDER  BY input_ts;

Поскольку вы используете стр. 9.4, вы можете использовать новое предложение агрегата FILTER. Связанный ответ:

Определение фрейма ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW используется по умолчанию, поэтому вам не нужно его объявлять.

Но в таблице базы данных нет естественного порядка. Не путайте это с электронной таблицей. Вам нужно определить его с помощью ORDER BY. Я использую воображаемый столбец input_ts, замените его (списком) столбцов, которые определяют ваш порядок сортировки. Более:

Я избегаю NOT IN, потому что он демонстрирует хитрое поведение со значениями NULL.

Приведение к numeric должно избежать целочисленного деления, которое обрезало бы дробные цифры и приводило к сомнительной полезности. Округление результата до двух дробных цифр.

person Erwin Brandstetter    schedule 13.03.2015
comment
Спасибо; есть ли способ явно заказать по первичному ключу? Или это так же плохо, как полагаться на текущий порядок по умолчанию? - person Michael Curry; 13.03.2015
comment
@MichaelCurry: Конечно, просто укажите там имена столбцов PK. Хорошо/плохо зависит от ваших требований. Порядок по умолчанию без ORDER BY — это просто произвольный порядок, который может измениться в любое время. Именно так Postgres может отображать строки наиболее удобным способом, как правило, в текущем физическом порядке строк на диске, который часто совпадает с порядком вставки строк. Но это может измениться в любое время, с VACUUM или CLUSTER или любым UPDATE или DELETE и т. д. - person Erwin Brandstetter; 13.03.2015

arrow_upward
1
arrow_downward

Используйте условную агрегацию. Вы не указали предложение order by, которое вам действительно нужно для оконной функции. Запрос, который вы хотите, выглядит примерно так:

SELECT sum(case when event_cd in ('20', '21', '22', '23') then 1 else 0 end) OVER (ORDER BY ??),
       sum(case when event_cd not in ('11', '14', '15', '16', '17') then 1 else 0 end) OVER (ORDER BY ??),
       (sum(case when event_cd in ('20', '21', '22', '23') then 1.0 else 0 end) OVER (ORDER BY ??) /
        sum(case when event_cd not in ('11', '14', '15', '16', '17') then 1 else 0 end) OVER (ORDER BY ??)
       ) as ratio 
FROM events
WHERE bat_id = 'ortid001';

Поместите соответствующий столбец порядка для ??.

person Gordon Linoff    schedule 13.03.2015
comment
в этом случае строки в базе данных уже находятся в правильном порядке. Итак, предложение OVER — это просто OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Это дает правильные результаты, но я не уверен, есть ли предпочтительный способ сделать это. - person Michael Curry; 13.03.2015
comment
Нет такой вещи, как таблица в правильном порядке. Таблицы представляют собой неупорядоченные наборы. - person Gordon Linoff; 14.03.2015