TIPP Wartbare SQL mit Funktionen und Variablen

Useful code snippets and help for problems with Velocity-/Java-Script Code

TIPP Wartbare SQL mit Funktionen und Variablen

Postby KaiBaechle » 08 Oct 2013, 10:28

Für eine Verkaufsanalyse per 31.10.2013, wird ein SQL benötigt. In der Tabelle PRODUCT sind die notwendigen Daten gespeichert


Code: Select all
PRODUCT_ID | PRODUCT_NAME | LAST_SALE
-----------+--------------+-----------
100001 | HAMMER | 02.09.2013
100002 | SCHAUFEL | 01.10.2013
100003 | MEISSEL | 19.07.2013
100004 | BOHRER | 08.10.2013
100005 | FEILE | 28.10.2013


Es soll eine Gruppierung nach dem letzten Verkauf des Produktes in den Schritten 1..30, 31..90 und 91.. erfolgen.

Am Beispiel von H2 (interne Engine), kann die Auswertung so aussehen


Code: Select all
SELECT CASE
         WHEN DATEDIFF('DAY', LAST_SALE, {d '2013-10-31'})   90 THEN '91.. TAGE'
       END LAST_SALE_GROUP,
       PRODUCT_ID,
       PRODUCT_NAME
FROM   PRODUCT



Das Ergebnis lautet


Code: Select all
LAST_SALE_GROUP | PRODUCT_ID | PRODUCT_NAME
----------------+------------+-------------
31..90 TAGE | 100001 | HAMMER
1..30 TAGE | 100002 | SCHAUFEL
91.. TAGE | 100003 | MEISSEL
1..30 TAGE | 100004 | BOHRER
30 TAGE | 100005 | FEILE


Soll das Ergebnis nun noch sortiert und auf die Ladenhüter gefiltert werden, so kommt das gleiche CASE/WHEN-Statement mehrmals zum Einsatz:


Code: Select all
SELECT CASE
         WHEN DATEDIFF('DAY', LAST_SALE, {d '2013-10-31'})   90 THEN '91.. TAGE'
       END LAST_SALE_GROUP,
       PRODUCT_ID,
       PRODUCT_NAME
FROM   PRODUCT
WHERE CASE
         WHEN DATEDIFF('DAY', LAST_SALE, {d '2013-10-31'})   90 THEN '91.. TAGE'
       END  '1..30 TAGE'
ORDER BY CASE
         WHEN DATEDIFF('DAY', LAST_SALE, {d '2013-10-31'})   90 THEN '91.. TAGE'
       END


Es ist leicht erkennbar, dass das Statement redundante Bestandteile hat und beim Anpassen auf den nächsten Monatsultimo oder dem Einführen eines neuen Zeitrasters fehleranfällig ist.

Abhilfe schafft das Einführen einer Funktion, die im SQL verwendet wird:

#TimeBucket($refDate:String)

Code: Select all
CASE
  WHEN DATEDIFF('DAY', LAST_SALE, $refDate)   90 THEN '91.. TAGE'
END


Das Analyse-SQL verkürzt sich und verwendet einheitlich die Variable $refDate


Code: Select all
#set($refDate = "{d '2013-10-31'}")

SELECT #TimeBucket($refDate) LAST_SALE_GROUP,
       PRODUCT_ID,
       PRODUCT_NAME
FROM   PRODUCT
WHERE  #TimeBucket($refDate)  '1..30 TAGE'
ORDER BY #TimeBucket($refDate)
 


Die Einführung der Funktion #TimeBucket und der Variablen $refDate hat den Code verständlicher, einfacher anpassbar und einfacher erweiterbar gemacht.
KaiBaechle
 
Posts: 33
Joined: 24 May 2013, 21:20

Return to Developers Corner



Who is online

Users browsing this forum: No registered users and 1 guest

cron