Find rows where value changes from positive to negative & v.v.
-
I have table, ordered by weeknumber, one column contains data I need to test for.
The data column to test contains positive or negative numbers.
How do I find those rows where the sign changes?
Example below should return week 4 and week 7
TIA,
Julian
Netherlands
WeekNr Data
1 18
2 33
3 1
4 -5
5 -6
6 -9
7 5
-
Assuming weeknumber is contiguous you can do this
SELECT a.WeekNr,a.Data
FROM mytable a
WHERE EXISTS(SELECT * FROM mytable b
WHERE b.WeekNr = a.WeekNr-1
AND SIGN(b.Data) <>SIGN(a.DATA))
This should work for non-contiguous weeknumber
SELECT a.WeekNr,a.Data
FROM mytable a
CROSS APPLY(SELECT TOP 1 * FROM mytable b WHERE b.WeekNr < a.WeekNr ORDER BY b.WeekNr DESC) ca
WHERE SIGN(ca.Data) <> SIGN(a.DATA);
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hi Mark,
Thank you. 🙂
Cheers,
Julian
SELECTA.Week, A.SALDORCNW
FROMMedewerkerUren A
WHEREEXISTS
(
SELECT*
FROMMedewerkerUren B
WHEREB.Week = A.Week-1
AND SIGN(B.SALDORCNW) <> SIGN(A.SALDORCNW)
and A.JAAR = B.JAAR
and A.PERSNR = B.PERSNR
)
AND A.JAAR = 2015
AND A.PERSNR = '014028'
-
Too bad you're not working in SQL 2012:
WITH SampleData (WeekNr, Data) AS
(
SELECT 1, 18
UNION ALL SELECT 2, 33
UNION ALL SELECT 3, 1
UNION ALL SELECT 4, -5
UNION ALL SELECT 5, -6
UNION ALL SELECT 6, -9
UNION ALL SELECT 7, 5
)
SELECT WeekNr, Data
FROM
(
SELECT *
,SignChange=SIGN(Data)+LAG(SIGN(Data),1,SIGN(Data)) OVER (ORDER BY WeekNr)
FROM SampleData
) a
WHERE SignChange=0;
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Mark Cowne (4/22/2015)
Assuming weeknumber is contiguous you can do thisSELECT a.WeekNr,a.Data
FROM mytable a
WHERE EXISTS(SELECT * FROM mytable b
WHERE b.WeekNr = a.WeekNr-1
AND SIGN(b.Data) <>SIGN(a.DATA))
This should work for non-contiguous weeknumber
SELECT a.WeekNr,a.Data
FROM mytable a
CROSS APPLY(SELECT TOP 1 * FROM mytable b WHERE b.WeekNr < a.WeekNr ORDER BY b.WeekNr DESC) ca
WHERE SIGN(ca.Data) <> SIGN(a.DATA);
Quick word of caution here, the latter query is very expensive although that would normally be masked in terms of execution time by a parallel execution plan.
😎
dwain.c (4/29/2015)
Too bad you're not working in SQL 2012:WITH SampleData (WeekNr, Data) AS
(
SELECT 1, 18
UNION ALL SELECT 2, 33
UNION ALL SELECT 3, 1
UNION ALL SELECT 4, -5
UNION ALL SELECT 5, -6
UNION ALL SELECT 6, -9
UNION ALL SELECT 7, 5
)
SELECT WeekNr, Data
FROM
(
SELECT *
,SignChange=SIGN(Data)+LAG(SIGN(Data),1,SIGN(Data)) OVER (ORDER BY WeekNr)
FROM SampleData
) a
WHERE SignChange=0;
Not really Dwain, this time the LAG is a just name in terms of performance.
For those who want to try this out, here is a test data DDL and generator
USE tempdb;GO
SET NOCOUNT ON;
GO
/* DDL and sample data */
-- /*
IF OBJECT_ID(N'dbo.TBL_SAMPLE_WEEK_DATA') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_WEEK_DATA;
CREATE TABLE dbo.TBL_SAMPLE_WEEK_DATA
(
WeekNr INT NOT NULL CONSTRAINT PK_DBO_TBL_SAMPLE_WEEK_DATA_WEEKNR PRIMARY KEY CLUSTERED
,Data INT NOT NULL
);
DECLARE @SAMPLE_SIZE INT = 1000000;
DECLARE @MAX_VALUE INT = 1000;
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
INSERT INTO dbo.TBL_SAMPLE_WEEK_DATA (WeekNr,Data)
SELECT
NM.N
,CHECKSUM(NEWID()) % @MAX_VALUE
FROM NUMS NM;
-- */
Simple test harness
DECLARE @INT_BUCKET01 INT = 0;DECLARE @INT_BUCKET02 INT = 0;
DECLARE @INT_BUCKET03 INT = 0;
DECLARE @BIT_BUCKET01 BINARY(4) = 0x00;
DECLARE @timer TABLE (T_TEXT VARCHAR(100) NOT NULL, T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME()));
INSERT INTO @timer(T_TEXT) VALUES('DRY RUN');
SELECT
@INT_BUCKET01 = WD.WeekNr
,@INT_BUCKET02 = WD.Data
FROM dbo.TBL_SAMPLE_WEEK_DATA WD
INSERT INTO @timer(T_TEXT) VALUES('DRY RUN');
INSERT INTO @timer(T_TEXT) VALUES('DRY RUN SIGN');
SELECT
@INT_BUCKET01 = WD.WeekNr
,@INT_BUCKET02 = WD.Data
,@INT_BUCKET03 = SIGN(WD.Data) --AS WD_SGN
FROM dbo.TBL_SAMPLE_WEEK_DATA WD
INSERT INTO @timer(T_TEXT) VALUES('DRY RUN SIGN');
INSERT INTO @timer(T_TEXT) VALUES('ROW NUMBER BROKEN SEQUENCE');
;WITH BASE_DATA AS
(
SELECT
WD.WeekNr
,ROW_NUMBER() OVER
(
ORDER BY WD.WeekNr ASC
) AS WEEK_RID
,WD.Data
,SIGN(WD.Data) AS DSGN
FROM dbo.TBL_SAMPLE_WEEK_DATA WD
)
SELECT
@INT_BUCKET01 = BD.WeekNr
,@INT_BUCKET02 = BD.Data
FROM BASE_DATA BD
WHERE EXISTS ( SELECT 1
FROM BASE_DATA BBD
WHERE BD.WEEK_RID = BBD.WEEK_RID + 1
AND BD.DSGN <> BBD.DSGN
)
;
INSERT INTO @timer(T_TEXT) VALUES('ROW NUMBER BROKEN SEQUENCE');
INSERT INTO @timer(T_TEXT) VALUES('INNER JOIN DENSE SEQUENCE');
SELECT
@INT_BUCKET01 = WD.WeekNr
,@INT_BUCKET02 = WD.Data
FROM dbo.TBL_SAMPLE_WEEK_DATA WD
INNER JOIN dbo.TBL_SAMPLE_WEEK_DATA W2
ON WD.WeekNr = W2.WeekNr + 1
WHERE SIGN(WD.Data) <> SIGN(W2.Data);
INSERT INTO @timer(T_TEXT) VALUES('INNER JOIN DENSE SEQUENCE');
INSERT INTO @timer(T_TEXT) VALUES('EXISTS DENSE SEQUENCE');
SELECT
@INT_BUCKET01 = a.WeekNr
,@INT_BUCKET02 = a.Data
FROM dbo.TBL_SAMPLE_WEEK_DATA a
WHERE EXISTS(SELECT * FROM dbo.TBL_SAMPLE_WEEK_DATA b
WHERE b.WeekNr = a.WeekNr-1
AND SIGN(b.Data) <>SIGN(a.DATA))
INSERT INTO @timer(T_TEXT) VALUES('EXISTS DENSE SEQUENCE');
INSERT INTO @timer(T_TEXT) VALUES('EXISTS BROKEN SEQUENCE');
SELECT
@INT_BUCKET01 = a.WeekNr
,@INT_BUCKET02 = a.Data
FROM dbo.TBL_SAMPLE_WEEK_DATA a
CROSS APPLY(SELECT TOP 1 * FROM dbo.TBL_SAMPLE_WEEK_DATA b WHERE b.WeekNr < a.WeekNr ORDER BY b.WeekNr DESC) ca
WHERE SIGN(ca.Data) <> SIGN(a.DATA);
INSERT INTO @timer(T_TEXT) VALUES('EXISTS BROKEN SEQUENCE');
INSERT INTO @timer(T_TEXT) VALUES('LAG BROKEN SEQUENCE');
SELECT
@INT_BUCKET01 = WeekNr
, @INT_BUCKET02 = Data
FROM
(
SELECT *
,SignChange=SIGN(Data)+LAG(SIGN(Data),1,SIGN(Data)) OVER (ORDER BY WeekNr)
FROM dbo.TBL_SAMPLE_WEEK_DATA
) a
WHERE SignChange=0;
INSERT INTO @timer(T_TEXT) VALUES('LAG BROKEN SEQUENCE');
/* Added this last one trying to produce something with a single scan
that would perform worse than the LAG. Note the parallel execution
makes up for the horrid sorts
*/
INSERT INTO @timer(T_TEXT) VALUES('ROW_NUMBER 2 BROKEN SEQUENCE');
;WITH BASE_DATA AS
(
SELECT
WD.WeekNr
,ROW_NUMBER() OVER
(
ORDER BY WD.WeekNr
) AS WD_RID
,WD.Data
,SIGN(1 + SIGN(WD.Data)) WD_SGN
FROM dbo.TBL_SAMPLE_WEEK_DATA WD
)
,SEQUENCED_GROUP AS
(
SELECT
BD.WeekNr
,ROW_NUMBER() OVER
(
PARTITION BY BD.WD_RID + BD.WD_SGN
ORDER BY BD.WD_RID
) AS RID_01
,ROW_NUMBER() OVER
(
PARTITION BY BD.WD_RID - BD.WD_SGN
ORDER BY BD.WD_RID
) AS RID_02
,BD.Data
FROM BASE_DATA BD
)
SELECT
@INT_BUCKET01 = SG.WeekNr
,@INT_BUCKET02 = SG.Data
FROM SEQUENCED_GROUP SG
WHERE SG.RID_01 = 2
OR SG.RID_02 = 2;
INSERT INTO @timer(T_TEXT) VALUES('ROW_NUMBER 2 BROKEN SEQUENCE');
SELECT
T.T_TEXT
,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION
FROM @timer T
GROUP BY T.T_TEXT
ORDER BY DURATION ASC
Results (avg of 3)
T_TEXT DURATION------------------------------ ---------
DRY RUN 142008
DRY RUN SIGN 179011
INNER JOIN DENSE SEQUENCE 465026
EXISTS DENSE SEQUENCE 471027
ROW NUMBER BROKEN SEQUENCE 1201068
EXISTS BROKEN SEQUENCE 1303075
ROW_NUMBER 2 BROKEN SEQUENCE 1432082
LAG BROKEN SEQUENCE 1579090
Statistics (IO,TIME)
---------------------------------------------------------------------DRY RUN
---------------------------------------------------------------------
Table 'TBL_SAMPLE_WEEK_DATA'. Scan count 1, logical reads 2110, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 139 ms.
---------------------------------------------------------------------
DRY RUN SIGN
---------------------------------------------------------------------
Table 'TBL_SAMPLE_WEEK_DATA'. Scan count 1, logical reads 2110, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 188 ms, elapsed time = 188 ms.
---------------------------------------------------------------------
ROW NUMBER BROKEN SEQUENCE
---------------------------------------------------------------------
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TBL_SAMPLE_WEEK_DATA'. Scan count 2, logical reads 4220, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1201 ms, elapsed time = 1204 ms.
---------------------------------------------------------------------
INNER JOIN DENSE SEQUENCE
---------------------------------------------------------------------
Table 'TBL_SAMPLE_WEEK_DATA'. Scan count 2, logical reads 4220, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 468 ms, elapsed time = 469 ms.
---------------------------------------------------------------------
EXISTS DENSE SEQUENCE
---------------------------------------------------------------------
Table 'TBL_SAMPLE_WEEK_DATA'. Scan count 2, logical reads 4220, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 484 ms, elapsed time = 478 ms.
---------------------------------------------------------------------
EXISTS BROKEN SEQUENCE
---------------------------------------------------------------------
Table 'TBL_SAMPLE_WEEK_DATA'. Scan count 1000005, logical reads 3189738, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3916 ms, elapsed time = 1372 ms.
---------------------------------------------------------------------
LAG BROKEN SEQUENCE
---------------------------------------------------------------------
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TBL_SAMPLE_WEEK_DATA'. Scan count 1, logical reads 2110, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1576 ms, elapsed time = 1566 ms.
---------------------------------------------------------------------
ROW_NUMBER 2 BROKEN SEQUENCE
---------------------------------------------------------------------
Table 'TBL_SAMPLE_WEEK_DATA'. Scan count 1, logical reads 2110, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 4084 ms, elapsed time = 1404 ms.
-
Not the first time I've seen LAG be LAME! But it is simple.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply