[mysql] orderen op "eerlijke" wijze

[Verwijderd]

stel: ik heb een table met 100 advertenties,

titel	advertentie	datum	bedrijf_id
audi a4	in zeer goede staat, bla bla	10-10-2009	1
volkswagen gold	van oude oma geweest	11-10-2009	2

Alle advertenties zijn maar van 5 verschillende bedrijven, dan wil ik in de zoekresultaten de lijst op deze manier tonen:

Advertentie van bedrijf 1
Advertentie van bedrijf 2
Advertentie van bedrijf 3
Advertentie van bedrijf 4
Advertentie van bedrijf 5
Advertentie van bedrijf 1
Advertentie van bedrijf 2
Advertentie van bedrijf 3
Advertentie van bedrijf 4
Advertentie van bedrijf 5
Advertentie van bedrijf 1
Advertentie van bedrijf 2
Advertentie van bedrijf 3
Advertentie van bedrijf 4
Advertentie van bedrijf 5

zodat elk bedrijf dus evenveel aandacht krijgt. (ORDERen op relevantie of datum is niet nodig)

hoe krijg ik dat ooit voor elkaar met een query? tis namelijk niet een vaste waarde waarop je kan ORDER'en.

[Voutloos]

En wat gebeurt er als niet alle bedrijven even veel advertenties hebben?

[Verwijderd]

bijv:

bedrijf 1 heeft 6 advertenties
bedrijf 2 heeft 4 advertenties
bedrijf 3 heeft 2 advertenties
bedrijf 4 heeft 1 advertenties
bedrijf 5 heeft 1 advertenties


resultaat zou dan zoiets als dit moeten zijn:

adverentie bedrijf 1
adverentie bedrijf 2
adverentie bedrijf 3
adverentie bedrijf 4
adverentie bedrijf 5
adverentie bedrijf 1
adverentie bedrijf 2
adverentie bedrijf 3
adverentie bedrijf 1
adverentie bedrijf 2
adverentie bedrijf 1
adverentie bedrijf 2
adverentie bedrijf 1
adverentie bedrijf 1

de volgorde van de advertenties maakt niet zoveel uit, het gaat er meer om dat de resultaten "geshuffled" zijn (niet RAND() want dan gaat het weer niet goed als ik naar de volgende pagina met zoekresultaten wil gaan)

Het komt namelijk voor dat een bedrijf in 1 x 50 nieuwe advertenties plaatst, en ik wil dus niet dat in de zoekresultaten eerst 50 advertenties van bedrijf 1 te zien zijn, daarna 40 advertenties van bedrijf 2 enz enz... .

[truegrit]

Zeer interessant vraagstuk. Ik ben geen SQL guru, maar wat in me opkomt is om elke advertentie een nummer te geven op basis van hoeveel advertenties hij reeds heeft geplaatst. Vervolgens kan je daar op sorteren.

Voorbeeld:
Bedrijf 1 heeft 3 advertenties:
advertentie 1.1
advertentie 1.2
advertentie 1.3

Bedrijf 2 heeft 2 advertenties:
advertentie 2.1
advertentie 2.2

Vervolgens doe je een query waarbij je kijkt op de hoeveelheid advertenties die een bedrijf heeft geplaatst voor de huidige, waarbij je dat veld gebruikt om te sorteren

Bedrijf 1:
advertentie 1.1: 2 nieuwere advertenties
advertentie 1.2: 1 nieuwere advertentie
advertentie 1.3: 0 nieuwere advertenties

Bedrijf 2:
advertentie 2.1: 1 nieuwere advertentie
advertentie 2.2: 0 nieuwere advertenties

als je dan sorteert op dat veld, ORDER BY <whatever> ASC krijg je volgens mij wat je wilt.

Edit: gare uitwerking van wat ik hierboven probeerde te vertellen

SQL:

SELECT     title
FROM         Advertentie AS a
ORDER BY 
      (SELECT     COUNT(id) AS Expr1
        FROM          Advertentie AS b
        WHERE      (geplaatst < a.geplaatst) AND (a.bedrijf = bedrijf)), bedrijf

SQL:

CREATE TABLE [dbo].[Advertentie](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [title] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
    [geplaatst] [datetime] NOT NULL,
    [bedrijf] [int] NULL
);

Originele data:

id          title                                              geplaatst               bedrijf
----------- -------------------------------------------------- ----------------------- -----------
1           Bedrijf A: katjes                                  2009-01-01 00:00:00.000 1
2           Bedrijf A: hondjes                                 2009-01-02 00:00:00.000 1
3           Bedrijf A: muisjes                                 2009-01-03 00:00:00.000 1
4           Bedrijf B: bmw                                     2009-01-02 00:00:00.000 2
5           Bedrijf B: audi                                    2009-01-08 00:00:00.000 2
6           computer                                           2009-01-09 00:00:01.000 3
7           laptop                                             2009-01-09 00:00:02.000 3
8           toetsenbord                                        2009-01-09 00:00:03.000 3
9           processor                                          2009-01-09 00:00:04.000 3
10          gpu                                                2009-01-09 00:00:05.000 3
11          telefoon                                           2009-01-09 00:00:06.000 3

resultaat na query

id          title
----------- --------------------------------------------------
1           Bedrijf A: katjes
4           Bedrijf B: bmw
6           computer
2           Bedrijf A: hondjes
5           Bedrijf B: audi
7           laptop
3           Bedrijf A: muisjes
8           toetsenbord
9           processor
10          gpu
11          telefoon

Hopelijk heb je hier wat aan

[ Voor 56% gewijzigd door truegrit op 06-11-2009 11:59 ]

[Verwijderd]

Pin me er niet aan vast, ik heb ff geen MySQL bij de hand om te testen.

Ik dacht dat je ook een tweede veld in een primary key auto_increment kan maken, die dus aan de hand van het eerste veld incrementeerd:

SQL:

create table bla
(
  bedrijf int not null,
  adv_nr int not null auto_increment,
  primary key (bedrijf, adv_nr)
);

Welke dan per bedrijf weer op 1 begint, dan kan je vervolgens met een query ala onderstaand de resultaten ophalen:

SQL:

select *
from bla
order by adv_nr asc,
bedrijf asc;

[Verwijderd]

de oplossing van truegrit is wel heel intressant, daar ga ik even mee expirimenteren.

de oplossing van dominique is niet te gebruiken, omdat je volgens mij dan geen rekening houdt met verwijderde advertenties, en wanneer je bijv advertenties in een bepaalde categorie bekijkt. (dat had ik er niet bij vermeld, maar een advertentie staat dus ook in een categorie, en per categorie moeten die resultaten ook zo ge-ORDER-ed worden)

ik ga er even mee spelen,

[ Voor 31% gewijzigd door Verwijderd op 06-11-2009 14:26 ]

[truegrit]

Ik denk niet dat die oplossing problemen oplevert met verwijderde advertenties of sorteren in categorieen, alleen wel met het feit dat het alleen werkt als je de oudste advertenties bovenaan wilt hebben.

Die oplossing die ik gaf wordt vaak gebruikt (al heb ik het wel zelf bedacht dus ben toch wel een beetje trots ), maar ik weet niet of die een goede performance heeft met veel advertenties. Daar zal die van dominique wel efficienter in zijn, als je die goed weet toe te passen.

[TJHeuvel]

Je kan het oplossen door alle advertenties per bedrijf in een andere array te zetten, en dan een voor een een advertentie laten zien.

Bijvoorbeeld (in PHP):

PHP:

//Vul deze met advertenties.
$bedrijf1 = array();
$bedrijf2 = array();
$bedrijf3 = array();
$bedrijf4 = array();
$bedrijf5 = array();

$bedrijfIndex = 1;

//Laat 100 advertenties zien
for($i = 0; $i < 100; $i++)
{
 if($bedrijfIndex == 1)
 {
   echo next($bedrijf1);
 } else if($bedrijfIndex == 2)
 {
   echo next($bedrijf2);
 } else if($bedrijfIndex == 3)
 {
   echo next($bedrijf3);
 } else if($bedrijfIndex == 4)
 {
   echo next($bedrijf4);
 } else if($bedrijfIndex == 5)
 {
   echo next$bedrijf5);
 }

 $bedrijfIndex++;
 if($bedrijfIndex > 5) //Zorg dat $bedrijfIndex onder de 6 blijft.
  $bedrijfIndex = 0;
}

(Natuurlijk moet je met Multidimensionale Arrays gaan werken om alle bedrijven op te slaan, ipv bedrijf1 t/m 5.)

[ Voor 9% gewijzigd door TJHeuvel op 06-11-2009 15:14 . Reden: 5 bedrijven toegevoegd. ]

[pedorus]

truegrit schreef op vrijdag 06 november 2009 @ 11:42:

SQL:

SELECT     title
FROM         Advertentie AS a
ORDER BY 
      (SELECT     COUNT(id) AS Expr1
        FROM          Advertentie AS b
        WHERE      (geplaatst < a.geplaatst) AND (a.bedrijf = bedrijf)), bedrijf

Iets herschrijven en rekening houden met gelijke inserts:

SQL:

select a.title
from Advertentie AS a left join Advertentie AS b on a.bedrijf=b.bedrijf and 
    (b.geplaatst<a.geplaatst or (b.geplaatst=a.geplaatst and b.id<a.id))
