[MySQL] sortowanie wyników Opcje

[Zigi]

Witam,
mam pewna tabele o takiej strukturze i danych

id | wiersz | kolumna | wartosc | arkusz_id
1|1 |1 | 45|1
2|1|2 |SCV |1
3|1 |3 |423 |1
4|1 |4 |455 |1
5|2 |1 |432 |1
6|2 |2 |BLO |1
7|2 |3 |42 |1
9|3 |1 |453 |1
10|3 |2 |SCV |2
12|3 |3 |453 |2
13|3 |4 |342 |2
14|4 |5 |354 |2
.....................

Jest to zaimportowany arkusz z excela chcialbym to posortowac tak, aby otrzywac w wyniku posortowane wartosci
w taki sposob, że kluczem glownym sortujacym jest testk w 2 kolumnie.
Udało mi się wymyślić takie zapytanie, ale ono nie sortuje tych wyników względem wartosci i 2 kolumny ale reszta jest zwracana tak jak potrzebuję.

[SQL] pobierz, plaintext 
SELECT * FROM `obrobka` GROUP BY (`obrobka_kolumna`=2 AND `obrobka_wartosc`), `obrobka_wiersz`, `obrobka_kolumna`
[SQL] pobierz, plaintext 

Ma ktos może pomysl jakiej komendy sortujacej uzyc?

[nevt]

jedynej jaka jest w SQL: ORDER BY ....

Ten post edytował nevt 8.02.2008, 08:08:31

[Zigi]

Niestety ORDER BY nie potrafie zmusic zeby posortowal tak jak tego potrzebuje.
Chcę,aby baza danych zwróciła mi w wyniku coś takiego:

id | wiersz | kolumna | wartosc | arkusz_id
5|2 |1 |432 |1
6|2 |2 |BLO |1
7|2 |3 |42 |1
9|3 |1 |453 |1
1|1 |1 | 45|1
2|1|2 |SCV |1
3|1 |3 |423 |1
4|1 |4 |455 |1
9|3 |1 |453 |1
10|3 |2 |SCV |2
12|3 |3 |453 |2
13|3 |4 |342 |2
14|4 |5 |354 |2
.....................

Czyli ma sortowac takimi jakby paczkami (aby wszystkie kolumny przypisane do danego wiersza i danego arkusza byly obok siebie) po `wartosc` tam gdzie `kolumna` jest rowna 2.

[nevt]

[SQL] pobierz, plaintext 
SELECT * FROM tabela ORDER BY arkusz_id, kolumna, wiersz, wartosc;
[SQL] pobierz, plaintext 

[Zigi]

Dzieki @nevt za zainteresowanie, ale niestety to nie jest to tego potrzebuje. to zapytanie sortuje w pierwszej kolejnosci po `arkusz_id` itd. Chyba dalem troche za malo danych przykladowych przez, co zle odczytales jak ma byc sortowane.
Ja natomiast potrzebuje zeby bylo to pogrupowane tak jak napsialem w poprzednim poscie czyli sortowanie wg. `wartosc` tam gdzie `kolumna`=2 a reszta `wierszy` ma byc obok tego, ktory jest wlasnie zwracany i przy tym sammym `arkusz_id.
Myslalem nad pozapytaniami, ale tez nie chce mi wyjsc...

Ten post edytował Zigi 8.02.2008, 14:55:12

[Indeo]

Namieszałeś trochę i nikt teraz nie rozumie Twoich intencji. Jeśli tylko jest logiczna zasada takiego sortowania to da sie je zapisać. Napisz jeszcze raz precyzyjnie o co Ci chodzi (nie myl nazw pól tabeli, którą podajesz a potem w opisie używasz innych) i podaj wyczerpujący przykład. Nie potrzeba podzapytań wystarczy użyć odpowiedniego ifa w najbardziej złożonych przypadkach używa sie podzapytań i sprzężeń tabeli do siebie samej.

[Zigi]

Postaram sie jeszcze raz wyjasnic jak maja wygladac zwrocone dane.
Podaje tym razem troche wiecej danych przykladowych jakie sa w tabeli

id | wiersz | kolumna | wartosc | arkusz_id
1|1 |1 | 45|1
2|1|2 |SCV |1
3|1 |3 |423 |1
4|1 |4 |455 |1
5|2 |1 |432 |1
6|2 |2 |BLO |1
7|2 |3 |42 |1
9|5 |1 |453 |2
10|5 |2 |SCV |2
12|5 |3 |453 |2
13|5 |4 |342 |2
14|7 |5 |354 |2
15|7 |1 |453 | 2
16|7 |2 |ARS |2
17|7 |3 |432 |2
18|7 |4 |453 |2
19|1 |1 |786 |3
20|1 |2 |GHD |3
21|1 |3 |534 |3
22|1 |4 |453 |3
23|2 |1 |321 |3
24|2 |2 |VCX |3
25|2 |3 |324 |3
.....................

Chce, aby dane byly posortowane wg `wartosc` tam gdzie `kolumna`= 2, czyli takie cos powinno zwrocic:

id | wiersz | kolumna | wartosc | arkusz_id
16|7 |2 |ARS |2
6|2 |2 |BLO |1
20|1 |2 |GHD |3
2|1|2 |SCV |1
10|5 |2 |SCV |2
24|2 |2 |VCX |3
......................

Potrzebuje jeszcze zeby `kolumna` o tej samej wartosci, co jest `wiersz` i `arkusz_id` byly rowniez zwrocone obok tego wiersza. Czyli dla pierwszego zwroconego rekordu w pierszym zapytania dane koncowe powinny wygladac nastepujaco:

id | wiersz | kolumna | wartosc | arkusz_id
15|7 |1 |453 | 2
16|7 |2 |ARS |2
17|7 |3 |432 |2
18|7 |4 |453 |2

Dobrze byloby rowniez gdyby byla mozliwosc posortowania tych danych wg `kolumna`. Cala przykladowa tabela po zwroceniu powinna wygladac nastepujaco:

id | wiersz | kolumna | wartosc | arkusz_id
15|7 |1 |453 | 2
16|7 |2 |ARS |2
17|7 |3 |432 |2
18|7 |4 |453 |2
5|2 |1 |432 |1
6|2 |2 |BLO |1
7|2 |3 |42 |1
19|1 |1 |786 |3
20|1 |2 |GHD |3
21|1 |3 |534 |3
22|1 |4 |453 |3
1|1 |1 | 45|1
2|1|2 |SCV |1
3|1 |3 |423 |1
4|1 |4 |455 |1
9|5 |1 |453 |2
10|5 |2 |SCV |2
12|5 |3 |453 |2
13|5 |4 |342 |2
23|2 |1 |321 |3
24|2 |2 |VCX |3
25|2 |3 |324 |3

Udalo mi sie zrobic pierwsza czesc zapytania jak sobie wymyslilem jak powinno wygladac

[SQL] pobierz, plaintext 
SELECT `obrobka_wartosc` FROM `tablica` WHERE `kolumna` = 2 ORDER BY `wartosc`
[SQL] pobierz, plaintext 

Niesety nie wiem, co do tego zapytania dokleic, aby zwrocilo tak jak tego oczekuje na koncu...

Ten post edytował Zigi 10.02.2008, 23:24:48

[heaven]

cos mi w tym wyniku nie pasuje bo jest 2 razy index 9

czy to pierwsze wystapienie id 9 jest bledne?

czy sa indexy ktore nie pasuja do zadnej paczki czyli nie ma dla tych samych wiersz i arkusz_id, kolumny = 2

Ten post edytował heaven 10.02.2008, 20:31:24

[Zigi]

Bardzo przepraszam faktycznie pierwsze wystapienie jest bledne. Zaraz to poprawie.

@heaven Nie za bardzo rozumiem twoje ostatnie pytanie. Co masz na mysli, ze indeksy nie pasuja do zadnej paczki? Czy chodzi o to, ze jest tylko jedena `kolumna` o szukanej przezemnie wartosci?

[heaven]

Wisisz mi piwo

[SQL] pobierz, plaintext 
SELECT t.id,t.wiersz,t.kolumna,t.wartosc,t.arkusz_id FROM tabela t LEFT JOIN (SELECT t .wiersz,t.arkusz_id, wartosc AS 'sortowanie' FROM tabela t WHERE kolumna = 2) AS t1 ON (t.wiersz=t1.wiersz AND t.arkusz_id=t1.arkusz_id)
ORDER BY sortowanie,t.wiersz,t.arkusz_id,t.kolumna
[SQL] pobierz, plaintext 

W wyniku dostajesz cos takiego

Kod

id    wiersz    kolumna    wartosc    arkusz_id
15        7        1        453        2
16        7        2        ARS        2
17        7        3        432        2
18        7        4        453        2
14        7        5        354        2
5         2        1        432        1
6         2        2        BLO        1
7         2        3        42         1
19        1        1        786        3
20        1        2        GHD        3
21        1        3        534        3
22        1        4        453        3
1         1        1        45         1
2         1        2        SCV        1
3         1        3        423        1
4         1        4        455        1
9         5        1        453        2
10        5        2        SCV        2
12        5        3        453        2
13        5        4        342        2
23        2        1        321        3
24        2        2        VCX        3
25        2        3        324        3

Ten post edytował heaven 11.02.2008, 12:52:32

[Zigi]

Bardzo Ci dziekuje @heaven za skonstruowanie zapytania, ktore zwraca wyniki tak jak tego oczekuje.

Niestety jest ono bardzo wolne. Do testow mam obecnie w tabeli 22 060 rekordy. Na kazda wartosc `wiersz` i `arkusz_id` przypada srednio ~5-6 wartosci `kolumna`. Zapytanie zwraca wyniki dopiero po ok 80 seundach na komputerze z bartonem 2,1Ghz...
W srodowsku produkcyjnym przewiduje, ze w tabeli bedzie ok 300-400 tys. wierszy. To zapytanie bedzie wykonywane, co pewien czas lokalnie na komputerze, ale nie moze ono trwac dluzej niz 5 minut...

Slaby jestem w zapytaniach SQL, ale sadze, ze szybkosc znacznie by wzrosla jakby usunac instrukcje JOIN i zastapic ja czyms innym...

[heaven]

Szczerze wątpie czy uda sie to zrobic bez JOIN. Mozna by join pominac ale musiabys dodac dodatkowa kolumne np. 'sortowanie' i tam wypelniac dane tak aby powstalo cos takiego.

Kod

id    wiersz    kolumna    wartosc    arkusz_id      sortowanie
  15        7        1        453        2            ARS
  16        7        2        ARS        2            ARS
  17        7        3        432        2            ARS
  18        7        4        453        2            ARS
  14        7        5        354        2            ARS
  5         2        1        432        1            BLO
  6         2        2        BLO        1            BLO
  7         2        3        42         1            BLO
  19        1        1        786        3            GHD
  20        1        2        GHD        3            GHD
  21        1        3        534        3            GHD
  22        1        4        453        3            GHD
  1         1        1        45         1            SCV
  2         1        2        SCV        1            SCV
  3         1        3        423        1            SCV
  4         1        4        455        1            SCV
  9         5        1        453        2            itd.....
  10        5        2        SCV        2
  12        5        3        453        2
  13        5        4        342        2
  23        2        1        321        3
  24        2        2        VCX        3
  25        2        3        324        3

czyli przy dodawaniu rekordu musiabys odszukac rekord w ktorym wiersz i arkusz_id sa takie same i kolumna = 2 i zawartosc 'wartosc' skopiowac do nowostawianego rekordu w miejsce 'sortowanie'. i wtedy sortowanie byloby tylko przy pomocy

[SQL] pobierz, plaintext 
SELECT * FROM tabela WHERE ... ORDER BY sortowanie,t.wiersz,t.arkusz_id,t.kolumna
[SQL] pobierz, plaintext 

Mysle ze jakbys jeszcze na wszystkie pola pozakladal index'y to przyspieszylo by to JOIN'a

[Zigi]

Tez nad tym rozwiazaniem, aby dodac ta dodatkowa kolumne, ale przez to pojawilyby sie problemy w innych miejscach w apliakcji tzn. ze miana wartosci w ostatniej kolumnie.

Niestety @heaven mam juz indexy pozakladane na wszystkie kolumny, ktore biora udzial w wyszukiwaniu Moze sa jeszcze jakies inne dzialania, ktore moglby zmusic mySQL do szybszej pracy?

Wymyslilem jeszcze takie cos, ale nie testowalem wydajnosci takiego rozwiazania. Moze by zrobic druga tale z sama wartoscia po ktorej sortuje, czyli umiescic w niej dane z tego podzapytania, ktore podal @heaven:

[SQL] pobierz, plaintext 
(SELECT t.wiersz,t.arkusz_id, wartosc AS 'sortowanie' FROM tabela t WHERE kolumna = 2)
[SQL] pobierz, plaintext 

I czy wtedy JOIN nie bylby szybszy, albo mozna by zrobic bardziej optymalne podzapytanie, ale chyba sama baza danych MySQL robi takie cos w czasie podzapytan. Jak Wy myslicie o tym rozwiazaniu?

[heaven]

Druga tabela wedlug mnie nic nie wniesie. SELECT wlasnie zwraca taka wirtualna tabele

Ja dalej oponuje za rozwiazaniem z dodatkowa kolumna. Przeciez zawsze po jakiejkolwiek zmianie jakiegokolwiek rekordu mozesz wywolac np. funkcje popraw_kolumne_sortowanie($id); czyli modyfikacja calego kodu polega tylko na dodaniu jednej liniki w kazdym miejscu gdzie moze byc modyfikowany rekord. Sama funkcja na nowo wypelnilaby w tym rekordzie kolumne sortowanie.

[Zigi]

Chyba jednak bede musial sie skusic na rozwiazanie z ta dodatkowa kolumna.
Mam teraz pytanie jakie zrobic zapytanie SQL z UPDATE, aby dodalo odpowiednia wartosc w kolumnie `sortowanie`? Dopiero po wstawieniu danych bedzie wybierana kolumna, po ktorej bedzie wykonywane `sortowanie`, a wiec w gre wchodzi tylko polecenie UPDATE...
Sadze zeby robic zapytanie, ktore zwracaloby jakie sa rozne `wartosc` w danym `arkusz_id`:

[SQL] pobierz, plaintext 
SELECT DISTINCT(wartosc) FROM `tabela` WHERE `kolumna`=2 ORDER BY 'wartosc'
[SQL] pobierz, plaintext 

dalsza czesc w petli po wszystkich wartosciach jakie zwrociloby pierwsze zapytanie:
PETLA BEGIN
zapytanie UPDATE (nie wiem dokladnie jak je zformulowac),ktore by dodawalo/zmienialo ta wartosc w polu `sortowanie` WHERE `wiersz`= '$wiersz'
PETLA END

Czy jest mozliwosc, aby zrobic to optymalniej? Czyli zeby wykonac to za pomoca jednej instrukcji SQL?

Ten post edytował Zigi 12.02.2008, 11:05:08

[heaven]

Zobacz to

[SQL] pobierz, plaintext 
SELECT t.id,t.wiersz,t.kolumna,t.wartosc,t.arkusz_id, (SELECT wartosc FROM tabela t1 WHERE kolumna = 2 AND t.wiersz=t1.wiersz AND t.arkusz_id=t1.arkusz_id LIMIT 1  ) AS sortowanie FROM tabela t
ORDER BY sortowanie,t.wiersz,t.arkusz_id,t.kolumna
[SQL] pobierz, plaintext 

Jesli to sortowanie tez jest wolne to jutro pomyslimy nad update ale wydaje mi sie ze mozna to zrobic jednym poleceniem.

Ten post edytował heaven 11.02.2008, 23:51:04

[Zigi]

Niesety cos z tym zapytaniem chyba jest nie tak. Wykonywalo sie 10 minut i nie chcialo sie zakonczyc...

Chyba bede sie przymierzal do zrobienia tej wersji z dodaniem dodatkowej kolumny, bo wydajnosciowo wyszukiwanie bedzie bardzo szybkie. A, ze reszta zapytan bedzie bardziej skomplikowana no coz. Cos za cos.

Ten post edytował Zigi 12.02.2008, 00:09:13

[heaven]

Zapytanie dziala ale widocznie przy takiej liczbie rekordow jest jeszcze gorsze niz z JOIN'em
Czyli dodajesz to tabeli kolumne:

[SQL] pobierz, plaintext 
ALTER TABLE `tabela` ADD COLUMN `sortowanie` VARCHAR(10) AFTER `arkusz_id`;
[SQL] pobierz, plaintext 

i zaloz na niej index!!!

Rozumiem ze masz juz jakas baze wiec trzeba teraz we wszystkich rekordach uzupelnic kolumne sortowanie. Mozna to zrobic jednym zapytaniem.
Nie wiem ile ono bedzie sie wykonywalo...

[SQL] pobierz, plaintext 
UPDATE tabela t, (SELECT * FROM tabela WHERE kolumna = 2) AS tab
SET t.sortowanie = tab.wartosc
WHERE t.wiersz = tab.wiersz AND t.arkusz_id=tab.arkusz_id
[SQL] pobierz, plaintext 

Jesli ta aktualizacja wykonalaby sie w miare szybko to wystarczyloby tylko aktualizowac ta kolumne przed zapytaniem zwracajacym posortowane elementy
To zapytanie sortowania teraz wyglada tak

[SQL] pobierz, plaintext 
SELECT * FROM tabela ORDER BY sortowanie, wiersz, arkusz_id, kolumna
[SQL] pobierz, plaintext 

Jezeli jednak zapytanie z update'm wszystkich elementow bedzie dlugo trwalo to trzeba teraz przy kazdej modyfikacji jakiegos rekordu updatowac w nim kolumne sortowanie, dla przykladu po zmienie rekordu 13 trzeba by wykonac taka operacje

[SQL] pobierz, plaintext 
UPDATE tabela t, (SELECT * FROM tabela WHERE kolumna = 2) AS tab
SET t.sortowanie = tab.wartosc
WHERE t.id = 13 AND t.wiersz = tab.wiersz AND t.arkusz_id=tab.arkusz_id
[SQL] pobierz, plaintext 

Nie wiem jak z czasem wykonania tego zapytania i jezeli mimo tego ze chodzi o jeden element jest duzy trzeba go rozbic na 2 zapytania
na poczatku pobrac dane rekordu 13 (wiersz i arkusz_id) a potem zrobic update'a
1)

[SQL] pobierz, plaintext 
SELECT wiersz, arkusz_id FROM tabela WHERE id = 13
[SQL] pobierz, plaintext 

2) ($wiersz i $arkusz_id sa wartosciami pobranymi z zapytnia 1)

[SQL] pobierz, plaintext 
UPDATE tabela t, (SELECT * FROM tabela WHERE wiersz = $wiersz AND arkusz_id = $arkusz_id AND kolumna = 2) AS tab
SET t.sortowanie = tab.wartosc
WHERE t.id = 13
[SQL] pobierz, plaintext 

mozna to drugie zapytanie jeszcze bardziej rozbic ale to tylko jesli to by znowu bylo za wolne po rozbiciu napewno bedzie szybkie
czyli: 1) jedynka pozostaje taka sama
2) ($wiersz i $arkusz_id sa wartosciami pobranymi z poprzedniego zapytania)

[SQL] pobierz, plaintext 
SELECT wartosc FROM tabela WHERE wiersz = $wiersz AND arkusz_id = $arkusz_id AND kolumna = 2
[SQL] pobierz, plaintext 

3) (i ta otrzymana $wartosc wstawiasz do update

[SQL] pobierz, plaintext 
UPDATE tabela
SET t.sortowanie = $wartosc
WHERE id = 13
[SQL] pobierz, plaintext 

No i jeszcze na koniec metoda wstawiania nowego rekordu zeby od razu byla uzupelniana kolumna sortowanie
Jesli wstawiasz wiersz w ktorym kolumna jest = 2 to wtedy warunek if ustwia kolumne sotowanie na wlasnie podawana wartosc a jesli jesli jest inna niz 2 to szuka wiersza w ktorym jest taki wiersz i arkusz id i kolumna = 2 i pobiera wartosc ktora pozniej wstawi w miejsce sortowania dla nowo dodawaneo wpisu

[SQL] pobierz, plaintext 
INSERT INTO tabela (wiersz,kolumna,wartosc,arkusz_id,sortowanie) SELECT $wiersz,$kolumna,$wartosc,$arkusz_id, (SELECT IF($kolumna=2,$wartosc,wartosc) FROM TABLE WHERE wiersz=$wiersz AND arkusz_id=$arkusz_id AND kolumna = 2)
[SQL] pobierz, plaintext 

Wazne ze najpierw musisz wstawic rekord z kolumna = 2 i dopiero nastepne ktore maja wiersz i arkusz_id takie same. Jesli nie to musz potem poprawic wszystkie rekordy gdzie bedzie w sortowanie NULL

Mam nadzieje ze cos z tego rozumiesz bo ja coraz mniej w razei czego zapraszam na gg (w profilu)

pozdrawiam...

[Zigi]

Kolumne na sortowanie dodalem juz wczoraj i indeks tez od razu zalozylem
Robilem dla testow UPDATE wszystkich wartosci w kolumnie `sortowanie` na jakas wartosc typu 'dfgsdfsdfsdfsfdgdsfs' w tabeli z ok 60 000 rekordow. Przez to, ze mialem zalozony indeks zapytanie wykonywalo sie ok 30-80s w zaleznosci od dlugosci pola `sortowanie`. Gdy pole `sortowanie` mialo zdjety index to samo zapytanie wykonywalo sie 2-10s. Tak sobie pomyslalem czy jest instrukcja, ktrora blokowala by czasowo aktualizowanie indexu na pewnej kolumnie na pewnien czas czyli wygladaloby to mniej wiecej tak:

[SQL] pobierz, plaintext 
TABLE `sortowanie` INDEX block
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
UPDATE tabela t, (SELECT * FROM tabela WHERE kolumna = 2) AS tab SET t.sortowanie = tab.wartosc WHERE t.wiersz = tab.wiersz AND t.arkusz_id=tab.arkusz_id
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
TABLE `sortowanie` INDEX unblock
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
TABLE `sortowanie` INDEX UPDATE
[SQL] pobierz, plaintext 

Sadze ze takie cos byloby szybsze od aktualizowania indexu po kazdym zapytaniu. Jestem pewnien, ze jest takie polecenie, ale nie moge go znalezc.

Bardzo dziekuje @heaven za komleksowe rozwiazanie problemu. Teraz pozostaje mi przetestowac Twoje zapytania jak beda sie wykonywaly na prawdziwej tabeli.

[heaven]

Musialbys chyba zdjac calkiem index i pozniej zalozyc od nowa. Tylko czy oby zalozenie index'u pozniej tez nie bedzie dlugo trwalo?. Nie mialem okazji jeszcze pracowac z duzymi tabelami wiec nie moge ci pomoc w kwesti wydajnosci.