שימוש ב- group by

[אליקוד]

שימוש ב- group by

שלום לכולם,
ניתקלתי בבחינה בשאלה הבאה:
מתוך הטבלה הנתונה ששמה Emp צריך להציג לכל מחלקה,
את שמו של העובד המבוגר ביותר והעובד הצעיר ביותר.
(אם צריך היה להציג את הגיל המבוגר ביותר והצעיר ביותר, הרי התשובה היתה פשוטה מאד).
הגעתי לפיתרון שאני מצרף, אבל נראה שצריך להיות פתרון פשוט יותר.
תודה מראש,

 

[sinaiy]

אין פתרון טוב ב SQL סטנדרטי

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

 

[zalomon]

window functions הן מזמן סטנדרט

וזה MYSQL שמדשדש מאחור... למרבה הצער

 

[pitoach]

זו פעולה מאוד פשוטה באמצעות RANK למשל

או ROW_NUMBER
בהנחה שהבנתי את הבעיה (זה בדרך כלל משפט לשם זהירות בלבד

)
* נכון לשרתי SQL כמובן

 

[pitoach]

או עם פונקציות חלון בעצם שזה הרבה יותר יעיל

 

[sefin]

RANK ן - ROW הם לא רק באורקל?

 

[כלליים]

אולי כך

את הלוכסנים יש להסיר, הם באו כדי לתעתע באבטחה של האתר, המונעת צירוף קוד.
ואגב: בקוד המקורי שלך, היה חסר משהו בGroupBy

Declar\\\e @Emp TABLE (
deptId INT ,
empName VARCHAR(30),
empAge INT );


inser\\\t into @Emp values
(1,'moshe',30),
(1,'john',28),
(1,'mark',45),
(3,'mahmood',35),
(3,'tonny',50),
(5,'jonny',48),
(5,'migel',31),
(5,'katan',41)



with CTE as (select distinct deptId from @Emp)
select Depts.deptId
,(select top 1 empName from @Emp t where t.deptId = Depts.deptId order by t.empAge desc) oldest
,(select top 1 empName from @Emp t where t.deptId = Depts.deptId order by t.empAge) youngest
from cte Depts

 

[pitoach]

כלליים למה לא עם פונקציית חלון? אתה יכול

להרוויח פי 3 משאבים בערך אני מעריך.

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

* עוד נקודה לבדוק: כמה פעמים מבוצע אצלך סריקה של הטבלה? זה הדבר הבא בתור שיהיה כבד. זניח אולי ביחס ל SORT אבל הבא בתור

 

[כלליים]

משלש סיבות

א. אינני יודע מה השואל יודע על פונקציות חלון. נתתי לו פתרון פשוט, על-סמך כלים שמן הסתם הוא מכיר.
ב. בגלל שלעיל נאמר שאי אפשר לעשות בSQL סטנדרטי, אז רציתי להראות פתרון בSQL סטנדרטי.
ג. חשבתי איך לבצע בפונקציות חלון, ומה אומר? הרבה יותר מסורבל... אולי השאילת תואץ בשבריר שניה, אבל הכתיבה הרבה יותר מסורבלת..
נניח שהשאילתה תרוץ ב10 השנים הקרובות 100,000 פעם, נחסוך למכונה אולי 100 שניות. לעומת זאת, כתיבת השאילתה הזו תצריך זמן אנושי הרבה יותר מ100 שניות.

כך יישמתי את פונקצית החלון. אולי טעיתי, וניתן לקצר.
with CTE as (select deptId
,empName
,ROW_NUMBER() over (PARTITION by deptId order by empage) yuoungRank
,ROW_NUMBER() over (PARTITION by deptId order by empage desc) oldRank
from @Emp)

select ranks1.deptId
,ranks1.empName youngest
,(select empName from CTE ranks2 where ranks2.deptId=ranks1.deptId and ranks2.oldRank=1) oldest
from cte ranks1
where ranks1.yuoungRank =1

 

[pitoach]

סיבות טובות

(דרך אגב לא התכוונתי לפונקציה

RANK שהיא גם יותר טובה אבל אפילו יותר טוב עם פונקציות חלון פשוטות של MIN ו MAX (גם אני מייד שלפתי את הפתרון עם RANK ואחרי כמה שניות חזרתי וכתבתי שעצם עדיף עם פוקנציות חלון)
* RANK עדיין מבצע SORT פעמיים בנפרד ולכן היחס יהיה 2-3 ביחס למה שכתבת קודם ואני מחפש 1-3

אני אעלה דוגמה עוד רגע אם כבר העלתי את זה

אני מקווה לעמוד בציפיות שלי LOL

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

 

[pitoach]

בסדר העלתי את השאילתה (כרגע עוד בלי הסברים)

כאן
http://ariely.info/Blog/newForumsFAQ/ForumsFAQ356/tabid/166/language/he-IL/Default.aspx

בעוד כמה דקות (אני כרגע הולך להוסיף) אני אוסיף מעט הערות והסברים על תוכנית ההרצה השונה ועל ההבלים שהביאו לתוצאה

כרגע ניתן לראות 3 שאילתות:
1. בעזרת שאילתה פנימית לוקח 45%
2. בעזרת RANK לוקח 30%
3. בעזרת MIN/MAX לוקח 15%
כאמור ההבדל הגדול הוא מספר ה SORT שמבוצע

 

[pitoach]

העלתי הכל כולל התמונות של תוכניות ההרצה

 

[כלליים]

נחמד מאד

אבל על זה נאמר, שאין ללמד לעלות מדרגות שתיים-שתיים, את מי שעדיין לא עולה אחת-אחת.
מעל-מקום, אני החכמתי מהמאמר..

 

[pitoach]

תודה על התודה

אם מעניין אותך אז יש בלוג חדש

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

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

 

[אליקוד]

תודה על כל התשובות,

תוכל בבקשה לציין באיזו סביבה חקרת את הביצועים של השאילתות שלך וצירפת צילומי מסך בבלוג ?
אני הרצתי את הדוגמה שלי ב- MySQL בגירסה החופשית.
אני כבר לא סטודנט, יש לי נסיון בסיסי ב- SQL
והשאלה שפירסמתי כאן היתה חלק מסידרת שאלות בבחינה לעבודה כבודק תוכנה.

 

[pitoach]

RANK לא קיים ב MYSQL

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

* כששואלים שאלה כדאי תמציק לציין באיזה סיבה אתה עובד

ולגבי השאלה שלך בקצרה:
בשרתי SQL יש מנוע מיטוב שבונה תוכנית הרצה של השאילתה מאחורי הקלעים. הדרך היחידה הנכונה למטב שאילתה זה לבדוק מה קורה מאחורי הקלעים, ז"א לבדוק את תוכנית ההרצה.

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

בקיצור: מדובר בשרת SQL

 

[zalomon]

אכן

ל-MYSQL אין Window functions, וזוהי מגבלה.
ישנו טריק בעזרת GROUP_CONCAT (המנגנון הכי דומה ל- window functions), וזה נראה כך:

Selecting a specific non aggregated column data in GROUP BY