שאלת SQL מראיון עבודה

[pitoach]

אתה מתכוון שאנחנו עברנו את הראיון

 

[maze82]

שאלת SQL מראיון עבודה

שלום לכולם, בראיון עבודה נתנו לי את השאלה הבאה אודה לכם אם מישהו יכול לפתור אותה: נתונה טבלה Numbers עם מספרים מ-1 עד X. המספרים עוקבים. יתכן וחלק מהמספרים חסרים. א. רשום שאילתה שמחזירה את המספרים החסרים מהטבלה. ב. האם השאילתה שלך תעבוד עם יש יותר שממספר חסר אחד? דוגמא לטבלה: 1 2 3 5 6 7 דוגמא נוספת: 1 2 4 5 7 8 נראה לי שפישלתי בשאלה הזו. אשמח לעזרה ממישהו שבקיא בקוד. תודה.

 

[stig2]

באורקל

על סמך הנתונים ששמת בהודעה

SELECT COL_NAME - 1 FROM (SELECT COL_NAME, (COL_NAME - NVL (LAG (COL_NAME, 1) OVER (ORDER BY COL_NAME), 1)) LAG FROM NUMBER) a WHERE a.LAG > 1;​

 

[D u k a s]

אם הבנתי אותך נכון...

select * from table where column is null מקווה שלזה התכוונת.

 

[גרי רשף]

החל מגרסת 2012 גם ב-SQL Server יש Lag..

..אבל בראיון כנראה התכוונו שתבצע Join עצמי של הטבלה בתנאי T1.N=T2.N-1.

 

[D u k a s]

איך JOIN ?

א. רשום שאילתה שמחזירה את המספרים החסרים מהטבלה.

 

[גרי רשף]

בערך כך..

Select T1.N+1 From Numbers T1 Left Join Numbers T2 On T1.N=T1.N-1 Where T1.N-1 Is Null​

זה יעבוד כל עוד אין כמה מספרים חסרים ברצף, אלא רק בודדים.

 

[pitoach]

עוד דרך זה לעשות שימוש בטבלת מספרים

קיימת במסד הנתונים מובנית טבלה עם מספרים בטבלאות המערכת (אבל אני לא זוכר עד איזה מספר היא מגיעה ולכן אני מכין לי תמיד במסד הנתונים לתחזוקה טבלת מספרים פשוטה) JOIN של טבלת המספרים הפשוטה המאונדקסת לעבודה מהירה וללא נתונים מיותרים עם הטבלה שלך יכול אולי להביא תוצאות בצורה יותר טובה (לא בדקתי אז יכול להיות שגם זה יהיה פחות טוב

אבל אני צופה שזה יהיה יותר טוב או זהה) פשוט מבצעים select N.Num from NumberTable N left join YourTable T on N.num = T.num where T.num Is Null עתה רק נשאר להוסיך תנאי של בחירה של מספרים מטבלת המספרים רק בין 1 לבין המספר האחרון בטבלה שלך למשל * אם הטבלה מאונדקסת ניתן לקבל את הנתון בלי משאבים כמעט בשאילתה פנימית ואם מדובר בשדה IDENTITY אפשר לעשות שימוש במשתנה @@IDENTITY ויש עוד דרכים בהתאם לאפיון מסד הנתונים שלך. * אני לא ליד מחשב עם SQL כרגע ואין לי להיכנס ב RDP אז כתבתי את הדברים מהראש ואם מישהו רוצה לבדוק מה עדיף הוא מוזמן

בכל מקרה בניגוד לשאילתה של גרי שרק מוצאת את החורים ואז צריך לבצע תהליך נוסף למשל במרה שהחור הוא של יותר ממספר אחד הרי ששאילתה זו תיתן תוצאה מדוייקת של מה שמבוקש ז"א כל המספרים שחסרים בטבלה (עתה נשאר לבדוק מה מיטבי יותר) ** הגיע הזמן שבוחנים יבינו עד כמה המבחנים התיאורטיים אבסורדיים לפעמים... אבל זה כבר דיון אחר

 

[pitoach]

דרך אגב יש גם פונקציית טבלת מספרים

היא פחות מיטבית (מכיוון שהיא מבצעת את החישוב במקום לשמור את הנתונים בצורה קבועה) אבל יותר נוחה לעבודה ואין את הצורך של החזקת טבלה עם מליוני רשומות (אם רוצים טבלה של מליון רשומות). מדובר על פונקציה פשוטה שמחזירה כתוצאה טבלה ומקבלת כנתון התחלתי 3 פרמטרים: מספר ראשון מספר אחרון קפיצה בין מספרים * ויש עוד דרכים קבות להגיע לטבלאות מספרים

 

[zalomon]

דוגמה ב-mysql

Generating numbers out of seemingly thin air הבעייה כאן היא כשהטווח של המספרים גדול מדי. אם בשאלה המקורית נתונים שני מספרים בלבד, 1, 10000000000, אז הפונקציה או טבלת המספרים שצריך לייצר הן גדולות מאוד (ומצד שני זו גם התשובה שצריך להחזיר, אז אולי זה לא כזה נורא).

 

[כלליים]

הגהת ט.ס.

Select T1.N+1 From Numbers T1 Left Join Numbers T2 On T1.N=T2.N-1 Where T2.N Is Null​

 

[stig2]

בעייתי

אם תקח את הדוגמה השניה שלו- 1 2 4 5 7 8 אזי השליפה שלך תחזיר 3 6 9 בעות שהבקשה היא להחזיר 3 6 בלבד

 

[כלליים]

אכן

כדבריך, צריך להוסיף תנאי:

Select T1.N+1 From Numbers T1 Left Join Numbers T2 On T1.N=T2.N-1 Where T2.N Is Null AND T1.N+1 < (select max(N) from Numbers)​

 

[arik23m]

מה עם ב.

"האם השאילתה שלך תעבוד עם יש יותר שממספר חסר אחד?" עבור הערכים: 1 2 3 5 6 7 10 השאילתא מחזירה 4,8 בלבד.

 

[כלליים]

לא

השאילתה שלי היא תיקון השאילתה של גרי רשף, שציין שהיא איננה מועילה אם חסר יותר ממספר אחד ברצף.

 

[arik23m]

 

[pitoach]

אתה סתם מסתבך... כתבתי את הפתרון כבר מזמן

לפני כמה עשרות יחידות זמן של הפורום

תבדוק למעלה כמה הודעות בכותרת "עוד דרך זה..." לגבי נושא המיטוב ומה עדיף: פרט לכך שזה עובד תמיד ונותן בדיוק מה שרצית תוכל גם לבדוק נושא של מיטוב (גם באורקל וגם ב MYSQL וגם ב SQL) כל עוד עובדים במסדי נתונים רלציוניים (בעבודה עם קוביות למשל אפשר להגיע לשאילתה הרבה יותר טובה). בדיקות בשרתים שונים תמיד יתרמו ללמוד מה הדרך המיטבית במקרים שונים

 

[stig2]

אני מסתבך...?

נתתי פיתרון פשוט בלי ג'ויינים וטבלאות מספרים והשד יודע מה עוד...

 

[pitoach]

נתת אכן פתרון יפה עם שימוש ב LAG

שקיים אבל רק בשרתים מסוימים

* לא בדקתי את הפתרון למרות שיש לי גם אורקל מותקן בשרת פיתוח אבל הרעיון ברור וטוב עם זה הדיון נטה לכיוון של הפתרון ללא LAG והתגובה שלך לכלליים ועל זה אני מדבר. הפנייה דרך אגב היתה דיי כללית ולא לאדם מסויים למרות שהגיעה פיזית כתגובה להודעה שלך

. אחרי הכל כל הודעה חייבת להיות תגובה להודעה אחרת פרט להודעה הראשית ** בלי קשר במקרה הנוכחי אני ממליץ לבדוק מבחינת מיטוב השוואה בין הפתרון עם LAG והפתרונות עם SELF JOIN והפתרון עם JOIN לטבלת מספרים. לא בדקתי ספציפית כאן אבל LAG לא תמיד נותן את הפתרון היעיל בשאילתות כמו כאן ביחס ל JOIN בגלל השימוש בחלון נע (נתונים שנמצאים בכמה חלונות יהיו לעיתים חלק מהחישוב גם בחלון האחר) בעוד ב JOIN נעשה שימוש בחלון אחד *** אם הבקשה היתה רק למצוא את החורים ולא את כל הערכים בתוך החורים אז העניין אחר, והשימוש ב LAG אמור להיות יותר טוב ו 2 שיטות ה JOIN אמורות לתת פתרון דיי דומה (אם לא זהה) להערכתי

 

[stig2]

אכן יש מספר פתרונות לבעיה

וכשזה המצב, ברור שצריך לבדוק את כולם ולבחור את הפתרון היעיל ביותר.