go backsqlschool blogs list

How to find unpaid balance

by Antonios Chatzipavlis

Πάντα όταν κάνω μάθημα για την 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



Ημερομηνία: 13 January 2017 22:32
Αξιολόγηση:
Κατηγορίες:
Tags:
Share it:

Αφήστε το σχόλιο σας - Leave your comment

Τα σχόλια έχουν κλείσει.
Επιτρέπονται μόνο τα σχόλια από τα μέλη του SqlSchool.gr.


newsletter subscription

Εάν επιθυμείτε να λαμβάνετε ενημέρωση από εμάς, δώστε μας το e-mail σας.
PASS chapter logo
Official Professional Association for SQL Server (PASS) chapter for Greece
Join to PASS