group by a.id
order by count(b.id), a.bedrijf

[Verwijderd]

CyCloneNL schreef op vrijdag 06 november 2009 @ 15:10:
Je kan het oplossen door alle advertenties per bedrijf in een andere array te zetten, en dan een voor een een advertentie laten zien.

Bijvoorbeeld (in PHP):

PHP:

//Vul deze met advertenties.
$bedrijf1 = array();
$bedrijf2 = array();
$bedrijf3 = array();
$bedrijf4 = array();
$bedrijf5 = array();

$bedrijfIndex = 1;

//Laat 100 advertenties zien
for($i = 0; $i < 100; $i++)
{
 if($bedrijfIndex == 1)
 {
   echo next($bedrijf1);
 } else if($bedrijfIndex == 2)
 {
   echo next($bedrijf2);
 } else if($bedrijfIndex == 3)
 {
   echo next($bedrijf3);
 } else if($bedrijfIndex == 4)
 {
   echo next($bedrijf4);
 } else if($bedrijfIndex == 5)
 {
   echo next$bedrijf5);
 }

 $bedrijfIndex++;
 if($bedrijfIndex > 5) //Zorg dat $bedrijfIndex onder de 6 blijft.
  $bedrijfIndex = 0;
}

(Natuurlijk moet je met Multidimensionale Arrays gaan werken om alle bedrijven op te slaan, ipv bedrijf1 t/m 5.)

ja, maar hoe ga je dit doen als je dus 10 resultaten per pagina wil laten zien, (zonder dus elke pagina 100 rows op te halen)

pedorus schreef op vrijdag 06 november 2009 @ 16:06:
[...]

Iets herschrijven en rekening houden met gelijke inserts:

SQL:

select a.title
from Advertentie AS a left join Advertentie AS b on a.bedrijf=b.bedrijf and 
    (b.geplaatst<a.geplaatst or (b.geplaatst=a.geplaatst and b.id<a.id))
group by a.id
order by count(b.id), a.bedrijf

hmm, het probleem met de bovenstaande query is dat ie heeeeeeeel traag is.. in phpmyadmin kan ik hem niet eens uitvoeren omdat ie te lang duurt.

[pedorus]

Verwijderd schreef op vrijdag 06 november 2009 @ 20:22:
hmm, het probleem met de bovenstaande query is dat ie heeeeeeeel traag is.. in phpmyadmin kan ik hem niet eens uitvoeren omdat ie te lang duurt.

Heb je de herschreven of de originele versie gebruikt? De originele versie maakt denk ik een dependent subquery, en dat is vrij traag. De herschreven versie zou met wat goede indexen een redelijke snelheid moeten halen, maar echt snel zal het niet worden denk ik. Doe anders een een explain.

[Verwijderd]

Ik denk dat je een tabel moet aanleggen met per bedrijf per categorie het aantal getoonde ads. Die kan je dan van laag naar hoog per cat sorteren en de ad met het laagste aantal view laten zien. zijn het er meerdere maakt niet uit gewoon een van de laagste laten zien. Daarna natuurlijk het aantal views met 1 ophogen.

[Verwijderd]

pedorus schreef op vrijdag 06 november 2009 @ 20:49:
[...]

Heb je de herschreven of de originele versie gebruikt? De originele versie maakt denk ik een dependent subquery, en dat is vrij traag. De herschreven versie zou met wat goede indexen een redelijke snelheid moeten halen, maar echt snel zal het niet worden denk ik. Doe anders een een explain.

de query geeft exact de uitkomst die ik zoek, alleen duurt ie echt te lang.. er staan nu 2000 rows in de Advertentie table, en hij doet nu al gemiddeld 3 seconden over de query.

snelste is wanneer ik een index plaats op 'geplaatst', dan doet de query er 2 seconden over bij 2000 rows.


dit is de uitkomst van de EXPLAIN

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	a	ALL	NULL	NULL	NULL	NULL	2000	Using where; Using temporary; Using filesort
1	SIMPLE	b	ALL	PRIMARY,geplaatst	NULL	NULL	NULL	2000

[pedorus]

Er zou in principe toch minimaal een key gebruikt kunnen worden:

SQL:

create index bedrijfplaatsing on Advertentie (bedrijf, geplaatst);            

of zelfs:

SQL:

create index bedrijfplaatsing_met_id on Advertentie (bedrijf, geplaatst,id);    

Maar waarschijnlijk is dit toch meer een geval van caching/tussenopslag van resultaten. Evengoed is het wel mooi als het originele resultaat zonder al te veel werk kan worden gemaakt.

[weia]

