Hoe maak ik hier één query van?
Voor de berekening van een KPI (Key Performance Indicator), de zgn. conversiepercentage moet ik twee queries uitvoeren in twee verschillende tabellen die niet via een JOIN zijn te koppelen.
De eerste query berekent het aantal hits op de webshop en de tweede het aantal uiteindelijke orders die deze bezoeken tot resultaat hebben.
De uiteindelijke score moet in procenten worden weergegeven.
De code voor het aantal hits op de webshop is:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT
COUNT(id) AS aantalhits
FROM
sys__logfile
WHERE
websitenaam = 'webshop' AND
MONTH(datum) = 7 AND YEAR(datum) = 2013
COUNT(id) AS aantalhits
FROM
sys__logfile
WHERE
websitenaam = 'webshop' AND
MONTH(datum) = 7 AND YEAR(datum) = 2013
De code voor het berekenen van het aantal uiteindelijke orders is:
Code (php)
1
2
3
4
5
6
2
3
4
5
6
SELECT
COUNT(DISTINCT(ordernummer)) AS aantalorders
FROM
shop__bestellingen
WHERE
MONTH(besteldatum) = 7 AND YEAR(besteldatum) = 2013
COUNT(DISTINCT(ordernummer)) AS aantalorders
FROM
shop__bestellingen
WHERE
MONTH(besteldatum) = 7 AND YEAR(besteldatum) = 2013
De berekening naar percentage is dan:
{code]
Code (php)
1
2
3
2
3
<?php
(aantalorders/aantalhits) * 100
?>
(aantalorders/aantalhits) * 100
?>
Hoe maak ik hier nu één query van?
George
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
COUNT(sys__logfile.id) AS aantalhits,
COUNT(DISTINCT(shop__bestellingen.ordernummer)) AS aantalorders
FROM
sys__logfile AS sys,
shop__bestellingen
OUTER JOIN
shop__bestellingen AS shop
ON
sys.datum = shop
WHERE
shop__bestellingen.websitenaam = 'webshop' AND
MONTH(sys.besteldatum) = 7 AND
YEAR(sys.besteldatum) = 2013
COUNT(sys__logfile.id) AS aantalhits,
COUNT(DISTINCT(shop__bestellingen.ordernummer)) AS aantalorders
FROM
sys__logfile AS sys,
shop__bestellingen
OUTER JOIN
shop__bestellingen AS shop
ON
sys.datum = shop
WHERE
shop__bestellingen.websitenaam = 'webshop' AND
MONTH(sys.besteldatum) = 7 AND
YEAR(sys.besteldatum) = 2013
Gewijzigd op 01/08/2013 17:45:22 door Jeroen VD
Het aantal hits dat deze berekening oplevert is 59122 terwijl de logfile slechts 21000 records bevat.
Hier gaat dus iets niet goed.....
Toevoeging op 01/08/2013 17:36:39:
ik heb geen idee haha, misschien een van de database experts hier? ik zie nu eventjes geen andere optie dan gewoon 2 queries te gebruiken.... joins weet ik niet zoveel van
Een mogelijke oplossing is om de counts ieder in een subquery uit te voeren en die simpel aan elkaar te joinen:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT a.aantalhits, b.aantalorders
FROM (
SELECT
COUNT(id) AS aantalhits
FROM
sys__logfile
WHERE
websitenaam = 'webshop' AND
MONTH(datum) = 7 AND YEAR(datum) = 2013
) a
INNER JOIN (
SELECT
COUNT(DISTINCT(ordernummer)) AS aantalorders
FROM
shop__bestellingen
WHERE
MONTH(besteldatum) = 7 AND YEAR(besteldatum) = 2013
) b
FROM (
SELECT
COUNT(id) AS aantalhits
FROM
sys__logfile
WHERE
websitenaam = 'webshop' AND
MONTH(datum) = 7 AND YEAR(datum) = 2013
) a
INNER JOIN (
SELECT
COUNT(DISTINCT(ordernummer)) AS aantalorders
FROM
shop__bestellingen
WHERE
MONTH(besteldatum) = 7 AND YEAR(besteldatum) = 2013
) b
De join op deze manier zal in principe een cartesisch product opleveren, maar dat is in dit geval geen punt omdat beide subqueries een enkele rij opleveren.
Of het de snelste manier is is een heel ander verhaal overigens...
Gewijzigd op 01/08/2013 17:43:52 door Erwin H
en wat is volgens jou de beste oplossing dan? ook een leerpuntje voor mij dit :)
Het idee van Erwin werkt goed. De cijfers komen overeen met die van als ik e.e.a. apart run.
Verder heb ik de query aangepast om direct ook het eprcentage te verkrijgen
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT a.aantalhits, b.aantalorders,(b.aantalorders/a.aantalhits)*100 as percentage
FROM (
SELECT
COUNT(id) AS aantalhits
FROM
sys__logfile
WHERE
websitenaam = 'webshop' AND
MONTH(datum) = 7 AND YEAR(datum) = 2013
) a
INNER JOIN (
SELECT
COUNT(DISTINCT(ordernummer)) AS aantalorders
FROM
shop__bestellingen
WHERE
MONTH(besteldatum) = 7 AND YEAR(besteldatum) = 2013
) b
FROM (
SELECT
COUNT(id) AS aantalhits
FROM
sys__logfile
WHERE
websitenaam = 'webshop' AND
MONTH(datum) = 7 AND YEAR(datum) = 2013
) a
INNER JOIN (
SELECT
COUNT(DISTINCT(ordernummer)) AS aantalorders
FROM
shop__bestellingen
WHERE
MONTH(besteldatum) = 7 AND YEAR(besteldatum) = 2013
) b
Bedankt voor jullie support.
Topic gesloten
George
Toevoeging op 01/08/2013 18:06:54:
Gewijzigd op 01/08/2013 18:07:47 door George van Baasbank
Jeroen VD op 01/08/2013 17:47:45:
wat doe je met die a en b?
Bij een subquery moet je altijd een tabel alias geven, omdat mysql anders geen naam heeft voor de virtuele tabel.
Jeroen VD op 01/08/2013 17:47:45:
en wat is volgens jou de beste oplossing dan? ook een leerpuntje voor mij dit :)
In dit geval zou ik het gewoon in twee queries doen denk ik, of via een union in 1 query. Als je namelijk op deze manier subqueries gebruikt dan kunnen indices niet meer optimaal gebruikt worden, wat je een tragere query oplevert. In dit geval is het misschien iets minder het geval, omdat er al gebruikt gemaakt wordt van YEAR() en MONTH() wat ook al betekent dat de index op zo'n kolom niet gebruikt kan worden.
Erwin H op 01/08/2013 18:45:19:
Bij een subquery moet je altijd een tabel alias geven, omdat mysql anders geen naam heeft voor de virtuele tabel.
ik heb deze constructie nog nooit zo gezien, vandaar de vraag.... met die subquery haal je toch een resultaten(set) op, en niet een tabel? dat is voor mij het vage, en het weglaten van AS
Erwin H op 01/08/2013 18:45:19:
In dit geval zou ik het gewoon in twee queries doen denk ik, of via een union in 1 query. Als je namelijk op deze manier subqueries gebruikt dan kunnen indices niet meer optimaal gebruikt worden, wat je een tragere query oplevert. In dit geval is het misschien iets minder het geval, omdat er al gebruikt gemaakt wordt van YEAR() en MONTH() wat ook al betekent dat de index op zo'n kolom niet gebruikt kan worden.
ik ga me toch weer eens verdiepen in het hele databasegebeuren, ik kan nog veel leren zo te zien :)
Gewijzigd op 01/08/2013 19:55:06 door Jeroen VD
Code (php)
1
2
3
2
3
SELECT username
FROM users
WHERE user_id IN (SELECT user_id FROM bans)
FROM users
WHERE user_id IN (SELECT user_id FROM bans)
In dit geval kan het natuurlijk ook wel anders, maar zo gebruik je de uitkomsten echt alleen als data.
Doe je dit echter:
Code (php)
1
2
3
4
5
2
3
4
5
SELECT user_id
FROM (
SELECT user_id
FROM bans
)
FROM (
SELECT user_id
FROM bans
)
Dan gebruik je de uitkomsten als een virtuele tabel. Het staat namelijk in de FROM clause. Dit zal nu echter een foutmelding opleveren, omdat er geen alias is gegeven en MySQL dus geen naam heeft voor de virtuele tabel.
De alias kan je met en zonder AS geven, dat keyword is noet verplicht. Ik merk dat ik het wel altijd gebruik als ik kolommen een alias geef, maar niet als ik het doe bij tabellen.
Jeroen VD op 01/08/2013 19:54:47:
ik kan nog veel leren zo te zien :)
Ik ook hoor. De helft van dat verhaal over het gebruik van een index papagaai ik ook van Ger :-)
@Erwin,
In jouw query is het een ander verhaal, maar de subquery zelf gebruikt de indexen wel, alleen het resultaat (de derived table) heeft geen indexen meer.
Overigens zou ik dit ook gewoon met 2 query's doen.