Temat: Łączenie dwóch zbiorów danych po nazwach kolumn

Witam, od jakiegoś czasu próbuję zautomatyzować swoją pracę - a często dostaję zbiory danych które muszę połączyć i bywa to zbyt czasochłonne, więc zacząłem od tego. Przykład :

tabela_1 o nazwach kolumn x1, x2, x3, x4, x5
tabela_2 o nazwach kolumn x6, x4, x3, x7,x8, x9

nazwy kolumn nie wymagają poprawy, zazwyczaj posiadam ~70 różnych kolumn, ~10 tys. wierszy, chciałbym połączyć tabela_1 oraz tabela_2 tak by powstały :

tabela_3 o nazwach kolumn x3, x4 (czyli tylko zmienne występujące w tabeli 1 i 2)
tabela_4 o nazwach kolumn x1,x2,x3,x4,x5,x6,x7,x8,x9 (czyli zmienne występujęce w tabeli 1 lub 2),

1. Próbowałem to zrobić przy pomocy UNION w MS Access 2010, jednak UNION skleja x1 z x6, x2 i x4 itd. - wogóle nie bierze pod uwagę nazw kolumn.
2. Nie chcę do tego używać wyszukaj.poziomo() + indeks w Excel.
3. Czy można to zrobić w MS Access ? Jeżeli nie to jakiego innego narzędzia użyć ? Czegoś na poziomie T-SQL ? Czy można to zrobić bez makr ?
4. Mógłbym to robić prostym skryptem w R, jednak szukam rozwiązania opartego na bazach danych.
5. Jak jest ogólnie z operacjami na kolumnach ? Czy można je posortować po nazwie kolumny ?

PozdrawiamTen post został edytowany przez Autora dnia 14.07.13 o godzinie 13:16

Temat: Łączenie dwóch zbiorów danych po nazwach kolumn

Chwila, wróćmy do UNION

Coś takiego:
SELECT x1, x2, x3, x4, x5, NULL as x6, NULL as x7, NULL as x8, NULL as x9 FROM t1
UNION
SELECT NULL, NULL, x3, x4, x5, x6, x7, x8, x9 FROM t2

zawiodło?

create table t1 (x1 int, x2 int, x3 varchar(100), x4 float)

create table t2 (x2 int, x4 float, x5 varchar(100))



insert into t1 values(1,1,'Ala ma kota', 2.5)

insert into t1 values(2,2,'Zosia ma psa', 4.6)



insert into t2 values(8,3.2,'aaaaa')

insert into t2 values(3,7.5,'bbbb')



SELECT x1, x2, x3, x4, NULL as x5 FROM t1

UNION

SELECT NULL, x2, NULL, x4, x5 FROM t2



x1	x2	x3		x4	x5

NULL	8	NULL		3,2	aaaaa

NULL	3	NULL		7,5	bbbb

1	1	Ala ma kota	2,5	NULL

2	2	Zosia ma psa	4,6	NULL

Co do operacji na kolumnach - to nie ta bajka. Model relacyjny ma swoje teoretyczne założenia.Ten post został edytowany przez Autora dnia 14.07.13 o godzinie 14:15

Temat: Łączenie dwóch zbiorów danych po nazwach kolumn

Adrian O.:
Chwila, wróćmy do UNION

Coś takiego:
SELECT x1, x2, x3, x4, x5, NULL as x6, NULL as x7, NULL as x8, NULL as x9 FROM t1
UNION
SELECT NULL, NULL, x3, x4, x5, x6, x7, x8, x9 FROM t2

zawiodło?

create table t1 (x1 int, x2 int, x3 varchar(100), x4 float)

create table t2 (x2 int, x4 float, x5 varchar(100))



insert into t1 values(1,1,'Ala ma kota', 2.5)

insert into t1 values(2,2,'Zosia ma psa', 4.6)



insert into t2 values(8,3.2,'aaaaa')

insert into t2 values(3,7.5,'bbbb')



SELECT x1, x2, x3, x4, NULL as x5 FROM t1

UNION

SELECT NULL, x2, NULL, x4, x5 FROM t2



x1	x2	x3		x4	x5

NULL	8	NULL		3,2	aaaaa

NULL	3	NULL		7,5	bbbb

1	1	Ala ma kota	2,5	NULL

2	2	Zosia ma psa	4,6	NULL

Co do operacji na kolumnach - to nie ta bajka.

dzięki, właśnie w ten sposób próbowałem sie z tym męczyć, jednak chciałbym to zrobić bez sortowania nazw kolumn - czy jest to wykonalna ?

