בדיקת רציפות ערכים

בדיקת רציפות ערכים

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

בבנק מס’ 1 ניתן לראות כי הכספומטים עבדו באופן רציף מ 08:30 בבוקר עד 09:55
בבנק מס’ 2 ניתן לראות כי מ 09:20 ועד 09:25 הכספומטים עבדו באופן רציף, לאחר מכן היתה הפסקה, ואז המשך עבודתן הרציפה היתה מ 09:30 ועד 09:55, שוב היתה הפסקה ואז המשך עבודתן הרציפה היתה מ 10:30 ועד 11:55.
בבנק מס’ 3 ניתן לראות כי הכספומטים עבדו באופן רציף מ 08:00 ועד 09:50, לאחר מכן היתה הפסקה, ואז המשך עבודתן הרציפה היתה מ 11:20 ועד 12:15


CREATE TABLE time_table
(bank_id int,
 atm_id int,
 strt_date datetime,
 end_date datetime)

image

 

כתבו שאילתה אשר מחזירה את פרקי הזמן הרציפים בהם עבדו הכספומטים בבנקים השונים. לבנק מס’ 1 השאילתא אמורה להחזיר שורה אחת – 08:30-09:55, לבנק מס’ 2 שלוש שורות – 09:20-09:25, 09:30-09:55, 10:30-11:55 ולבנק מס’ 3 שתי שורות – 08:00-09:50 ו – 11:20-12:50.

 

image

18 תגובות

  1. עומר יעקבי
    עומר יעקבי ב13/04/2015 ב15:05

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

    אשמח לראות את תשובתך לחידה בשביל לראות אם יש דרך טובה יותר לממש את זה ב sql משאני כתבתי 🙂

    תודה

    השאר תגובה
    • ramkedem
      ramkedem ב13/04/2015 ב22:10

      היי עומר,

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

       -- part 1, create a view to identify the contiguousy
 CREATE VIEW contiguous_vu
 AS
 SELECT bank_id, strt_date, 
              ISNULL(end_date, '9999-12-31') end_date,
              CASE
              WHEN strt_date > MAX(ISNULL(end_date, '9999-12-31'))
			                   over(PARTITION BY bank_id ORDER BY strt_date 
                                    ROWS BETWEEN UNBOUNDED PRECEDING
                                    AND 1 preceding)
              THEN 1
              END contiguous_identifier
              FROM time_table
-- part 2, group the different parts 
SELECT bank_id, MIN(strt_date), MAX(end_date)
FROM
	(	SELECT	bank_id, strt_date, end_date, contiguous_identifier ,
		SUM(contiguous_identifier) over(PARTITION BY bank_id ORDER BY strt_date) contiguousy
		FROM contiguous_vu
	)	TAB 
