Zliczanie wystąpień rekordu w tabelach Opcje

[zoltodziob]

Witajcie.
Postanowiłem założyć konto na Forum. Od kilku miesięcy korzystałem z jego pomocy, głównie przeszukując archiwalne tematy, jednak dzisiaj potrzebuję Waszej pomocy.
Mam do wykonania takie zapytanie MYSQL: muszę sprawdzić czy podany rekord id_klienta o wartości dajmy na to 10 w tabeli KLIENCI został użyty w innych tabelach. Zapytanie miało by zwraca łączną liczbę wystąpień we wszystkich tabelach (np. w 6 podanych tabelach). Próbowałem za pomocą JOIN ale strasznie skomplikowane przy wielu tabelach. Czytałem też o HAVING, ale trochę tego nie ogarniam. Dodam też pracuję przy pomocy Codeigniter. Macie jakiś pomysł? (IMG:style_emoticons/default/wink.gif)

[mmmmmmm]

Funkcje agregujące (Count) przyjacielem twym. Łącz kolejno z każdą z tabel (tych 6 podanych) i daj WHERE + Count. Sprawdzić ilość możesz sobie na kliencie.

[zoltodziob]

Nie wiem czy dobrze rozumiem, ale poniższy kod na pewno nie działa:

[SQL] pobierz, plaintext 
SELECT COUNT(k.klient_id) AS ilość
FROM klienci k
JOIN tabela1 t1 ON t1.klient_id = k.klient_id
JOIN tabela2 t2 ON t2.klient_id = k.klient_id
WHERE k.klient_id = 10
[SQL] pobierz, plaintext 

Ten post edytował zoltodziob 10.05.2015, 10:22:30

[lukasz1985]

Zlicz z każdej tabeli osobno i zsumuj

[PHP] pobierz, plaintext 
 
$a = $this->db->where("id_klienta", $id_klienta)->get("tabela1")->result_array();
$count_a = count($a);
 
$b = $this->db->where("id_klienta", $id_klienta)->get("tabela2")->result_array();
$count_b = count($b);
 
$c = $this->db->where("id_klienta", $id_klienta)->get("tabela3")->result_array();
$count_c = count($c);
 
$suma = $count_a + $count_b + $count_c;
[PHP] pobierz, plaintext 

Ten post edytował lukasz1985 10.05.2015, 11:19:51

[zoltodziob]

Takiego rozwiązania chciałem uniknąć. Chciałem to zrobić przy pomocy jednego zapytania. Generalnie potrzebuję tego do sprawdzenia, czy dany klient może zostać usunięty z bazy, to znaczy czy nie jest do niego przypisane żadne zamówienie, płatność i inne. Jeśli takowe wystąpią to system miałby nie pozwolić usunąć.

[viking]

Od tego jest sam silnik BD. Innodb oraz ON DELETE RESTRICT.

[DarkAbso]

Od tego jest sam silnik BD. Innodb oraz ON DELETE RESTRICT.

Dokładnie, pod warunkiem, iż dobrze relacje są porobione. Jeśli nie to spróbuj usuwać takim zapytaniem :

[SQL] pobierz, plaintext 
DELETE FROM klient
WHERE id = 10
AND id NOT IN (	SELECT id_klient FROM test1
		UNION ALL
		SELECT id_klient FROM test2)
[SQL] pobierz, plaintext 

Gdzie NOT IN łączysz sobie tabele za pomocą UNION ALL. , a gdzie id to interesujące Ciebie id klienta.

Jeżeli chcesz zliczać to:

[SQL] pobierz, plaintext 
SELECT COUNT(id_klient) FROM
(SELECT id_klient FROM test1
UNION ALL
SELECT id_klient FROM test2) AS test
WHERE id_klient = 10
[SQL] pobierz, plaintext 

Ten post edytował DarkAbso 11.05.2015, 12:26:36

[mmmmmmm]

Omatkoboska...
NOT IN bez warunku na NULL? http://sqlfiddle.com/#!9/9f0e5/1
Powinno być tak: http://sqlfiddle.com/#!9/9f0e5/2
W tym drugim wydajnościowo do dupy maksymalnie. WHERE powinien być w środku, w każdym podzapytaniu.

[DarkAbso]

Omatkoboska...
NOT IN bez warunku na NULL? http://sqlfiddle.com/#!9/9f0e5/1
Powinno być tak: http://sqlfiddle.com/#!9/9f0e5/2
W tym drugim wydajnościowo do dupy maksymalnie. WHERE powinien być w środku, w każdym podzapytaniu.

Ciężko odpowiedzieć precyzyjnie na pytanie jeżeli nie znam schematu bazy. Jeżeli w tabelach na id_klient jest ustawione, iż nie może przyjmować wartości null to niema problemu, w ręcz dodatkowy warunek jest zbędny "bo wydajność" (niby niewielka będzie różnica, ale przy komercyjnych rozwiązania każdy czas się liczy).
Co do drugiego zapytania to masz rację mogłem ograniczyć wcześniej i nie rzeźbić po wszystkich rekordach. Zresztą jest to proponowane rozwiązanie. Ten problem ma kilka rozwiązań, a mysql niestety ma kilka ograniczeń między innymi brak klauzuli WITH za pomocą której można w przejrzysty sposób ładnie załatwić sprawę bez zagnieżdżonych zapytań. Sprawę można załatwić również za pomocą INNER JOIN lub NOT EXISTS. Jak to się mówi temat woda, ale dzięki za zwrócenie uwagi. Na drugi raz będę dokładniej analizował problemy na tym forum, aby podać precyzyjną odpowiedź.

Ten post edytował DarkAbso 11.05.2015, 14:50:01

[zoltodziob]

