Alternatywy dla łączenia łańcuchów lub wykonywania procedur, aby zapobiec powtórzeniu kodu zapytania SQL?

Oświadczenie: Proszę o wyrozumiałość jako kogoś, kto korzysta z baz danych tylko przez ułamek swojego czasu pracy. (Przez większość czasu programuję w C ++ w swojej pracy, ale co nieparzysty miesiąc muszę wyszukiwać / naprawiać / dodawać coś do bazy danych Oracle.)

Wielokrotnie potrzebowałem pisać złożone zapytania SQL, zarówno dla zapytań ad-hoc, jak i zapytań wbudowanych w aplikacje, w których duże części zapytań powtarzały „kod”.

Pisanie takich obrzydliwości w tradycyjnym języku programowania sprawiłoby ci poważne kłopoty, ale ja ( ja ) nie znalazłem jeszcze żadnej przyzwoitej techniki, aby zapobiec powtórzeniu kodu zapytania SQL.

Edycja: Po pierwsze, chcę podziękować autorom odpowiedzi, którzy w znakomity sposób poprawili mój oryginalny przykład . To pytanie nie dotyczy jednak mojego przykładu. Chodzi o powtarzalność zapytań SQL. Jako takie, odpowiedzi ( JackP , Leigh ) do tej pory wykonują świetną robotę, pokazując, że można zmniejszyć powtarzalność, pisząc lepsze zapytania . Jednak nawet wtedy masz do czynienia z pewną powtarzalnością, której najwyraźniej nie da się usunąć: zawsze mnie to dręczyło za pomocą SQL. W „tradycyjnych” językach programowania mogę dość dużo refaktoryzować, aby zminimalizować powtarzalność w kodzie, ale z SQL wydaje się, że nie ma (?) Narzędzi, które to umożliwiają, z wyjątkiem pisania mniej powtarzalnej instrukcji na początek.

Zauważ, że ponownie usunąłem znacznik Oracle, ponieważ byłbym naprawdę zainteresowany tym, czy nie ma bazy danych lub języka skryptowego, który pozwalałby na coś więcej.

Oto jeden z takich klejnotów, który dzisiaj zebrałem razem. Zasadniczo zgłasza różnicę w zestawie kolumn jednej tabeli. Przejrzyj poniższy kod, szczególnie. duże zapytanie na końcu. Będę kontynuować poniżej.

--
-- Create Table to test queries
--
CREATE TABLE TEST_ATTRIBS (
id NUMBER PRIMARY KEY,
name  VARCHAR2(300) UNIQUE,
attr1 VARCHAR2(2000),
attr2 VARCHAR2(2000),
attr3 INTEGER,
attr4 NUMBER,
attr5 VARCHAR2(2000)
);

--
-- insert some test data
--
insert into TEST_ATTRIBS values ( 1, 'Alfred',   'a', 'Foobar', 33, 44, 'e');
insert into TEST_ATTRIBS values ( 2, 'Batman',   'b', 'Foobar', 66, 44, 'e');
insert into TEST_ATTRIBS values ( 3, 'Chris',    'c', 'Foobar', 99, 44, 'e');
insert into TEST_ATTRIBS values ( 4, 'Dorothee', 'd', 'Foobar', 33, 44, 'e');
insert into TEST_ATTRIBS values ( 5, 'Emilia',   'e', 'Barfoo', 66, 44, 'e');
insert into TEST_ATTRIBS values ( 6, 'Francis',  'f', 'Barfoo', 99, 44, 'e');
insert into TEST_ATTRIBS values ( 7, 'Gustav',   'g', 'Foobar', 33, 44, 'e');
insert into TEST_ATTRIBS values ( 8, 'Homer',    'h', 'Foobar', 66, 44, 'e');
insert into TEST_ATTRIBS values ( 9, 'Ingrid',   'i', 'Foobar', 99, 44, 'e');
insert into TEST_ATTRIBS values (10, 'Jason',    'j', 'Bob',    33, 44, 'e');
insert into TEST_ATTRIBS values (12, 'Konrad',   'k', 'Bob',    66, 44, 'e');
insert into TEST_ATTRIBS values (13, 'Lucas',    'l', 'Foobar', 99, 44, 'e');

insert into TEST_ATTRIBS values (14, 'DUP_Alfred',   'a', 'FOOBAR', 33, 44, 'e');
insert into TEST_ATTRIBS values (15, 'DUP_Chris',    'c', 'Foobar', 66, 44, 'e');
insert into TEST_ATTRIBS values (16, 'DUP_Dorothee', 'd', 'Foobar', 99, 44, 'e');
insert into TEST_ATTRIBS values (17, 'DUP_Gustav',   'X', 'Foobar', 33, 44, 'e');
insert into TEST_ATTRIBS values (18, 'DUP_Homer',    'h', 'Foobar', 66, 44, 'e');
insert into TEST_ATTRIBS values (19, 'DUP_Ingrid',   'Y', 'foo',    99, 44, 'e');

insert into TEST_ATTRIBS values (20, 'Martha',   'm', 'Bob',    33, 88, 'f');

-- Create comparison view
CREATE OR REPLACE VIEW TA_SELFCMP as
select 
t1.id as id_1, t2.id as id_2, t1.name as name, t2.name as name_dup,
t1.attr1 as attr1_1, t1.attr2 as attr2_1, t1.attr3 as attr3_1, t1.attr4 as attr4_1, t1.attr5 as attr5_1,
t2.attr1 as attr1_2, t2.attr2 as attr2_2, t2.attr3 as attr3_2, t2.attr4 as attr4_2, t2.attr5 as attr5_2
from TEST_ATTRIBS t1, TEST_ATTRIBS t2
where t1.id <> t2.id
and t1.name <> t2.name
and t1.name = REPLACE(t2.name, 'DUP_', '')
;

-- NOTE THIS PIECE OF HORRIBLE CODE REPETITION --
-- Create comparison report
-- compare 1st attribute
select 'attr1' as Different,
id_1, id_2, name, name_dup,
CAST(attr1_1 AS VARCHAR2(2000)) as Val1, CAST(attr1_2 AS VARCHAR2(2000)) as Val2
from TA_SELFCMP
where attr1_1 <> attr1_2
or (attr1_1 is null and attr1_2 is not null)
or (attr1_1 is not null and attr1_2 is null)
union
-- compare 2nd attribute
select 'attr2' as Different,
id_1, id_2, name, name_dup,
CAST(attr2_1 AS VARCHAR2(2000)) as Val1, CAST(attr2_2 AS VARCHAR2(2000)) as Val2
from TA_SELFCMP
where attr2_1 <> attr2_2
or (attr2_1 is null and attr2_2 is not null)
or (attr2_1 is not null and attr2_2 is null)
union
-- compare 3rd attribute
select 'attr3' as Different,
id_1, id_2, name, name_dup,
CAST(attr3_1 AS VARCHAR2(2000)) as Val1, CAST(attr3_2 AS VARCHAR2(2000)) as Val2
from TA_SELFCMP
where attr3_1 <> attr3_2
or (attr3_1 is null and attr3_2 is not null)
or (attr3_1 is not null and attr3_2 is null)
union
-- compare 4th attribute
select 'attr4' as Different,
id_1, id_2, name, name_dup,
CAST(attr4_1 AS VARCHAR2(2000)) as Val1, CAST(attr4_2 AS VARCHAR2(2000)) as Val2
from TA_SELFCMP
where attr4_1 <> attr4_2
or (attr4_1 is null and attr4_2 is not null)
or (attr4_1 is not null and attr4_2 is null)
union
-- compare 5th attribute
select 'attr5' as Different,
id_1, id_2, name, name_dup,
CAST(attr5_1 AS VARCHAR2(2000)) as Val1, CAST(attr5_2 AS VARCHAR2(2000)) as Val2
from TA_SELFCMP
where attr5_1 <> attr5_2
or (attr5_1 is null and attr5_2 is not null)
or (attr5_1 is not null and attr5_2 is null)
;

Jak widać, zapytanie do wygenerowania „raportu różnic” używa tego samego bloku SQL SELECT 5 razy (może to być 42 razy!). Uderza mnie to jako absolutnie martwy mózg (wolno mi to powiedzieć, w końcu napisałem kod), ale nie byłem w stanie znaleźć żadnego dobrego rozwiązania tego problemu.

• Jeśli byłoby to zapytanie w jakimś rzeczywistym kodzie aplikacji, mógłbym napisać funkcję, która łączy to zapytanie jako ciąg, a następnie wykonałbym zapytanie jako ciąg.

  • -> Budowanie ciągów jest okropne i okropne do testowania i utrzymywania. Jeśli „kod aplikacji” jest napisany w języku takim jak PL / SQL, wydaje się tak źle, że boli.

• Alternatywnie, jeśli użyje się go z PL / SQL lub podobnego, sądzę, że istnieją pewne procedury, aby uczynić to zapytanie łatwiejszym do utrzymania.

  • -> Rozwijanie czegoś, co można wyrazić w jednym zapytaniu, w etapach proceduralnych, aby zapobiec powtórzeniu kodu, również jest błędne.

• Jeśli to zapytanie byłoby potrzebne jako widok w bazie danych, to - o ile rozumiem - nie byłoby innego sposobu niż utrzymanie definicji widoku, tak jak napisałem powyżej. (!!?)

  • -> Naprawdę musiałem trochę popracować nad definicją widoku 2-stronicowego, gdy nie było to dalekie od powyższego stwierdzenia. Oczywiście zmiana czegokolwiek w tym widoku wymagała ponownego wyszukiwania tekstu wyrażenia regularnego nad definicją widoku w celu ustalenia, czy ta sama instrukcja podrzędna została użyta w innym wierszu i czy należy tam zmienić.

Tak więc, jak mówi tytuł - jakie są techniki, aby uniknąć konieczności pisania takich obrzydliwości?

Jesteś zbyt skromny - Twój SQL jest dobrze i zwięźle napisany, biorąc pod uwagę podejmowane zadanie. Kilka wskazówek:

• t1.name <> t2.namejest zawsze prawdziwe, jeśli t1.name = REPLACE(t2.name, 'DUP_', '')- możesz upuścić to pierwsze
• zwykle chcesz union all. unionoznacza union allnastępnie upuść duplikaty. W tym przypadku może to nie mieć znaczenia, ale zawsze używanie union alljest dobrym nawykiem, chyba że wyraźnie chcesz usunąć duplikaty.

• jeśli chcesz przeprowadzać porównania numeryczne po rzutowaniu na varchar, warto rozważyć następujące kwestie:

  create view test_attribs_cast as 
  select id, name, attr1, attr2, cast(attr3 as varchar(2000)) as attr3, 
         cast(attr4 as varchar(2000)) as attr4, attr5
  from test_attribs;
  
  create view test_attribs_unpivot as 
  select id, name, 1 as attr#, attr1 as attr from test_attribs_cast union all
  select id, name, 2, attr2 from test_attribs_cast union all
  select id, name, 3, attr3 from test_attribs_cast union all
  select id, name, 4, attr4 from test_attribs_cast union all
  select id, name, 5, attr5 from test_attribs_cast;
  
  select 'attr'||t1.attr# as different, t1.id as id_1, t2.id as id_2, t1.name, 
         t2.name as name_dup, t1.attr as val1, t2.attr as val2
  from test_attribs_unpivot t1 join test_attribs_unpivot t2 on(
         t1.id<>t2.id and 
         t1.name = replace(t2.name, 'DUP_', '') and 
         t1.attr#=t2.attr# )
  where t1.attr<>t2.attr or (t1.attr is null and t2.attr is not null)
        or (t1.attr is not null and t2.attr is null);

  drugi widok jest rodzajem unpivotoperacji - jeśli masz co najmniej 11 g, możesz to zrobić bardziej zwięźle z unpivotklauzulą ​​- zobacz tutaj przykład

• Mówię, że nie idź ścieżką proceduralną, jeśli możesz to zrobić w SQL, ale ...
• Dynamiczny SQL jest prawdopodobnie warty rozważenia pomimo problemów, które wspominasz o testowaniu i konserwacji

--EDYTOWAĆ--

Aby odpowiedzieć na bardziej ogólną stronę pytania, istnieją techniki zmniejszania liczby powtórzeń w SQL, w tym:

• Widoki - wiesz o tym :)
• Typowe wyrażenia tabelowe (patrz tutaj na przykład)
• poszczególne funkcje bazy danych, takie jak decode(patrz odpowiedź Leigha, w jaki sposób można to ograniczyć powtarzanie), funkcje okna i zapytania hierarchiczne / rekurencyjne, aby wymienić tylko kilka

Ale nie można przynieść pomysły OO na świat SQL bezpośrednio - w wielu wypadkach powtórzenie jest w porządku, jeśli zapytanie jest czytelny i dobrze napisany, i byłoby nierozsądne, aby uciekać się do dynamicznego SQL (na przykład) właśnie w celu uniknięcia powtórzeń.

Ostatnie zapytanie zawierające sugerowaną zmianę Leigha i CTE zamiast widoku może wyglądać mniej więcej tak:

with t as ( select id, name, attr#, 
                   decode(attr#,1,attr1,2,attr2,3,attr3,4,attr4,attr5) attr
            from test_attribs
                 cross join (select rownum attr# from dual connect by rownum<=5))
select 'attr'||t1.attr# as different, t1.id as id_1, t2.id as id_2, t1.name, 
       t2.name as name_dup, t1.attr as val1, t2.attr as val2
from t t1 join test_attribs_unpivot t2 
               on( t1.id<>t2.id and 
                   t1.name = replace(t2.name, 'DUP_', '') and 
                   t1.attr#=t2.attr# )
where t1.attr<>t2.attr or (t1.attr is null and t2.attr is not null)
      or (t1.attr is not null and t2.attr is null);

Oto alternatywa dla widoku test_attribs_unpivot dostarczonego przez JackPDouglas ^(+1), który działa w wersjach wcześniejszych niż 11g i wykonuje mniej skanów przy pełnej tabeli:

CREATE OR REPLACE VIEW test_attribs_unpivot AS
   SELECT ID, Name, MyRow Attr#, CAST(
      DECODE(MyRow,1,attr1,2,attr2,3,attr3,4,attr4,attr5) AS VARCHAR2(2000)) attr
   FROM TEST_ATTRIBS 
   CROSS JOIN (SELECT level MyRow FROM dual connect by level<=5);

Jego ostatnie zapytanie może być użyte bez zmian w tym widoku.

Często napotykam podobny problem, aby porównać dwie wersje tabeli dla nowych, usuniętych lub zmienionych wierszy. Jakiś miesiąc temu opublikował rozwiązanie dla SQL Server przy użyciu PowerShell tutaj .

Aby dostosować go do twojego problemu, najpierw tworzę dwa widoki, aby oddzielić oryginał od zduplikowanych wierszy

CREATE OR REPLACE VIEW V1_TEST_ATTRIBS AS 
select * from TEST_ATTRIBS where SUBSTR(name, 1, 4) <> 'DUP_'; 

CREATE OR REPLACE VIEW V2_TEST_ATTRIBS AS 
select id, REPLACE(name, 'DUP_', '') name, attr1, attr2, attr3, attr4, attr5 from TEST_ATTRIBS where SUBSTR(name, 1, 4) = 'DUP_'; 

a następnie sprawdzam zmiany za pomocą

SELECT 1 SRC, NAME, ATTR1, ATTR2, ATTR3, ATTR4, ATTR5 FROM V1_TEST_ATTRIBS
MINUS
Select 1 SRC, NAME, ATTR1, ATTR2, ATTR3, ATTR4, ATTR5 from V2_TEST_ATTRIBS
UNION
SELECT 2 SRC, NAME, ATTR1, ATTR2, ATTR3, ATTR4, ATTR5 FROM V2_TEST_ATTRIBS
MINUS
SELECT 2 SRC ,NAME, ATTR1, ATTR2, ATTR3, ATTR4, ATTR5 FROM V1_TEST_ATTRIBS
ORDER BY NAME, SRC;

Stąd mogę znaleźć twoje oryginalne identyfikatory

Select NVL(v1.id, v2.id) id,  t.name, t.attr1, t.attr2, t.attr3, t.attr4, t.attr5 from
(
SELECT 1 SRC, NAME, ATTR1, ATTR2, ATTR3, ATTR4, ATTR5 FROM V1_TEST_ATTRIBS
MINUS
Select 1 SRC, NAME, ATTR1, ATTR2, ATTR3, ATTR4, ATTR5 from V2_TEST_ATTRIBS
UNION
SELECT 2 SRC, NAME, ATTR1, ATTR2, ATTR3, ATTR4, ATTR5 FROM V2_TEST_ATTRIBS
MINUS
Select 2 SRC ,NAME, ATTR1, ATTR2, ATTR3, ATTR4, ATTR5 from V1_TEST_ATTRIBS
) t
LEFT JOIN V1_TEST_ATTRIBS V1 ON T.NAME = V1.NAME AND T.SRC = 1
LEFT JOIN V2_TEST_ATTRIBS V2 ON T.NAME = V2.NAME AND T.SRC = 2
ORDER by NAME, SRC;

BTW: MINUS i UNION i GROUP BY traktują różne wartości NULL jako równe. Dzięki tym operacjom zapytania stają się bardziej eleganckie.

Wskazówka dla użytkowników programu SQL Server: MINUS nazywa się tam Z WYJĄTKIEM, ale działa podobnie.