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 ««12

Calculate Weighted Median Expand / Collapse
Author
Message
Posted Wednesday, March 3, 2010 10:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, April 17, 2010 9:39 AM
Points: 16, Visits: 73
Hi,

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

Hope this clears.

Thanks


Post #876140
Posted Wednesday, March 3, 2010 10:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:47 PM
Points: 7,154, Visits: 15,645
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?
Post #876188
Posted Wednesday, March 3, 2010 11:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:47 AM
Points: 6,842, Visits: 13,368
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
Post #876217
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse