April 2, 2014 at 4:01 pm
In my SQL Server database, I have table with the following records
counter, value1
12345, 10.1
12370, 10.5
12390, 9.7
Let's assume that I input a gap value of 5. I need to fill in the data between the Record 1 and Record 2 by increment of 5 as specified in the input parameter on the counter column.
For example using Record 1 and Record 2, here are the additional data needs to be inserted into the table.
1234510.1 --> Record 1
1235010.1
1235510.1
1236010.1
1236510.1
1237010.5 --> Record 2
1237510.5
1238010.5
1238510.5
123909.7 --> Record 3
Currently, I am using a cursor to read from the table and select MIN counter from the table. Then use a LOOP to fill in the gap and insert it into another table. I have over 10000 records and after fill up the gap, I might end up with even more records. Just want to see if I can get any other efficient way to achieve this.
In case you are wondering why I want to fill in the gap, I need to calculate the average value for my record set after considering all valid data points in between.
Thanks for your input.
April 2, 2014 at 4:15 pm
You don't need to actually INSERT rows to use those values in a computation. Simply determine the number of "steps" between each row, and multiply the value in the first one by that.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 2, 2014 at 4:45 pm
I did try to use Record 2 - Record 1 then divide by 5 which is the input gap. However, I still need to populate each record with data from Record 1.
For example using my example
12345 --> Record 1
12350
12355
12360
12365
12370 --> Record 2
12370 - 12345 = 25
25 divide by gap 5 = 5 points
Probably due to my understanding, I don't see much value from this method. I still need to loop through each record and assign the value from Record 1. Correct me if I am wrong. Thanks.
April 2, 2014 at 6:01 pm
First of all, you should provide sample data in a consumable form if you want to get better and faster answers. I did it for you this time because you're new to this forum, but you should try to include it in the future.
Second, stop thinking about cursors as they're bad for performance as you might have noticed. SQL Server is designed to work with sets of data and not row by row.
Here's an example on how you could implement Scott's idea along with sample data and comparison test.
CREATE TABLE #Test1(
[counter]int,
value1decimal(12,4))
INSERT #Test1
SELECT 12345, 10.1 UNION ALL
SELECT 12370, 10.5 UNION ALL
SELECT 12390, 9.7
CREATE TABLE #Test2(
[counter]int,
value1decimal(12,4))
INSERT #Test2
SELECT 12345,10.1 UNION ALL --> Record 1
SELECT 12350,10.1 UNION ALL
SELECT 12355,10.1 UNION ALL
SELECT 12360,10.1 UNION ALL
SELECT 12365,10.1 UNION ALL
SELECT 12370,10.5 UNION ALL --> Record 2
SELECT 12375,10.5 UNION ALL
SELECT 12380,10.5 UNION ALL
SELECT 12385,10.5 UNION ALL
SELECT 12390,9.7 --> Record 3
DECLARE @gap int = 5;
WITH CTE AS(
SELECT *, ROW_NUMBER() OVER( ORDER BY [counter]) rn
FROM #Test1
)
SELECT SUM(ISNULL( (b.[counter] - a.[counter]) / @gap, 1) * a.value1) / SUM(ISNULL( (b.[counter] - a.[counter]) / @gap, 1))
FROM CTE a
LEFT
JOIN CTE b ON a.rn = b.rn - 1
SELECT AVG( value1)
FROM #Test2;
GO
DROP TABLE #Test1
DROP TABLE #Test2
I can't use the AVG directly, that's why the average is calculated in the traditional way.
April 2, 2014 at 6:04 pm
Hi ScottPletcher, I changed my logic to calculate the number of data points between Record 1 and Record 2 and then, use a WHILE LOOP to navigate through the data points and insert it into a new table.
Managed to reduce my execution time by 30%.
Thanks for the idea.
April 2, 2014 at 6:07 pm
Hi Luis Cazares,
Thanks for your suggestion. I will try it out.
For the data, I will prepare it next time. Sorry for the inconvenient.
Actually, my counter is a date in seconds. I got to do this to prepare a data for stagnant point to plot a trend chart.
April 2, 2014 at 6:17 pm
I got lost with your last post. Do you mean that you really need to show all the possible values in the gap so they're available in a chart?
If you need that, you might want to look for a tally(numbers) table solution.
I'll try to work on one, but I need to be sure that's what you actually need. As Scott said, you don't want to generate all the values if you don't actually need them.
April 2, 2014 at 6:37 pm
Hi Luis,
Actually the same table data set generated used by a few reports. First one is chart and then some calculations using those data points. That's why I need a new table populated with all the data set I want.
The solution I had which count the difference between points and populate seems to work for me in this case.
Thanks for your assistance and I will look into the TALLY function to broaden my knowledge.
April 2, 2014 at 6:41 pm
If you post your cursor solution, we could help you to convert it to a set based solution. It will run a lot faster if done properly and will change the way you see things. 😉
April 9, 2014 at 12:24 am
Y'all probably gonna think I'm a might touched in the head for coming up with something like this but it does do the work in one pass of the table and it gets the same result that Luis's query does.
SELECT SUM(n*ISNULL(v1, v2))/SUM(n)
FROM
(
-- Calculate weighted averages from the gaps
SELECT n=MAX(c)+(MAX([counter])-MIN([counter]))/5
,v1=MAX(CASE rn2%2 WHEN 0 THEN v END)
,v2=MAX(CASE rn2%2 WHEN 1 THEN v END)
FROM
(
SELECT b.[counter], a.v, a.c
,rn2=ROW_NUMBER() OVER (ORDER BY b.[counter])
FROM
(
-- Calculate islands
SELECT IslandStart=MIN([Counter]), IslandEnd=MAX([counter])
,v=AVG(value1), c=COUNT(*)
FROM
(
SELECT [counter], value1,
rn1=ROW_NUMBER() OVER (ORDER BY [counter])
FROM #Test1
) a
GROUP BY [counter]/5-rn1
) a
CROSS APPLY
(
VALUES (IslandStart-5), (IslandEnd+5)
) b ([counter])
) a
GROUP BY rn2/2
) a;
I reckon I may be over-thinking the problem a bit.
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply