August 15, 2012 at 8:20 am
I am trying to write a script to read the current row and previous row per device and work out the difference reporting values over X (> than 20 in example below).
ID DEVICE VALUE
9 456 70
8 456 60
7 123 70
6 123 60
5 456 50
4 456 10
3 123 50
2 123 20
1 123 10
Script would return
ID 3
ID 5
Large table to be queried.
Thank you for any help.
Scott
August 15, 2012 at 8:35 am
scott_lotus (8/15/2012)
I am trying to write a script to read the current row and previous row per device and work out the difference reporting values over X (> than 20 in example below).ID DEVICE VALUE
9 456 70
8 456 60
7 123 70
6 123 60
5 456 50
4 456 10
3 123 50
2 123 20
1 123 10
Script would return
ID 3
ID 5
Large table to be queried.
Thank you for any help.
Scott
--==First, create some sample data ==--
SELECT ID, DEVICE, VALUE
INTO #yourSampleTable
FROM (VALUES(9, 456, 70),(8, 456, 60),(7, 123, 70),
(6, 123, 60),(5, 456, 50),(4, 456, 10),
(3, 123, 50),(2, 123, 20),(1, 123, 10)
)a(ID, DEVICE, VALUE);
--== Now for a solution ==--
SELECT a.ID, a.DEVICE, a.VALUE
FROM (SELECT ID, DEVICE, VALUE,
ROW_NUMBER() OVER(PARTITION BY DEVICE ORDER BY ID) AS pos
FROM #yourSampleTable) a
LEFT OUTER JOIN (SELECT ID, DEVICE, VALUE,
ROW_NUMBER() OVER(PARTITION BY DEVICE ORDER BY ID) AS pos
FROM #yourSampleTable) b ON a.DEVICE = b.DEVICE AND a.pos = b.pos+1
WHERE a.VALUE-b.VALUE > 20;
Returns: -
ID DEVICE VALUE
----------- ----------- -----------
3 123 50
5 456 50
August 15, 2012 at 8:36 am
This has been solved in other posts and seems to be a very common issue.
Here's one solution (I added DDL and sample data in an usable format and you should try to do this for your next posts, as it will give you better and faster answers).
DECLARE @Tabletable(
idint,
device int,
value int)
INSERT @Table
SELECT 9, 456, 70 UNION ALL
SELECT 8, 456, 60 UNION ALL
SELECT 7, 123, 70 UNION ALL
SELECT 6, 123, 60 UNION ALL
SELECT 5, 456, 50 UNION ALL
SELECT 4, 456, 10 UNION ALL
SELECT 3, 123, 50 UNION ALL
SELECT 2, 123, 20 UNION ALL
SELECT 1, 123, 10 ;
WITH CTE AS (
SELECT id,
device,
value,
ROW_NUMBER() OVER(ORDER BY device, id) rownum
FROM @Table)
SELECT a.id
FROM CTE a
JOIN CTE b ON a.rownum = b.rownum + 1
WHERE a.value - b.value > 20
EDIT: See what I meant when saying this is only one solution? Now you have three different ones but one could have problems with missing ids. And mine had probably a problem with the order (corrected now).
August 15, 2012 at 8:36 am
Next time pleas include a small script that creates the table and inserts data instead of drawing a table. This will save time for anyone that tries to help you.
declare @tbl table (ID int, DEVICE int, VALUE int)
insert into @tbl (ID, DEVICE, VALUE) VALUES (9, 456, 70)
insert into @tbl (ID, DEVICE, VALUE) VALUES (8, 456, 60)
insert into @tbl (ID, DEVICE, VALUE) VALUES (7, 123, 70)
insert into @tbl (ID, DEVICE, VALUE) VALUES (6, 123, 60)
insert into @tbl (ID, DEVICE, VALUE) VALUES (5, 456, 50)
insert into @tbl (ID, DEVICE, VALUE) VALUES (4, 456, 10)
insert into @tbl (ID, DEVICE, VALUE) VALUES (3, 123, 50)
insert into @tbl (ID, DEVICE, VALUE) VALUES (2, 123, 20)
insert into @tbl (ID, DEVICE, VALUE) VALUES (1, 123, 10)
select t2.ID
from @tbl as t1 inner join @tbl as t2 on t1.ID = t2.ID -1
where t2.VALUE - t1.VALUE > 20
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 15, 2012 at 8:39 am
scott_lotus (8/15/2012)
I am trying to write a script to read the current row and previous row per device and work out the difference reporting values over X (> than 20 in example below).ID DEVICE VALUE
9 456 70
8 456 60
7 123 70
6 123 60
5 456 50
4 456 10
3 123 50
2 123 20
1 123 10
Script would return
ID 3
ID 5
Large table to be queried.
Thank you for any help.
Scott
There is no such thing as current row and previous row in a SQL table.
What do you mean by current row per device? Is the one with the largest ID?
How did you manage to calculate 3 and 5? Based on what?
August 15, 2012 at 8:44 am
Luis Cazares (8/15/2012)
DECLARE @Tabletable(
idint,
device int,
value int)
INSERT @Table
SELECT 9, 456, 70 UNION ALL
SELECT 8, 456, 60 UNION ALL
SELECT 7, 123, 70 UNION ALL
SELECT 6, 123, 60 UNION ALL
SELECT 5, 456, 50 UNION ALL
SELECT 4, 456, 10 UNION ALL
SELECT 3, 123, 50 UNION ALL
SELECT 2, 123, 20 UNION ALL
SELECT 1, 123, 10 ;
WITH CTE AS (
SELECT id,
device,
value,
ROW_NUMBER() OVER(ORDER BY id) rownum
FROM @Table)
SELECT a.id
FROM CTE a
JOIN CTE b ON a.rownum = b.rownum + 1
WHERE a.value - b.value > 20
This will only work if the ID for each DEVICE is always next to the previous DEVICE.
Try this sample data instead: -
DECLARE @Table TABLE (id INT, device INT, value INT);
INSERT @Table
SELECT 5, 456, 50
UNION ALL SELECT 4, 456, 10
UNION ALL SELECT 3, 123, 50
UNION ALL SELECT 2, 456, 20
UNION ALL SELECT 1, 123, 45;
Expected result would be ID 5, because it is the only one where the previous ID for the device is more than 20. Your code would instead report back 3 and 5.
If the OP does only want the previous row, then you'd be better off using the ID column instead of a ROW_NUMBER.
Adi Cohn-120898 (8/15/2012)
ideclare @tbl table (ID int, DEVICE int, VALUE int)
insert into @tbl (ID, DEVICE, VALUE) VALUES (9, 456, 70)
insert into @tbl (ID, DEVICE, VALUE) VALUES (8, 456, 60)
insert into @tbl (ID, DEVICE, VALUE) VALUES (7, 123, 70)
insert into @tbl (ID, DEVICE, VALUE) VALUES (6, 123, 60)
insert into @tbl (ID, DEVICE, VALUE) VALUES (5, 456, 50)
insert into @tbl (ID, DEVICE, VALUE) VALUES (4, 456, 10)
insert into @tbl (ID, DEVICE, VALUE) VALUES (3, 123, 50)
insert into @tbl (ID, DEVICE, VALUE) VALUES (2, 123, 20)
insert into @tbl (ID, DEVICE, VALUE) VALUES (1, 123, 10)
select t2.ID
from @tbl as t1 inner join @tbl as t2 on t1.ID = t2.ID -1
where t2.VALUE - t1.VALUE > 20
Same reasons as above for why this wouldn't work, unless the OP wants to ignore the device.
August 15, 2012 at 8:46 am
Thanks for the reply , will give the above a go.
Sorry for poor explanation.
Current row = the row i am querying , bearing in mind i need to query all rows per device.
Previous row = the row prior to the one i am looking at per device.
Arrived at 3 and 5 because when reading row 5 the value is > 20 when compared to the previous (row 4) for that devices.
Sorry , finding it hard to explain , easier to show.
Scott
Edit to say, not sure is UNION is a good idea, find it very slow on + billions records.
August 15, 2012 at 8:49 am
I agree with you Cadavre, but I realized that after posting and realizing the OP was saying devices instead of ids. I corrected it before I saw your new post.
August 15, 2012 at 8:50 am
scott_lotus (8/15/2012)
Edit to say, not sure is UNION is a good idea, find it very slow on + billions records.
The UNIONs that some have posted are part of creating sample data so that they could test their solutions (really this is something you should have provided with your post).
The actual solutions on offer are as follows: -
Cadavre
SELECT a.ID, a.DEVICE, a.VALUE
FROM (SELECT ID, DEVICE, VALUE,
ROW_NUMBER() OVER(PARTITION BY DEVICE ORDER BY ID) AS pos
FROM #yourSampleTable) a
LEFT OUTER JOIN (SELECT ID, DEVICE, VALUE,
ROW_NUMBER() OVER(PARTITION BY DEVICE ORDER BY ID) AS pos
FROM #yourSampleTable) b ON a.DEVICE = b.DEVICE AND a.pos = b.pos+1
WHERE a.VALUE-b.VALUE > 20;
Luis Cazares
WITH CTE AS (
SELECT id,
device,
value,
ROW_NUMBER() OVER(ORDER BY device, id) rownum
FROM @Table)
SELECT a.id
FROM CTE a
JOIN CTE b ON a.rownum = b.rownum + 1
WHERE a.value - b.value > 20;
Adi Cohn-120898
select t2.ID
from @tbl as t1 inner join @tbl as t2 on t1.ID = t2.ID -1
where t2.VALUE - t1.VALUE > 20
Luis Cazares (8/15/2012)
I agree with you Cadavre, but I realized that after posting and realizing the OP was saying devices instead of ids. I corrected it before I saw your new post.
Yep, that fixes the issue with your code. Amusingly it now produces the exact same execution plan as mine 😉
August 15, 2012 at 9:26 am
CELKO (8/15/2012)
Now we have to fix the bad schema with DML. Look up the new CREATE SEQUENCE statement and use it. Here is an untested attempt:WITH Corrected_Tests (device_id, test_value, device_test_seq)
AS
(SELECT device_id, test_value
ROW_NUMBER()
OVER (PARTITION BY device_id
ORDER BY test_seq)
FROM Tests),
Delta_Test (device_id, test_value, device_test_seq, test_delta)
AS
(SELECT device_id, test_value, device_test_seq,
test_values
- LAG (test_value)
OVER (PARTITION BY device_id
ORDER BY test_seq)
FROM Corrected_Tests)
SELECT device_id, device_test_seq
FROM Delta_Test
WHERE test_delta > 20;
Out of curiosity, why post a SQL Server 2012 solution in a SQL Server 2008 forum?
August 15, 2012 at 9:27 am
The UNIONs that some have posted are part of creating sample data so that they could test their solutions (really this is something you should have provided with your post).
I have 2 small remarks:
1) Don't jump to conclusion:-) I did test my solution but didn't notice that the original poster wanted that per device.
2) I don't think that the people that try to answer the questions should write the script that creates the table and insert the data. I think that this is something that should be done by the poster that asks the question.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 15, 2012 at 9:27 am
CELKO (8/15/2012)
Please post DDL so that other people do not have to guess at keys, data types and constraint when they create the tablet o answer your question. Is this what you meant?Create TABLE Tests
(test_seq INTEGER NOT NULL PRIMARY KEY,
device_id INTEGER NOT NULL,
test_value INTEGER NOT NULL);
INSERT INTO Tests
VALUES
(9, 456, 70),
(8, 456, 60),
(7, 123, 70),
(6, 123, 60),
(5, 456, 50),
(4, 456, 10),
(3, 123, 50),
(2, 123, 20),
(1, 123, 10);
I am going to assume that the rest sequence number is how you defined the ordering of the tests results. You did not tell us. Having a global ordering is a really bad idea; each device should have its own sequence (a really bad design would have used an IDENTITY).
Now we have to fix the bad schema with DML. Look up the new CREATE SEQUENCE statement and use it. Here is an untested attempt:
WITH Corrected_Tests (device_id, test_value, device_test_seq)
AS
(SELECT device_id, test_value
ROW_NUMBER()
OVER (PARTITION BY device_id
ORDER BY test_seq)
FROM Tests),
Delta_Test (device_id, test_value, device_test_seq, test_delta)
AS
(SELECT device_id, test_value, device_test_seq,
test_values
- LAG (test_value)
OVER (PARTITION BY device_id
ORDER BY test_seq)
FROM Corrected_Tests)
SELECT device_id, device_test_seq
FROM Delta_Test
WHERE test_delta > 20;
Mr. Celko, you started off well, then you went off to la la land. This is a SQL Server 2008 forum, therefore suggesting the OP should use CREATE SEQUENCE is simply wrong.
It really would be helpful if you would restrict your answers to the capabilities available to version of MS SQL Server being used.
August 15, 2012 at 9:30 am
Adi Cohn-120898 (8/15/2012)
The UNIONs that some have posted are part of creating sample data so that they could test their solutions (really this is something you should have provided with your post).
I have 2 small remarks:
1) Don't jump to conclusion:-) I did test my solution but didn't notice that the original poster wanted that per device.
2) I don't think that the people that try to answer the questions should write the script that creates the table and insert the data. I think that this is something that should be done by the poster that asks the question.
Adi
I have 2 small replies to your small remarks.
1) I was talking to the original poster (hence why his post was quoted above my comment) and informing him that the reason you have included UNIONs was to test your solution. Not only do I know you tested it, I had actually stated so.
2) Again, I was talking to the original poster (hence why his post was quoted above my comment) and informing him that he should have included sample data scripts.
Were your remarks intended to agree with me?
August 15, 2012 at 9:41 am
Cadavre (8/15/2012)
CELKO (8/15/2012)
Now we have to fix the bad schema with DML. Look up the new CREATE SEQUENCE statement and use it. Here is an untested attempt:WITH Corrected_Tests (device_id, test_value, device_test_seq)
AS
(SELECT device_id, test_value
ROW_NUMBER()
OVER (PARTITION BY device_id
ORDER BY test_seq)
FROM Tests),
Delta_Test (device_id, test_value, device_test_seq, test_delta)
AS
(SELECT device_id, test_value, device_test_seq,
test_values
- LAG (test_value)
OVER (PARTITION BY device_id
ORDER BY test_seq)
FROM Corrected_Tests)
SELECT device_id, device_test_seq
FROM Delta_Test
WHERE test_delta > 20;
Out of curiosity, why post a SQL Server 2012 solution in a SQL Server 2008 forum?
Probably to encourage quicker adoption of new technology. 😉
August 15, 2012 at 9:58 am
Do ISO standards say anything about:
- writing readable code?
- writing code according to the software's version used?
- reading previous comments?
- be aware of the requirements in general and not making assumptions?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply