en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
אוג16

Written by: ronen ariely
16/08/2011 20:44 RssIcon

שליפת רשומות בסדר אקראי

 

הקדמה:

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

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

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

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

רקע: הכנה של טבלה עם נתונים לצורך ההדגמות

על מנת שנוכל לדון בדוגמאות זהות ולאפשר לקורא להריץ את הדוגמאות כאן נכין לעצמינו טבלה פשוטה עליה נריץ את הדוגמאות

CREATE TABLE [MyTbl](
      [id] [int] NOT NULL
) ON [PRIMARY]
GO

נכניס כמה נתונים לטבלה שלנו על מנת שנוכל להציג תוצאות השאילתות

insert [MyTbl] ([id]) values (2)
insert [MyTbl] ([id]) values (4)
insert [MyTbl] ([id]) values (55)
insert [MyTbl] ([id]) values (1)
insert [MyTbl] ([id]) values (3)
insert [MyTbl] ([id]) values (21)
Go

נבדוק את כל הנתונים שיש לנו כרגע בטבלה

select * from [MyTbl]
GO

 

דגמה למורכבות של הבעיה בשרת SQL

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

WITH RndID (RndId) AS (SELECT RAND())
SELECT [id],RndID.RndId
FROM [MyTbl],RndID
order by RndID.RndId
GO

או אף בצורה פשוטה יותר:

SELECT [id],RAND() as rnd
FROM [MyTbl]
order by rnd
GO

הרצת השאילתה על מסד נתונים מסוג SQL יגרור את התוצאות הבאות למשל:

1   0.968695835070872
2   0.968695835070872
3   0.968695835070872
4   0.968695835070872
21  0.968695835070872
55  0.968695835070872
55  0.968695835070872

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

על מנת לבצע סידור של הנתונים עלינו להגיע למצב בו יש לנו עמודה עם נתונים שונים.

דוגמאות בשרתים ומסדי נתונים שונים

יש הרבה דרכים כדי לבחור רשומה\שורה אקראית מטבלת מסד נתונים. הנה כמה למשל במשפטי SQL שאינם דורשים לוגיקת יישום נוסף, אבל כל שרת מסד הנתונים דורש תחביר SQL שונה.

MYSQL:
SELECT id FROM MyTbl
ORDER BY RAND()
 
POSTGRESQL:
SELECT id FROM MyTbl
ORDER BY RANDOM()
 
MICROSOFT SQL SERVER:
SELECT id FROM MyTbl
ORDER BY NEWID()
 
SELECT [id],NEWID() AS [RandomNumber] FROM [MyTbl]
order by [RandomNumber]
 
-- הוספת ערך שלם רנדומלי
ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber]
 
IBM DB2
SELECT id, RAND() as MyRand FROM MyTbl
ORDER BY MyRand
 
ORACLE:
SELECT id FROM MyTbl ORDER BY dbms_random.value
 
ACCESS:
Select id,rnd(id) from MyTbl
order by rnd(id)
 
--ליתר ביטחון למנוע בעיה של זכירת תוצאות קודמות
Select id ,rnd(id),datepart("s",now()) ,CDbl(Now()) ,rnd(1000*id*CDbl(now())) from MyTbl
order by rnd(1000*id*CDbl(now()))
 
SELECT A RANDOM RECORD (MORE WAYS):
-- works fine as it is in SQL server. This logic will work in any other database after query matching
SELECT TOP 10
       [id]
       , RAND(
              -- כאן מגיע ערך מספרי הקובע את תוצאת הרנדום עבור הסשן הנוכחי
              --כל שימוש באותו ערך פנימי יביא את אותם תוצאות
              -- הדבר חשוב למשל כדי שנוכל להמשיך להישתמש בערך הרנדומלי שלנו
              --בהמשך השאילתה בלי שצריך להכניס אותו למשתנה
              1000        
              -- לכן אם הערך בתוך הרנדום משתנה על פי מספר הרשומה
              -- הרי שנקבל בכל רשומה בחירת מספר רנדומלי אחר
              *[id]
              --     ללא חלק זה אכן ייבחר ערך חדש בכל רשומה
              --     אבל הוא יהיה קבוע בכל פעם שנריץ את השאילתה
              --     מכיוון שהשרת שומר תוצאות שאילתות בקש
              --     לכן פשוט יש להוסיף פרמטר ברנדום שמשתנה בכל הרצה
              --     למשל הזמן הנוכחי
              *DATEPART(millisecond, GETDATE())
              ) as MyRnd
    FROM [MyTbl]
    ORDER BY RAND(
              1000*[id]
              *DATEPART(millisecond, GETDATE())
              )
Go

 

Tags: SQL , Random
Categories: SQL