Получить значения из первой и последней строки в группе

Question

Я новичок в Postgres, пришёл из MySQL и надеюсь, что кто-нибудь из вас сможет мне помочь.

У меня есть таблица с тремя столбцами: name, week, а также value, В этой таблице есть запись имен, недели, в которую они записали высоту, и значения их высоты. Что-то вроде этого:

Name  |  Week  | Value
------+--------+-------
John  |  1     | 9
Cassie|  2     | 5
Luke  |  6     | 3
John  |  8     | 14
Cassie|  5     | 7
Luke  |  9     | 5
John  |  2     | 10
Cassie|  4     | 4
Luke  |  7     | 4

То, что я хочу, это список для пользователя значения минимальной недели и максимальной недели. Что-то вроде этого:

Name  |minWeek | Value |maxWeek | value
------+--------+-------+--------+-------
John  |  1     | 9     | 8      | 14
Cassie|  2     | 5     | 5      | 7
Luke  |  6     | 3     | 9      | 5

В Postgres я использую этот запрос:

select name, week, value
from table t
inner join(
select name, min(week) as minweek
from table
group by name)
ss on t.name = ss.name and t.week = ss.minweek
group by t.name
;

Однако я получаю сообщение об ошибке:

столбец "w.week" должен появляться в предложении GROUP BY или использоваться в статистической функции
Позиция: 20

Это хорошо работает для меня в MySQL, поэтому мне интересно, что я делаю здесь не так?

Answer 1

Это немного больно, потому что у Postgres есть приятные оконные функции first_value() а также last_value(), но это не функции агрегации. Итак, вот один из способов:

select t.name, min(t.week) as minWeek, max(firstvalue) as firstvalue,
       max(t.week) as maxWeek, max(lastvalue) as lastValue
from (select t.*, first_value(value) over (partition by name order by week) as firstvalue,
             last_value(value) over (partition by name order by week) as lastvalue
      from table t
     ) t
group by t.name;

Answer 2

Существуют различные более простые и быстрые способы.

2x DISTINCT ON

SELECT *
FROM  (
   SELECT DISTINCT ON (name)
          name, week AS first_week, value AS first_val
   FROM   tbl
   ORDER  BY name, week
   ) f
JOIN (
   SELECT DISTINCT ON (name)
          name, week AS last_week, value AS last_val
   FROM   tbl
   ORDER  BY name, week DESC
   ) l USING (name);

Или короче:

SELECT *
FROM  (SELECT DISTINCT ON (1) name, week AS first_week, value AS first_val
       FROM   tbl ORDER BY 1,2) f
JOIN  (SELECT DISTINCT ON (1) name, week AS last_week, value AS last_val
       FROM   tbl ORDER BY 1,2 DESC) l USING (name);

Просто и легко понять. Также самый быстрый в моих тестах. Подробное объяснение DISTINCT ON:

• Выберите первую строку в каждой группе GROUP BY?

first_value() составного типа

Агрегатные функции min() или же max() не принимать составные типы в качестве входных данных. Вы должны будете создать пользовательские агрегатные функции (что не так сложно).
Но оконные функции first_value() а также last_value() делать Опираясь на это, мы можем разработать очень простые решения:

Простой запрос

SELECT DISTINCT ON (name)
       name, week AS first_week, value AS first_value
     ,(first_value((week, value)) OVER (PARTITION BY name
                                        ORDER BY week DESC))::text AS l
FROM   tbl t
ORDER  BY name, week;

Вывод содержит все данные, но значения за последнюю неделю помещаются в анонимную запись. Вам могут понадобиться разложенные значения.

Разложенный результат с условным использованием табличного типа

Для этого нам нужен хорошо известный тип, который регистрирует типы содержащихся элементов в системе. Адаптированное определение таблицы позволит напрямую использовать оппортунистический тип таблицы:

CREATE TABLE tbl (week int, value int, name text) -- note optimized column order

week а также value приходи первым.

SELECT (l).name, first_week, first_val
     , (l).week AS last_week, (l).value AS last_val
FROM (
   SELECT DISTINCT ON (name)
          week AS first_week, value AS first_val
         ,first_value(t) OVER (PARTITION BY name ORDER BY week DESC) AS l
   FROM   tbl t
   ORDER  BY name, week
   ) sub;

Декомпозированный результат от определенного пользователем типа строки

Тем не менее, это, вероятно, невозможно в большинстве случаев. Просто используйте пользовательский тип из CREATE TYPE (постоянный) или из CREATE TEMP TABLE (для специального использования):

CREATE TEMP TABLE nv(last_week int, last_val int);  -- register composite type

SELECT name, first_week, first_val, (l).last_week, (l).last_val
FROM (
   SELECT DISTINCT ON (name)
          name, week AS first_week, value AS first_val
         ,first_value((week, value)::nv) OVER (PARTITION BY name
                                               ORDER BY week DESC) AS l
   FROM   tbl t
   ORDER  BY name, week
   ) sub;

В локальном тесте на Postgres 9.3 с аналогичной таблицей из 50 тыс. Строк каждый из этих запросов был значительно быстрее, чем принятый в настоящее время ответ. Тест с EXPLAIN ANALYZE,

SQL Fiddle отображает все.