go backarticles

Articles of SQLschool.gr Team

Missing Values – Get the last non-null value

Fivi Panopoulou

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

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

BOOK_ID LOG_TIME ACTION_TYPE MEMBER_ID
1 2019-02-01 10:00 LEND 1232
1 2019-02-07 10:00 RETURN NULL
1 2019-02-07 03:00 LEND 1321
1 2019-02-13 10:00 RETURN NULL
1 2019-03-02 09:00 LEND 981
1 2019-03-08 08:00 EXTEND NULL
1 2019-03-10 06:00 RETURN NULL
2 2020-01-03 09:30 LEND 123
2 2020-01-08 09:30 RETURN NULL
2 2020-01-15 01:00 LEND, 789
2 2020-01-22 10:00 EXTEND NULL
2 2020-01-28 03:00 EXTEND NULL
2 2020-02-02 09:00 RETURN NULL
2 2020-02-02 09:00 LEND 452
2 2020-02-08 03:00 EXTEND NULL

Μέθοδος Α – APPLY BASED

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

Query

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;

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

BOOK_ID LOG_TIME ACTION_TYPE MEMBER_ID
1 2019-02-01 10:00 LEND 1232
1 2019-02-07 10:00 RETURN 1232
1 2019-02-07 03:00 LEND 1321
1 2019-02-13 10:00 RETURN 1321
1 2019-03-02 09:00 LEND 981
1 2019-03-08 08:00 EXTEND 981
1 2019-03-10 06:00 RETURN 981
2 2020-01-03 09:30 LEND 123
2 2020-01-08 09:30 RETURN 123
2 2020-01-15 01:00 LEND, 789
2 2020-01-22 10:00 EXTEND 789
2 2020-01-28 03:00 EXTEND 789
2 2020-02-02 09:00 RETURN 452
2 2020-02-02 09:00 LEND 452
2 2020-02-08 03:00 EXTEND 452

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

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

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

Query

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 μας δίνει τις τιμές που χρειαζόμαστε.

BOOK_ID LOG_TIME ACTION_TYPE MEMBER_ID SUB_PARTITION_ID
1 2019-02-01 10:00 LEND 1232 1
1 2019-02-07 10:00 RETURN NULL 1
1 2019-02-07 03:00 LEND 1321 2
1 2019-02-13 10:00 RETURN NULL 2
1 2019-03-02 09:00 LEND 981 3
1 2019-03-08 08:00 EXTEND NULL 3
1 2019-03-10 06:00 RETURN NULL 3
2 2020-01-03 09:30 LEND 123 1
2 2020-01-08 09:30 RETURN NULL 1
2 2020-01-15 01:00 LEND, 789 2
2 2020-01-22 10:00 EXTEND NULL 2
2 2020-01-28 03:00 EXTEND NULL 2
2 2020-02-02 09:00 RETURN NULL 2
2 2020-02-02 09:00 LEND 452 3
2 2020-02-08 03:00 EXTEND NULL 3

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

Query

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 του άρθρου:

https://www.itprotoday.com/sql-server/last-non-null-puzzle

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

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

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

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

Query - not correct result

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;
Το πρόβλημα προκύπτει από το ότι έχουμε ένα DATETIME2(7) πεδίο αντί για integer στο οποίο στηρίζουμε το max και αυτό όταν γίνεται cast σε binary έχει την παρακάτω μορφή.

DATETIME2(7)
image

*precision


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

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

Fivi Panopoulou

Fivi Panopoulou

Fivi is a Software Engineer specializing in SQL Development. Building data flows and transforming data to enable their meaningful business interpretation is her pursuit. She works with software and reporting systems, from analysis and design to implementation and maintenance.
She has been part of Microsoft technology oriented communities since an undergraduate student. SQL School has inspired her to work with data and become a SQL Server enthusiast from her early steps.

  

Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.