Ik ben absolute noob op programeren/scripting/database gebied, maar ik snap niet waarom je dit met een query wilt oplossen. In je eerste post zeg je:

Verwijderd schreef op vrijdag 06 november 2009 @ 10:59:
zodat elk bedrijf dus evenveel aandacht krijgt. (ORDERen op relevantie of datum is niet nodig).

Als ik het goed begrijp is het dus een statische lijst? Zo ja, dan kun je toch alleen bij het toevoegen van een nieuwe advertentie de volgorde laten berekenen?

Ik zal het wel fout hebben, maar ik post het toch maar even voor de zekerheid. Op zijn minst kan ik hier iets van leren.

[pedorus]

Je hebt gelijk dat je de volgorde waarschijnlijk beter van te voren kunt berekenen (ondanks de dataredundantie). Een suggestie/testje om alles te herberekenen:

SQL:

alter table Advertentie add column (volgorde int, nummer_van_bedrijf int);

set @nummer=0,@vorigBedrijf=NULL;
update Advertentie set nummer_van_bedrijf=
        @nummer:=if(bedrijf=@vorigBedrijf,@nummer+1,0*@vorigBedrijf:=bedrijf)
    order by bedrijf, geplaatst, id;
set @nummer=-1;
update Advertentie set volgorde=@nummer:=@nummer+1
    order by nummer_van_bedrijf,bedrijf;

[Verwijderd]

pedorus schreef op maandag 09 november 2009 @ 21:14:
Je hebt gelijk dat je de volgorde waarschijnlijk beter van te voren kunt berekenen (ondanks de dataredundantie). Een suggestie/testje om alles te herberekenen:

SQL:

alter table Advertentie add column (volgorde int, nummer_van_bedrijf int);

set @nummer=0,@vorigBedrijf=NULL;
update Advertentie set nummer_van_bedrijf=
        @nummer:=if(bedrijf=@vorigBedrijf,@nummer+1,0*@vorigBedrijf:=bedrijf)
    order by bedrijf, geplaatst, id;
set @nummer=-1;
update Advertentie set volgorde=@nummer:=@nummer+1
    order by nummer_van_bedrijf,bedrijf;

ja alleen het moet dus ook mogelijk zijn in de advertenties te zoeken op een trefwoord, en die zoekresultaten zouden dan dus ook "eerlijk" ge-ordered moeten worden.


met de deze index

code:

create index bedrijfplaatsing_met_id on Advertentie (bedrijf, geplaatst,id);

is de query wel een stuk sneller, maar doet er nog steeds 1.5 seconden over, bij 3000 rows.

[Hipska]

Random resultaten en met meerdere pagina's is heel goed te doen met rand(n)

http://dev.mysql.com/doc/...ctions.html#function_rand

als je met php bijvoorbeeld voor n een random getal berekend en dat bewaart in je sessie, en op de volgende pagina precies dezelfde n gebruikt, dan kun je random rijen pagineren..

[weia]

Oplossing gevonden lijkt mij. (Hipska)

Sowieso lijkt mij een willekeurig systeem "eerlijker" dan de eerder genoemde volgorde. Anders heeft een adverteerder met veel advertenties zijn advertenties vooral onderaan staan. Waardoor adverteerders misschien minder posten. Met een random lijst wordt de aandacht verdeeld naar het aantal advertenties dat er geplaatst is. Oftewel meer geld is meer aandacht.

Edit: ik neem aan dat rand ook erg snel is?

[ Voor 5% gewijzigd door weia op 11-11-2009 14:01 ]

[Verwijderd]

weia schreef op woensdag 11 november 2009 @ 14:00:
Oplossing gevonden lijkt mij. (Hipska)

Sowieso lijkt mij een willekeurig systeem "eerlijker" dan de eerder genoemde volgorde. Anders heeft een adverteerder met veel advertenties zijn advertenties vooral onderaan staan. Waardoor adverteerders misschien minder posten. Met een random lijst wordt de aandacht verdeeld naar het aantal advertenties dat er geplaatst is. Oftewel meer geld is meer aandacht.

Edit: ik neem aan dat rand ook erg snel is?

dat klopt niet helemaal wat je zegt.

Kijk, er zijn bedrijven die elke dag 10 nieuwe advertenties plaatsen, maar ik wil dus dat adverteerders die bijv maar 1 advertentie per week plaatsen niet worden "ondergesneeuwd" door de bedrijven die elke dag 10 advertenties plaatsen.

dus bijv,
bedrijf_id =1 heeft totaal 5 advertenties.
bedrijf_id = 2 heeft totaal 100 advertenties

dan ziet de resultaat lijst er zo uit:

bedrijf_id
1
2
1
2
1
2
1
2
1
2
2
2
2
2
2
2
2
2
2
2
... enz