Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1112131415

Linking to the Previous Row Expand / Collapse
Author
Message
Posted Tuesday, April 12, 2011 1:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 8:26 AM
Points: 109, Visits: 490
_ms65g_ (4/12/2011)
No, if we change the DENSE_RANK with ROW_NUMBER the result will not be correct. You can rum my first script at my first post with row_number. So you will see an empty result set.


I see what you are doing now, it is a re-grouping - basically like a pivot.
Post #1092403
Posted Tuesday, April 12, 2011 3:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 27, 2011 8:16 AM
Points: 13, Visits: 64
Well, in my application it seems about the same as the Outer Apply method. I only need the previous (not the next) day's value, for 5000 series of about 1000 elements. Finding those 5 million values and doing a brief calculation on them takes 3-4 mins on my laptop, with either method...
Post #1092469
Posted Tuesday, April 12, 2011 3:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 8:26 AM
Points: 109, Visits: 490
AlistairNY (4/12/2011)
Well, in my application it seems about the same as the Outer Apply method. I only need the previous (not the next) day's value, for 5000 series of about 1000 elements. Finding those 5 million values and doing a brief calculation on them takes 3-4 mins on my laptop, with either method...


That's probably because the cross-join triples the set first. Did you use a modified cross-join with only -1, 0? I still expect that at large data sizes the cross-join will start to level out with the other methods - cross-joins are expensive. Still, the cross-join/pivot is a cool trick.
Post #1092474
Posted Tuesday, April 12, 2011 3:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 27, 2011 8:16 AM
Points: 13, Visits: 64
Yes, I cut down the other table to -1 and 0. If there's anything else I could try, very happy to :)

I partitioned the rank function by a SeriesId as well, but otherwise ran Mohammad's solution. Any advantage in replacing the CTE with an indexed temp table?
Post #1092477
Posted Wednesday, April 13, 2011 12:30 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:02 AM
Points: 648, Visits: 1,874
Looks like a very innovative and interesting approach.

_ms65g_ (4/12/2011)
Can you demonstrate your technique for matching current rows with previous and next values is faster and more efficient as my best solution?


Can you propose a version of your script compatible with the benchmark tests? At which point I'll be glad to test your script and include the results.

Best regards,

David.
Post #1092583
Posted Thursday, April 14, 2011 8:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 24, 2012 11:32 AM
Points: 56, Visits: 880
Sorry for late reply.
Here you are:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
--Mohammad Salimabadi Solution
WITH C0 AS
(
SELECT Ph.*, I.item,
ROW_NUMBER() OVER (PARTITION BY Ph.ItemId
ORDER BY Ph.PriceStartDate) AS rownum
FROM PriceHistory AS Ph
JOIN Items AS I
ON Ph.itemid = I.itemid
),
C1 AS
(
SELECT itemid, item, PriceStartDate, price,
rownum + k AS grp_fct,
k
FROM C0
CROSS JOIN
(
VALUES (-1),
( 0),
( 1)
) AS D(k)
)
SELECT MAX(CASE WHEN k = 0 THEN item END) AS Item,
MAX(CASE WHEN k = 1 THEN price END) AS OldPrice,
MAX(CASE WHEN k = 0 THEN price END) AS RangePrice,
MAX(CASE WHEN k = 0 THEN PriceStartDate END) AS StartDate,
MAX(CASE WHEN k = -1 THEN PriceStartDate END) AS EndDate
FROM C1
GROUP BY itemid, grp_fct
HAVING MIN(PriceStartDate) < MAX(PriceStartDate);

--A Common Solution
WITH PriceCompare AS
(
SELECT i.Item,
ph.ItemId, ph.PriceStartDate, ph.Price,
ROW_NUMBER() OVER (PARTITION BY ph.ItemId
ORDER BY PriceStartDate) AS rownum
FROM Items i
JOIN PriceHistory ph
ON i.ItemId = ph.ItemId
)
SELECT currow.Item,
prevrow.Price AS OldPrice,
currow.Price AS RangePrice,
currow.PriceStartDate AS StartDate,
nextrow.PriceStartDate AS EndDate
FROM PriceCompare currow
LEFT JOIN PriceCompare nextrow
ON currow.rownum = nextrow.rownum - 1
AND currow.ItemId = nextrow.ItemId
LEFT JOIN PriceCompare prevrow
ON currow.rownum = prevrow.rownum + 1
AND currow.ItemId = prevrow.ItemId;

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

/*
--Mohammad Salimabadi Solutio:
Table 'PriceHistory'. Scan count 3, logical reads 6
Table 'Items'. Scan count 1, logical reads 2

--A Common Solution
Table 'PriceHistory'. Scan count 5, logical reads 48
Table 'Items'. Scan count 1, logical reads 2
*/

Post #1093578
Posted Wednesday, April 27, 2011 7:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 24, 2012 11:32 AM
Points: 56, Visits: 880
/*Matching Adjacent Rows based on a consecutive value*/

CREATE TABLE Nums
(nbr INTEGER NOT NULL PRIMARY KEY,
val INTEGER NOT NULL);


INSERT INTO Nums (nbr, val)
VALUES (1, 0), (5, 7), (9, 4);

--===========Mohammad Salimabadi Solution #2
SELECT T2.*, T1.*, T3.*
FROM Nums AS T1
LEFT JOIN Nums AS T2
ON T2.nbr = (SELECT MAX(nbr)
FROM Nums
WHERE nbr < T1.nbr)
LEFT JOIN Nums AS T3
ON T3.nbr = (SELECT MIN(nbr)
FROM Nums
WHERE nbr > T1.nbr);

--==========Mohammad Salimabadi Solution #3
SELECT pre_nbr, N1.val AS pre_val,
C.nbr AS cur_nbr, C.val AS cur_val,
nxt_nbr, N2.val AS nxt_val
FROM (
SELECT MAX(CASE WHEN N1.nbr > N2.nbr THEN N2.nbr ELSE NULL END) AS pre_nbr,
N1.nbr, N1.val,
MIN(CASE WHEN N1.nbr < N2.nbr THEN N2.nbr ELSE NULL END) AS nxt_nbr
FROM Nums AS N1,
Nums AS N2
GROUP BY N1.nbr, N1.val
) AS C
LEFT JOIN Nums AS N1
ON C.pre_nbr = N1.nbr
LEFT JOIN Nums AS N2
ON C.nxt_nbr = N2.nbr;

--=========Mohammad Salimabadi Solution #4
SELECT CAST(SUBSTRING(concat_pre, 1, 4) AS integer) AS pre_nbr,
CAST(SUBSTRING(concat_pre, 5, 8) AS integer) AS pre_val,
nbr, val,
CAST(SUBSTRING(concat_nxt, 1, 4) AS integer) AS pre_nbr,
CAST(SUBSTRING(concat_nxt, 5, 8) AS integer) AS pre_val
FROM (
SELECT (
SELECT MAX(CAST(nbr AS BINARY(4)) + CAST(val AS BINARY(4)))
FROM Nums
WHERE nbr < T.nbr
) AS concat_pre,
nbr, val,
(
SELECT MIN(CAST(nbr AS BINARY(4)) + CAST(val AS BINARY(4)))
FROM Nums
WHERE nbr > T.nbr
) AS concat_nxt
FROM Nums AS T
) AS D


Post #1099454
Posted Friday, May 27, 2011 7:21 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:02 AM
Points: 648, Visits: 1,874
Firstly apologies, Mohammad, for the obscenely long reply.

Before we look at the results, let me just say that the test data, and code that I used are those that I posted previously. So you are free to perform exactly the same test in your own environment.

I compared your method with the other methods previously discussed, and unfortunately the results were rather disappointing. Indeed it's generally the worst performer.

For the full resultset I used your code exactly as is. For the "Price rises" and the "One Item" queries, I amended your query to put the results in a further CTE and then filtered the results. (extract below)

C3 AS
(
SELECT MAX(CASE WHEN k = 0 THEN item END) AS Item,
MAX(CASE WHEN k = 1 THEN price END) AS OldPrice,
MAX(CASE WHEN k = 0 THEN price END) AS RangePrice,
MAX(CASE WHEN k = 0 THEN PriceStartDate END) AS StartDate,
MAX(CASE WHEN k = -1 THEN PriceStartDate END) AS EndDate
FROM C1
GROUP BY itemid, grp_fct
HAVING MIN(PriceStartDate) < MAX(PriceStartDate)
)
SELECT * FROM C3 where Item='Item 512'

Full Resultset Method Execution Time
CTE View 13 secs
Temp Table with rownumber 33 secs
Table Variable with rownumber 47 secs
Temp Table with identity 16 secs
Table Variable with identity Cancelled after 1 hour
Cross Apply 12 secs
Mohammad Salimabadi Solution 131 secs
One Item
CTE View 2 secs
Temp Table with rownumber 5 secs
Table Variable with rownumber 4 secs
Temp Table with identity 6 secs
Table Variable with identity 83 secs
Cross Apply 0 sesc
Mohammad Salimabadi Solution 85 secs
Price Rises
CTE View 8 secs
Temp Table with rownumber 19 secs
Table Variable with rownumber 17 secs
Temp Table with identity 12 secs
Table Variable with identity Not Run
Cross Apply 8 secs
Mohammad Salimabadi Solution 114 secs

Please try to reproduce this in your own environment, and let me know if you manage to bring improvements to the results.

Best regards,

David McKinney.
Post #1116223
« Prev Topic | Next Topic »

Add to briefcase «««1112131415

Permissions Expand / Collapse