GROUP BY bank_id, contiguousy
ORDER BY 1, 2
      השאר תגובה
      • עומר יעקבי
        עומר יעקבי ב14/04/2015 ב17:55

        היי רם,
        תשובה מצוינת, אהבתי מאוד 🙂

        2 השגות יש לי:
        1. יצירת view לשם ביצוע שאילתא הוא מיותר ואף מזיק, בהינתן שהקוד אינו ניתן להרצה חוזרת בלי drop, יכול להיות שכבר יש אובייקט בשם כזה ב database במעבר סביבות וכו. במקרה כזה best practice יהיה להשתמש ב- with.
        2. זה כבר pet peeve שלי: הביטוי strt_date > MAX(ISNULL(end_date, '9999-12-31') אינו מבטיח סתירה במובן ה"קלאסי" של המילה במקרה שה end_date הוא null. ניקח את המקרה המופרך שמייקרוסופט "מרחיבה" את שדה ה datetime כדי לתמוך באנליזות עתידיות לתוך שנות ה- 10 אלף והלאה, כתיבה מסגנון זה כבר לא תתפוס בלי לשנות את הערך המקסימלי לערך המקסימלי של טיפוס הנתונים ה"מורחב".
        במקרים כאלה אני לרוב משתמש ב strt_date>max(isnull(end_date,strt_date+1)) שיבטיח לך סתירה מוחלטת.

        בכל אופן, את היכולת של rows between לא הכרתי – מצוין 🙂

        את הפיתרון כתבתי על דף, אבל אעתיק לך חלק ע"מ להמחיש:

        select t1.bank_id, t1.strt_date, t1.end_date, t1.end_date - min(t2.strt_date) as diff
from time_Table t1 join time_table t2 on t1.strt_date<t2.strt_date
group by t1.bank_id, t1.strt_date, t1.end_date

        את זה מגדירים כ- d1 עם with ועושים self join כשהטבלה הראשונה בעלת diff חיובי והשניה שלילי או null, כש strt_date של הראשונה קטן מהשניה ו- bank_id שווים, ומביאים את הזמנים המקסימלים והמינימליים. כמובן שמעורבת כאן עוד לוגיקה שמטפלת בכל מקרי הקצה למיניהם אבל קצרה היריעה 🙂

        תודה

        השאר תגובה
        • ramkedem
          ramkedem ב15/04/2015 ב07:17

          היי עומר,

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

          השאר תגובה
        • מלכה
          מלכה ב15/12/2015 ב19:54

          שלום רם …שאלה מעניינת …לצערי לא הצלחתי לפתור אותה ב sql …
          תוכל לשלוח את הפיתרון המלא ??

          השאר תגובה
      • Anton Gulyaev
        Anton Gulyaev ב22/08/2015 ב18:11

        Hi Ram, in fact when I tried to run that code, it didn't work quite well for me because it grouped rows that it weren't suppose to. What worked for me is when I modified the code like this:

            CREATE VIEW contiguous_vu
    AS
    SELECT bank_id, strt_date, 
                  ISNULL(end_date, '9999-12-31') end_date,
                  CASE
                  WHEN strt_date <= MAX(ISNULL(end_date, '9999-12-31'))
                                   over(PARTITION BY bank_id ORDER BY strt_date 
                                        ROWS BETWEEN UNBOUNDED PRECEDING
                                        AND 1 preceding)
                  THEN 1
	    		  ELSE 0
                  END contiguous_identifier,
		    	  ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as [Row_n]
		    	  FROM time_table

    GO

    CREATE VIEW contiguous_vu1
    AS
    SELECT  bank_id, strt_date, end_date, contiguous_identifier, 
    										CASE 
    										WHEN idn = 1 THEN 0
										ELSE 1
										END endofperiod  
            FROM contiguous_vu a
		JOIN (SELECT Row_n, LEAD(contiguous_identifier, 1, 0) OVER(ORDER BY (SELECT NULL)) idn FROM                    contiguous_vu)b
    		ON a.Row_n = b.Row_n

    GO

    CREATE VIEW begins
    AS
    SELECT bank_id, strt_date, ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as Row_n
    FROM contiguous_vu1 ab
    WHERE ab.contiguous_identifier = 0

    GO

    CREATE VIEW ends
    AS 
    SELECT end_date, ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as Row_n
    FROM contiguous_vu1 ab
    WHERE ab.endofperiod = 1

    GO

    SELECT begins.bank_id, begins.strt_date, ends.end_date
    FROM begins
    FULL OUTER JOIN ends
    ON begins.Row_n = ends.Row_n
        השאר תגובה
      • דודו מרקוביץ
        דודו מרקוביץ ב02/07/2017 ב19:28

        היי רם
        בפיתרון המוצע יש הנחה סמוייה של סדר מיון זהה עבור שתי פונקציות החלון.
        ההנחה הזו לא מבוססת מכיוון ש- "ORDER BY strt_date" הוא לא דטרמיניסטי (כספומטים שונים מאותו הסניף יכולים להתחיל לעבוד בדיוק באותה השעה).
        במקרה ובו פרק זמן רציף נפתח עם שני כספומטים שמתחילים לפעול באותה השעה, הפיתרון המוצע עשוי להחזיר תוצאות שגויות.
        הדרך לתקן את זה היא ע"י הרחבת המיון ל- "ORDER BY strt_date,atm_id"

        השאר תגובה
  2. זהר אלקיים
    זהר אלקיים ב15/04/2015 ב22:43

    היי רם,

    קבל פתרון נחמד מאורקל 12 (אני בדיוק כותב על זה פוסט אצלי בבלוג) – שימוש ב-pattern matching. הפוסט יהיה מוכן בקרוב אבל הנה הפתרון שלי לבעיה שהצגת:

    SELECT *
	FROM time_table MATCH_RECOGNIZE (
    PARTITION BY bank_id
		ORDER BY strt_date
    MEASURES   match_number() as mno
              ,first(strt_date) as frst
              ,last(end_date) as lastd
    one ROW PER MATCH
    PATTERN (s w*)
    DEFINE
      w as w.strt_date < prev(w.end_date)
     );

    מדובר בפיצ'ר חדש שמאפשר לעשות התניה בין שורות באמצעות regular expression – ממש מגניב.. 🙂

    זהר

    השאר תגובה
    • ramkedem
      ramkedem ב16/04/2015 ב09:04

      היי זהר, פיצ'ר בהחלט מרשים ! תודה 🙂

      השאר תגובה
  3. שירה
    שירה ב06/01/2016 ב09:44

    אני הצלחתי בדרך של CTE רקורסיבי

     WITH ATM_CTE (bankId, strtDate, endDate,r_num)
AS
(
    SELECT t.bank_id, t.strt_date, t.end_date,1
    FROM time_table t 
 
	union all

	SELECT  t.bank_id,t.strt_date,a.endDate,r_num+1
    FROM time_table t
	join ATM_CTE a
	on a.strtDate>=t.strt_date and a.strtDate<=t.end_date and bank_id=bankId and (t.strt_date!=a.strtDate and t.end_date!=a.endDate)
)
SELECT   a.bankId,a.strtDate,a.endDate
FROM ATM_CTE a  where r_num=(select max(r_num) from ATM_CTE aa where a.bankId=aa.bankId and (a.strtDate=aa.strtDate or a.endDate=aa.endDate))
 order by bankId,strtDate
    השאר תגובה
    • שירה
      שירה ב06/01/2016 ב10:25

      מצאתי טעות קטנה: השאילתה לא תחשב נכון אם יש רצף שמוכל בתוך רצף אם למשל נוסיף לבנק 2 רצף בין 9:22-9:24
      הנה התיקון:

      WITH ATM_CTE (bankId, strtDate, endDate,r_num)
AS
(
    SELECT t.bank_id, t.strt_date, t.end_date,1
    FROM time_table t 
 
	union all

	SELECT  t.bank_id,t.strt_date,a.endDate,r_num+1
    FROM time_table t
	join ATM_CTE a
	on a.strtDate&gt;=t.strt_date and a.strtDate&lt;=t.end_date and t.end_date<a>aa.strtDate and a.endDate=aa.strtDate and a.endDateaa.strtDate and a.endDate&lt;=aa.endDate)))
 order by bankId,strtDate
      השאר תגובה
  4. שירה
    שירה ב06/01/2016 ב10:28

    משום מה לא העתיק טוב

    WITH ATM_CTE (bankId, strtDate, endDate,r_num)
