[SQL] Between

Door Niels van K op 14-03-2013 14:32 gewijzigd op 14-03-2013 14:38

1.639 views

Goedemiddag,

Ik geraak er even niet meer uit (wellicht te lang bezig geweest :-) ). Ik heb de volgende query:

SELECT DISTINCT c.cus_id, c.firstname, c.lastname, c.credit, c.address, c.postal, c.city FROM customer_list AS c, transactions AS t WHERE t.transdate NOT BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 18 MONTH) AND DATE(NOW()) AND t.to_customer != '1' AND t.from_customer = c.cus_id ORDER BY c.lastname ASC

De bedoeling is om de klantgegevens uit de database te halen die geen transacties hebben gehad in de periode van de 18 afgelopen maanden. Echter krijg ik ook de resultaten terug van klanten die wel transacties hebben gehad in de afgelopen maanden en transacties hebben gehad voor de 18 maanden.

Aangezien mijn SQL kennis niet al te groot is, zou ik het zo in PHP kunnen omschrijven:

<?php
$cus_query = mysql_query("SELECT cus_id, firstname, lastname, credit, address, postal, city FROM customer_list ORDER BY lastname ASC");
while ($cus_result = mysql_fetch_assoc($cus_query))
{
$trans_query = mysql_query("SELECT FROM transactions WHERE from_customer = '" .
$cus_result['cus_id'] .
"' AND transdate BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 18 MONTH) AND DATE(NOW()) AND to_customer != '1'");
if (mysql_num_rows($trans_query) == 0)
{
echo '<tr>
<td>' . $cus_result['cus_id'] . '</td>
<td>' . $cus_result['firstname'] . '</td>
<td>' . $cus_result['lastname'] . '</td>
<td>' . $cus_result['address'] . '</td>
<td>' . $cus_result['postal'] . '</td>
<td>' . $cus_result['city'] . '</td>
<td>' . $cus_result['credit'] . '</td>
</tr>';
}
}
?>

Dit werkt ook, maar natuurlijk niet netjes en de performance op deze manier is ook niet al te best (> 100.000 transacties).

Kan iemand mij een schop in de goede richting geven om dit enkel met één SQL query af te kunnen?

B.V.D.

Niels.

Ward van der Put

14-03-2013 15:14

Je kunt de relatie tussen de twee tabellen omkeren. Uit de transacties selecteer je de klant-ID's van alle klanten waarvan de laatste transactie ouder dan 18 maanden is. Vervolgens koppel je daaraan met een JOIN op de klant-ID de overige klantgegevens.

Ger van Steenderen

15-03-2013 13:07

Dat kan maar dan vallen de klanten die geen transacties hebben er buiten.

Niels van K

07-04-2013 15:34

Excuses voor mijn erg late reactie, er was iets tussen gekomen.

@ Ward: Goed idee, maar dan krijg je het probleem wat Ger beschrijft.

Ik heb vanmiddag nog even gepuzzeld, maar ik krijg het niet voor elkaar met alleen SQL queries. Met PHP wil het wel lukken, alleen de performance is 0.

Kan iemand mij een duwtje in de goede richting geven?

Ger van Steenderen

07-04-2013 15:51 gewijzigd op 07-04-2013 15:52


SELECT c.cus_id, c.lastname
FROM customers c
LEFT JOIN transactions t
	ON t.cus_id = c.cus_id
	AND t.transdate BETWEEN CURRENT_DATE AND CURRENT_DATE - INTERVAL 18 MONTH
WHERE t.cus_id IS NULL

Niels van K

07-04-2013 16:01


SELECT c.cus_id, c.lastname 
FROM customer_list AS c
LEFT JOIN transactions AS t
    ON t.from_customer = c.cus_id
    AND t.transdate BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 18 MONTH) AND DATE(NOW())
WHERE t.from_customer IS NULL

Zal hem dan moeten worden. Echter loopt de DB hierop vast. Waarom gebruik je de IS NULL functie in de WHERE clause?

Ger van Steenderen

07-04-2013 16:14 gewijzigd op 07-04-2013 16:15

Door de LEFT JOIN blijven de kolommen uit de rechter tabel die niet aan de join voorwaarden voldoen leeg.
Heeft een klant altijd minimaal 1 transactie kun je de query simpeler maken door een INNER JOIN en een GROUP BY :


SELECT c.cus_id, c.last_name, MAX(t.transdate) lasttrans
FROM
	customer c
INNER JOIN
	transactions t
	ON t.from_customer = c.cus_id
GROUP BY
	 c.cus_id, c.last_name
HAVING MAX(t.transdate) < CURRENT_DATE - INTERVAL 18 MONTH

Niels van K

07-04-2013 16:22

Oke, ik snap hem. In 99% van de gevallen zal er minimaal één transactie bestaan, maar er is een kleine kans dat dit niet het geval is.

Echter, iedere klant betaalt lidmaatschap aan klant nummer 1 elke maand. Deze transacties moeten niet meegeteld worden.


SELECT c.cus_id, c.lastname, MAX(t.transdate) lasttrans
FROM
customer_list c
INNER JOIN
transactions t
ON t.from_customer = c.cus_id AND t.to_customer != 1
GROUP BY
c.cus_id, c.lastname
HAVING MAX(t.transdate) < CURRENT_DATE - INTERVAL 18 MONTH

Ger van Steenderen

07-04-2013 16:37

Oké, werkt dit nu beter?

Niels van K

07-04-2013 16:43 gewijzigd op 07-04-2013 16:44

Ik krijg veel rijen terug, maar heb er een paar tussen uit gepakt, en het ziet er goed uit!

Het enige 'nadeel' wat ik van je begrijp, is dat klanten die _geen_ transacties hebben, worden ook niet meegenomen in het lijstje? Is dit ook nog te verhelpen?

Ger van Steenderen

07-04-2013 16:56

Ja, een LEFT JOIN ervan maken. Ik denk dat dat al voldoende is, anders een extra voorwaarde aan de HAVING toe voegen (OR MAX(t.transdate) IS NULL).

Reageren

Inloggen om te reageren