-
morethan 3 consecutive rows
We have to find and flag the occurance of values >=3 if it happens more than 3 consecutive cycles/rows.It could happen within same subtype or different subtypes
example:-
time subtype value flag
7:00 a 2
7:00 a 0
7:00 a 5
7:03 a 3
7:03 a 2
7:03 a 1
7:06 a 3 Y
7:06 a 3 Y
7:06 a 4 Y
7:09 b 5 Y
7:09 b 2
7:09 b 1
Last edited by rajan1; 03-31-2006 at 03:38 PM.
-
Is this it?
Code:
select myTime from myTable
group by myTime
having count(*) >= 3
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
or maybe this is what you want:
Code:
select * from (
select myTime, subtype, value, flag,
count(*) over (partition by myTime) cntr
from myTable
) where cntr >= 3
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
sounds more complicated.. i think it will be a sliding window, ordered by time, looking back over 4 rows, to see if the sum value over the 4 rows is >= 12 and the min value for the 4 rows is >= 3
thing is.. dude hasnt said which rows he wants to flag
-
Thanks a lot for the response.
Sorry,my bad. This is how the data looks like in the table case_study
time subtype value
7:00 a 2
7:00 a 0
7:00 a 5
7:03 a 3
7:03 a 2
7:03 a 1
7:06 a 3
7:06 a 3
7:06 a 4
7:09 b 5
7:09 b 2
7:09 b 1
In this we need to find value >=3 when it occurs three or more than three consecutive times/rows. In the above example the bold rows have values in the value column >=3 and it occurs more than 3 times consecutively. We have to flag or alert such rows.
-
Am I right in thinking that the table doesn't have a primary key?
If I'm right, this is going to be painful.
P.S. I might have spotted that "value" was a column name if you'd formated your post with code tags (the # icon produces [ code ] and [ / code ])
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Yes, there is no primary key.
Here is one SQL (in bold) i found while googling, here the author compare the values between consecutive rows and gets the difference between two consecutive row values.I am not able to apply this to my requirement.
I want to compare consecutive rows in a table, e.g., 1-2, 2-3, etc. Now I was thinking of doing a self join for this. But the problem is, how do I do this, as none of the columns are predictable. For example, if I have the following data:
name quantity cost
A 2 20
H 7 24
L 1 12
...
and I want to find out the difference between the cost of A and H, H and L, and so on.
This question posed on 06 January 2006
Interesting question. We need to join each row to the row which has the lowest name of all the names that are greater than the name in the row being joined. Simple, yes? I mean, once you say it like that, it's easy to understand, right?
Well, once you say it like that, it's easy to write the SQL for, too.
select t1.name
, t1.cost as t1_cost
, t2.cost as t2_cost
, t2.cost - t1.cost as diff
from yourtable as t1
left outer
join yourtable as t2
on t2.name
= ( select min(name)
from yourtable
where name > t1.name )
name t1_cost t2_cost diff
A 20 24 4
H 24 12 -12
L 12 -- --
It's an unusual ON condition, because you don't see one like this every day. It may be rare but it is perfectly valid.
Thanks for the tips.
-
Why don't you use simple "lead" analytical fn?
Code:
SQL> select * from int;
NAME QTY COST
---------- ---------- ----------
A 2 20
H 7 24
L 1 12
SQL> get int2
1 select name, qty, cost,
2 lead(cost) over ( order by name) nextcost
3* from int
SQL> /
NAME QTY COST NEXTCOST
---------- ---------- ---------- ----------
A 2 20 24
H 7 24 12
L 1 12
Tamil
-
Thanks a lot for your response.
I really don't want the difference between consecutive rows. What i really want is the occurance of more than 3 consecutive rows having value>=3
time subtype value
7:00 a 2
7:00 a 0
7:00 a 5
7:03 a 3
7:03 a 2
7:03 a 1
7:06 a 3
7:06 a 3
7:06 a 4
7:09 b 5
7:09 b 2
7:09 b 1
In the above example the bold rows have values in the value column >=3 and it occurs more than 3 times consecutively. We have to flag or alert such rows.
-
You said the table does not have PK.
Then what is the sorting order to determine 3 consecutive rows that have value >= 3?
Tamil
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|