Somma i risultati di alcune query e poi trova i primi 5 in SQL

Ho 3 domande:

table: pageview SELECT event_id, count(*) AS pageviews FROM pageview GROUP BY event_id ORDER BY pageviews DESC, rand() LIMIT 1000 table: upvote SELECT event_id, count(*) AS upvotes FROM upvote GROUP BY event_id ORDER BY upvotes DESC, rand() LIMIT 1000 table: attending SELECT event_id, count(*) AS attendants FROM attending GROUP BY event_id ORDER BY attendants DESC, rand() LIMIT 1000 

Mi piacerebbe combinare gli event_id di tutte e 3 le query ordinate per importo e quindi scegliere il top 5. Come faccio?

EDIT: QUI È QUELLO CHE HO FATTO PER FARE ACCADERE:

 SELECT event_id, sum(amount) AS total FROM ( (SELECT event_id, count(*) AS amount FROM pageview GROUP BY event_id ORDER BY amount DESC, rand() LIMIT 1000) UNION ALL (SELECT event_id, count(*) as amount FROM upvote GROUP BY event_id ORDER BY amount DESC, rand() LIMIT 1000) UNION ALL (SELECT event_id, count(*) as amount FROM attending GROUP BY event_id ORDER BY amount DESC, rand() LIMIT 1000) ) x GROUP BY 1 ORDER BY sum(amount) DESC LIMIT 5; 

La domanda lascia spazio all’interpretazione. Per UNION le righe risultanti di tutte e tre le query e quindi selezionare le 5 righe con gli “importi” più alti:

 (SELECT event_id, count(*) AS amount FROM pageview GROUP BY event_id ORDER BY pageviews DESC, rand() LIMIT 1000) UNION ALL (SELECT event_id, count(*) FROM upvote GROUP BY event_id ORDER BY upvotes DESC, rand() LIMIT 1000) UNION ALL (SELECT event_id, count(*) FROM attending GROUP BY event_id ORDER BY attendants DESC, rand() LIMIT 1000) ORDER BY 2 DESC LIMIT 5; 

Il manuale:

Per applicare ORDER BY o LIMIT a un singolo SELECT , posizionare la clausola all’interno delle parentesi che racchiudono SELECT .

UNION ALL , quindi i duplicati non vengono rimossi.


Se vuoi aggiungere i conteggi per ogni event_id , questa query dovrebbe farlo:

 SELECT event_id, sum(amount) AS total FROM ( (SELECT event_id, count(*) AS amount FROM pageview GROUP BY event_id ORDER BY pageviews DESC, rand() LIMIT 1000) UNION ALL (SELECT event_id, count(*) FROM upvote GROUP BY event_id ORDER BY upvotes DESC, rand() LIMIT 1000) UNION ALL (SELECT event_id, count(*) FROM attending GROUP BY event_id ORDER BY attendants DESC, rand() LIMIT 1000) ) x GROUP BY 1 ORDER BY sum(amount) DESC LIMIT 5; 

La parte difficile qui è che non tutti event_id sarà presente in tutte e tre le query di base. Quindi devi fare attenzione che un JOIN non perda le righe completamente e le aggiunte non NULL .

Usa UNION ALL , non UNION . Non vuoi rimuovere righe identiche, vuoi aggiungerle.

La x è una scorciatoia per AS x – un alias di tabella. È necessario che una sottoquery abbia un nome. Può essere qualsiasi altro nome qui.

La funzione SOL FULL OUTER JOIN non è implementata in MySQL (l’ultima volta che ho guardato), quindi devi fare i conti con UNION. FULL OUTER JOIN avrebbe unito tutte e tre le query di base senza perdere righe.

Rispondi alla domanda successiva

 SELECT event_id, sum(amount) AS total FROM ( (SELECT event_id, count(*) / 100 AS amount FROM pageview ... ) UNION ALL (SELECT event_id, count(*) * 5 FROM upvote ... ) UNION ALL (SELECT event_id, count(*) * 10 FROM attending ... ) ) x GROUP BY 1 ORDER BY sum(amount) DESC LIMIT 5; 

Oppure, se si desidera utilizzare i conteggi di base in diversi modi:

 SELECT event_id ,sum(CASE source WHEN 'p' THEN amount / 100 WHEN 'u' THEN amount * 5 WHEN 'a' THEN amount * 10 ELSE 0 END) AS total FROM ( (SELECT event_id, 'p'::text AS source, count(*) AS amount FROM pageview ... ) UNION ALL (SELECT event_id, 'u'::text, count(*) FROM upvote ... ) UNION ALL (SELECT event_id, 'a'::text, count(*) FROM attending ... ) ) x GROUP BY 1 ORDER BY 2 DESC LIMIT 5;