Обновить таблицу с оконной функцией

Question

У меня есть таблица в Redshift с дублированной строкой, которую я хочу удалить,

для этого я создал поданный идентификатор и хочу обновить его, чтобы удалить дублированные строки. Я пытаюсь выполнить этот запрос, но он не работает

update mr_usage
set id=row_number () over (partition by uid,date(ts),title order by ts)

Я получил следующую ошибку:

ОШИБКА: невозможно использовать функцию окна в ОБНОВЛЕНИИ

Я ищу способ обновить это поле

Answer 1

Другое возможное решение (без CTE) использует UPDATE .. FROM синтаксис с подзапросом напрямую

UPDATE mr_usage
SET id = sub.new_id
FROM (
    SELECT 
        id, ROW_NUMBER() OVER (PARTITION BY uid, date(ts), title ORDER BY ts) AS new_id
    FROM
        mr_usage
) sub
WHERE id = sub.id

Но это также доступно начиная с PostgreSQL 8.4.

Answer 2

Вы можете попробовать CTE для достижения этой цели, хотя такое ОБНОВЛЕНИЕ не удалит дубликаты строк.

WITH n AS (
  SELECT
    id AS current_id,
    ROW_NUMBER() OVER (PARTITION BY uid, date(ts), title ORDER BY ts) AS new_id
  FROM
    mr_usage
)
UPDATE
  mr_usage
SET
  id = n.new_id
FROM
  n
WHERE
  mr_usage.id = n.current_id;