Complex percent calculation - new OVER clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Complex percent calculation - new OVER clause

Author

Topic

OverMyHead
Starting Member

3 Posts

Posted - 2007-06-28 : 12:36:31

[code]create table #UserContent (
ID_UserContent int ,
ContentName varchar(64),
PopularityForever float
)
create table #UserContentPopularity (
ID_UserContentPopularity int,
ID_UserContent int,
ID_AccountPrefs int
)

insert into #UserContent(ID_UserContent, ContentName,PopularityForever) values(1, 'Sunset.jpg', 0);
insert into #UserContent(ID_UserContent, ContentName,PopularityForever) values(2, 'Boardwalk.jpg', 0);
insert into #UserContent(ID_UserContent, ContentName,PopularityForever) values(3, 'Ocean.jpg', 0);
insert into #UserContent(ID_UserContent, ContentName,PopularityForever) values(4, 'UtahVacation.jpg', 0);

insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(1,1,5);
insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(2,1,5);
insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(3,1,5);
insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(4,2,6);
insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(5,2,5);
insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(6,2,7);
insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(7,2,4);
insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(8,2,5);
insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(9,3,3);
insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(10,3,2);
insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(11,3,3);[/code]

Scenario:

User's content items are stored in #UserContent. Any time any user views that content, a record is inserted into #UserContentPopularity.

My Task:

A query must be created to populate the #UserContent.PopularityForever as a percent of the total views across all content, with 100.0 being the most popular and 0.0 as the least popular. It gets tricky for me, because I must disregard multiple views by the same user (ID_AccountPrefs). In other words, the content viewed 20 times by 2 users is less popular than the content viewed 10 times by 5 users.

My Attempts:

[code]
select ID_UserContent, count(*) TotalUniqueVotes
from (SELECT ID_UserContent, ID_AccountPrefs, count(*) as TotalVotes
FROM #UserContentPopularity
group by ID_UserContent, ID_AccountPrefs
) UniqueFilteredTotals group by ID_UserContent
[/code]

The preceeding code block successfully returns the unqiue user views for each #UserContent record. I would then need to express the TotalUniqueVotes as a percentage of the total, and update @UserContent.PopularityForever.

I explored the OVER clause new to SQL 2005. I believe that it is what I need, but I am having a hard time getting it to work for me.

Any help is greatly appreciated. My login says it all.

Thanks.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-28 : 14:02:32

funny name. hope it doesn't last though.


declare @total int
select @total=count(*) from #UserContentPopularity

update uc 
set PopularityForever=up.Popularity
from #UserContent uc
join (select ID_UserContent, 1.0*count(*)/@total as Popularity from #UserContentPopularity group by ID_UserContent) up
on up.ID_UserContent = uc.ID_UserContent

elsasoft.org

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-28 : 14:02:49

looks little messy but something like this:



Declare @t table (Id int, totvotes int)
insert into @t 
select ID_UserContent, count(*) TotalUniqueVotes
from (SELECT ID_UserContent, ID_AccountPrefs, count(*) as TotalVotes
FROM #UserContentPopularity
group by ID_UserContent, ID_AccountPrefs
) UniqueFilteredTotals group by ID_UserContent

select *  
,percentage =  convert(decimal(10,2),(convert(Decimal(10,2),totvotes)/(select convert(Decimal(10,2),sum(totvotes)) from @t T2) ) * 100)
from @t

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

OverMyHead
Starting Member

3 Posts

Posted - 2007-06-28 : 17:32:55

quote:
hope it doesn't last though

You!...If if lasts I need to find a new job.

Thank you so much guys.

I had to combine both suggestions in order to suit my needs. Jezemine, your suggestion resulted in the same percentage for ID_UserContent 1 and 3, so it wasn't accurate. Note that Sunset.jpg has 3 views from the same user (1 unique view) while Ocean.jpg has 2 unique views.

So, using the correlated subquery concept from jezemine and the logic
and the logic from dinakar, the following code is the end result.

Thanks again.




create table #UserContent (
	ID_UserContent int , 
	ContentName varchar(64), 
	PopularityForever float
)
create table #UserContentPopularity (
	ID_UserContentPopularity int,
	ID_UserContent int,
	ID_AccountPrefs int 
)

