Witam.

Mam 2 tabele:

[SQL] pobierz, plaintext 
CREATE TABLE `psPhotosRating` (
  `id_photo_rating` INT(11) NOT NULL,
  `id_user` INT(11) NOT NULL,
  `id_uploaded_files` INT(11) NOT NULL,
  `rating` INT(2) NOT NULL,
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
CREATE TABLE `psUploadedFiles2` (
  `id_uploaded_files` INT(10) UNSIGNED NOT NULL,
  `enable` CHAR(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `id_user` INT(11) NOT NULL DEFAULT '0',
  `file_path` VARCHAR(150) COLLATE utf8_unicode_ci NOT NULL,
  `file_name` VARCHAR(75) COLLATE utf8_unicode_ci NOT NULL,
  `creation_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `category` BIGINT(20) NOT NULL DEFAULT '0',
  `tags` text COLLATE utf8_unicode_ci,
  `description` mediumtext COLLATE utf8_unicode_ci,
  `promo_in_front` CHAR(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `count` BIGINT(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
ALTER TABLE `psPhotosRating`
  ADD PRIMARY KEY (`id_photo_rating`);
 
ALTER TABLE `psPhotosRating`
  MODIFY `id_photo_rating` INT(11) NOT NULL AUTO_INCREMENT;
 
ALTER TABLE `psUploadedFiles2`
  MODIFY `id_uploaded_files` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;
 
[SQL] pobierz, plaintext 

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:

[SQL] pobierz, plaintext 
INSERT INTO `psPhotosRating` (`id_photo_rating`, `id_user`, `id_uploaded_files`, `rating`, `timestamp`) VALUES
(1, 36, 11, 4, '2019-02-06 09:09:08'),
(2, 112, 5, 5, '2019-02-06 09:54:34'),
(3, 16, 11, 1, '2019-02-04 11:20:24');
 
[SQL] pobierz, plaintext 

Próbowałem coś takiego:

[SQL] pobierz, plaintext 
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;
[SQL] pobierz, plaintext 

ale nie do końca działa to poprawnie

średnia to suma/ilość

[SQL] pobierz, plaintext 
SELECT p.*
FROM psuploadedfiles2 p 
LEFT JOIN psphotosrating r ON p.id_uploaded_files=r.id_uploaded_files
GROUP BY p.id_uploaded_files
ORDER BY sum(r.rating)/count(r.id_photo_rating) DESC
[SQL] pobierz, plaintext