חיפוש אלמנטים בטבלה יחד עם קיום מספר תנאים של תכונה (Attribute)

Question

היי,

יש 3 טבלאות:

tblWords - ID, name

tblFiles - ID, name

tblWordsAndFiles - word_id, file_id

 

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

או לדוגמה, ליצור שאילתה שתמצא את כל המסמכים כך שבאותו מסמך יש את המילה "hello" וגם "word"  או   "apple".

 

אשמח לעזרה

Answer 1

שלום ירון,

אנא עבור על ההסבר בקישור הבא:
http://ariely.info/Blog/tabid/83/EntryId/70/Data-Definition-Language-DDL-and-Data-Manipulation-Language-DML.aspx

לאחר קריאת הקישור תוכל להבין מה זה DDL+DML ותוכל לספק לנו את המידע שאנחנו צריכים על מנת לעזור לך :-)
ז"א שאילתות ליציאת הטלאות הרלוונטיות ולהכנסת כמה נתונים לדוגמה, עליהם נוכל לסחק ולערוך את הדיון.

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

אני מאוד ממליץ לעבור על כל המדריך הבא. כל הלימוד של שפת SQL לא צריך לקחת יותר מיומיים שלוש:http://www.w3schools.com/sql/
אני מאוד ממלי לא לקפוץ רק לפתרון שאתה מחפש. זה מאוד יפגע בך בעתיד אם לא יהיה לך בסיס מינימלי של שפת SQL. אבל הפתרון שלך כמובן נמצא בשיעור על הפילטרים (אם הבנתי אותך נכון): http://www.w3schools.com/sql/sql_where.asp

* שים לב שיש שם גם אפשרות לתרגל און-ליין.

אני מקווה שזה נות לך את מה שאתה מחפש. אם עדיין אתה מתקשה או שלא הבנתי אותך נכון, אז אנא צריף לנו DDL+DML ונסה להבהיר את הנושא שוב :-)

 ברוך הבא לפורום דרך אגב, וחג שמח

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]

Answer 2

אנסה להסביר בצורה ברורה יותר.

יש לי טבלה בשם tblWords שהיא מכילה את כל המילים בעולם (לכאורה).

יש לי טבלה בשם tblFiles שהיא מכילה את כל קבצי הטקסט שלי.

ויש טבלה נוספת שנקראת tblWordsAndFiles שהיא מכילה רשומה של כל מילה בקובץ הטקסט הספציפי.

לדוגמה:

tblWords
****************
ID  |  name
****************
1   |  hello
2   |  world
3   |  something
****************

tblFiles
****************
ID  |  name
****************
1    |  myfile.txt
2    |  file.txt
****************


tblWordsAndFiles
*********************
word_id   |   file_id
1              |   1
2              |   1
2              |   2
3              |   2

אז בדוגמה זו לקובץ myfile.txt מכיל את המילה hello וגם מכיל את המילה world, והוא אינו מכיל את המילה something.
והקובץ file.txt מכיל את המילה world וגם את המילה something ואינו מכיל את המילה hello.

אני רוצה לכתוב 2 שאילתות:

1. שאילתה שתמצא את כל המסמכים שמכילים את המילה hello וגם את המילה world.
(במקרה הזה הקבצים שהיו צריכים לחזור הוא רק myfile.txt)

2. שאילתה שתמצא את כל המסמכים שמכילים את המילה hello וגם את המילה world  או שיש בה את המילה something.
(במקרה הזה הקבצים שהיו צריכים לחזור הם myfile.txt, file.txt)

אי אפשר לפתור את זה עם where פשוט. תנסה זה לא יעבוד...
גם join לא עזר כאן.

אשמח לעזרה

Answer 3

ההסבר הרבה יותר ברור כרגע, אבל לא ניראה לי שעברת על הקישור ששמתי לך מעל :-(

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

2 שאילתות A ו B בשני מסדי נתונים XX ו YY יכולות לפעול באופן שונה לחלוטין. יכול להיות בקלות מצב שבשרתצ אחד עדיפה שאילתה A ובשרת שני עדיפה שאילתה B. למשל אינדקס בטבלה יכול לשנות את כל הסיפור.

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

לעצם השאלה הראשונה, תבדוק אם זה מה שאתה מחפש :-)

/***************************************  DDL - queries to create the tables! */
-- Don't give us stories :-( but simple DDL code

CREATE TABLE tblWords(
	ID       INT Primary key,
	name     NVARCHAR(100)
)
GO

CREATE TABLE tblFiles(
	ID       INT Primary key,
	name     NVARCHAR(100)
)
GO

-- This is the relations table!
-- you can see I use FOREIGN KEY with REFERENCES to the main tbles
CREATE TABLE tblWordsAndFiles(
	word_id       INT FOREIGN KEY REFERENCES tblWords(ID),
	[file_id]     INT FOREIGN KEY REFERENCES tblFiles(ID) -- This is very bad name for column! This is a reseve name
)
GO

/***************************************  DML - queries to insert some sample data */
-- Again, instead of stories we need code to insert the sample data
insert tblWords (ID,name)
values
(1, 'hello'),
(2, 'world'),
(3, 'something')
GO

insert tblFiles (ID,name)
values
(1, 'myfile.txt'),
(2, 'file.txt')
GO

insert tblWordsAndFiles (word_id,[file_id])
values
(1, 1),
(2, 1),
(2, 2),
(3, 2)
GO

select * from tblWordsAndFiles
select * from tblWords
select * from tblFiles
GO

/*************************************** first option for solution to the question */
;with MyCTE as (
	select --*, 
		tblWords.ID, ROW_NUMBER() over (partition by tblWordsAndFiles.[file_id] order by tblWords.name) RN
	from tblWords
	left join tblWordsAndFiles on tblWordsAndFiles.word_id = tblWords.ID
	where tblWords.name = 'hello' or tblWords.name = 'world'
)
select * 
from tblFiles
where tblFiles.ID in (
	select MyCTE.ID from MyCTE where RN = 2
)
GO

/***************************************  clean our database */
DROP TABLE tblWordsAndFiles
DROP TABLE tblWords
DROP TABLE tblFiles

  

אני מקווה שזה עוזר לך.

* אני עדיין מציע לפרסם את ה DDL שלך אם אתה רוצה שאילתה מיטבית ולא רק "שאילתה עובדת" :-)

** אני ממליץ לחשוב אם השימוש ב FTS יכול להתאים לכם במקום להתאמץ להכין טבלת מילים.

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]

Answer 4

הנה פתרון נוסף שונה לחלוטין :-)

כאמור יכולים להיות מאות פתרונות שונים

/*************************************** Second option for solution to the question */
select --*,
	FName
from (
	select --WF.file_id, WF.word_id, 
		F.name as FName, W.name as WName
	from tblWordsAndFiles WF
	left JOIN tblWords W on WF.word_id = W.ID
	left join tblFiles F on WF.file_id = F.ID
) as T 
pivot(
	max(WName)
	for WName in ([hello],[world],[something])
)P
where 
	(not [hello] is null and not [world] is null) -- this filter you need for both questions
	or (not [something] is null)-- question 2 need to add this filter
GO

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

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]

Answer 5

אם יש לנו CLUSTERED אינדקס על טבלת tblWordsAndFiles (מומלץ מאוד לחשוב על להוסיף אותו אם הוא לא קיים)

create CLUSTERED INDEX IX_tblWordsAndFiles_fileId
    ON tblWordsAndFiles([file_id]);
GO

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

;with MyCTE as (
	select 
		tblWords.ID, RN
	from tblWords
	left join (select *, ROW_NUMBER() over (partition by [file_id] order by [file_id]) RN from tblWordsAndFiles) as tblWordsAndFiles
		on tblWordsAndFiles.word_id = tblWords.ID
	where tblWords.name = 'hello' or tblWords.name = 'world'
)
select * 
from tblFiles
where tblFiles.ID in (
	select MyCTE.ID from MyCTE where RN = 2
)
GO

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

* כאמור הבסיס לפתרון נכון הוא לקבל את הנתונים הנכונים כולל DDL :-)

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

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

where words like '%hello%' and words like '%word%'

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

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

או בקוד זה יקראה כך:

where words + ',' like '%,hello,%' and words + ',' like '%,world,%'

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

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

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

with allDocsAndWords as
(
	select 
		F.ID as FID,F.name as FName, W.name as WName
	from tblFiles f, tblWords w, tblWordsAndFiles fw
	where	f.ID = fw.file_id and w.ID = fw.word_id
),
allDocsAndWordsFlat as
(
SELECT
    FID,(
        SELECT ',' + WName
        FROM allDocsAndWords
		where FID = adaw.FID
        FOR XML PATH ('')
    ) AS words
FROM allDocsAndWords adaw
GROUP BY FID
)
select *
from allDocsAndWordsFlat
where words + ',' like '%,hello,%' and words + ',' like '%,world,%'
GO

עתה בואו נראה כמה נתונים מעניינים על יעילות ארבעת הפתרונות שהוצגו כאן על ידי בדיקה כמות IO. חשוב לציין בבדיקות מיטוב נתונים כגון גרסת השרת וה DDL+DML בו עשינו שימוש. גרסת השרת שאני בודק בו היא 2014 וה DDL+DML כפי שהצגתי מעל (ללא אינדקסים).

פתרון ראשון:

(1 row(s) affected)
Table 'tblWordsAndFiles'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblWords'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblFiles'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 
פתרון שני

(1 row(s) affected)
Table 'tblFiles'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblWords'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblWordsAndFiles'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 
פתרון שלישי (הפתרון שאני מציגע בראש ההודעה הנוכחית)

(1 row(s) affected)
Table 'tblWordsAndFiles'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblWords'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblFiles'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 
פתרון רביעי (מבוסס על שימוש ב XML וחיבור הנתונים שלרשרת ארוכה)

(1 row(s) affected)
Table 'tblWords'. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblWordsAndFiles'. Scan count 3, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblFiles'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]

Answer 6

שלום ירון,

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

תודה!
-----------------------------
גיא גלנצר
יועץ ומדריך SQL Server
http://www.madeiradata.com