Przetworzenie wyników z tabel - może ktoś pomoże ?

Witam,
pierwszy raz muszę tak "mocno" ingerować w przetworzenie wyników pobieranych z bazy danych, że nie mam za bardzo pomyslu (i pewnie wiedzy) aby uzyskać końcowy, pożądany efekt..

O co chodzi?

Mam tabele z danymi (dane wejściowe):

id | user_id | total_value | partial1_value | parial2_value | partial3_value | partial4_value
1 | 1 | 30 | 10 | 8 | 7 | 5
2 | 2 | 35 | 10 | 10 | 8 | 7
3 | 2 | 31 | 9 | 9 | 8 | 7
4 | 3 | 29 | 10 | 10 | 4 | 5
5 | 3 | 29 | 10 | 8 | 4 | 7

I teraz chciałbym:

1. Wybrać tylko najwyższy total_value dla danego użytkownika (user_id). Tutaj akurat wpadłem na kod który pomaga uzyskać pożądany efekt:

 SELECT * FROM table AS t1
WHERE (SELECT COUNT(*) FROM table AS t2 WHERE t2.user_id = t1.user_id AND t2.total_value > t1.total_value) = 0;

W ten sposób pozbywamy się wiersza z wartością total_value = 31.

2. Jeśli wartość total_value jest równa dla danego użytkownika powinien zostać ten wynik, który ma większa liczbę 10-tek, potem 9-tek, etc.. czyli musiałby być zastosowany jakiś algorytm, który mógłby sprawdzić ile w danym wierszu jest np. 10-tek, i tak jak widać na przykładzie, powinien zostać wiersz o id = 4.

..czyli wyjściowy wynik miałby wyglądać tak:

id | user_id | total_value | partial1_value | parial2_value | partial3_value | partial4_value
1 | 1 | 30 | 10 | 8 | 7 | 5
2 | 2 | 35 | 10 | 10 | 8 | 7
4 | 3 | 29 | 10 | 10 | 4 | 5

Jakie macie pomysły, aby uzyskać taki efekt ? Proszę o wszelakie sugestie, kawałki kodu, słowa klucze które pomogłyby mi dojść do końcowego rezultatu.. Osobiście wydaje mi się, że trzeba będzie użyć do tego procedury, ale co ta procedura powinna zawierać :P (tzn. jak np. sprawdzać te wiersze pod względem występowania ilości 10-tek, 9-tek i porównywania tego względem kolumny user_id ? )

Pozdrawiam,
Michał

mikajlo napisał(a):

(tzn. jak np. sprawdzać te wiersze pod względem występowania ilości 10-tek, 9-tek i porównywania tego względem kolumny user_id ? )

Przemnóż kolejne kolumny i wybierz te z największym iloczynem. Jeśli wiersz ma mieć najwięcej dziesiątek to iloczyn kolejnych kolumn będzie największy. Jeśli wiersze posiadają taką samą ilość dziesiątek to największy będzie iloczyn tych wierszy, które mają najwięcej dziewiątek itd.
albo sumuj będzie bezpieczniej z zerami jeśli mogą się pojawić.

wstyd! :)

Znalazłem chwilę czasu by się zmierzyć z tym (ciekawym) zadaniem... :)
Sprawdź poniższe zapytanie:

select * from t join (
select user_id, max(power(10, partial_value1)+power(10, partial_value2)+power(10, partial_value3)+power(10, partial_value4)) liczba from t
group by user_id) x on t.user_id=x.user_id and power(10, partial_value1)+power(10, partial_value2)+power(10, partial_value3)+power(10, partial_value4)=x.liczba

Wprawdzie sprawdzałem na postgreSQL, ale na MySQL też powinno pójść (dlatego zastosowałem POWER).
Trochę uprościłem założenia (nie biorę maksymalnej wartości, tylko ten drugi warunek - na to samo powinno wyjść)
EDIT: Aha, o trochę zmieniłem nazwy kolumn...

@Marcin.Miga - już miałem gratulować i dziękować za pomysł (i zainteresowanie) i rozwiązanie problemu, ale jednak okazało się, że nie jest tak kolorowo..

Wykonałem Twoje zapytanie pod trochę większą ilość kolumn (15) i okazało się :

dla total_value = 85 ; suma potęg partial_value = 21113123010
dla total_value = 95 ; suma potęg partial_value = 14202131010

czyli teraz sortując malejąco według kolumny liczba wychodzi na to, że 85 jest większe niż 95 (a to wszystko dlatego, że na wartość total_value = 85 składały się dwie 10-tki a na total_value = 95 jedna :P ).

Dla pewności policzyłem to sobie ręcznie w Calc'u i wygląda to tak:

Podsumowując - szkoda, że to tak prosto nie poszło :(

ps. Ja już ogólnie zacząłem kombinować z kursorem w procedurze aby uzyskać jakoś "iteracyjnie" pożądany wynik, ale coś nie bardzo mi to idzie .. stąd też Twoje rozwiązanie po pierwszym teście niezmiernie mnie uszczęśliwiło, a tu..

EDIT: Chyba, że to co zaproponowałeś zastosować tylko do równych wyników typu total_value 85 i 85 (którzy cząstkowo jest większy).. Musiałbym to sprawdzić i jeśli będzie OK to połączyć to "w całość".

W takim razie może brzydsza wersja "siłowa" ?

SELECT
  *
FROM
  tab ta
WHERE
  ta.id = (
    SELECT
      id
    FROM
      (SELECT 
        tta.id, tta.user_id,
        SUM(IF(tta.v = 10, tta.ile, 0)) c10,
        SUM(IF(tta.v = 9, tta.ile, 0)) c9,
        SUM(IF(tta.v = 8, tta.ile, 0)) c8,
        SUM(IF(tta.v = 7, tta.ile, 0)) c7,
        SUM(IF(tta.v = 6, tta.ile, 0)) c6,
        SUM(IF(tta.v = 5, tta.ile, 0)) c5,
        SUM(IF(tta.v = 4, tta.ile, 0)) c4,
        SUM(IF(tta.v = 3, tta.ile, 0)) c3,
        SUM(IF(tta.v = 2, tta.ile, 0)) c2,
        SUM(IF(tta.v = 1, tta.ile, 0)) c1,
        SUM(IF(tta.v = 0, tta.ile, 0)) c0
      FROM 
        (SELECT
          tt.id,
          tt.v,
          COUNT(tt.v) ile,
          tt.user_id
        FROM
          (SELECT
            tb.id, tb.user_id, tb.partial1_value v
          FROM 
            tab tb
          UNION ALL
          SELECT
            tb.id, tb.user_id, tb.partial2_value
          FROM  
            tab tb
          UNION ALL
          SELECT
            tb.id, tb.user_id, tb.partial3_value
          FROM 
            tab tb
          UNION ALL
          SELECT
            tb.id, tb.user_id, tb.partial4_value
          FROM 
            tab tb
          ) tt
        GROUP BY
        tt.id, tt.v, tt.user_id
      ) tta
      JOIN (
        SELECT
          tb.id
        FROM 
          tab tb
        JOIN (
          SELECT
            tc.id,
            tc.user_id,
            MAX(tc.total_value) mtv
          FROM 
            tab tc 
          GROUP BY
            tc.user_id
        ) s_tt ON tb.user_id = s_tt.user_id AND tb.total_value = s_tt.mtv
      ) ttb ON tta.id = ttb.id
      GROUP BY
        id, user_id
    ) tt
  WHERE 
    tt.user_id = ta.user_id
  ORDER BY
    c10 DESC, c9 DESC, c8 DESC, c7 DESC, c6 DESC, c5 DESC, 
    c4 DESC, c3 DESC, c2 DESC, c1 DESC, c0 DESC
  LIMIT 1
  ) 

@matek3005 - zamieszczam tabele, na której przeprowadzałem test. Co do założeń to ma to tak działać jak napisałeś.

ps. Po części poradziłem sobie już z tym problemem (przynajmniej mi się tak wydaje), jednak do rozwiązania użyłem dość skomplikowanego kursora + tabele tymczasową, co pewnie nie jest "najszczęśliwszym" rozwiązaniem..

 
 DROP TABLE IF EXISTS `tab`;
 CREATE TABLE `tab` (
  id INT(10) NOT NULL AUTO_INCREMENT,
  user_id INT(10) NOT NULL,
  total_value INT(5) NOT NULL,
  partial1_value INT(5) DEFAULT NULL,
partial2_value INT(5) DEFAULT NULL,
partial3_value INT(5) DEFAULT NULL,
partial4_value INT(5) DEFAULT NULL,
  PRIMARY KEY (id)
 ) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

insert into tab (user_id,total_value,partial1_value,partial2_value,partial3_value,partial4_value) values (1,30,10,8,7,5);
insert into tab (user_id,total_value,partial1_value,partial2_value,partial3_value,partial4_value) values (2,35,10,10,8,7);
insert into tab (user_id,total_value,partial1_value,partial2_value,partial3_value,partial4_value) values (3,31,9,9,8,7);
insert into tab (user_id,total_value,partial1_value,partial2_value,partial3_value,partial4_value) values (4,29,10,10,4,5);
insert into tab (user_id,total_value,partial1_value,partial2_value,partial3_value,partial4_value) values (5,29,10,8,4,7);
insert into tab (user_id,total_value,partial1_value,partial2_value,partial3_value,partial4_value) values (6,38,10,10,8,10);
insert into tab (user_id,total_value,partial1_value,partial2_value,partial3_value,partial4_value) values (7,35,10,10,10,5);

No to dla tej tabelki, którą zamieściłeś mój select działa tak jak tego oczekujesz. W tej tabeli masz dokładnie jeden rekord dla każdego user_id więc siłą rzeczy zwróci ci on dokładnie to samo co zwykły select bo nie ma co liczyć, ale gdy dodasz np:

INSERT INTO tab (user_id,total_value,partial1_value,partial2_value,partial3_value,partial4_value) VALUES (7,35,10,10,9,6);
INSERT INTO tab (user_id,total_value,partial1_value,partial2_value,partial3_value,partial4_value) VALUES (7,34,4,10,10,10);

to już dla user_id = 7 zwróci Ci tylko rekord z id = 7 ponieważ te rekordy, które dodałem nie spełniają założeń:

1. pierwszy rekord pomimo, że ma total_value = 35 to jednak ma mniej 10 niż ten rekord pod id = 7
2. drugi rekord nie ma największego total_value

Nadal nie jestem do końca przekonany, czy dobrze zrozumiałem Twój problem.

Ponadto rozwiązanie z tabelą tymczasową jest dużo lepsze niż ten mój select jednak założyłem (bezpodstawnie), że ma być to w formie jednego zapytania z podzapytaniami. W innym wypadku algorytm znacznie się ułatwia i przyspiesza :)

Pytanie, które zadam jest związane tematem więc nie będe zakładał nowego wątku..

Chodzi o to, że nastąpiła zmiana typu z int na double(6,4) dla partial_value i total_value i zastanawiam się, w jaki sposób można "wyselectować" wartość samej końcówki po przecinku (pomijając liczbę dziesiątek)..

Przykład: z liczby **15.5003 ** chce uzyskać 3 (czyli samą końcówkę.. a ściśle mówią to ostatnie 3 cyfry czyli **003 **jakby patrzeć pod względem formatu).

ps. Szukałem też sposobu na uzyskaniu "pierwszej" części wyniku czyli: 15.5 i tutaj znalazłem taką metode:

SELECT FORMAT(total_value,1) FROM tab

SELECT total_value * 10000 % 1000 FROM tab;