Έστω ότι έχουμε μια δανειστική βιβλιοθήκη που τα μέλη της μπορούν να δανείζονται βιβλία για μια εβδομάδα. Έχουμε τον παρακάτω πίνακα στον οποίο καταγράφονται οι κινήσεις (δανεισμός, επιστροφή, επέκταση του δανεισμού) για κάθε βιβλίο (BOOK_ID). Το σύστημα καταγράφει τον κωδικό του μέλους της βιβλιοθήκης που δανείστηκε το βιβλίο (MEMBER_ID) αλλά μόνο όταν γίνεται ο δανεισμός. Στις επόμενες ενέργειες αυτού το μέλους για το συγκεκριμένο βιβλίο (επέκταση ή επιστροφή) το σύστημα δεν καταγράφει τον κωδικό του μέλους και έτσι μας λείπει η πληροφορία αυτή σε πολλαπλά rows.

Πώς μπορούμε να πάρουμε τα περιεχόμενα του πίνακα με συμπληρωμένο το MEMBER_ID για όλα τα rows;

Μέθοδος Α – APPLY BASED

Ο απλούστερος τρόπος είναι να χρησιμοποιήσουμε τον apply operator και έτσι για κάθε row να βρούμε το τελευταίο non null MEMBER_ID value με ημερομηνία μικρότερη του row που κοιτάμε.

SELECT L_OUT.BOOK_ID, 
       L_OUT.LOG_TIME, 
       L_OUT.ACTION_TYPE, 
       MEMBER_FIND.MEMBER_ID
FROM LENDING_LOG AS L_OUT
CROSS APPLY
(    SELECT TOP 1 MEMBER_ID
    FROM LENDING_LOG AS L_IN 
    WHERE L_OUT.BOOK_ID = L_IN.BOOK_ID
        AND L_IN.LOG_TIME <= L_OUT.LOG_TIME
        AND L_IN.MEMBER_ID IS NOT NULL
    ORDER BY LOG_TIME DESC
) AS MEMBER_FIND
ORDER BY BOOK_ID, LOG_TIME;

Έτσι λαμβάνουμε το παρακάτω επιθυμητό αποτέλεσμα

Mέθοδος Β - WINDOW FUNCTIONS BASED

Μια δεύτερη προσέγγιση θα ήταν να χρησιμοποιήσουμε window functions για να πάρουμε το επιθυμητό αποτέλεσμα. Το εμπόδιο που έχουμε εδώ είναι ότι δεν είναι σταθερός ο αριθμός των εγγραφών που έχουν null σε κάθε περίπτωση και έτσι δεν μπορεί να ορισθεί απευθείας το window frame από το οποίο θέλουμε το πρώτο value ή ο αριθμός γραμμών που πρέπει να πάμε πίσω με την LAG.

ια να το λύσουμε αυτό, χρειάζεται ένα ενδιάμεσο βήμα στο οποίο θα πρέπει να ομαδοποιήσουμε τις εγγραφές του κάθε βιβλίου που στην πραγματικότητα αναφέρονται στον ίδιο δανεισμό και θα πρέπει να πάρουν το ίδιο MEMBER_ID.

SELECT 
    BOOK_ID, 
    LOG_TIME, 
    ACTION_TYPE, 
    MEMBER_ID ,
    COUNT(MEMBER_ID) OVER (
        PARTITION BY BOOK_ID     
        ORDER BY LOG_TIME
        ROWS UNBOUNDED PRECEDING) as  SUB_PARTITION_ID
FROM LENDING_LOG
ORDER BY BOOK_ID, LOG_TIME;

Αφού η εμφάνιση ενός MEMBER_ID σηματοδοτεί την αρχή κάθε MEMBER_ID, ένα rolling count των MEMBER_IDs μας δίνει τις τιμές που χρειαζόμαστε.

Πλέον αφού μπορούμε εύκολα να προσδιορίσουμε τον κάθε δανεισμό βιβλίου, αρκεί να πάρουμε την πρώτη τιμή.

WITH CTE_LENDING_LOG_P AS
(
    SELECT 
        BOOK_ID, 
        LOG_TIME, 
        ACTION_TYPE, 
        MEMBER_ID ,
        COUNT(MEMBER_ID) OVER (
            PARTITION BY BOOK_ID 
            ORDER BY LOG_TIME
            ROWS UNBOUNDED PRECEDING) as  SUB_PARTITION_ID
    FROM LENDING_LOG
)
SELECT 
    BOOK_ID, 
    LOG_TIME, 
    ACTION_TYPE, 
    FIRST_VALUE(MEMBER_ID) OVER 
        (PARTITION BY BOOK_ID, SUB_PARTITION_ID 
         ORDER BY LOG_TIME 
         ROWS UNBOUNDED PRECEDING)
         AS MEMBER_ID
FROM CTE_LENDING_LOG_P
ORDER BY BOOK_ID, LOG_TIME;

H Α μέθοδος με το apply είναι πιο απλή στην ανάγνωση του κώδικα. Επηρεάζεται όμως σημαντικά από το indexing του πίνακα. Αν λοιπόν υπάρχει ή μπορεί να υπάρξει covering index για το apply είναι μια καλή επιλογή. Αν όχι η δεύτερη προσέγγιση κατά πάσα πιθανότητα θα αποδώσει σημαντικά καλύτερα. Σε κάθε περίπτωση η δεύτερη προσέγγιση θα έχει λιγότερα reads.

Extra Μέθοδος

Αν και ο σκοπός αυτού του post ήταν η ανάδειξη της δεύτερης μεθόδου που έχει αποδειχθεί χρήσιμη και προσωπικά αγαπημένη λύση, μια ακόμη ενδιαφέρουσα προσέγγιση και γενικός αποδοτική λύση περιγράφεται στο solution 2 του άρθρου:

Στην περίπτωση μας η ακριβής μεταφορά αυτής της λύσης έχει μια μικρή παγίδα οπότε ας πάμε να τη δούμε.

Η ιδέα είναι η εξής:

• Αν ενώσουμε τα LOG_TIME και MEMBER_ID η τιμή που προκύπτει θα είναι NULL στα rows που το MEMBER_ID είναι null.
• Η γραμμή που έχει το MEMBER_ID που θέλουμε μέσα σε ένα partition ανά βιβλίο, είναι αυτή με το μέγιστο τέτοιο concatenation ως εκείνο το row.
• Από τη μέγιστη τιμή αυτή μπορούμε να απομονώσουμε πάλι το MEMBER_ID.

SELECT 
    BOOK_ID,  
    LOG_TIME, 
    ACTION_TYPE,     
    CAST(SUBSTRING( 
        MAX( CAST(LOG_TIME as binary(9))
             + CAST(MEMBER_ID as binary(4))) 
        OVER (
             PARTITION BY BOOK_ID 
             ORDER BY LOG_TIME 
             ROWS UNBOUNDED PRECEDING
        ),10,4) AS INT) as MEMBER_ID
FROM LENDING_LOG
ORDER BY BOOK_ID, LOG_TIME;

*precision

Το time part λοιπόν είναι πριν από το date part κάνοντάς το ακατάλληλο για το sorting που χρειαζόμαστε. Μπορούμε να προσπεράσουμε το πρόβλημα αυτό με διάφορους τρόπους όπως μετατρέποντας την ημερομηνία σε nanoseconds:

CAST(DATEDIFF_BIG(NANOSECOND,'1970-01-01', LOG_TIME) as binary(8))