Łączenie dwóch kolumn z jednego zapytania jako kolejny rekord

Witajcie,

Poniższe zapytanie:

SELECT p1.pid AS 'pid1', p1.pt AS 'pt1', p2.pid AS 'pid2', p2.pt AS 'pt2'
FROM ptab p1
LEFT JOIN ptab p2
ON p2.oid = p1.pid
WHERE p1.oid = 'E25C14993B681B673E16F128'

w wyniku generuje tabele:

|          pid1            | pt1 |           pid2           | pt2  |
+--------------------------+-----+--------------------------+------+
| E2702472E6540A775A89B04F | 0   | NULL                     | NULL |
| E2AA47E7CE6E963C825FF132 | 1   | E2A2AA23422F5DDAE734A84F | 0    |
| E2AA47E7CE6E963C825FF132 | 1   | E28D9CB75EC3A7815E9C7815 | 0    |

Czy istnieje sposób by scalić kolumny pid1 oraz pid2, uwzględniając pt1 i pt2, gdy są równe 0?

Czyli, rezultatem działania powinna być poniższa tabela:

|        pid1 + pid2       |
+--------------------------+
| E2702472E6540A775A89B04F |
| E2A2AA23422F5DDAE734A84F |
| E28D9CB75EC3A7815E9C7815 |

Dziękuję

może napisz dokładnie kiedy jest brana wartość z kolumny pid1 a kiedy z pid2

@abrakadaber:

abrakadaber napisał(a):

może napisz dokładnie kiedy jest brana wartość z kolumny pid1 a kiedy z pid2

Ok

Tabela jest zdefiniowana jak poniżej:

CREATE TABLE ptab
(
    oid TEXT NOT NULL,
    pid TEXT NOT NULL,
    ot INTEGER NOT NULL,
    pt INTEGER NOT NULL
);

Kolumna oid, może czasem zawierać dane z kolumny pid.
Kolumny ot oraz pt określają pewną hierarchię danych, determinującą strukturę drzewa.

Wyjaśnię to na przykładzie.

Drzewo ma maksymalną głębokość równą 3. To gdzie znajdzie się obiekt z kolumny oid, jest uzależnione od ot oraz pt.

Jeśli ot ma wartość 2, tzn., że obiekt z oid jest na głębokości/poziomie 3 drzewa, a kolumna pid definiuje obiekt nadrzędny (pt dla niego ma wartość 1), jednak ten będzie miał nad sobą korzeń.

Wspomniany obiekt z kolumny pid, znajduje się w kolumnie oid, ale jego wartość ot jest już równa 1, natomiast w pid znajduje się korzeń oznaczony wartością 0 w pt.

Warianty są dwa dla obiektów z oid:

• może znajdować się zaraz pod korzeniem,
• znajduje się pod pid, a te pod korzeniem.

Czyli wizualnie:
korzeń --> pid --> oid
lub
korzeń --> oid

Chciałbym teraz dowiedzieć się, czy obiekt z oid (dla ot równe 2), zawiera się już pod wybranym korzeniem, niezależnie czy bezpośrednio, czy pośrednio poprzez pid.

=== Edycja ===
Z zapytania jakie podałem w pierwszym poście, jestem wstanie uzyskać wymagany rezultat, poprzez dodatkową analizę zwróconej tabeli, ale zastanawiałem się, czy nie istnieje jakiś sposób w SQL by uzyskać gotowy wynik.

Struktury drzewiaste opera się o rekurencję, czyli coś w rodzaju:

id,
parent_id

gdzie parent_id to klucz obcy do kolumny id w tej samej tabeli, w ten sposób łatwo zrobić "drzewo" o dowolnym poziomie zagłębienia.

TomRZ napisał(a):

Struktury drzewiaste opera się o rekurencję, czyli coś w rodzaju:

id,
parent_id

gdzie parent_id to klucz obcy do kolumny id w tej samej tabeli, w ten sposób łatwo zrobić "drzewo" o dowolnym poziomie zagłębienia.

