none
שאילתא מורכבת RRS feed

  • שאלה

  • Hi all !
    i have the following table structure :
    id(Identity)  time  action
    1              10:00   1
    2              10:05   1
    3              10:10   2
    4              10:15   2
    5              10:20   2
    7              10:30   1
    8              10:35   1
    9              10:40   1
    10            10:45   1
    11            10:50   2
    12            10:55   2
    13            11:00   2
    14            11:05   1
    15            11:10   1
    i need for each group with action id 2 (column 'action')
    to find time difference. the result set should look like the following:
    action id       timediff
        2             15min
        2             10min
    (the logic should be : calculate time diff between find the last action id 2 and the first action id 2 for each group of action id 2.)

    id(Identity)    time       action
    1                 10:00         1
    2                 10:05         1
    3                 10:10         2 * from
    4                 10:15         2
    5                 10:20         2 * to
    7                 10:30         1

    i need to do it without any loops or cursors , i it possible , i will appreciate any help , tnx .   Sharon
    <//span>

    יום שני 16 אפריל 2012 07:25

תשובות

  • היי שרון,

    זה אמור לעזור לך!

    ותודה רבה לגיא גלנצר על הטיפים!

    חקיקת יוסי

    SELECT ActionId, DATEDIFF(MINUTE,TMP2.ActionMinTime, TMP2.ActionMaxTime) AS Diff FROM
    (
    SELECT MIN(SomeTime) AS ActionMinTime , MAX(SomeTime) AS ActionMaxTime,ActionId
    
    
    FROM 
    (
    SELECT ROW_NUMBER() OVER (ORDER BY Id) AS RN2,
    		 ROW_NUMBER() OVER (PARTITION BY ActionId ORDER BY Id ) AS RN,
    		 ROW_NUMBER() OVER (ORDER BY Id) - ROW_NUMBER() OVER (PARTITION BY ActionId ORDER BY Id ) AS Dif ,
    		 ActionId,
    		 SomeTime
    FROM	
    	#TimeAction
    
     ) AS Tmp
    
    GROUP BY Dif,ActionId
    ) AS 
    TMP2
    

    • סומן כתשובה על-ידי sharonof יום שני 16 אפריל 2012 13:25
    יום שני 16 אפריל 2012 08:28
  • היי שרון,

    אוליי אני לא מבין משהו אבל יכול להיות שיש לך טעות בתוצאה שכתבת שאתה מצפה לה ?

    השתי קבוצות שקיימות הן :

    קבוצה ראשונה בעלת
    3              10:10   2
    4              10:15   2
    5              10:20   2

    קבוצה שניה בעלת
    11            10:50   2
    12            10:55   2
    13            11:00   2

    בקבוצה הראשונה סכ"ה הזמן בין הרשומה הראשונה לאחרונה הוא - 10 דקות.
    בקבוצה השניה סכ"ה הזמן בין הרשומה הראשונה לאחרונה הוא גם כן - 10 דקות.

    אם הבנתי נכון את השליפה שאתה מחפש למה בתוצאות רשמת שאתה מצפה ל-
    action id       timediff
        2             15min
        2             10min

    אני לא הבנתי את מה שאתה רוצה נכון יכול להיות, תוכל להסביר ?



    חיים פישנר.

    • הוצע כתשובה על-ידי haim fishner יום שני 16 אפריל 2012 21:21
    • סומן כתשובה על-ידי sharonof יום שלישי 24 אפריל 2012 12:52
    יום שני 16 אפריל 2012 19:55
  • במידה והבנתי אותך נכון וגם לפי התשובה של יוסי חקיקת ואני מצטרף לתרומה הקטנה של גרי ולשפיציות של יוסי וגיא רק אשנה את הOrder by במקום על הId אלא על הTime
    בתכלס זאת החלטה שלך על פי מה עדיף לך למיין, משום מה Time מרגיש לי תמיד יותר אמין למיין על פיו, אבל זה ממש לא בהכרח.
    ואשים גם את הDDL + DML..

    הכנסתי את כל ה24 שעות עם בהפרש של 5 דקות קבוע, וכל שורה מקבלת AcationID משתנה (1 או 2) בהסתמך על זה שהזמן שלך הוא מטיפוס time(0)f ובהפרש קבוע
    אז השליפה גם לא אמורה להזיז לך, כי היא תתבצע במהירות על סכ"ה 288 רשומות.

    הינה הסקריפט:

    use [tempdb] go Create Table MyTbl(ID Int identity,[Time] time(0),[Action] Int); set nocount on; declare @a time(0) = '00:00:00'; declare @i int = 0; while @i < 24*60/5 begin insert into MyTbl select DATEADD(minute, @i*5, @a), ABS(CHECKSUM(newid()))%2 + 1 set @i += 1; end
    GO With T As ( Select [Time], [Action], ROW_NUMBER() OVER(order by [Time]) ranko, ROW_NUMBER() OVER(partition by [Action] order by [Time]) franko, ROW_NUMBER() OVER(order by [Time]) - ROW_NUMBER() OVER(partition by [Action] order by [Time]) defrakno From MyTbl --order by [Time] ) Select T.[Action], MIN(T.[Time]), MAX(T.[Time]), DATEDIFF(MINUTE, MIN(T.[Time]), MAX(T.[Time])) DateDiffrance From T where [Action] = 2 group by T.[Action], T.defrakno

    שים לב

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

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

    create index IX_Time on MyTbl (time, action)

    אבל שוב כל זה במידה ובאמת הבנתי אותך נכון וטעית בתוצאה שכתבת שאתה מצפה לה (10, 15) במקום (10, 10).


    חיים פישנר.



    • נערך על-ידי haim fishner יום שני 16 אפריל 2012 20:46 מחסור בGO ;)
    • הוצע כתשובה על-ידי haim fishner יום שלישי 17 אפריל 2012 18:27
    • סומן כתשובה על-ידי sharonof יום שלישי 24 אפריל 2012 12:52
    יום שני 16 אפריל 2012 20:41
  • אני לא מצליח להבין למה את החלק הזה צריך לבצע זה שעוזר ולא זה ששואל את השאלה. מה גם שיש לו את הטבלאות והוא יכול ב 2 לחיצות עכבר לייצר את השאילתה המדוייקת בכמה שניות

    בכל מקרה כל הכבוד על המאמץ :-)

    אני בדרך כלל לא ניגש בכלל לבעיה מסוג "כתיבת שאילתה מיטבית" או דיון לעומק בשאילתה בלי שיש לי DDL+DML כדי לשחזר את הבעיה. ואני חושב ששואל השאלה יכול להשקיע מעטו כדי לעזור לנו לעזור לו

    • נערך על-ידי pituachMVP, Editor יום שלישי 17 אפריל 2012 05:08
    • סומן כתשובה על-ידי sharonof יום שלישי 24 אפריל 2012 12:52
    יום שלישי 17 אפריל 2012 05:07
    מנחה דיון
  • ואני שוב מבקש שמעט תעזור לנו לעזור לך

    אנא הוסף DDL+DML ותחסוך מאיתנו את החלק הזה


    signature

    • סומן כתשובה על-ידי sharonof יום שלישי 24 אפריל 2012 12:52
    יום שני 16 אפריל 2012 08:18
    מנחה דיון
  • תודה רבה

    זה גאוני :) לפחות בשבילי

    שרון

    • סומן כתשובה על-ידי sharonof יום שלישי 24 אפריל 2012 12:52
    יום שני 16 אפריל 2012 13:25
  • היי שרון,

    שמחתי לעזור!

    המשך יום נעים,

    חקיקת יוסי

    • סומן כתשובה על-ידי sharonof יום שלישי 24 אפריל 2012 12:52
    יום שני 16 אפריל 2012 14:44