(cząsto mam sytuację, gdy w tabela_1 są zmienne x1,...,x70, a w tabela_2 np.: x12,x45,x56, x77, x78, x89, x71,...,x100, a w wyniku chciałby plik zawietający x1,...,x100, wtedy "ręczne" pisanie kwerendy odpada, chyba, że zrobię makro które mi wygeneruje ten fragment kwerendy ;)

Temat: Łączenie dwóch zbiorów danych po nazwach kolumn

Tam nie było sortowania nazw kolumn. Chodzi Ci, że trzeba je ustawić tak, by sobie odpowiadały? No to niestety, ale tak. Muszą do siebie pasowac, a tam, gdzie brakuje danej kolumny musi pojawić się NULL.

Jako takiego "sortowania kolumn" nie ma. Brak takiego operatora w modelu relacyjnym.

W przypadku bazy danych zapewne skończy się dynamicznym SQLem, w którym odczytasz z odpowiednich (dla każdego RDBMS - innych) tabel systemowych listy kolumn potrzebnych tabel źródłowych i na tej podstawie wygenerujesz odpowiedniego selecta, który wrzuci dane do nowej tabeli tymczasowej (można opakować procedurą) bądź zwróci odpowiedni rekordset (funkcją).

Temat: Łączenie dwóch zbiorów danych po nazwach kolumn

Najprosciej jednak bedzie uzyc Create table as select albo zbudowac widoki jezeli zachodzi taka potrzeba.

Temat: Łączenie dwóch zbiorów danych po nazwach kolumn

Tylko że wtedy Kamil będzie musiał i tak i tak podawać wszystkie kolumny jawnie. A tu chodzi o automat. Jednak R ma tutaj swoje zalety :)

Temat: Łączenie dwóch zbiorów danych po nazwach kolumn

Ciężko powiedzieć jaki silnik, ale... nazwy kolumn to meta-dane. Każdy porządny silnik umie na nich pracować. Jeżeli tylko to jest tabela, a nie select - powinno dać się wykonać zapytanie, które zwórci zestaw zapytań generujących dane... Albo inaczej. Trzeba zrobić selecta, który odpyta się o nazwy kolumn i na tej podstawie wygeneruje kolejnego selecta, który zwórci dane. Skoro robić macro to można z miejsca w SQLu. ;)

Temat: Łączenie dwóch zbiorów danych po nazwach kolumn

Mi do głowy przychodzi kombinacja unpivot/pivot, wtedy kolejność w jakiej kolumny występują nie ma znaczenia. Widziałem takie "ficzersy" tylko w SQL Server. Może Access też dorobił się czegoś podobnego. W końcu ta sama stajnia...

Temat: Łączenie dwóch zbiorów danych po nazwach kolumn

Posługując się przykładowymi danymi Adriana Olszewskiego (zamiana nazw tabel t1->k1 i t2->k2).
Uniwersalne rozwiązanie dla Oracle:

create table k1 (x1 int, x2 int, x3 varchar(100), x4 float);

create table k2 (x2 int, x4 float, x5 varchar(100));



insert into k1 values(1,1,'Ala ma kota', 2.5);

insert into k1 values(2,2,'Zosia ma psa', 4.6);



insert into k2 values(8,3.2,'aaaaa');

insert into k2 values(3,7.5,'bbbb');



--wspólne kolumny

DECLARE

   S VARCHAR2(1000);

   W VARCHAR2(2000);

  BEGIN

    FOR C_REC IN (

      SELECT A.COLUMN_NAME

      FROM USER_TAB_COLUMNS A

      WHERE A.TABLE_NAME = 'K1'

      INTERSECT

      SELECT A.COLUMN_NAME

      FROM USER_TAB_COLUMNS A

      WHERE A.TABLE_NAME = 'K2' 

      ORDER BY 1) LOOP

    S := S || ' ,' ||  C_REC.COLUMN_NAME  ;

   END LOOP;

  W := ' CREATE OR REPLACE VIEW V1 AS SELECT 1 TABELA_NR  ' ||  S || ' FROM K1 UNION ALL  SELECT 2 TABELA_NR  ' || S || ' FROM K2';

   EXECUTE IMMEDIATE W;

  END;

  /



 CREATE SEQUENCE KOL_NAME

 START WITH     1

 INCREMENT BY   1;





--wszystkie kolumny z obu tabel