Domyślam się, że sugerujesz bym uprościł tabelę do dwóch kolumn i założył, że jedna z nich to dzieci, a druga rodzice... być może masz rację, tylko musze się zastanowić, czy dodatkowe informacje z ot i pt, mogłyby być z niej usunięte i np. przeniesione gdzieś indziej.

Dziękuję

ProNOP napisał(a):

TomRZ napisał(a):

Struktury drzewiaste opera się o rekurencję, czyli coś w rodzaju:

Domyślam się, że sugerujesz bym uprościł tabelę do dwóch kolumn i założył, że jedna z nich to dzieci, a druga rodzice... być może masz rację, tylko musze się zastanowić, czy dodatkowe informacje z ot i pt, mogłyby być z niej usunięte i np. przeniesione gdzieś indziej.

Dziękuję

Te dwie kolumny to istota rekurencji, ale możesz dodać dodatkowe informacje - np. stopień zagnieżdżenia, ścieżkę do rodzica, etc.

Np.:
id,
parent_id,
name (text),
level,
parent_path (text)

I teraz powiedzmy ze chodzi o kategorie, przykładowe dane:

1, null, "samochody", 1, null
2, 1, "ciężarowe", 2, "1"
3, 2, "specjalistyczne", 3, "1,2"
4, 3, "betoniarki", 4, "1,2,3"

Bardzo wygodne w odczycie, ale nieco mniej wygodne przy wstawianiu danych i aktualizacji. Wyobraź sobie że teraz ciężarowe tracą kategorię - rodzica i stają się kategorią szczytową - musisz aktualizować kolumny "level" i "parent_path" dla wszystkich dzieci tej kategorii.

TomRZ napisał(a):

ProNOP napisał(a):

TomRZ napisał(a):

Struktury drzewiaste opera się o rekurencję, czyli coś w rodzaju:

Domyślam się, że sugerujesz bym uprościł tabelę do dwóch kolumn i założył, że jedna z nich to dzieci, a druga rodzice... być może masz rację, tylko musze się zastanowić, czy dodatkowe informacje z ot i pt, mogłyby być z niej usunięte i np. przeniesione gdzieś indziej.

Dziękuję

Te dwie kolumny to istota rekurencji, ale możesz dodać dodatkowe informacje - np. stopień zagnieżdżenia, ścieżkę do rodzica, etc.

Np.:
id,
parent_id,
name (text),
level,
parent_path (text)

I teraz powiedzmy ze chodzi o kategorie, przykładowe dane:

1, null, "samochody", 1, null
2, 1, "ciężarowe", 2, "1"
3, 2, "specjalistyczne", 3, "1,2"
4, 3, "betoniarki", 4, "1,2,3"

Bardzo wygodne w odczycie, ale nieco mniej wygodne przy wstawianiu danych i aktualizacji. Wyobraź sobie że teraz ciężarowe tracą kategorię - rodzica i stają się kategorią szczytową - musisz aktualizować kolumny "level" i "parent_path" dla wszystkich dzieci tej kategorii.

Zgadza się, dlatego dodatkowe informacje o samych obiektach z oid i pid są w osobnych tabelach.

Na szybko wydaje mi się, że rozwiązanie jakie zaproponowałeś (uproszczenie tabeli, jak zwykle chciałem sobie utrudnić życie...) jest dobrą drogą i choć nie odpowiada na moje pytanie to z dużym prawdopodobieństwem, rozwiązuje mój problem, więc uznaję to za odpowiedź.

Dziękuję

Niestety, ale te dodatkowe informacje w ot i pt nie mogą zostać pominięte (muszę wiedzieć z jakim obiektem mam do czynienia), co oznacza, że uproszczenie tabeli utrudni dalsze akcje.

Wracam wiec do pierwszego postu i proszę o pomoc...

@TomRZ: Możesz dodać ot i pt do tej tabeli.

Tak i one się w niej znajdują.

