13. marts 2004 - 01:38Der er
37 kommentarer og 1 løsning
SQL, SELECT (200 pts)
Hejsa
Jeg har en tabel, rating, den indeholder infornationer om nogle billeder man kan stemme på. Hvert billede har en owner, og en avgrate der er gennemsnittet af de stemmer det pågældende billede har fået. Jeg vil gerne producere en liste af owners sorteret efter hvor gode deres billeder er. Det har jeg gjort sådanher:
SELECT owner AS id, AVG(avgrate) FROM rating WHERE owner <> 0 AND views > 15 GROUP BY owner ORDER BY AVG(avgrate) DESC;
views er antallet af stemmer, og jeg har gjort således at et billede skal ha minst 15 stemmer. Grunden til at jeg siger WHERE owner <> 0 er fordi at der ikke er nogen garanti for at et billede har en owner, og så er owner = 0. Altså, her er en liste over alle owners sorteret efter hvor gode deres billeder er.
Nu vil jeg bare gerne ha den samme liste, men sorteringen skal kun fokusere på hver enkelt owners bedste 25 billeder.... Hvordan gør jeg det??
Skal den vise de 25 bedste billeder for en bestemt bruger? Så kan man bruge GROUP BY billedeID hvis der en kolonne som indeholder information omkring hvilket billedet det er:
ownerID = 25 SQL = "SELECT TOP 25 billedeID, AVG(avgrate) FROM rating WHERE owner = " & ownerID & " AND views > 15 GROUP BY billedeID ORDER BY AVG(avgrate) DESC;"
nej, den skal vise en liste over alle brugere, sorteret efter hvem der i snit har de bedste billeder, men det snit skal ikke beregnes på baggrund af alle brugerens billeder, men kun de bedste 25 af brugerens billeder.
well - jeg får 25 billeder sorteret efter avgrate.. det første billede har lidt over 6 i date og ejes af owner 11.. næste billede ejes oges af owner 11 og et længere nede af owner 26 osv osv..
Forestil dig at man tager owner 1, og regner gennemsnittet ud på hans bedste 25 billeder. Det gennemsnit er måske 4.5
Det gør man så med alle owners, og så sortere man owners således at den owner der skrives først er den hvis 25 bedste billeder udgør det bedste gennemsnit.
Hej Nej, jeg har forsøgt lidt af hvert men jeg kan ikke får den til at tage top 25 fra hver bruger.. Jeg har også søgt på google omkring det og der er stillet et par spørgsmål rundt omkring om det samme eller noget der ligner, men der er heller ikke kommet svar på hvordan man får TOP x fra hver bruger...
giv mig lige lidt mere tid jeg tror jeg er ved at havde den som en SQL sætning, indtid vidre har jeg dette, som tager top 25 fra hver gruppe (owner):
SQL = "SELECT * FROM rating yt1 " SQL = SQL & "WHERE [name] IN (SELECT TOP 25 [name] FROM rating yt2 where yt2.owner = yt1.owner order by yt2.avgrate desc, yt2.name)"
SQL = "SELECT avg(avgrate), owner FROM rating yt1 " SQL = SQL & "WHERE [name] IN (SELECT TOP 4 [name] FROM rating yt2 where yt2.owner = yt1.owner order by yt2.avgrate desc, yt2.name) group by owner order by avg(avgrate) desc"
SQL = SQL & "WHERE [name] IN << havde bare ændret name til owner.. men har ikke overblik til at se hvad jeg skal ændre/rette for at få det til at virke..
Noget andet er, hvis jeg prøver i Access at køre denne sql sætning du skrev du mente ville tage top 25 fra hver gruppe (owner), så går Access ned..
SQL = "SELECT * FROM rating yt1 " SQL = SQL & "WHERE [name] IN (SELECT TOP 25 [name] FROM rating yt2 where yt2.owner = yt1.owner order by yt2.avgrate desc, yt2.name)"
Den virker fint hos mig, på en access dog har jeg også kun nogle få poster..
Alternativt skal du prøve denne, men jeg tror ikke det bliver bedre af det:
SQL = "SELECT owner, AVG(avgrate) AS Best3OfLast4PurchaseAvg, count(avgrate) FROM " SQL = SQL & "(SELECT owner, avgrate FROM rating C WHERE [name] IN " SQL = SQL & "(SELECT TOP 25 [name] FROM (SELECT owner, [name], avgrate FROM rating A WHERE [name] IN " SQL = SQL & "(SELECT [name] FROM rating WHERE owner=A.owner) " SQL = SQL & "ORDER BY avgrate DESC, [name] desc) AS B " SQL = SQL & "WHERE B.owner=C.owner ORDER BY avgrate DESC, [name])) AS X " SQL = SQL & "GROUP BY owner ORDER BY avg(avgrate) DESC"
Den sidste du skrev kan Access heller ikke afvikle. Ved ikke om det er fordi der er en fejl i SQL-sætningen, eller om det bar er fordi den er så tung at det tager 10 mins at afvikle den.
I mellemtiden har jeg programmeret mig ud af det med et "tilfredsstillende" resultat. Hvis du stadig gerne vil prøve at finde en løsning på problemet, så kan jeg godt sende dig databasen.
Kan godt acceptere "Ja så må man programmere det" som svar hvis det er. Tak for hjælpen i al flad. :)
Der er ikke fejl i SQL sætningen den virker fint hos mig.
Det er en meget tung SQL sætning fordi man er nød til at lave en JOIN yt2.owner = yt1.owner på owner kolonne for at kunne få top 25 fra hver bruger. Så udervejs er man oppe på Owner1*Owner1+Owner2*Owner2+Owner3*Owner3+Owner4*Owner4+... records, så når du har 5000 records vel den nok tage en del tid at udføre.
eagleeye: tager dig på ordet, og at det virker. med 5000 records kan jeg godt se at den fremgangsmåde du har lavet bliver meget tung, og sikkert ikke kan køres.. men tager dig på ordet med at det virker på færre records. tak for al den tid du har brugt på det her.
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