כל התגובות

  • ואני שוב מבקש שמעט תעזור לנו לעזור לך

    אנא הוסף DDL+DML ותחסוך מאיתנו את החלק הזה


    signature

    • סומן כתשובה על-ידי sharonof יום שלישי 24 אפריל 2012 12:52
    יום שני 16 אפריל 2012 08:18
    מנחה דיון
  • היי שרון,

    זה אמור לעזור לך!

    ותודה רבה לגיא גלנצר על הטיפים!

    חקיקת יוסי

    SELECT ActionId, DATEDIFF(MINUTE,TMP2.ActionMinTime, TMP2.ActionMaxTime) AS Diff FROM
    (
    SELECT MIN(SomeTime) AS ActionMinTime , MAX(SomeTime) AS ActionMaxTime,ActionId
    
    
    FROM 
    (
    SELECT ROW_NUMBER() OVER (ORDER BY Id) AS RN2,
    		 ROW_NUMBER() OVER (PARTITION BY ActionId ORDER BY Id ) AS RN,
    		 ROW_NUMBER() OVER (ORDER BY Id) - ROW_NUMBER() OVER (PARTITION BY ActionId ORDER BY Id ) AS Dif ,
    		 ActionId,
    		 SomeTime
    FROM	
    	#TimeAction
    
     ) AS Tmp
    
    GROUP BY Dif,ActionId
    ) AS 
    TMP2
    

    • סומן כתשובה על-ידי sharonof יום שני 16 אפריל 2012 13:25
    יום שני 16 אפריל 2012 08:28
  • תודה רבה

    זה גאוני :) לפחות בשבילי

    שרון

    • סומן כתשובה על-ידי sharonof יום שלישי 24 אפריל 2012 12:52
    יום שני 16 אפריל 2012 13:25
  • היי שרון,

    שמחתי לעזור!

    המשך יום נעים,

    חקיקת יוסי

    • סומן כתשובה על-ידי sharonof יום שלישי 24 אפריל 2012 12:52
    יום שני 16 אפריל 2012 14:44
  • זו תרומתי הצנועה (מצרף את ה-DDL לנוחות מי שרוצה לנסות):

    Use tempdb;

    Create Table MyTbl(ID Int,
    [Time] Time,
    Action Int);
    Insert Into MyTbl
    Values
    (2,'10:05',1),
    (3,'10:10', 2), 
    (4,'10:15',2),
    (5,'10:20',2),
    (7,'10:30',1),
    (8,'10:35',1),
    (9,'10:40',1),
    (10,'10:45',1),
    (11,'10:50',2),
    (12,'10:55',2),
    (13,'11:00',2),
    (14,'11:05',1),
    (15,'11:10',1);

    With T As
    (Select ID-Row_Number() Over(Order By [Time]) Kvuza,
    *
    From MyTbl
    Where Action=2)
    Select DateDiff(Minute,Min([Time]),Max([Time])) [Diff]
    From T
    Group By Kvuza;


    Geri Reshef http://gerireshef.wordpress.com

    • הוצע כתשובה על-ידי haim fishner יום שני 16 אפריל 2012 20:43
    יום שני 16 אפריל 2012 19:13
  • היי שרון,

    אוליי אני לא מבין משהו אבל יכול להיות שיש לך טעות בתוצאה שכתבת שאתה מצפה לה ?

    השתי קבוצות שקיימות הן :

    קבוצה ראשונה בעלת
    3              10:10   2
    4              10:15   2
    5              10:20   2

    קבוצה שניה בעלת
    11            10:50   2
    12            10:55   2
    13            11:00   2

    בקבוצה הראשונה סכ"ה הזמן בין הרשומה הראשונה לאחרונה הוא - 10 דקות.
    בקבוצה השניה סכ"ה הזמן בין הרשומה הראשונה לאחרונה הוא גם כן - 10 דקות.

    אם הבנתי נכון את השליפה שאתה מחפש למה בתוצאות רשמת שאתה מצפה ל-
    action id       timediff
        2             15min
        2             10min

    אני לא הבנתי את מה שאתה רוצה נכון יכול להיות, תוכל להסביר ?



    חיים פישנר.

    • הוצע כתשובה על-ידי haim fishner יום שני 16 אפריל 2012 21:21
    • סומן כתשובה על-ידי sharonof יום שלישי 24 אפריל 2012 12:52
    יום שני 16 אפריל 2012 19:55
  • במידה והבנתי אותך נכון וגם לפי התשובה של יוסי חקיקת ואני מצטרף לתרומה הקטנה של גרי ולשפיציות של יוסי וגיא רק אשנה את הOrder by במקום על הId אלא על הTime
    בתכלס זאת החלטה שלך על פי מה עדיף לך למיין, משום מה Time מרגיש לי תמיד יותר אמין למיין על פיו, אבל זה ממש לא בהכרח.
    ואשים גם את הDDL + DML..

    הכנסתי את כל ה24 שעות עם בהפרש של 5 דקות קבוע, וכל שורה מקבלת AcationID משתנה (1 או 2) בהסתמך על זה שהזמן שלך הוא מטיפוס time(0)f ובהפרש קבוע
    אז השליפה גם לא אמורה להזיז לך, כי היא תתבצע במהירות על סכ"ה 288 רשומות.

    הינה הסקריפט:

    use [tempdb] go Create Table MyTbl(ID Int identity,[Time] time(0),[Action] Int); set nocount on; declare @a time(0) = '00:00:00'; declare @i int = 0; while @i < 24*60/5 begin insert into MyTbl select DATEADD(minute, @i*5, @a), ABS(CHECKSUM(newid()))%2 + 1 set @i += 1; end
    GO With T As ( Select [Time], [Action], ROW_NUMBER() OVER(order by [Time]) ranko, ROW_NUMBER() OVER(partition by [Action] order by [Time]) franko, ROW_NUMBER() OVER(order by [Time]) - ROW_NUMBER() OVER(partition by [Action] order by [Time]) defrakno From MyTbl --order by [Time] ) Select T.[Action], MIN(T.[Time]), MAX(T.[Time]), DATEDIFF(MINUTE, MIN(T.[Time]), MAX(T.[Time])) DateDiffrance From T where [Action] = 2 group by T.[Action], T.defrakno

    שים לב

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

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

    create index IX_Time on MyTbl (time, action)

    אבל שוב כל זה במידה ובאמת הבנתי אותך נכון וטעית בתוצאה שכתבת שאתה מצפה לה (10, 15) במקום (10, 10).


    חיים פישנר.



    • נערך על-ידי haim fishner יום שני 16 אפריל 2012 20:46 מחסור בGO ;)
    • הוצע כתשובה על-ידי haim fishner יום שלישי 17 אפריל 2012 18:27
    • סומן כתשובה על-ידי sharonof יום שלישי 24 אפריל 2012 12:52
    יום שני 16 אפריל 2012 20:41
  • אני לא מצליח להבין למה את החלק הזה צריך לבצע זה שעוזר ולא זה ששואל את השאלה. מה גם שיש לו את הטבלאות והוא יכול ב 2 לחיצות עכבר לייצר את השאילתה המדוייקת בכמה שניות

    בכל מקרה כל הכבוד על המאמץ :-)

    אני בדרך כלל לא ניגש בכלל לבעיה מסוג "כתיבת שאילתה מיטבית" או דיון לעומק בשאילתה בלי שיש לי DDL+DML כדי לשחזר את הבעיה. ואני חושב ששואל השאלה יכול להשקיע מעטו כדי לעזור לנו לעזור לו

    • נערך על-ידי pituachMVP, Editor יום שלישי 17 אפריל 2012 05:08
    • סומן כתשובה על-ידי sharonof יום שלישי 24 אפריל 2012 12:52
    יום שלישי 17 אפריל 2012 05:07
    מנחה דיון