SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculate Weighted Median


Calculate Weighted Median

Author
Message
ashok_raja
ashok_raja
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 73
Hi,

There is no index because this is a temp table to find the Median value per Code.

Hope this clears.

Thanks
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29713 Visits: 19006
Since you're using a temp table - you will want to add a clustered index in order to make the running totals work.

You should start by reading Jeff's article on the matter (so you can understand why the clustered index is such a hot topic all of a sudden):

http://www.sqlservercentral.com/articles/T-SQL/68467/

Also since you're using temp tables, you should pre-aggregate the data so that there is ONE record per code+value combination. Referring back to your example records, having multiple likes of code=160, value= 40 would make like substantially more complicated than necessary. Praggregating would also allow you to know what your target number is on the running total.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
LutzM
LutzM
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23903 Visits: 13559
Ok, here's what I came up with.
It is imporant to understand the quirky update concept as well as the requirements that need to be fulfilled. So, I urge you to carefully read the article Chris mentioned (the same article is referenced in my sample code, too...)

SET NOCOUNT ON

--- original data
CREATE TABLE #dr_temp(
[PK] [INT] IDENTITY(1,1) NOT NULL,
[ID] [INT] NOT NULL,
Code [NVARCHAR](10) NOT NULL,
[VALUE] [INT] NOT NULL,
[Volume] [INT] NOT NULL
)

INSERT INTO #dr_temp(ID,Code,VALUE,Volume) VALUES(1,'120',40,30778)
INSERT INTO #dr_temp(ID,Code,VALUE,Volume) VALUES(1,'120',40,24860)
INSERT INTO #dr_temp(ID,Code,VALUE,Volume) VALUES(1,'120',40,82043)
INSERT INTO #dr_temp(ID,Code,VALUE,Volume) VALUES(1,'120',41,136116)
INSERT INTO #dr_temp(ID,Code,VALUE,Volume) VALUES(1,'120',42,106886)
INSERT INTO #dr_temp(ID,Code,VALUE,Volume) VALUES(1,'120',45,179646)

-- column added to hold RunningTotal values
ALTER TABLE #dr_temp ADD RunningTotal INT

-- add clustered index to guarantee the specific order for the running total
CREATE CLUSTERED INDEX IX_#dr_temp_ID_Volume ON #dr_temp (ID,PK);

-- perform the "quirky update"
-- for details, please see Jeff Modens related article:
-- Link: http://www.sqlservercentral.com/articles/T-SQL/68467/

DECLARE @PrevID INT
DECLARE @RunningTotal INT

UPDATE #dr_temp
SET
@RunningTotal = RunningTotal =
CASE
WHEN ID = @PrevID THEN @RunningTotal + Volume ELSE Volume
END,
@PrevID = ID
FROM #dr_temp
WITH (TABLOCKX) OPTION (MAXDOP 1)

;WITH cte AS -- calculate the median per ID
(
SELECT
pk,
ID,
MedianDiff = (RunningTotal - (MAX(RunningTotal) OVER (PARTITION BY id))/2.0 )
FROM #dr_temp
), cte2 AS -- find the PK values holding values larger than the median and number thos PK's
(
SELECT
pk AS pkSub,
ID,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY pk ) AS ROW
FROM cte
WHERE MedianDiff > 0
)
-- final output: values of the row that holds the 2nd value larger than the median
SELECT #dr_temp.*
FROM cte2
INNER JOIN #dr_temp
ON cte2.id = #dr_temp.id
AND pksub=pk
WHERE ROW=2

DROP TABLE #dr_temp





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search