none
אתגר... RRS feed

  • דיון כללי

  • הסתכלתי עכשיו בפוסט של אורן http://www.sqlserver.co.il/?p=2952 שמדבר על GO רב-פעמי.

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

    אז האתגר שלכם הוא כזה - נתונה טבלה עם שמות הישובים בארץ + מספר התושבים.

    http://www.cbs.gov.il/ishuvim/ishuv2008/bycode.xls

    ונתונים הנתונים הבאים על ביזור שמות פרטיים ומשפחה (לא מצאתי קובץ כזה של ישראל אז הנה האמריקאי):

    http://www.census.gov/genealogy/names/names_files.html

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

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

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

    בהצלחה!!!


    • שינה את הסוג Meir Pinto יום ראשון 28 אוגוסט 2011 08:09
    יום שלישי 19 יולי 2011 06:38

כל התגובות

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

    הכוונה שלך שנבצע חיבור של כל האפשרויות הקיימות? ז"א מעין הכפלה קרטזית ללא חזרות של כל שם פרטי עם כל שם משפחה ולהתאים תוצאות לנתונים לסטטיסטיקה שיש שם?

    דרך אגב: לשים את הפתרון כאן או לשלוח :-)?

    טוב בכל מקרה אני נוטה כרגע לפתרון הבא מבחינת לוגיקה:

    שלב 1. לאחר שאבין את לוגיקה של מהיכן לקחת את הנתונים בהנחה שהם רשומים בפורמט פשוט או בקובץ כמו אקסל,טקסט, XML וכו, אז מה שיש לבצע זה לפתח תוכנית קטנה ב C# אשר תבצע קריאה של כל הנתונים וטיוב שלהם לקובץ טקטס פשוט (אני מעריך בעבודה לא ארוכה ב VISUAL STUDIO אחרי שמבינים את הלוגיקה של מקור הנתונים בהנחה שהוא לא מורכב מדי במבנה והטיוב לא מורכב אלא רק קריאת נתונים).

    שלב 2: שימוש פשוט ב BULK INSERT להכניס את כל הנתונים במכה אחת (למשל קובץ טקסט בהפרדת פסיק)

     

    --Bulk Insert from text file
    BULK
    INSERT BulkInsertTest
    FROM 'c:\BulkInsertTest.txt'
    WITH(
      FIELDTERMINATOR
    = ',',
      ROWTERMINATOR = '\n'
    )
    GO

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


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

    *** LOL + OFF
    אם יש לי נתונים של כל תושבי ישראל מעל 18 נכון לתקופת הבחירות האחרונה זה מתאים לביצוע :-) ואם יש לי במקרה גם נתונים של כל התלמידים בישראל שרשומים במשרד החינוך מתקופה שפיתחתי עבורם מערכת מידע, אז השילוב נותן לך את מה שרצית?

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





    יום שלישי 19 יולי 2011 14:12
    מנחה דיון
  • שלום פיתוח.

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

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

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

    רק הביזור צריך להיות בהתאם.

    יש? :-)

    עמי


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--
    יום רביעי 20 יולי 2011 14:26
  • ברור :-)

    הרבה יותר מורכב אם כן ממה שחשבתי קודם

    תרגיל נחמד (עכשיו השאלה אם יהיה כוח להגיע לזה כי קודם חשבתי על משהו כמו 15 דקות עבודה כולל פתיחת תוכנת ה VS)


    יום רביעי 20 יולי 2011 15:45
    מנחה דיון
  • לאן לשלוח את הפתרון ?
    שבת 23 יולי 2011 10:03
  • כאן בפורום נראה לי מקום מצויין.

    כך יוכלו כולם ללמוד \ לבדוק \ להעיר \ לשאול \ ...


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--
    שבת 23 יולי 2011 13:07
  • אוקי .. קבל והרץ 

    התגובה נערכה על ידי עמי לוין:

     =================

    מאות שורות הקוד נמחקו...

    =================

    היי עופר,

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

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

    תודה,

    עמי.

    • נערך על-ידי Ami Levin יום שלישי 26 יולי 2011 05:57 צורף קוד ארוך מאוד כטקסט
    יום ראשון 24 יולי 2011 11:46
  • אהלן עופר...

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

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

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

    ** למעשה אפשר היה לבצע את כל התרגיל בקובץ אקסל עם מעט נוסחאות ואפילו בלי צורך בכתיבת קוד VBA אם לא היה צורך במליון רשומות אלא למשל 64K רשומות. לא שזה היה הדרך שהייתי בוחר... זו רק נקודה OFF

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

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

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

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

    יום ראשון 24 יולי 2011 15:12
    מנחה דיון
  • שלום רב

    ראשית תודה רבה על התגובה זה מאד נחמד 

    לגבי מורכבות ומשך זמן הפיתוח.. 

    משך הזמן שישבתי על הפתרון הוא בערך הזמן שישבת וחשבת מה לכתוב לי.

    לגבי הפתרון הוא פשוט ביותר מכמה סיבות 

    הוא מאפשר לכולם לבדוק את הפתרון , פשוט לקחת את הסקריפט ולהריץ.

    לגבי שורות כפולות ה union מאחד שורות כפולות .

    לא רציתי לבנות ssis או פקודות bulk insert או לכתוב משהו ב vba ולהשתמש בכמה קבצים בחידה יש עוד מקור של נתונים אז זה לא רק bulk אחד .

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

     

    יום ראשון 24 יולי 2011 19:37
  • אני מזמין את כל אוהבי SQL להיכנס ולתמוך בשאלות של אנשים בפורומים השונים. זה גם ייתן לך את הכיף של עבודה ב SQL מעבר לשעות העבודה וזה גם נותן סיפוק כשאכן אתה מצליח לפתור בעיות ואתגרים אמיתיים מהחיים ולא לשם פרסים (אם עולים מפעם לפעם) וכמובן העיקר זה העובדה שאתה עוזר לאנשים שצריכים עזרה.
    אשמח לראות אותך מצתרף לכוחות התומכים בהתנדבות...אני ממתין לראות אותך :-) להתראות בפורומים השונים (אם אתה רוצה רשימה של פורומים פעילים אשמח לספק).

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

    ייתכן שעוד יהיה לי כוח לשים קוד אבל לא נראה לי כרגע :-)

    בכל מקרה אם זה עוזר למישהו אז... ייבוא הנתונים יכול להעשות בדרכים נוספות פחות יעילות מ Bulk Insert ועדיין להעשות בצורה של פקודה אחת. ניתן לייבא את הנתונים ישירות מהמיקום שלהם באינטרנט ולצרף את הקוד של השאילתה כך שמי שיבצע העתק/הדבק יוכל להריץ את זה בלי לעשות כלום.

    שיטות נוספות לייבוא את הנתונים:

    • openrowset
    • bcp - ככל הנראה הדרך שהייתי בוחר בה מפני שמאפשר עבודה קלה עם עברית + מבצע למעשה bulk insert
    • linked server

    ועוד


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

    מקור האתגר היה בכלל בנושא הכנסת הנתונים ופתרון יעיל יותר משימוש ב GO XXX, אם הבנתי נכון: "הסתכלתי עכשיו בפוסט של אורן http://www.sqlserver.co.il/?p=2952 שמדבר על GO רב-פעמי."



    יום שני 25 יולי 2011 04:08
    מנחה דיון
  • היי עופר,

    נאלצתי למחוק את תגובתך הארוכה - ראה למטה.

    אשמח אם תצרף שוב הפתרון כקובץ או בלינק.

    עמי


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--
    יום שלישי 26 יולי 2011 05:58
  • צודק ..עמי 

     

    זה הקישור לכל המעוניין 

    http://www.upf.co.il/file/191161926.html

    זה קובץ sql פשוט עם כל הקוד.

     

    עופר

     

    יום שלישי 26 יולי 2011 07:32
  • באיחור אופנתי, אבל רק עכשיו היה לי זמן לשחק עם זה...

    http://www.filedropper.com/createdata

    בגדול קובץ ה-setup יוצר DB, יוצר את הטבלאות של הפיזור הסטטיסטי, וטוען אותן.

    קיצצתי קצת את הנתונים - אני מקווה שתסלחו לי :)

    הקובץ createdata עושה את העבודה.

    הסיפור די פשוט:

    בכל הטבלאות של הפיזור הסטטיסטי, f הוא האחוז של אותו מופע מכלל האוכלוסייה.

    בזמן הטעינה הכפלתי את האחוז הזה בקבוע בכדי לפשט את הקוד בהמשך.

    לדוגמה, אם יש לנו מופעים של a,b,c

    a 0.5

    b 0.3

    c 0.2

    אני ביצעתי טעינה של:

    a 5

    b 3

    c 2

     

    כך אני יכול לבצע את ה non-equal join:

     FN.f >= i כאשר הטבלה i מכילה מספרים רצים.

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

    התוצאה היא סט שמכיל 5 שורות ל a, שלוש שורות ל b, ושתי שורות ל c.

    השלב הבא מנפח את הסט המצומצם (שמכיל את ההתפלגות הסטטיסטית הרצויה) לגודל הסט הנדרש בעזרת cross join מול טבלת i.

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

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

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

    יום חמישי 08 ספטמבר 2011 17:02