Zgadza się id_klient nie może przyjmować wartości NULL. Kombinuję z łączeniem JOIN, ale nie wychodzi. Zastanawiam się nad ON DELETE RESTRICT. Czytałem trochę o tym, ale nie wiem czy odpowiednio uda mi się ustawić odpowiednie relacje - jestem laikiem w tej sprawie. A wy jakie rozwiązanie proponujecie, żeby było jak najbardziej wydajne? Baza będzie się składać z ponad 3000 rekordów jeśli chodzi o klientów i do tego dochodzą tabele związane z zamówieniami itp.

[salfunglandyare]

on delete restrict lub no action spowoduje, że podczas próby usunięcia dostaniesz warning z bazy danych, że rekordu nie można usunąć, bo istnieją rekordy zależne - w innym przypadku - usuniesz. Constraints nie zadziała przy myisam (domyślnym silniku mysql), musisz ustawić na innodb.
Jeśli chcesz załatwić to jednym zapytaniem - da się - left join, np:

[SQL] pobierz, plaintext 
SELECT klienci.id, (count(a.id) + count(b.id) + count(c.id)) AS ilosc FROM klienci
LEFT JOIN costam1 AS a ON (a.id_klient = klienci.id)
LEFT JOIN costam2 AS b ON (b.id_klient = klienci.id)
LEFT JOIN costam3 AS c ON (c.id_klient = klienci.id)
WHERE klienci.id = ID_KLIENTA
GROUP BY klienci.id;
[SQL] pobierz, plaintext 

podstawiasz swoje dane, w wyniku dostajesz ID oraz ilosc jako sumę rekordów ze wszystkich tabel

//edit: Jeszcze info o constraints - fajna opcja - cascade, w przypadku, gdy chcesz usunąć rekord, cascade pozwoli Ci usunąć wszystkie rekordy zależne, ale WAŻNE jest odpowiednie stowrzenie struktury tabel, niekiedy przydaje się set null - tam gdzie zamiast usunięcia rekordu powinna zostać wyrzucona dana o identyfikatorze elementu

Ten post edytował salfunglandyare 12.05.2015, 20:49:24

[zoltodziob]

Zrobię chyba tak jak zaproponował salfunglandyare. Wszystkim dziękuję za udział w dyskusji.

[DarkAbso]

Zapytanie z left join'ami prawdopodobnie będzie najdłużej się wykonywało, ale 3000 rekordów to "żadna" ilość dla baz danych. Rozwiązanie wybierz takie na którym będzie najłatwiej Tobie pracować.

[salfunglandyare]

Cóż, jestem pewny, że szybciej niż subqueries (IMG:style_emoticons/default/wink.gif) ale też nie napisałem, że to rozwiązanie optymalne - ale chyba najlepsze jeśli chodzi o rozwiązanie w stylu 'jedno zapytanie' (IMG:style_emoticons/default/biggrin.gif)

[DarkAbso]

Nie do końca i już wytłumaczę dlaczego (chociaż mogę się mylić ). Najpierw budujesz widok tabeli klienci połączonej z innymi (costam1 itd...), a następnie dopiero ograniczasz where (czyli kleimy wszystko a później dopiero wycinamy). Najlepiej było by od razu ograniczyć do rekordów które potrzebujemy. W tym wyniku takie zapytanie zagnieżdżone będzie szybsze:

[SQL] pobierz, plaintext 
SELECT COUNT(klient_id) FROM
(SELECT klient_id FROM tabela1 WHERE klient_id = 1
UNION ALL
SELECT klient_id FROM tabela2 WHERE klient_id = 1
UNION ALL
SELECT klient_id FROM tabela3 WHERE klient_id = 1) AS test
[SQL] pobierz, plaintext 

Do tego użyte jest jedno wywołanie funkcji count i brak dodawania co też wpływa na tempo zapytania.

W Twoim zapytaniu można kombinować z ograniczeniami od razu przy join'ach co by przyśpieszyło zapytanie.

Zresztą z ciekawości wykonałem test i go powtórzyłem kilka razy. Co prawda na małej ilości danycm ale moje zapytanie wykonuje się 0 ms, a Twoje 1 ms. Z ciekawości jutro sprawdzę na większej ilości danych. (IMG:style_emoticons/default/smile.gif)

[salfunglandyare]

masz rację, ale ja pisałem o subquerries w sensie select w where innego selecta. Co do unii, masz pewnie racje w przypadku, gdy tych tabel jest relatywnie mało, gdyż każdy select jest wykonywany z osobna i "doklejany" do wyniku, suma z tego może wykonywać się krócej dla takich zapytań, kto wie, czy nie agregując tak:

[SQL] pobierz, plaintext 
SELECT sum(s) AS suma FROM
(
SELECT count(klient_id) AS s FROM tabela1 WHERE klient_id = 1
UNION ALL
SELECT cpunt(klient_id) AS s FROM tabela2 WHERE klient_id = 1
UNION ALL
SELECT count(klient_id) AS s FROM tabela3 WHERE klient_id = 1
);
[SQL] pobierz, plaintext 

nie wyjdzie szybciej. Joiny maja swoja potege w join buffer i w indeksach (IMG:style_emoticons/default/smile.gif)

[DarkAbso]

Jak już pisałem, temat woda. Jest kilka rozwiązań, ale dobrze, że ktoś porusza kwestię wydajności zapytań. Dla zoltodziob nie będzie różnicy, którą opcję wybierze jak ma kilka tabel po kilkaset rekordów. Problem pojawia się jak jest kilkadziesiąt tabel po kilka milionów rekordów. (IMG:style_emoticons/default/smile.gif) Miło, że ktoś chce się dzielić swoimi spostrzeżeniami i wiedzą. Jutro jak się uda to przetestuje rozwiązania na większe ilości danych. (IMG:style_emoticons/default/smile.gif)