Как объединить несуществующие строки без объединения?

#sql #oracle

#sql #Oracle

Вопрос:

У меня есть таблица с историей изменений данных, которая выглядит как:

  -------- ---------- --------------- 
| id     | key      | value         |
 -------- ---------- --------------- 
| UUID-1 | editedBy | moderator     |
 -------- ---------- --------------- 
| UUID-1 | comments | deleted...    |
 -------- ---------- --------------- 
| UUID-2 | editedBy | low_moderator |
 -------- ---------- --------------- 
| UUID-2 | comments | deleted...    |
 -------- ---------- --------------- 
| UUID-3 | editedBy | admin         |
 -------- ---------- --------------- 
| UUID-4 | editedBy | admin2        |
 -------- ---------- ---------------

Теперь я хочу выбрать ключ-значение с помощью редактора и ключ-значение с помощью comments (которое не совпадает с deleted... ), поэтому я сделал это таким образом.

 with table1 as 
(
   SELECT
      sch.rid,
      sch.rkey,
      sch.rvalue,
      coalesce( (
      SELECT
         rvalue 
      FROM
         test sch2 
      WHERE
         sch.rid = sch2.rid 
         AND sch2.rkey = 'comments' ), ' ' ) AS comments 
      FROM
         test sch 
      WHERE
         rkey = 'editedBy'
)
SELECT
   * 
FROM
   table1 t1 
WHERE
   t1.comments != 'deleted...'

и я получил желаемый результат

  -------- ---------- -------- ---------- 
| id     | key      | value  | comments |
 -------- ---------- -------- ---------- 
| UUID-3 | editedBy | admin  |          |
 -------- ---------- -------- ---------- 
| UUID-4 | editedBy | admin2 |          |
 -------- ---------- -------- ----------

Но мне кажется, что мой скрипт слишком сложный, и есть способы сделать это лучше, но я не могу найти их один.

Ответ №1:

Вы можете использовать NOT EXISTS :

 SELECT * 
  FROM test t 
 WHERE NOT EXISTS ( SELECT 0
                      FROM test
                     WHERE rkey = 'comments' 
                       AND rvalue = 'deleted...'
                       AND rid = t.rid );

или, в качестве альтернативы, вы можете использовать такой метод, содержащий аналитическую функцию :

 WITH t AS
(
SELECT t.*, 
       SUM(CASE WHEN rkey = 'comments' AND rvalue='deleted...' 
                THEN 1 
                ELSE 0 
                END) OVER (PARTITION BY rid) AS cnt
  FROM test t 
)
SELECT rid, rkey, rvalue
  FROM t
 WHERE cnt = 0;

Demo

Ответ №2:

Ваше описание предполагает, что у вас есть только два ключа comments и editedBy в строках с одинаковым UUID и что первый является необязательным.

Поэтому просто используйте OUTER JOIN и отфильтруйте строку, которую вы не хотите видеть (строки с комментарием)

 select 
  ed.rid, ed.rkey, ed.rvalue
from table1 ed
left outer join table1 del
on ed.RID = del.RID and del.rkey = 'comments'
where ed.rkey = 'editedBy'
and del.rkey is NULL /* no comment */

Ответ №3:

Функции окна:

 SELECT t.*
FROM (SELECT t.*,
             MAX(comments) OVER (PARTITION BY rid) as comments
      FROM test t
     ) t 
WHERE t1.comments <> 'deleted...'