April 30, 2009 at 12:56 pm
Hi All,
This is something similar to fibonacci series. I am finding it difficult to perform the following task. I wrote a script for the following but that is not effecient, it takes a long time and there are millions of records. Can anyone help me on this please.
My data looks like this
ID Num1
1 5.00
1 6.12
1 5.23
1 3.15
2 3.28
2 3.56
2 1.24
3 6.12
3 2.50
The result set:
ID Num1 Num2
1 5.00 5.00
1 6.12 11.12
1 5.23 16.35
1 3.15 19.50
2 3.28 3.28
2 3.56 6.84
2 1.24 8.08
3 6.12 6.12
3 2.50 8.62
Thanks in Advance,
Ashu
April 30, 2009 at 1:36 pm
How are you enforcing the order of the numbers? SQL Server only guarantees order if you have an explicit order by.
Here's a solution that works on your example data, but I don't know how it would work with millions of rows.
DECLARE @data TABLE (id INT, num NUMERIC(10, 2))
INSERT INTO @data
(
id,
num
)
SELECT
1,
5.00
UNION ALL
SELECT
1,
6.12
UNION ALL
SELECT
1,
5.23
UNION ALL
SELECT
1,
3.15
UNION ALL
SELECT
2,
3.28
UNION ALL
SELECT
2,
3.56
UNION ALL
SELECT
2,
1.24
UNION ALL
SELECT
3,
6.12
UNION ALL
SELECT
3,
2.50 ;
WITH cteOrder
AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS row_id,
id,
num
FROM
)
SELECT
A.id,
A.num,
ISNULL(SUM(b.Num), 0) + A.num AS num2
FROM
cteOrder A LEFT JOIN
cteOrder B
ON A.id = B.id AND
A.ROW_id > B.row_Id
GROUP BY
A.id,
A.num
ORDER BY
id,
num2
Can you post what you are currently doing?
Thanks,
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 30, 2009 at 1:50 pm
Is what you're doing just a partitioned running total? That's what it looks like.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 30, 2009 at 1:57 pm
Awesome!!!!!!!!!!!:-D
It worked perfectly and it is so fast as well than my script.
Thank you so much Jack 🙂
April 30, 2009 at 2:01 pm
You are welcome, but I am still concerned about how you are guaranteeing order.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 30, 2009 at 2:18 pm
It is ordered based on the id first and then the id2
ID ID2 Num1
1 1 5.00
1 2 6.12
1 3 5.23
1 4 3.15
2 1 3.28
2 2 3.56
2 3 1.24
3 1 6.12
3 2 2.50
Thank You,
Ashu
April 30, 2009 at 2:27 pm
By the way, this has nothing to do with Fibonacci series, nor is it related to them (at least not what you have described so far). You are just summing serial values.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 30, 2009 at 2:36 pm
ashu (4/30/2009)
It is ordered based on the id first and then the id2ID ID2 Num1
1 1 5.00
1 2 6.12
1 3 5.23
1 4 3.15
2 1 3.28
2 2 3.56
2 3 1.24
3 1 6.12
3 2 2.50
Thank You,
Ashu
If you have the second sequential id then it is even easier because you don't need the CTE.
SELECT
A.id,
A.id2,
A.num,
ISNULL(SUM(b.Num), 0) + A.num AS num2
FROM
@data A LEFT JOIN
@data B
ON A.id = B.id AND
A.id2 > B.id2
GROUP BY
A.id,
A.id2,
A.num
ORDER BY
id,
A.id2,
num2
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 30, 2009 at 3:08 pm
It worked.
It took 4.23 mins to execute 1,366,368 rows.(Config in my machine is less , so i think its fines.)
Thank you so much Jack it was real good help.:-)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply