SUM & GROUP BY для массива составного типа

У меня есть столбец с массивом составного типа (text, decimal, timestamp) в качестве типа данных. Я хочу создать запрос для суммирования суммы двойного столбца составного типа. Также я хочу выполнить группу по дате (день-месяц-год) даты и времени.

Может ли кто-нибудь показать мне пример объяснить, как это сделать?

Определение таблицы и типа:

create type stage as (
   Stage_Name        text,
   Stage_Distance    decimal,
   Stage_Start_Time  timestamp
);

CREATE TABLE "Event" (
  "Id" serial NOT NULL,
  "Location" text,
  "Date_Range" daterange,
  "Surface" text,
  "Stage_Information" stage[],
  CONSTRAINT "PK_Event" PRIMARY KEY ("Id")
);

Пример данных

{"(Newtownards,1.5,\"2015-04-03 18:28:00\")"
,"(\"Bulls Brook\",13.4,\"2015-04-04 09:04:00\")"}

Ожидаемые результаты:

Сумма (1,5 + 13,4) = 14,9

Группа по 03.04.2015, 04.04.2015


postgresql types sql aggregate-functions composite-types
person Alan Mulligan    schedule 27.03.2015    source источник
comment
Тип создается с двойными кавычками, таблица без?   -  person Erwin Brandstetter    schedule 28.03.2015
comment
Да, они созданы, как показано выше   -  person Alan Mulligan    schedule 28.03.2015
comment
Если вы сгруппируете по дням, вы получите две строки с 2015-04-03 | 1.5 и 2015-04-04 | 13.4 и не суммой обоих.   -  person Erwin Brandstetter    schedule 28.03.2015
comment
ОШИБКА: синтаксическая ошибка в или около ( LINE 1: SELECT e.id, s.(st).stage_start_time::date AS day   -  person Alan Mulligan    schedule 28.03.2015
comment
Должно быть исправлено сейчас. Открывающая скобка была неуместна в моем обновлении. Круглые скобки должны быть вокруг полного имени столбца.   -  person Erwin Brandstetter    schedule 28.03.2015

Ответы (1)


arrow_upward
3
arrow_downward

Предполагая текущую версию Postgres 9.4 из-за отсутствия информации.

Правильный дизайн

Прежде всего, рассмотрите возможность нормализации базы данных. Дополнительная таблица вместо столбца "Stage_Information" обычно является лучшим решением:

CREATE TABLE stage (
  stage_id  serial PRIMARY KEY
, event_id  int NOT NULL REFERENCES event
, name      text        -- possibly NOT NULL
, distance  numeric     -- possibly NOT NULL
, starttime timestamp   -- possibly NOT NULL
);

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

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

Тогда запрос будет таким:

SELECT e.id, s.starttime::date AS day
     , sum(s.distance) AS sum_distance
FROM   "Event" e
LEFT   JOIN stage s ON s.event_id = e.id
WHERE  e.id = 1
GROUP  BY 1, 2;

Решение проблемы под рукой

Пока вы придерживаетесь текущего дизайна, вам нужно unnest() массив для применения агрегатных функций к его элементам. Затем вам нужно разложить составные значения. Используйте соединение LATERAL:

SELECT e.id, (s.st).stage_start_time::date AS day
     , sum((s.st).stage_distance) AS sum_distance
FROM   "Event" e
LEFT   JOIN LATERAL unnest(e."Stage_Information") s(st) ON true
WHERE  e.id = 1
GROUP  BY 1, 2;

Обратите внимание на круглые скобки вокруг (s.st) (псевдоним столбца для невложенного столбца). Они нужны вам для доступа к элементам составного типа (типа строки). .

Почему LEFT JOIN LATERAL ... ON true?

person Erwin Brandstetter    schedule 27.03.2015
comment
Теперь я получаю ОШИБКУ: обозначение столбца .stage_distance применяется к типу stage[], который не является составным типом LINE 1: SELECT (Stage_Information).Stage_Distance::date AS day, обновленный вопрос - person Alan Mulligan; 28.03.2015
comment
@AlanMulligan: Хорошо, это массив составного типа. Объясняет, почему у вас был массив в заголовке. Требует более изощренного подхода. Что именно вы хотите посчитать? Сумма по отдельному столбцу или по всей таблице? Пожалуйста, добавьте свою версию Postgres к вопросу. - person Erwin Brandstetter; 28.03.2015
comment
Мне нужно суммировать Stage_Distance, где id = 1, и выбрать Stage_Start_Time, где id = 1, сгруппировать по Stage_Start_Time (день-месяц-год), если они имеют смысл - person Alan Mulligan; 28.03.2015
comment
@AlanMulligan: Вы не можете сразу выбрать группу времени и по дням. Логически противоречиво. Пожалуйста, добавьте примеры значений и желаемый результат к вашему вопросу, если вам трудно объяснить. - person Erwin Brandstetter; 28.03.2015