Wynik działania kwerendy, jaką zamieściłem w pierwszym poście:

|          pid1            | pt1 |           pid2           | pt2  |
+--------------------------+-----+--------------------------+------+
| E2702472E6540A775A89B04F | 0   | NULL                     | NULL |
| E2AA47E7CE6E963C825FF132 | 1   | E2A2AA23422F5DDAE734A84F | 0    |
| E2AA47E7CE6E963C825FF132 | 1   | E28D9CB75EC3A7815E9C7815 | 0    |

Czy istnieje możliwość by odfiltrować powyższą tabelę i w wyniku dostać poniższy rezultat (dla pt1 i pt2 równe zero):

|          result          |
+--------------------------+
| E2702472E6540A775A89B04F |
| E2A2AA23422F5DDAE734A84F |
| E28D9CB75EC3A7815E9C7815 |

Dziękuję

a co jeśli pt1 i pt2 równocześnie są równe zero, albo ani jedno ani drugie nie jest równe zero ? pid1 czy pid2 ?

grzegorz_so napisał(a):

a co jeśli pt1 i pt2 równocześnie są równe zero ? pid1 czy pid2 ?

Grzegorzu, właśnie to napisałem:
@ProNOP: Czy istnieje możliwość by odfiltrować powyższą tabelę i w wyniku dostać poniższy rezultat (dla pt1 i pt2 równe zero)

... o to chodzi, gdy oba są równe zero, by połączyć wyniki dwóch kolumn, odrzucić wszystko pozostałe i dostać w wyniku dane:

|          result          |
+--------------------------+
| E2702472E6540A775A89B04F |
| E2A2AA23422F5DDAE734A84F |
| E28D9CB75EC3A7815E9C7815 |

Zauważ, że ów 3 wiersze to te same wartości z tabeli wynikowej (oznaczyłem je pomiędzy > <):

|           pid1             | pt1 |            pid2            | pt2  |
+----------------------------+-----+----------------------------+------+
| >E2702472E6540A775A89B04F< | 0   | NULL                       | NULL |
|  E2AA47E7CE6E963C825FF132  | 1   | >E2A2AA23422F5DDAE734A84F< | 0    |
|  E2AA47E7CE6E963C825FF132  | 1   | >E28D9CB75EC3A7815E9C7815< | 0    |

zagnieżdżony select z klauzulą case when

ale co znaczy połączyć?
jeśli masz dwa wiersze z pt1 i pt2 równymi zero i RÓŻNE wartości w polach pid1 i pid2 ?

masz dwuargumentową funkcję result (pt1,pt2) gdzie dziedziną zmiennych pt1 i pt2 jest trzy elementowy zbiór wartości (null,0,1)
w sumie 9 wariantów (3x3)

grzegorz_so napisał(a):

ale co znaczy połączyć?

jeśli masz dwa wiersze z pt1 i pt2 równymi zero i RÓŻNE wartości w polach pid1 i pid2 ?

Załóżmy na potrzeby przykładu, że:

CREATE TABLE "test2" (
	"pid1"	TEXT,
	"pt1"	INTEGER DEFAULT 0,
	"pid2"	TEXT,
	"pt2"	INTEGER DEFAULT 0
);

-- Wypełnienie tabeli
INSERT INTO test2 VALUES('E2702472E6540A775A89B04F', 0, NULL, NULL);
INSERT INTO test2 VALUES('E2AA47E7CE6E963C825FF132', 1, 'E2A2AA23422F5DDAE734A84F', 0);
INSERT INTO test2 VALUES('E2AA47E7CE6E963C825FF132', 1, 'E28D9CB75EC3A7815E9C7815', 0);

Po wykonaniu:

SELECT * FROM test2

otrzymam:

|          pid1            | pt1 |           pid2           | pt2  |
+--------------------------+-----+--------------------------+------+
| E2702472E6540A775A89B04F | 0   | NULL                     | NULL |
| E2AA47E7CE6E963C825FF132 | 1   | E2A2AA23422F5DDAE734A84F | 0    |
| E2AA47E7CE6E963C825FF132 | 1   | E28D9CB75EC3A7815E9C7815 | 0    |

