Πάντα όταν κάνω μάθημα για την T-SQL οι μαθητές μου με ρωτάνε να τουσ δείξω πως μπορούν να λύσουν διάφορα προβλήματα που αντιμετωπίζουν στην εργασία τους και που τους έχουν παιδέψει αρκετά για να βρουν λύση ή δεν έχουν ακόμα βρει λύση και μαστορεύουν τα δεδομένα με χειρωνακτικό τρόπο στο Excel.
Αυτές τις ημέρες κάνω μάθημα σε ανθρώπους που εργάζονται σε ένα μεγάλο χρηματοπιστωτικό οργανισμό και οι οποίοι είναι αυτό που λέμε Information workers, δηλαδή άνθρωποι που βασικό αντικείμενο εργασίας έχουν μέσα από τους ωκεανούς δεδομένων που έχουν να αντλήσουν πληροφορίες που η διοίκηση ζητάει κάθε φορά.
Το ερώτημα ήταν συγκεκριμένο και σαφές αλλά επειδή δεν έχω τέτοια δεδομένα στην διάθεση μου θα το μεταφέρω σε άλλο επιχειρηματικό χώρο που είναι και περισσότερο οικείος στους περισσότερους.
Για το παράδειγμα θα χρησιμοποιήσω μια γνωστή σε όλους database την Northwind. Όπως είναι γνωστό η συγκεκριμένη database σαν θέμα της έχει την διαχείριση παραγγελιών. Επίσης θα πρέπει να σημειωθεί ότι η λύση στο ερώτημα μπορεί να υλοποιηθεί σε SQL Server 2012 και μεγαλύτερο καθώς το μάθημα ήταν T-SQL σε SQL Server 2016.
Το πρόβλημα
Η απαίτηση του συναδέλφου ήταν η εξής:
"Θέλω να μπορώ με ένα γρήγορο και όχι περίπλοκο τρόπο και αν είναι δυνατό με ένα query να βρω τις παραγγελίες για τις οποίες ο πελάτης μου χρωστάει χρήματα."
Αρκετοί μπορεί να έχετε ακούσει αυτό το πρόβλημα σαν "εύρεση ανεξόφλητων τιμολογίων" ή "παλαιότητα υπολοίπου" αν και στο παράδειγμα δεν χρησιμοποιώ ημερομηνίες αλλά αυτό δεν είναι δύσκολο να μπει.
Παραδοχές
Επειδή η Northwind database δεν έχει πουθενά τις πληρωμές των παραγγελιών αυτό που έκανα για να έχω κάτι τέτοιο στα δεδομένα μου, έφτιαξα ένα query στο οποίο βρίσκω το συνολικό τζίρο των παραγγελιών που ο κάθε πελάτης έχει και με μια απλή και τυχαία παραδοχή από το τζίρο παίρνω το 60% το οποίο θεωρώ ότι έχει πληρώσει. Αυτό το query το έκανα ένα view το οποίο είναι το παρακάτω
create view CustomerPayments
as
select o.CustomerID
, cast ( sum(d.UnitPrice*d.Quantity) * 0.60 as decimal(10,2) ) as payments
from dbo.Orders as o
join dbo.[Order Details] as d on o.OrderID = d.OrderID
group by o.CustomerID;
goΚαι τα αποτελέσματα του είναι
CustomerID payments
---------- ----------
ALFKI 2757.72
ANATR 841.77
ANTON 4509.21
AROUT 8283.90
BERGS 16180.89
BLAUS 1943.88
BLONP 11452.80
BOLID 3178.68
BONAP 14310.57
BOTTM 13564.62
Η λύση
Το να βρει κανείς το ποσό που έχει κάθε παραγγελία δεν είναι δύσκολο καθώς μπορεί με ένα απλό group by να το κάνει. Απλά θα πρέπει να έχει εκτός από τον αριθμό της παραγγελίας και τον κωδικό του πελάτη.
Το query για αυτό είναι το παρακάτω και ένα δείγμα από τα αποτελέσματα που αυτό επιστρέφει είναι στην συνέχεια
select o.CustomerID
, o.OrderID
, cast ( sum(d.UnitPrice*d.Quantity) as decimal(10,2) ) as OrderAmt
from dbo.Orders as o
join dbo.[Order Details] as d on o.OrderID = d.OrderID
group by o.CustomerID,o.OrderID
order by 1,2
CustomerID OrderID OrderAmt
---------- ----------- -----------
ALFKI 10643 1086.00
ALFKI 10692 878.00
ALFKI 10702 330.00
ALFKI 10835 851.00
ALFKI 10952 491.20
ALFKI 11011 960.00
ANATR 10308 88.80
ANATR 10625 479.75
ANATR 10759 320.00
ANATR 10926 514.40
ANTON 10365 403.20
ANTON 10507 881.25
ANTON 10535 2156.50
ANTON 10573 2082.00
ANTON 10677 956.90
ANTON 10682 375.50
ANTON 10856 660.00
AROUT 10355 480.00
AROUT 10383 899.00
Επειδή πρέπει να γίνει επεξεργασία στα δεδομένα αυτά αλλά και να συνδεθούν με τα δεδομένα των πληρωμών ανά πελάτη επέλεξα το συγκεκριμένο query να το βάλω σε ένα CTE (Common Table Expression)
with CustomersOrders as
(
select o.CustomerID
, o.OrderID
, cast ( sum(d.UnitPrice*d.Quantity) as decimal(10,2) ) as OrderAmt
from dbo.Orders as o
join dbo.[Order Details] as d on o.OrderID = d.OrderID
group by o.CustomerID,o.OrderID
)
select * from CustomersOrders
order by 1,2
CustomerID OrderID OrderAmt
---------- ----------- ------------
ALFKI 10643 1086.00
ALFKI 10692 878.00
ALFKI 10702 330.00
ALFKI 10835 851.00
ALFKI 10952 491.20
ALFKI 11011 960.00
ANATR 10308 88.80
ANATR 10625 479.75
ANATR 10759 320.00
ANATR 10926 514.40
ANTON 10365 403.20
ANTON 10507 881.25
ANTON 10535 2156.50
ANTON 10573 2082.00
ANTON 10677 956.90
ANTON 10682 375.50
ANTON 10856 660.00
AROUT 10355 480.00
AROUT 10383 899.00
AROUT 10453 453.00
AROUT 10558 2142.90
Η σύνδεση είναι εύκολη ένα join μεταξύ του CTE και των πληρωμών αρκεί αλλά αυτό που πρέπει να γίνει είναι να έχω ανά γραμμή το cumulative άθροισμα των παραγγελιών ώστε να μπορώ να το αφαιρώ από το ποσό πληρωμής. Αυτό μπορώ να το κάνω με χρήση του SQL windowing και framing. Επειδή θέλω να είναι εύκολη στην ανάγνωση η λύση αλλά και γιατί το τελικό αποτέλεσμα στο output να μπορεί να μεταβληθεί εύκολα επέλεξα να έχω derived CTE
with CustomersOrders as
(
select o.CustomerID
, o.OrderID
, cast ( sum(d.UnitPrice*d.Quantity) as decimal(10,2) ) as OrderAmt
from dbo.Orders as o
join dbo.[Order Details] as d on o.OrderID = d.OrderID
group by o.CustomerID,o.OrderID
),
DataPrep as
(
select co.customerid
, co.orderid
, co.orderamt
, cp.payments
, sum(co.OrderAmt) over ( partition by co.Customerid
order by co.orderid
rows between unbounded preceding
and current row) as RunningTotal
from CustomersOrders as co
join CustomerPayments as cp on co.customerid=cp.CustomerID
)
select * from DataPrep;
customerid orderid orderamt payments RunningTotal
---------- ----------- ----------- --------------- ---------------
ALFKI 10643 1086.00 2757.72 1086.00
ALFKI 10692 878.00 2757.72 1964.00
ALFKI 10702 330.00 2757.72 2294.00
ALFKI 10835 851.00 2757.72 3145.00
ALFKI 10952 491.20 2757.72 3636.20
ALFKI 11011 960.00 2757.72 4596.20
ANATR 10308 88.80 841.77 88.80
ANATR 10625 479.75 841.77 568.55
ANATR 10759 320.00 841.77 888.55
ANATR 10926 514.40 841.77 1402.95
ANTON 10365 403.20 4509.21 403.20
ANTON 10507 881.25 4509.21 1284.45
ANTON 10535 2156.50 4509.21 3440.95
ANTON 10573 2082.00 4509.21 5522.95
ANTON 10677 956.90 4509.21 6479.85
ANTON 10682 375.50 4509.21 6855.35
ANTON 10856 660.00 4509.21 7515.35
AROUT 10355 480.00 8283.90 480.00
AROUT 10383 899.00 8283.90 1379.00
AROUT 10453 453.00 8283.90 1832.00
AROUT 10558 2142.90 8283.90 3974.90
AROUT 10707 1704.00 8283.90 5678.90
AROUT 10741 285.00 8283.90 5963.90
AROUT 10743 336.00 8283.90 6299.90
AROUT 10768 1477.00 8283.90 7776.90
AROUT 10793 191.10 8283.90 7968.00
AROUT 10864 282.00 8283.90 8250.00
Πλέον τα πράγματα είναι απλά μια αφαίρεση ένα case στο where και αυτό ήταν όλο.
with CustomersOrders as
(
select o.CustomerID
, o.OrderID
, cast ( sum(d.UnitPrice*d.Quantity) as decimal(10,2) ) as OrderAmt
from dbo.Orders as o
join dbo.[Order Details] as d on o.OrderID = d.OrderID
group by o.CustomerID,o.OrderID
),
DataPrep as
(
select co.customerid
, co.orderid
, co.orderamt
, cp.payments
, sum(co.OrderAmt) over ( partition by co.Customerid
order by co.orderid
rows between unbounded preceding
and current row) as RunningTotal
from CustomersOrders as co
join CustomerPayments as cp on co.customerid=cp.CustomerID
)
select p.CustomerID,p.OrderID,p.OrderAmt
, case
when p.payments-p.runningtotal > 0 then 0
else 1
end as status
from DataPrep as p
where case
when p.payments-p.runningtotal > 0 then 0
else 1
end = 1
order by 1,2
CustomerID OrderID OrderAmt status
---------- ----------- --------------------------------------- -----------
ALFKI 10835 851.00 1
ALFKI 10952 491.20 1
ALFKI 11011 960.00 1
ANATR 10759 320.00 1
ANATR 10926 514.40 1
ANTON 10573 2082.00 1
ANTON 10677 956.90 1
ANTON 10682 375.50 1
ANTON 10856 660.00 1
AROUT 10920 390.00 1
AROUT 10953 4675.00 1
AROUT 11016 491.50 1
BERGS 10672 4210.50 1
BERGS 10689 630.00 1
BERGS 10733 1459.00 1
BERGS 10778 96.50 1
Επίλογος
Φυσικά μπορούν να γίνουν αρκετές αλλαγές στο τελικό αποτέλεσμα αλλά ο σκοπός μου είναι με το συγκεκριμένο άρθρο είναι να σας περάσω το πως πρέπει να σκέφτεστε αλλά και να δείξω για ακόμα μια φορά την ομορφιά των CTEs αλλά και την δύναμη τoυ SQL Windowing & Framing
//antonch