База данных: выберите последние ненулевые записи

Вот вопрос, который я переживаю. Скажем, у меня есть таблица с серией временных меток и номер детали в качестве первичного ключа. В таблице хранятся инкрементные изменения, что означает, что для каждой отметки времени, если поле изменяется, это изменение записывается. Если поле не изменяется, то для новой метки времени оно равно NULL. Вот основная идея.

 part | timestamp | x-pos | y-pos | status
------+-----------+-------+-------+--------
 a5   |       151 |     5 |    15 |      g
 a5   |       153 |  NULL |    17 |   NULL

(part, timestamp) является основным ключом. NULL во второй записи указывают значения, которые не изменяются со времени первой записи.

То, что я хочу сделать, - это выбрать самые последние значения для каждого поля, сгруппированного по части. Например, учитывая приведенные выше записи, результаты будут 153,5,17, g для части a5.

На данный момент у меня есть этот взломанный запрос.

    ((SELECT x-pos FROM part_changes WHERE x-pos IS NOT NULL
    ORDER BY timestamp DESC
    LIMIT 1)

    UNION

    (SELECT y-pos FROM part_changesWHERE y-pos IS NOT NULL
    ORDER BY timestamp DESC
    LIMIT 1)

    UNION

    (SELECT status FROM part_changes WHERE status IS NOT NULL
    ORDER BY timestamp DESC
    LIMIT 1))

Но это возвращает один столбец, что означает, что я могу использовать групповое для организации.

Там должен быть более элегантный способ делать вещи, например, используя COALESCE или IS NULL творческим способом. Но я застрял и не могу понять. У кого-нибудь появилась идея?

И нет, я не могу изменить структуру базы данных.

РЕДАКТОР: Руах имеет правильную идею. Единственная проблема теперь заключается в группировке по частям. Кажется, я не могу обойти LIMIT 1 для группировки несколькими частями. Есть идеи?

mdahlman, я не слишком хорошо знаком с аналитическими функциями в postgresql. Таким образом, если это решение было бы проще, чем сложный запрос, то обязательно отправьте свою идею.

РЕДАКТИРОВАТЬ 2: Спасибо всем за помощь. Я думаю, что у меня есть достаточно хорошее представление о том, что мне нужно делать.

Ответ 1

Вместо того, чтобы использовать UNION, похоже, что вам действительно нужны подзапросы в списке полей. То есть вместо (SELECT ...) UNION (SELECT ...) UNION (SELECT ...) вы хотите SELECT (SELECT ...), (SELECT ...), (SELECT ...).

Например:

SELECT part,
       ( SELECT x_pos
           FROM part_changes
          WHERE part = pc.part
            AND x_pos IS NOT NULL
          ORDER
             BY timestamp DESC
          LIMIT 1
       ) AS x_pos,
       ( SELECT y_pos
           FROM part_changes
          WHERE part = pc.part
            AND y_pos IS NOT NULL
          ORDER
             BY timestamp DESC
          LIMIT 1
       ) AS y_pos,
       ( SELECT status
           FROM part_changes
          WHERE part = pc.part
            AND status IS NOT NULL
          ORDER
             BY timestamp DESC
          LIMIT 1
       ) AS status
  FROM ( SELECT DISTINCT
                part
           FROM part_changes
       ) AS pc
;

Но в этот момент я бы действительно подумал о написании хранимой процедуры.

В качестве альтернативы:

SELECT DISTINCT
       part,
       FIRST_VALUE(x_pos) OVER
         ( PARTITION BY part
               ORDER BY CASE WHEN x_pos IS NULL
                             THEN NULL
                             ELSE TIMESTAMP
                         END DESC NULLS LAST
         ) AS x_pos,
       FIRST_VALUE(y_pos) OVER
         ( PARTITION BY part
               ORDER BY CASE WHEN y_pos IS NULL
                             THEN NULL
                             ELSE TIMESTAMP
                         END DESC NULLS LAST
         ) AS y_pos,
       FIRST_VALUE(status) OVER
         ( PARTITION BY part
               ORDER BY CASE WHEN status IS NULL
                             THEN NULL
                             ELSE TIMESTAMP
                         END DESC NULLS LAST
         ) AS status
  FROM part_changes
;

Ответ 2

Только для одной части это даст вам ответ.. благодаря ruakh

Но мне не нравится эта версия.

SELECT 
    (SELECT timestamp  FROM part_changes WHERE part = $part 
    ORDER BY timestamp DESC
    LIMIT 1) as timestamp,

    (SELECT x-pos FROM part_changes WHERE part = $part and x-pos IS NOT NULL
    ORDER BY timestamp DESC
    LIMIT 1) as xpos,

    (SELECT y-pos FROM part_changes WHERE part = $part and  y-pos IS NOT NULL
    ORDER BY timestamp DESC
    LIMIT 1) as ypos,

    (SELECT status FROM part_changes WHERE part = $part and status IS NOT NULL
    ORDER BY timestamp DESC
    LIMIT 1)) as status

Ответ 3

список соответствующих временных меток:

select max timestamp from part_changes where x_POS is not null group by part

Вы можете сделать это: разрешить вызов этого вида1

SELECT part_changes.part, part_changes.x-pos 
FROM part_changes left join view1 on part_changes.part = view1.part
WHERE x-pos IS NOT NULL 
 AND part_changes.timestamp = view1.timestamp 
GROUP BY part_changes.part

Посмотрите, куда я иду? Это должно дать вам полный список для x-pos.

Ответ 4

Руах прав. Альтернатива: Напишите пользовательский агрегат, используя SQL-CLR. Этот агрегат может выполнять нижний верхний верх над вашими строками и помнить первое ненулевое значение для каждого столбца.

Вы также можете сделать это в приложении. Сделайте свои строки запроса запроса в отсортированном порядке партиями, скажем, 10 строк. Совокупность этих партий, как описано выше. Если после выполнения текущей партии нулевой столбец получает следующую партию.