Jakie kwerendy/procedury/... powinienem teraz wykonać na powyższej tabeli by w rezultacie otrzymać jedną kolumnę z zagregowanymi danymi?

|          result          |
+--------------------------+
| E2702472E6540A775A89B04F |
| E2A2AA23422F5DDAE734A84F |
| E28D9CB75EC3A7815E9C7815 |

select 
 case 
   when pt1=1 and pt2 =1 then .....
   when pt1=1 and pt2 =0 then .....
   when pt1=1 and pt2 is null  then .....
   when pt1=0 and pt2 =1 then .....
   when pt1=0 and pt2 =0 then .....
   when pt1=0 and pt2 is null  then .....
   when pt1 is null and pt2 =1 then .....
   when pt1 is null and  pt2 =0 then .....
   when pt1 is null and pt2 is null  then .....
 end as result 
from 
 (
   SELECT 
       p1.pid AS 'pid1', p1.pt AS 'pt1', p2.pid AS 'pid2', p2.pt AS 'pt2'
   FROM 
     ptab p1
    LEFT JOIN ptab p2 
    ON p2.oid = p1.pid 
    WHERE p1.oid = 'E25C14993B681B673E16F128'
) 

pisane z głowy

masz dwargumentową ową funkcję result(pt1,pt2) gdzie wspólną dziedziną zmiennych pt1 i pt2 jest trzyelementowy zbiór (Null,0,1) . Razem 9 wariantów
jeśli wyeliminujemy "null" ze zbioru będącego dziedziną funkcji to zostaną 4 warianty

select 
 case 
   when pt1=1 and pt2 =1 then .....
   when pt1=1 and pt2 =0 then .....
   when pt1=0 and pt2 =1 then .....
   when pt1=0 and pt2 =0 then .....
 end as result 
from 
 (
   SELECT 
       p1.pid AS 'pid1', p1.pt AS 'pt1', p2.pid AS 'pid2', p2.pt AS 'pt2'
   FROM 
     ptab p1
    LEFT JOIN ptab p2 
    ON p2.oid = p1.pid 
    WHERE p1.oid = 'E25C14993B681B673E16F128'
) 

@ProNOP:

Jakie kwerendy/procedury/... powinienem teraz wykonać na powyższej tabeli by w rezultacie otrzymać jedną kolumnę z zagregowanymi danymi?

agregacja w SQL jest czymś innym , to np. sum(), count (), avg() i jeszcze kilka innych funkcji w połączniu z klauzulą "group by"

O ile dobrze zrozumiałem Twój problem, to można to zrobić w ogólności za pomocą zapytań reukrsywnych. Dla wskaznego silnika: https://sqlite.org/lang_with.html

Wspomniałeś, że masz maksymalnie 3 poziomy takiej hierarchii, to można to zrobić za pomocą union all:

with 
level0 as  (
  /* Wybierasz rodziców/roota   */
  select t.*, 0 lvl from test2 t where pt2 is null 
),
level1 as (
  /* Wybierasz  dzieci rodziców */
  select t.*, 1 lvl from test2 t join level0 l on t.pt2=l.pt1
),
level2 as (
  /* Wybierasz dzieci dzieci   */
  select t.*,2 lvl from test2 t join level1 l on t.pt2=l.pt1
)
select pid1,lvl as pid  from level0 
union all 
select pid2,lvl from level1 
union all 
select pid2,lvl from level2
order by lvl
;

Rekursywne ma tę zaletę, że nie trzeba modyfikować zapytania jeśli wprowadzisz kolejne poziomy hierarchii.

Wiem, że UNION to zuo, ale może tak...

SELECT pid1 FROM test2 WHERE pt1=0
UNION
SELECT pid2 FROM test2 WHERE pt2=0