AS
(
    SELECT t.bank_id, t.strt_date, t.end_date,1
    FROM time_table t 
 
	union all

	SELECT  t.bank_id,t.strt_date,a.endDate,r_num+1
    FROM time_table t
	join ATM_CTE a
	on a.strtDate&gt;=t.strt_date and a.strtDate&lt;=t.end_date and t.end_date<a>aa.strtDate and a.endDate=aa.strtDate and a.endDateaa.strtDate and a.endDate&lt;=aa.endDate)))
 order by bankId,strtDate
    השאר תגובה
  5. דוד חוגי
    דוד חוגי ב01/03/2016 ב16:01 
    SELECT ROW_NUMBER () OVER ( ORDER BY bank_id,strt_date)  R ,* INTO #input FROM dbo.time_table  
-- SELECT * FROM #input

CREATE TABLE #output ( BankId INT,F datetime , T datetime );

DECLARE @cnt int = (SELECT COUNT (*) FROM #input )
DECLARE @idx int = 1


DECLARE @B INT , @FT datetime,@TT datetime
WHILE @idx  F AND @FT &lt; T AND T (SELECT MAX(T) FROM #output WHERE bankId = @B )


SET @idx = @idx +1 
END

SELECT * FROM #output

DROP TABLE #output
DROP TABLE #input
    השאר תגובה
  6. דוד חוגי
    דוד חוגי ב01/03/2016 ב16:08

    זה עובד ב SQL-SERVER
    ==================================

    SELECT ROW_NUMBER () OVER ( ORDER BY bank_id,strt_date)  R ,* INTO #input FROM dbo.time_table  
-- SELECT * FROM #input

CREATE TABLE #output ( BankId INT,F datetime , T datetime );

DECLARE @cnt int = (SELECT COUNT (*) FROM #input )
DECLARE @idx int = 1


DECLARE @B INT , @FT datetime,@TT datetime
WHILE @idx  F AND @FT &lt; T AND T (SELECT MAX(T) FROM #output WHERE bankId = @B )


SET @idx = @idx +1 
END

SELECT * FROM #output

DROP TABLE #output
DROP TABLE #input
    השאר תגובה
  7. אהרון
    אהרון ב22/05/2016 ב14:23

    תרגיל ממש יפה שאפשר ללמוד ממנו המון!

    השאר תגובה
  8. stas
    stas ב20/02/2017 ב16:13

    תרגיל מאתגר , פטרתי את התרגיל הזה קצת דומה לדרך של רם

    

 if object_id ('tempdb..#tb1') is not null
drop table #tb1
 select row_number() over (order by bank_id asc) as RowNum ,  bank_id,strt_date,end_date into #tb1  from time_table

 if object_id ('tempdb..#tb2') is not null
drop table #tb2
 select row_number() over (order by bank_id asc)+1 as RowNum , bank_id,strt_date,end_date into #tb2   from time_table

with t as (
 select  a.bank_id,a.strt_date, a.end_date,
case when ( row_number() over ( PARTITION  by a.bank_id order by   a.strt_date asc ))=1 then null else b.end_date end as N
 from #tb a
 full join #tb2 b on a.rowNum = b.RowNum where  a.bank_id is not null 
 ) 
 select bank_id,min(strt_date) as Sdate,min(end_date) as eDate
  from (
		 select sum (mark ) over (partition by  a.bank_id  order by   a.strt_date asc )  as Summ, *  
		 from (
				select *,case when IsNull(N,'2915-04-12 09:15:00.000') > Strt_date then 0 else 1 end as Mark  from t
			  )a 
        ) subA
  group by bank_id , Summ
  ORDER BY 1, 2

    השאר תגובה

השאר תגובה