PostgreSQL - как я должен использовать first_value()?

Ответ 1

SQL Fiddle

Все функции, которые вы использовали, действуют на оконный фрейм, а не на раздел. Если опустить конец кадра, это текущая строка. Чтобы оконный кадр был всем разделом, объявите его в предложении frame (range...):

SELECT  
    cstamp,
    price,
    date_trunc('hour',cstamp) AS h,
    floor(EXTRACT(minute FROM cstamp) / 5) AS m5,
    min(price) OVER w,
    max(price) OVER w,
    first_value(price) OVER w,
    last_value(price) OVER w
FROM trades
Where date_trunc('hour',cstamp) = timestamp '2013-03-29 09:00:00'
WINDOW w AS (
    PARTITION BY date_trunc('hour',cstamp) , floor(extract(minute FROM cstamp) / 5)
    ORDER BY cstamp
    range between unbounded preceding and unbounded following
    )
ORDER BY cstamp;

Ответ 2

Вот быстрый запрос для иллюстрации поведения:

select 
  v,
  first_value(v) over w1 f1,
  first_value(v) over w2 f2,
  first_value(v) over w3 f3,
  last_value (v) over w1 l1,
  last_value (v) over w2 l2,
  last_value (v) over w3 l3,
  max        (v) over w1 m1,
  max        (v) over w2 m2,
  max        (v) over w3 m3,
  max        (v) over () m4
from (values(1),(2),(3),(4)) t(v)
window
  w1 as (order by v),
  w2 as (order by v rows between unbounded preceding and current row),
  w3 as (order by v rows between unbounded preceding and unbounded following)

Вывод вышеуказанного запроса можно увидеть здесь (SQLFiddle здесь):

| V | F1 | F2 | F3 | L1 | L2 | L3 | M1 | M2 | M3 | M4 |
|---|----|----|----|----|----|----|----|----|----|----|
| 1 |  1 |  1 |  1 |  1 |  1 |  4 |  1 |  1 |  4 |  4 |
| 2 |  1 |  1 |  1 |  2 |  2 |  4 |  2 |  2 |  4 |  4 |
| 3 |  1 |  1 |  1 |  3 |  3 |  4 |  3 |  3 |  4 |  4 |
| 4 |  1 |  1 |  1 |  4 |  4 |  4 |  4 |  4 |  4 |  4 |

Немногие думают о неявных кадрах, которые применяются к функциям окна, которые принимают предложение ORDER BY. В этом случае окна по умолчанию соответствуют кадру ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Подумайте об этом так:

• В строке с v = 1 упорядоченный оконный фрейм охватывает v IN (1)
• В строке с v = 2 упорядоченный оконный фрейм охватывает v IN (1, 2)
• В строке с v = 3 упорядоченный оконный фрейм охватывает v IN (1, 2, 3)
• В строке с v = 4 упорядоченный оконный фрейм охватывает v IN (1, 2, 3, 4)

Если вы хотите предотвратить это поведение, у вас есть два варианта:

• Используйте явное предложение ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING для упорядоченных функций окна
• Не используйте предложение ORDER BY в этих функциях окна, которые позволяют исключить их (как MAX(v) OVER())

Более подробная информация приведена в в этой статье о LEAD(), LAG(), FIRST_VALUE() и LAST_VALUE()

Ответ 3

Результат max() как функция окна основывается на определении фрейма.

Определение фрейма по умолчанию (с ORDER BY) начинается с начала фрейма до последнего однорангового узла текущей строки (включая текущую строку и, возможно, больше строк, ранжирующих одинаково в соответствии с ORDER BY). В отсутствие ORDER BY (как в моем ответе вы ссылаетесь), или если ORDER BY рассматривает каждую строку раздела как равную (как в первом примере), все строки в разделе являются одноранговыми, а max() производит одинаковый результат для каждой строки в разделе, эффективно рассматривая все строки раздела.

В документации:

Параметр обрамления по умолчанию RANGE UNBOUNDED PRECEDING, который является такой же, как RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. С ORDER BY, это устанавливает, что кадр будет всеми строками из начала раздела вверх по текущей строке последнего партнера. Без ORDER BY все строки раздел включаются в рамку окна, поскольку все строки становятся сверстников текущей строки.

Смелый акцент мой.

Простым решением будет опустить ORDER BY в определении окна - так же, как я продемонстрировал в примере, на который вы ссылаетесь.

Все подробные сведения о спецификациях фрейма в главе Вызовы функций окна в руководстве.