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
אפר6

Written by: ronen ariely
06/04/2013 11:03 RssIcon

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

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

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

/****************************************************************************************************  */
/***************************** Finding the records which repeated more than X of times in continuously */
/****************************************************************************************************  */
 
/***************************************************************************************************** */
/*** writen by: Ariely Ronen ************************************************************************* */
/*** Web Site:  http://ariely.info/ ****************************************************************** */
/*** Blog RSS:  http://ariely.info/Blog/tabid/83/rssid/2/ ******************************************** */
/***************************************************************************************************** */
 
/*
This blog is not discussed the issue of optimizing !!!
In this blog I show the thought steps from the question to the solution,
analyzing the results in each step and moving the next step until the final result.
 
Next, after we reach an appropriate query, it is time to optimize and try to get a better result.
*/
 
--  מומלץ להביט בבלוג הבא בו אני דן על מיטוב ושיטות שונות. ניתן לנצל שיטות אלא גם בשאילתה הנוכחית
-- כאן אני רק מציג דוגמה אחת ראשונה שקפצה לי בראש ללא דיון במיטוב
--  ניתן להתבסס על הרעיונות בבלוג כאמור עם הפתרון הנוכחי ולהגיע לשאילתות המתטאימות למסד הנתונים שלכם
-- Select First "Log entery" row from Log Table:
-- במקרה שלנו במקום לקחת את הרשומה הראשונה בכל קבוצת נתונים יהיה עלינו לקחת את הרשומה האחרונה בכל קבוצת נתונים ולבדוק כמה נתונים היו בקבוצה זו
 
 
/***************************************************************************************************** */
USE QQ
GO
 
/***************************************************************************************************** */
-- Determine the number of times the data should repeated in continuously
declare @NumberOfConsecutive int = 2
 
/********************************************************************************************* DDL+DML */
declare @Tbl TABLE(Id  INT IDENTITY,Name CHAR,Status TINYINT)
INSERT INTO @Tbl(Name,Status)
SELECT 'a',0 UNION ALL
SELECT 'a',0 UNION ALL
SELECT 'b',1 UNION ALL
SELECT 'a',0 UNION ALL
SELECT 'a',0 UNION ALL
SELECT 'b',0 UNION ALL
SELECT 'a',0 UNION ALL
SELECT 'b',0 UNION ALL
SELECT 'b',0 UNION ALL
SELECT 'b',1 UNION ALL
SELECT 'c',0 UNION ALL
SELECT 'c',0 UNION ALL
SELECT 'c',1 UNION ALL
SELECT 'b',1
 
INSERT INTO @Tbl(Name,Status)
SELECT 'k',0 UNION ALL
SELECT 'k',0 UNION ALL
SELECT 'k',0 UNION ALL
SELECT 'k',0 UNION ALL
SELECT 'k',1 UNION ALL
SELECT 'k',0 UNION ALL
SELECT 'k',1 UNION ALL
SELECT 'k',1 UNION ALL
SELECT 'k',0 UNION ALL
SELECT 'k',0 UNION ALL
SELECT 'k',1 UNION ALL
SELECT 'k',1
 
/********************************************************************************************  */
select * from @Tbl
/*
-- To verify our results we select all the data in the table and mark the records that meet our requirements,
-- in the case of searching 2 consecutive rows, and in the case of searching 3 consecutive
1   a   0
2   a   0       <== 2
3   b   1
4   a   0
5   a   0       <== 2
6   b   0
7   a   0
8   b   0
9   b   0       <== 2
10  b   1
11  c   0
12  c   0       <== 2
13  c   1
14  b   1
15  k   0
16  k   0       <== 2
17  k   0       <== 3
18  k   0
19  k   1
20  k   0
21  k   1
22  k   1       <== 2
23  k   0
24  k   0       <== 2
25  k   1
26  k   1       <== 2
*/
 
/********************************************************************************************  */
select
    ROW_NUMBER() over (order by [id]) - ROW_NUMBER() over (partition by [Name],[Status] order by [id]) RN
    ,ROW_NUMBER() over (order by [id]) , ROW_NUMBER() over (partition by [Name],[Status] order by [id])
    , *
from @Tbl
order by [id]
 
/********************************************************************************************  */
select RANK() over (partition by RN,[Name],[Status] order by [id]) MyRank,*
from (
    select
        ROW_NUMBER() over (order by [id]) - ROW_NUMBER() over (partition by [Name],[Status] order by [id]) RN
        , *
    from @Tbl
) T
order by [id]
 
/******************************************************************************************** ----- */
/******************************************************************************************** Final */
/******************************************************************************************** ----- */
select [MyRank],[Id],[Name],[Status] from (
    select RANK() over (partition by RN,[Name],[Status] order by [id]) MyRank,*
    from (
        select
            ROW_NUMBER() over (order by [id]) - ROW_NUMBER() over (partition by [Name],[Status] order by [id]) RN
            , *
        from @Tbl
    ) T
) T
where MyRank = @NumberOfConsecutive
order by [id]
 
-- These results obtained in the case of the Search 2  identical consecutiverecords, which we received as expected
/*
2   2   a   0
2   5   a   0
2   9   b   0
2   12  c   0
2   16  k   0
2   22  k   1
2   24  k   0
2   26  k   1
*/
 
-- These results obtained in the case of the Search 3  identical consecutiverecords, which we received as expected
/*
17  k   0
*/
 
 
 
/******************************************************************************************** what more? */
-- we use this to get the max Number Of Consecutive rows
-- we can get all Consecutive rows in begger Consecutive then X
-- אם אין חשיבות בבדיקה של הרציפות לנתונים עם שם שונה אלא רוצים לבדוק רק את הרציפות לפי שדה הסטאטוס עבור כל שם בנפרד
-- אז כל מה שצריך זה בשאילתה הפנימית ביותר לבצע את הסידור לפי שם ורק אז לפי המספר
-- במקום
-- order by [id]
-- פשוט נבצע בכל מקום
-- order by [Name],[Id]
select [MyRank],[Id],[Name],[Status] from (
    select RANK() over (partition by RN,[Name],[Status] order by [Name],[id]) MyRank,*
    from (
        select
            ROW_NUMBER() over (order by [Name],[Id]) - ROW_NUMBER() over (partition by [Name],[Status] order by [Name],[Id]) RN
            , *
        from @Tbl
    ) T
) T
where MyRank = @NumberOfConsecutive
order by Id
 
 
/******************************************************************************************** Have fun */
-- I hope you enjoyed the reading
-- have fun and i will see you in the next blog,
-- Ronen
 
/******************************************************************************************** Close script */
GO

ניתן להוריד את הסקריפט המלא כאן