go backarticles

Articles of SQLschool.gr Team

T-SQL Challenge #1:Different Level Ordering in Hierarchy

Antonios Chatzipavlis

Το πρόβλημα

Πριν από μερικές μέρες μια συνάδελφος ήρθε με το εξής πρόβλημα, ήθελε να δείξει κάποια δεδομένα σε ένα treeview control σε ένα web page. Στην ουσία ήταν μια ιεραρχία που από την δομή του πίνακα έβγαινε αρκετά εύκολα με ένα order by clause. Όμως δεν ήταν τόσο απλά τα πράγματα, ήθελε να υπάρχει ταξινόμηση ανά επίπεδο ιεραρχίας το οποίο ορίζονταν από ένα άλλο πεδίο.

Για να εξηγήσουμε καλύτερα την πρόκληση αυτή ας έρθουμε να δούμε το πώς ήταν τα δεδομένα της

Ο πίνακας της ήταν ο εξής

CREATE TABLE T (A INT, B INT, C INT, D INT, P INT)

Το πεδίο Α ορίζει το 1ο level της ιεραρχίας, το Β το 2ο, το C το 3ο, το D το 4ο και το P είναι αυτό που ορίζει το σειρά του αντίστοιχου στοιχείου στο level σε σχέση με τα άλλα στοιχεία στο ίδιο level.

Ας έρθουμε να γεμίσουμε τον πίνακα αυτό με μερικά δεδομένα

-- 1st Level
insert into T values (1,null,null,null,2), (2,null,null,null,1), (3,null,null,null,3)

--2nd Level
insert into T
select y.A,x.A,x.B,null,x.P from T x cross join T y

--3rd Level
insert into T
select distinct y.A,x.A,x.B,null,x.P from (select * from T where B is not null) x
cross join (select * from T where B is not null) y

--4th Level
insert into T
select distinct y.A,x.A,x.B,x.C,x.P from (select * from T where C is not null) x
cross join (select * from T where C is not null) y

Εάν εκτελέσουμε το παρακάτω query θα έχουμε άμεσα την ιεραρχία μας αλλά χωρίς τον ορισμό της σειράς του στοιχείου ανά level.

select * from T order by A,B,C,D

To αποτέλεσμα μας θα είναι το παρακάτω

A B C D P
1 NULL NULL NULL 2
1 1 NULL NULL 2
1 1 1 NULL 2
1 1 1 1 2
1 1 1 2 1
1 1 1 3 3
1 1 2 NULL 1
1 1 2 1 2
1 1 2 2 1
1 1 2 3 3
1 1 3 NULL 3
1 1 3 1 2
1 1 3 2 1
1 1 3 3 3
1 2 NULL NULL 1
1 2 1 NULL 2
1 2 1 1 2
1 2 1 2 1
1 2 1 3 3
1 2 2 NULL 1
1 2 2 1 2
1 2 2 2 1
1 2 2 3 3
1 2 3 NULL 3
1 2 3 1 2
1 2 3 2 1
1 2 3 3 3
1 3 NULL NULL 3
1 3 1 NULL 2
1 3 1 1 2
1 3 1 2 1
1 3 1 3 3
1 3 2 NULL 1
1 3 2 1 2
1 3 2 2 1
1 3 2 3 3
1 3 3 NULL 3
1 3 3 1 2
1 3 3 2 1
1 3 3 3 3
2 NULL NULL NULL 1
2 1 NULL NULL 2
2 1 1 NULL 2
2 1 1 1 2
2 1 1 2 1
2 1 1 3 3
2 1 2 NULL 1
2 1 2 1 2
2 1 2 2 1
2 1 2 3 3
2 1 3 NULL 3
2 1 3 1 2
2 1 3 2 1
2 1 3 3 3
2 2 NULL NULL 1
2 2 1 NULL 2
2 2 1 1 2
2 2 1 2 1
2 2 1 3 3
2 2 2 NULL 1
2 2 2 1 2
2 2 2 2 1
2 2 2 3 3
2 2 3 NULL 3
2 2 3 1 2
2 2 3 2 1
2 2 3 3 3
2 3 NULL NULL 3
2 3 1 NULL 2
2 3 1 1 2
2 3 1 2 1
2 3 1 3 3
2 3 2 NULL 1
2 3 2 1 2
2 3 2 2 1
2 3 2 3 3
2 3 3 NULL 3
2 3 3 1 2
2 3 3 2 1
2 3 3 3 3
3 NULL NULL NULL 3
3 1 NULL NULL 2
3 1 1 NULL 2
3 1 1 1 2
3 1 1 2 1
3 1 1 3 3
3 1 2 NULL 1
3 1 2 1 2
3 1 2 2 1
3 1 2 3 3
3 1 3 NULL 3
3 1 3 1 2
3 1 3 2 1
3 1 3 3 3
3 2 NULL NULL 1
3 2 1 NULL 2
3 2 1 1 2
3 2 1 2 1
3 2 1 3 3
3 2 2 NULL 1
3 2 2 1 2
3 2 2 2 1
3 2 2 3 3
3 2 3 NULL 3
3 2 3 1 2
3 2 3 2 1
3 2 3 3 3
3 3 NULL NULL 3
3 3 1 NULL 2
3 3 1 1 2
3 3 1 2 1
3 3 1 3 3
3 3 2 NULL 1
3 3 2 1 2
3 3 2 2 1
3 3 2 3 3
3 3 3 NULL 3
3 3 3 1 2
3 3 3 2 1
3 3 3 3 3

Εάν κάποιος δοκιμάσει να να λύσει το πρόβλημα αυτό απλά βάζοντας και το P στο order by δεν θα πάρει το επιθυμητό αποτέλεσμα καθώς θέλω διαφορετικό ordering ανά level.

Η λύση

Για να λυθεί η άσκηση αυτή ιδανικό και εύκολο στην υλοποίηση pattern είναι αυτό των Common Table Expressions (CTE).

Ας δούμε την λύση πρώτα και ας την εξηγήσουμε αμέσως μετά

WITH R (A,B,C,D,P,O)
AS 
(
-- 1st level
SELECT A,B,C,D,P,RIGHT('00000'+CAST(P AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(A AS NVARCHAR(5)),5) 
                FROM T WHERE B IS NULL 
-- 2nd level
UNION ALL
SELECT A,B,C,D,P,RIGHT('00000'+CAST((SELECT P FROM T X WHERE X.A =T.A AND X.B IS NULL) AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(A AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(P AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(B AS NVARCHAR(5)),5)
    FROM T WHERE B IS NOT NULL AND C IS NULL 
-- 3rd Level
UNION ALL
SELECT A,B,C,D,P,RIGHT('00000'+CAST((SELECT P FROM T X WHERE X.A =T.A AND X.B IS NULL) AS NVARCHAR(5)),5)
                 +RIGHT('00000'+CAST(A AS NVARCHAR(5)),5)+
                 +RIGHT('00000'+CAST((SELECT P FROM T X WHERE X.A =T.A AND X.B =T.B AND X.C IS NULL) AS NVARCHAR(5)),5)
                 +RIGHT('00000'+CAST(B AS NVARCHAR(5)),5)+
                 +RIGHT('00000'+CAST(P AS NVARCHAR(5)),5)
                 +RIGHT('00000'+CAST(C AS NVARCHAR(5)),5) 
    FROM T WHERE B IS NOT NULL AND C IS NOT NULL AND D IS NULL 
--4th level
UNION ALL
SELECT A,B,C,D,P,RIGHT('00000'+CAST((SELECT P FROM T X WHERE X.A =T.A AND X.B IS NULL) AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(A AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST((SELECT P FROM T X WHERE X.A =T.A AND X.B =T.B AND X.C IS NULL) AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(B AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST((SELECT P FROM T X WHERE X.A =T.A AND X.B =T.B AND X.C=T.C AND D IS NULL) AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(C AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(P AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(D AS NVARCHAR(5)),5)
                  
    FROM T WHERE B IS NOT NULL AND C IS NOT NULL AND D IS NOT NULL 
)
SELECT * FROM R ORDER BY O

Η εξήγηση της λύσης

Στην περίπτωση μας ξέρουμε τον αριθμό των επιπέδων που έχουμε, καθώς επίσης ξέρουμε και το κριτήριο με το οποίο σε κάθε επίπεδο θέλουμε να τα ταξινομήσουμε το οποίο δεν είναι άλλο από το πεδίο P.

Για να το πετύχουμε αυτό θα πρέπει να έχουμε παράξουμε μια τιμή (Ο στο CTE) που με βάση αυτή θα γίνεται η τελική ταξινόμηση. Η τιμή αυτή θα πρέπει να έχει την δομή ταξινόμηση-level για κάθε γραμμή δεδομένων για το αντίστοιχο level, αλλά και για τους πατέρες αυτού.

Αρχίζοντας από το 1ο level (το εξασφαλίζουμε αυτό με το WHERE B IS NULL) όπου παράγουμε την τιμή για το Ο πεδίο προσθέτοντας την θέση που ορίζετε από το P και το level που ορίζεται από το πεδίο A ως εξής

RIGHT('00000'+CAST(P AS NVARCHAR(5)),5)+RIGHT('00000'+CAST(A AS NVARCHAR(5)),5)

Ο λόγος που χρησιμοποιούμε την μορφή αυτή είναι γιατί πρέπει να εξασφαλίσουμε την ορθή ταξινόμιση καθώς αν δεν χρησιμοποιούμε την RIGHT(‘00000’+CAST(…),5) θα βρεθούμε αντιμέτωποι με το 15 να έρχετε πριν το 2 αν υποθέσουμε ότι οι τιμές σε δύο διαφορετικά records ήταν αυτές.

Συνεχίζουμε με το 2ο level (το εξασφαλίζουμε αυτό με το WHERE B IS NOT NULL AND C IS NULL) παράγουμε την τιμή τιμή για το Ο πεδίο όπως παραπάνω αλλά επειδή είναι το δεύτερο επίπεδο θα πρέπει να προσθέσουμε την αντίστοιχη τιμή του πατέρα του πριν από αυτή την οποία και διαβάζουμε με ένα correlated subquery  όπως παρακάτω

  RIGHT('00000'+CAST((SELECT P FROM T X WHERE X.A =T.A AND X.B IS NULL) AS NVARCHAR(5)),5)
 +RIGHT('00000'+CAST(A AS NVARCHAR(5)),5)
 +RIGHT('00000'+CAST(P AS NVARCHAR(5)),5)
 +RIGHT('00000'+CAST(B AS NVARCHAR(5)),5)

Πράττουμε το ίδιο και για τα επόμενα επίπεδα και στο τέλος λέμε στο CTE να τα κάνει order by O. (SELECT * FROM R ORDER BY O)

Το τελικό αποτέλεσμα

Αυτό θα έχει σαν αποτέλεσμα να ικανοποιήσουμε την ανάγκη μας στο 100% όπως φαίνεται παρακάτω μετά από την εκτέλεση της παραπάνω λύσης.

A B C D P O
2 NULL NULL NULL 1 0000100002
2 2 NULL NULL 1 00001000020000100002
2 2 2 NULL 1 000010000200001000020000100002
2 2 2 2 1 0000100002000010000200001000020000100002
2 2 2 1 2 0000100002000010000200001000020000200001
2 2 2 3 3 0000100002000010000200001000020000300003
2 2 1 NULL 2 000010000200001000020000200001
2 2 1 2 1 0000100002000010000200002000010000100002
2 2 1 1 2 0000100002000010000200002000010000200001
2 2 1 3 3 0000100002000010000200002000010000300003
2 2 3 NULL 3 000010000200001000020000300003
2 2 3 2 1 0000100002000010000200003000030000100002
2 2 3 1 2 0000100002000010000200003000030000200001
2 2 3 3 3 0000100002000010000200003000030000300003
2 1 NULL NULL 2 00001000020000200001
2 1 2 NULL 1 000010000200002000010000100002
2 1 2 2 1 0000100002000020000100001000020000100002
2 1 2 1 2 0000100002000020000100001000020000200001
2 1 2 3 3 0000100002000020000100001000020000300003
2 1 1 NULL 2 000010000200002000010000200001
2 1 1 2 1 0000100002000020000100002000010000100002
2 1 1 1 2 0000100002000020000100002000010000200001
2 1 1 3 3 0000100002000020000100002000010000300003
2 1 3 NULL 3 000010000200002000010000300003
2 1 3 2 1 0000100002000020000100003000030000100002
2 1 3 1 2 0000100002000020000100003000030000200001
2 1 3 3 3 0000100002000020000100003000030000300003
2 3 NULL NULL 3 00001000020000300003
2 3 2 NULL 1 000010000200003000030000100002
2 3 2 2 1 0000100002000030000300001000020000100002
2 3 2 1 2 0000100002000030000300001000020000200001
2 3 2 3 3 0000100002000030000300001000020000300003
2 3 1 NULL 2 000010000200003000030000200001
2 3 1 2 1 0000100002000030000300002000010000100002
2 3 1 1 2 0000100002000030000300002000010000200001
2 3 1 3 3 0000100002000030000300002000010000300003
2 3 3 NULL 3 000010000200003000030000300003
2 3 3 2 1 0000100002000030000300003000030000100002
2 3 3 1 2 0000100002000030000300003000030000200001
2 3 3 3 3 0000100002000030000300003000030000300003
1 NULL NULL NULL 2 0000200001
1 2 NULL NULL 1 00002000010000100002
1 2 2 NULL 1 000020000100001000020000100002
1 2 2 2 1 0000200001000010000200001000020000100002
1 2 2 1 2 0000200001000010000200001000020000200001
1 2 2 3 3 0000200001000010000200001000020000300003
1 2 1 NULL 2 000020000100001000020000200001
1 2 1 2 1 0000200001000010000200002000010000100002
1 2 1 1 2 0000200001000010000200002000010000200001
1 2 1 3 3 0000200001000010000200002000010000300003
1 2 3 NULL 3 000020000100001000020000300003
1 2 3 2 1 0000200001000010000200003000030000100002
1 2 3 1 2 0000200001000010000200003000030000200001
1 2 3 3 3 0000200001000010000200003000030000300003
1 1 NULL NULL 2 00002000010000200001
1 1 2 NULL 1 000020000100002000010000100002
1 1 2 2 1 0000200001000020000100001000020000100002
1 1 2 1 2 0000200001000020000100001000020000200001
1 1 2 3 3 0000200001000020000100001000020000300003
1 1 1 NULL 2 000020000100002000010000200001
1 1 1 2 1 0000200001000020000100002000010000100002
1 1 1 1 2 0000200001000020000100002000010000200001
1 1 1 3 3 0000200001000020000100002000010000300003
1 1 3 NULL 3 000020000100002000010000300003
1 1 3 2 1 0000200001000020000100003000030000100002
1 1 3 1 2 0000200001000020000100003000030000200001
1 1 3 3 3 0000200001000020000100003000030000300003
1 3 NULL NULL 3 00002000010000300003
1 3 2 NULL 1 000020000100003000030000100002
1 3 2 2 1 0000200001000030000300001000020000100002
1 3 2 1 2 0000200001000030000300001000020000200001
1 3 2 3 3 0000200001000030000300001000020000300003
1 3 1 NULL 2 000020000100003000030000200001
1 3 1 2 1 0000200001000030000300002000010000100002
1 3 1 1 2 0000200001000030000300002000010000200001
1 3 1 3 3 0000200001000030000300002000010000300003
1 3 3 NULL 3 000020000100003000030000300003
1 3 3 2 1 0000200001000030000300003000030000100002
1 3 3 1 2 0000200001000030000300003000030000200001
1 3 3 3 3 0000200001000030000300003000030000300003
3 NULL NULL NULL 3 0000300003
3 2 NULL NULL 1 00003000030000100002
3 2 2 NULL 1 000030000300001000020000100002
3 2 2 2 1 0000300003000010000200001000020000100002
3 2 2 1 2 0000300003000010000200001000020000200001
3 2 2 3 3 0000300003000010000200001000020000300003
3 2 1 NULL 2 000030000300001000020000200001
3 2 1 2 1 0000300003000010000200002000010000100002
3 2 1 1 2 0000300003000010000200002000010000200001
3 2 1 3 3 0000300003000010000200002000010000300003
3 2 3 NULL 3 000030000300001000020000300003
3 2 3 2 1 0000300003000010000200003000030000100002
3 2 3 1 2 0000300003000010000200003000030000200001
3 2 3 3 3 0000300003000010000200003000030000300003
3 1 NULL NULL 2 00003000030000200001
3 1 2 NULL 1 000030000300002000010000100002
3 1 2 2 1 0000300003000020000100001000020000100002
3 1 2 1 2 0000300003000020000100001000020000200001
3 1 2 3 3 0000300003000020000100001000020000300003
3 1 1 NULL 2 000030000300002000010000200001
3 1 1 2 1 0000300003000020000100002000010000100002
3 1 1 1 2 0000300003000020000100002000010000200001
3 1 1 3 3 0000300003000020000100002000010000300003
3 1 3 NULL 3 000030000300002000010000300003
3 1 3 2 1 0000300003000020000100003000030000100002
3 1 3 1 2 0000300003000020000100003000030000200001
3 1 3 3 3 0000300003000020000100003000030000300003
3 3 NULL NULL 3 00003000030000300003
3 3 2 NULL 1 000030000300003000030000100002
3 3 2 2 1 0000300003000030000300001000020000100002
3 3 2 1 2 0000300003000030000300001000020000200001
3 3 2 3 3 0000300003000030000300001000020000300003
3 3 1 NULL 2 000030000300003000030000200001
3 3 1 2 1 0000300003000030000300002000010000100002
3 3 1 1 2 0000300003000030000300002000010000200001
3 3 1 3 3 0000300003000030000300002000010000300003
3 3 3 NULL 3 000030000300003000030000300003
3 3 3 2 1 0000300003000030000300003000030000100002
3 3 3 1 2 0000300003000030000300003000030000200001
3 3 3 3 3 0000300003000030000300003000030000300003

Reusability

Το παραπάνω query μπορεί εύκολα να γίνει ένα view το οποίο θα μπορεί να καλεστεί από οπουδήποτε και να παρέχει άμεσα το επιθυμητό αποτέλεσμα χωρίς να χρειαστεί να κάνω κάτι έξτρα στην εφαρμογή που θα το καλέσει. (ξέρετε τώρα αγαπητοί μου συνάδελφοι developers, λίστες επί λιστών, loops μέσα σε loops και άλλα τέτοια ωραία πράγματα τα οποία κάνουμε επειδή μας αρέσει το OOP (object oriented programming, η επεξήγηση αυτή για τους αγαπητούς admins),…). Απλά θέλει μια μικρή αλλαγή στο τελικό SELECT καθώς αυτό έχω ORDER BΥ και όπως είναι γνωστό για να παίξει αυτό σε view θέλει στο SELECT να βάλω την TOP. Για την περίπτωση αυτή το SELECT θα είναι το παρακάτω

SELECT TOP 9999999999 * FROM R ORDER BY O

antonch


Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.


Relative Articles

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.