DECLARE

   S1 VARCHAR2(1000);

   S2 VARCHAR2(1000);

   S  VARCHAR2(1000);

   W VARCHAR2(1000);

   F1 NUMBER(1);

   F2 NUMBER(1);

   SEQ NUMBER;

  BEGIN

    FOR C_REC IN (

      SELECT A.COLUMN_NAME

      FROM USER_TAB_COLUMNS A

      WHERE A.TABLE_NAME = 'K1'

      UNION

      SELECT A.COLUMN_NAME

      FROM USER_TAB_COLUMNS A

      WHERE A.TABLE_NAME = 'K2' 

      ORDER BY 1) LOOP

    S := S || ' ,NULL ' || C_REC.COLUMN_NAME;

    SELECT NVL((SELECT 1  FROM USER_TAB_COLUMNS B WHERE B.TABLE_NAME='K1' AND B.COLUMN_NAME=C_REC.COLUMN_NAME),0) INTO F1 FROM DUAL;

    SELECT NVL((SELECT 1  FROM USER_TAB_COLUMNS B WHERE B.TABLE_NAME='K2' AND B.COLUMN_NAME=C_REC.COLUMN_NAME),0) INTO F2 FROM DUAL;

    SELECT KOL_NAME.NEXTVAL INTO SEQ FROM DUAL; 

    IF F1=1  THEN

         S1 := S1 || ' ,' || C_REC.COLUMN_NAME;

    ELSE S1 := S1 || ' ,' || 'NULL K1_' || SEQ ; 

    END IF;

    IF F2=1  THEN

         S2 := S2 || ' ,' || C_REC.COLUMN_NAME;

    ELSE S2 := S2 || ' ,' || 'NULL K2_' || SEQ;

    END IF;

   END LOOP;

  W := ' CREATE OR REPLACE VIEW V2 AS SELECT NULL TABELA_NR ' || S || ' FROM DUAL WHERE 1=2 '

       || '  UNION ALL SELECT 1 TABELA_NR  ' ||  S1 || ' FROM K1 UNION ALL  SELECT 2 TABELA_NR  ' || S2 || ' FROM K2';

   EXECUTE IMMEDIATE W;

  END;

  /  





  select * from v1;

     TABELA_NR         X2  X4

     1                 1   2,5

     1                 2   4,6

     2                 8   3,2

     2                 3   7,5



 select * from v2;

       TABELA_NR  X1  X2  X3            X4      X5

       1          1    1  Ala ma kota   2,5  

       1          2    2  Zosia ma psa  4,6  

       2               8                3,2     aaaaa

       2               3                7,5     bbbb

Tylko załaduj dane do tabel i jazda :)

Temat: Łączenie dwóch zbiorów danych po nazwach kolumn

Przepraszam, jeśli się nie zagłębiłem dostatecznie w temacie, ale chyba wystarczy tutaj dla "tabela_3":
Select b.x3, a.x4 From tabela_1 a LEFT JOIN tabela_2 b ON a.x3=b.x3
Co do "tabela_4", skoro nazwy kolumn nie ulegają zmianie (mam nadzieję że ich ilość również się nie zmienia), to:

SELECT x1, x2, x3, x4, NULL, NULL, NULL, NULL (Null tyle razy ile jest kolumn w tabela_2) FROM tabela_1
UNION
SELECT NULL, NULL, NULL, NULL (Null tyle razy ile jest kolumn w tabela_1), x6, x4, x3, x7,x8, x9 FROM tabela_2

Jeśli chcesz poskładać to odpowiednio dla powtarzających się kolumn w jednej i drugiej tabeli, należało by je odpowiednio wyświetlić w odpowiednim miejscu, więc mniej więcej coś takiego:
SELECT x1, x2, x3, x4, NULL, NULL, NULL, NULL (Null tyle razy ile jest kolumn w tabela_2) FROM tabela_1
UNION
SELECT NULL, NULL, x3,x4, NULL (Null tyle razy ile jest kolumn w tabela_1), x6, x7,x8, x9 FROM tabela_2
Powyżej kolumna x3 i x4 zostanie umieszczona w tym samym miejscu.

jeśli chcesz wykonywać na tym dodatkowe operacje to może CTE:
With MOJE_DANE As (
SELECT x1, x2, x3, x4, NULL, NULL, NULL, NULL (Null tyle razy ile jest kolumn w tabela_2) FROM tabela_1
UNION
SELECT NULL, NULL, x3,x4, NULL (Null tyle razy ile jest kolumn w tabela_1), x6, x7,x8, x9 FROM tabela_2
)
Select * FROM MOJE_DANE order by x3

Wszystko w T-SQL.

Jeśli są jakieś błędy, to sorry, bo pisałem z Palca

Temat: Łączenie dwóch zbiorów danych po nazwach kolumn

Jeśli lista kolumn się nie zmienia i jest ich niewiele, to na pewno wystarczy napisać ręcznego UNION'a.

A co jeśli za każdym razem ilość i nazwy kolumn mogą się dowolnie zmieniać? Jak taki automat do tworzenia widoku wygląda w T-SQL? Jestem ciekaw, bo nie znam się na SQL Serverze.