insert into #UserContent(ID_UserContent, ContentName,PopularityForever) values(1, 'Sunset.jpg', 0);
insert into #UserContent(ID_UserContent, ContentName,PopularityForever) values(2, 'Boardwalk.jpg', 0);
insert into #UserContent(ID_UserContent, ContentName,PopularityForever) values(3, 'Ocean.jpg', 0);
insert into #UserContent(ID_UserContent, ContentName,PopularityForever) values(4, 'UtahVacation.jpg', 0);

insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(1,1,5);
insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(2,1,5);
insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(3,1,5);
insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(4,2,6);
insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(5,2,5);
insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(6,2,7);
insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(7,2,4);
insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(8,2,5);
insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(9,3,3);
insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(10,3,2);
insert into #UserContentPopularity (ID_UserContentPopularity, ID_UserContent, ID_AccountPrefs ) values(11,3,3);


DECLARE @UniqueVotes table (ID_UserContent int, TotalUniqueVotes int)
INSERT INTO @UniqueVotes
SELECT ID_UserContent, COUNT(*) TotalUniqueVotes FROM (SELECT ID_UserContent, ID_AccountPrefs, count(*) as TotalVotes
			FROM #UserContentPopularity
			GROUP BY ID_UserContent, ID_AccountPrefs
			) UniqueFilteredTotals GROUP BY ID_UserContent
UPDATE #UserContent 
   SET PopularityForever=(SELECT  convert(decimal(10,2),(convert(Decimal(10,2),TotalUniqueVotes)/(select convert(Decimal(10,2),sum(TotalUniqueVotes)) from @UniqueVotes T2) ) * 100)
						FROM @UniqueVotes UV WHERE #UserContent.ID_UserContent = UV.ID_UserContent)
					   WHERE #UserContent.ID_UserContent IN (SELECT ID_UserContent 
								  FROM #UserContentPopularity)
SELECT * FROM #UserContent;

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-28 : 18:39:38

I guess I didn't understand your requirement.

the way I see it, pictures 1 and 3 both got the same number of votes in your sample data, so shouldn't they get the same popularity?

elsasoft.org

OverMyHead
Starting Member

3 Posts

Posted - 2007-06-28 : 19:51:31

Yes, there was an ambiguity in my post (my boss hates those). I am actually dealing with a view of content note a vote. A user who views the content five times really only counts as one view.

Since I will be dealing with a large amonut of data, I realized that a percentile approach would be even better than what we have here so far.

CTE (common table expression) to calculate a percentile:



DECLARE @UniqueViews table (ID_UserContent int, TotalUniqueViews int)
INSERT INTO @UniqueViews
SELECT ID_UserContent, COUNT(*) TotalUniqueViews FROM (SELECT ID_UserContent, ID_AccountPrefs, count(*) as TotalViews
			FROM #UserContentPopularity
			GROUP BY ID_UserContent, ID_AccountPrefs
			) UniqueFilteredTotals GROUP BY ID_UserContent
SELECT * FROM @UniqueViews ORDER BY TotalUniqueViews;

;with maxRank as
( select count(*) as maxRank
    from @UniqueViews
), ranker as
( select ID_UserContent,
         TotalUniqueViews,
         100.0 * row_number() over
         ( order by TotalUniqueViews, ID_UserContent ) / maxRank
            as percentile
    from maxRank
   cross join @UniqueViews
)
select * from ranker


ID_UserContent TotalUniqueViews percentile
-------------- ---------------- ---------------------------------------
1              1                33.333333333333
3              2                66.666666666666
2              4                100.000000000000

Thanks again for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources