Witaj Gościu! ( Zaloguj | Rejestruj )

Forum PHP.pl

 
Reply to this topicStart new topic
> Zapytanie MySQL do rankingu zdjęć
trifek
post 6.02.2019, 13:37:20
Post #1





Grupa: Zarejestrowani
Postów: 340
Pomógł: 0
Dołączył: 28.09.2015

Ostrzeżenie: (0%)
-----


Witam.

Mam 2 tabele:

  1. CREATE TABLE `psPhotosRating` (
  2. `id_photo_rating` INT(11) NOT NULL,
  3. `id_user` INT(11) NOT NULL,
  4. `id_uploaded_files` INT(11) NOT NULL,
  5. `rating` INT(2) NOT NULL,
  6. `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  8.  
  9. CREATE TABLE `psUploadedFiles2` (
  10. `id_uploaded_files` INT(10) UNSIGNED NOT NULL,
  11. `enable` CHAR(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  12. `id_user` INT(11) NOT NULL DEFAULT '0',
  13. `file_path` VARCHAR(150) COLLATE utf8_unicode_ci NOT NULL,
  14. `file_name` VARCHAR(75) COLLATE utf8_unicode_ci NOT NULL,
  15. `creation_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  16. `category` BIGINT(20) NOT NULL DEFAULT '0',
  17. `tags` text COLLATE utf8_unicode_ci,
  18. `description` mediumtext COLLATE utf8_unicode_ci,
  19. `promo_in_front` CHAR(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  20. `count` BIGINT(20) NOT NULL
  21. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  22.  
  23. ALTER TABLE `psPhotosRating`
  24. ADD PRIMARY KEY (`id_photo_rating`);
  25.  
  26. ALTER TABLE `psPhotosRating`
  27. MODIFY `id_photo_rating` INT(11) NOT NULL AUTO_INCREMENT;
  28.  
  29. ALTER TABLE `psUploadedFiles2`
  30. MODIFY `id_uploaded_files` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
  31. COMMIT;
  32.  

psUploadedFiles2 - tabela jest bazą danych zdjęć
psPhotosRating - tabla z głosami oddanymi na psUploadedFiles2 psUploadedFiles2

Nie każdy obrazek ma oddane na niego głosy.

Potrzebuję zapytanie SQL wyświetlające listę zdjęć (psUploadedFiles2) posortowanych po rankingu z psPhotosRating (średniej głosów).
średnia głosów = ilość głosów / wartość głosów (psPhotosRating => rating)

Wie ktoś może jak ułożyć takie zapytanie?


Chciałbym żeby ranking był liczony jako suma głosów oddanych na zdjęcie (psPhotosRating -> suma rating)) / ilość głosów oddanych na dane zdjęcie.
Wynik miałby być posortowany według wartości uzyskanej w powyższym dzieleniu.

W tabeli psPhotosRating mam przykładowo rekordy:
  1. INSERT INTO `psPhotosRating` (`id_photo_rating`, `id_user`, `id_uploaded_files`, `rating`, `timestamp`) VALUES
  2. (1, 36, 11, 4, '2019-02-06 09:09:08'),
  3. (2, 112, 5, 5, '2019-02-06 09:54:34'),
  4. (3, 16, 11, 1, '2019-02-04 11:20:24');
  5.  


Próbowałem coś takiego:
  1. SELECT (SELECT CONCAT(file_path, '/', file_name) AS image FROM psUploadedFiles B WHERE category = user.id_user AND enum =3 ORDER BY number ASC LIMIT 1) AS image2, user.username, user.url_address, files.*, CONCAT(file_path, '/', file_name) AS image, (SELECT COUNT(id_photo_rating) FROM psPhotosRating WHERE id_uploaded_files = photos.id_uploaded_files) AS numbers, (SELECT SUM(rating) FROM psPhotosRating WHERE id_uploaded_files = photos.id_uploaded_files) AS summary, (SELECT ROUND(AVG(rating),2) FROM psPhotosRating WHERE id_uploaded_files = photos.id_uploaded_files) AS avgratting FROM psPhotosRating photos JOIN psUploadedFiles2 files ON files.id_uploaded_files = photos.id_uploaded_files JOIN psUser user ON user.id_user = files.id_user GROUP BY id_uploaded_files ORDER BY avgratting DESC;


ale nie do końca działa to poprawnie sad.gif
Go to the top of the page
+Quote Post
borabora
post 8.02.2019, 16:17:47
Post #2





Grupa: Zarejestrowani
Postów: 116
Pomógł: 33
Dołączył: 8.09.2014

Ostrzeżenie: (0%)
-----


Cytat(trifek @ 6.02.2019, 14:37:20 ) *
średnia głosów = ilość głosów / wartość głosów (psPhotosRating => rating)

średnia to suma/ilość
  1. SELECT p.*
  2. FROM psuploadedfiles2 p
  3. LEFT JOIN psphotosrating r ON p.id_uploaded_files=r.id_uploaded_files
  4. GROUP BY p.id_uploaded_files
  5. ORDER BY sum(r.rating)/count(r.id_photo_rating) DESC
Go to the top of the page
+Quote Post

Reply to this topicStart new topic
1 Użytkowników czyta ten temat (1 Gości i 0 Anonimowych użytkowników)
0 Zarejestrowanych:

 



RSS Wersja Lo-Fi Aktualny czas: 19.03.2024 - 04:30