none
Find the average value from the Top 5 of that value RRS feed

  • Question

  • I have a table called UserToSubject, which consists of 5 fields (Affinity, MTEV, SubjectID, UserID, UserToSubjectID)

    I know how to do this in two queries, but if this can be combined into 1 query that would be ideal.

    MTEV is an expertise value  of SQL Type Decimal 18,6

    I have a requirement to find the Average of the Top 5 MTEV values for the followers of a given user:

    This query find the Top 5 MTEV values for the followers of a given User in a given Subject.

    Create PROCEDURE [dbo].[UserToSubject_FetchTopExperts]
    
    	-- Parameter for @UserID
    	@UserID int,
    	@SubjectID int
    
    AS
    BEGIN
    
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON
    
        -- Begin Select Statement
        Select Top 5 [Affinity],[MTEV],[SubjectID],[UserID],[UserToSubjectID]
    
        -- From tableName
        From [UserToSubject] Where [UserID] In (Select FollowerUserID From UserFollower Where UserIDBeingFollowed = @UserID And SubjectID = @SubjectID)
        
        -- Only Return Items That Have An MTEV Set
    	And [MTEV] > 0
    	
    	-- Order By The MTEV in reverse order
    	Order By MTEV Desc
        
    
    END

    And from the above results I can take the UserToSubjectID's and build a comma delimited list, and this query will Average the MTEV of the top 5 results:

    Note: dbo.Split is a sql function that splits a comma delimited list, so essentially it is the same as saying:

    Where [UserToSubjectID] In (17519,17148,17590,17397,17814)

    Create PROCEDURE [dbo].[UserToSubject_FindAverageMTEV]
    
    	-- Parameter for list of UserIDs
    	@TopFiveUserToSubjectIDs nchar(80)
    
    AS
    BEGIN
    
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON
    
        -- Begin Select Statement
        Select AVG(MTEV)
    
        -- From tableName
        From [UserToSubject]
        
        -- Only Find Records That The UserToSubjectID Is in the list given.
        Where [UserToSubjectID] In (Select cast(Value as int) from dbo.Split(@TopFiveUserToSubjectIDs,','))
    
    END

    Calling two stored procedures works fine, and this is a 'Crawler' so speed is not really an issue, but my question is, is there a way to combine these two queries so I can just pass in a UserID, SubjectID combination and return the Average(MTEV) of the top five followers for a given user.

    I have some other procedures that use a Temp table to do something similiar but my attempts to combine the two queries have not worked.

    Thanks if you can help.


    Corby





    Thursday, May 24, 2012 11:03 PM

Answers

  • Why can't you simply do this?

    Create PROCEDURE [dbo].[UserToSubject_FetchTopExperts]
    
    	-- Parameter for @UserID
    	@UserID int,
    	@SubjectID int
    
    AS
    BEGIN
    
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON
    
        ;With CTE
        As
        (
        Select Top 5 A.[UserToSubjectID]
    
        -- From tableName
        From	[UserToSubject] A
    				Inner Join
    			UserFollower B
    				On A.UserID = B.FollowerUserID
    	Where	B.UserIDBeingFollowed = @UserID 
    			And B. SubjectID = @SubjectID
    			And A.[MTEV] > 0
    	Order By MTEV Desc
    	)
    	
    	Select	AVG(MTEV)
    	From	[UserToSubject] U
    	Where	[UserToSubjectID] In
    			(	Select	[UserToSubjectID]
    				From	CTE
    			)
      
    END

    Thursday, May 24, 2012 11:28 PM

All replies

  • Try:

    Create PROCEDURE [dbo].[UserToSubject_FetchTopExperts]
    
    	-- Parameter for @UserID
    	@UserID int,
    	@SubjectID int
    
    AS
    BEGIN
    
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON
    
        -- Begin Select Statement
       ;with cte as ( Select Top 5 [Affinity],[MTEV],[SubjectID],[UserID],[UserToSubjectID]
    
        -- From tableName
        From [UserToSubject] Where [UserID] In (Select FollowerUserID From UserFollower Where UserIDBeingFollowed = @UserID And SubjectID = @SubjectID)
        
        -- Only Return Items That Have An MTEV Set
    	And [MTEV] > 0
    	
    	-- Order By The MTEV in reverse order
    	Order By MTEV Desc
        )
    select *, AVG(MTEV* 1.0) OVER () as [Average MTEV]
    from cte
    
    END

    This will produce the result + Average of MTEV of 5 top followers. If you just want AVG MTEV to be returned, change the second query to be

    select AVG(MTEV * 1.0) as [Avergage MTEV] from cte


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by Sanil Mhatre Thursday, May 24, 2012 11:46 PM
    Thursday, May 24, 2012 11:13 PM
    Moderator
  • Why can't you simply do this?

    Create PROCEDURE [dbo].[UserToSubject_FetchTopExperts]
    
    	-- Parameter for @UserID
    	@UserID int,
    	@SubjectID int
    
    AS
    BEGIN
    
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON
    
        ;With CTE
        As
        (
        Select Top 5 A.[UserToSubjectID]
    
        -- From tableName
        From	[UserToSubject] A
    				Inner Join
    			UserFollower B
    				On A.UserID = B.FollowerUserID
    	Where	B.UserIDBeingFollowed = @UserID 
    			And B. SubjectID = @SubjectID
    			And A.[MTEV] > 0
    	Order By MTEV Desc
    	)
    	
    	Select	AVG(MTEV)
    	From	[UserToSubject] U
    	Where	[UserToSubjectID] In
    			(	Select	[UserToSubjectID]
    				From	CTE
    			)
      
    END

    Thursday, May 24, 2012 11:28 PM
  • Try this
    Create PROCEDURE [dbo].[UserToSubject_FindAverageMTEV]

        -- Parameter for list of UserIDs
        @TopFiveUserToSubjectIDs nchar(80)

    AS
    BEGIN

        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON

        -- Begin Select Statement
        Select AVG(MTEV)

        -- From tableName
        From (    Select Top 5 [Affinity],[MTEV],[SubjectID],[UserID],[UserToSubjectID]

        -- From tableName
        From [UserToSubject] Where [UserID] In (Select FollowerUserID From UserFollower Where UserIDBeingFollowed = @UserID And SubjectID = @SubjectID)
        
        -- Only Return Items That Have An MTEV Set
        And [MTEV] > 0
        
        -- Order By The MTEV in reverse order
        Order By MTEV Desc)

    END

    Many Thanks & Best Regards, Hua Min

    Friday, May 25, 2012 1:25 AM
  • Thank you all for your answers, I tried the other two suggestions and kept getting a 'Create/Alter Procedure must be the first line in the procedure.

    Steve's solution was correct, except for you had B.SubjectID = @SubjectID and it was actually A.SubjectID, but you had no way of knowing.

    Thank you very much.

    Here is the final solution:

    Create PROCEDURE [dbo].[UserToSubject_FindAverageMTEV]
    
    	-- Parameter for @UserID
    	@UserID int,
    	@SubjectID int
    
    AS
    BEGIN
    
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON
    
        ;With CTE
        As
        (
        Select Top 5 A.[UserToSubjectID]
    
        -- From tableName
        From	[UserToSubject] A
    				Inner Join
    			UserFollower B
    				On A.UserID = B.FollowerUserID
    	Where	B.UserIDBeingFollowed = @UserID 
    			And A.SubjectID = @SubjectID
    			And A.[MTEV] > 0
    	Order By MTEV Desc
    	)
    	
    	Select	AVG(MTEV)
    	From	[UserToSubject] U
    	Where	[UserToSubjectID] In
    			(	Select	[UserToSubjectID]
    				From	CTE
    			)
      
    END


    Corby


    Friday, May 25, 2012 3:43 PM
  • \Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions AND formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible AND not local dialect. 

    This is minimal polite behavior on SQL forums. 

    Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

    >> I have a table called UserToSubject, which consists of 5 fields aic: columns are not fields (Affinity, mtev, subject_id, user_id, user_to_subject_id) <<

    Now we have to start guessing about the schema AND try to figure out correct names in a good data model. Look at your comments; they are redundant AND useless. 

    >> mtev is an expertise value of SQL Type Decimal (18, 6) <<

    We seldom see a measurement of that size and precision. Can you explain it? How do you measure “expertise” to six decimal places? I have trouble getting physical measurements like that! I suspect that you did no research on this at all. 

    That weird “UserToSubject” is a phrase and not a valid table name. 

    >> I have a requirement to find the Average of the Top 5 mtev values for the followers of a given user .. and from the above results I can take the user_to_subject_id's and build a comma delimited list, and this query will average the mtev of the top 5 results: <<

    NO! Please read any book on RDBMS and pay attention the concept First Normal Form so you will never consider anything like this again. That split function is a kludge to mimic BASIC in SQL

    >> I have some other procedures that use a Temp table to do something similar .. <<

    temp tables are usually a kludge to fake a COBOL scratch tape in SQL.  Your approach to SQL is fundamentally wrong. Since you skipped the most basic Netiquette, I will have to invent a table to show code.

    CREATE TABLE Gradebook
    (student_id CHAR(10) NOT NULL,
     test_nbr INTEGER NOT NULL
      CHECK (test_nbr > 0),
     PRIMARY KEY (student_id, test_nbr),
     test_score DECIMAL (5,2) DEFAULT 0.0 NOT NULL
      CHECK (test_score >= 0.00))

    I think this example explains itself. We hate procedural code, so SQL programming uses VIEWs instead. This shows us each student and the average of his top five test scores. 

    CREATE VIEW Best_Five_Scores
    AS
    SELECT student_id, AVG(test_score) AS test_score_avg
      FROM (SELECT student_id, test_score,
                    ROW_NUMBER() 
                    OVER (PARTITION BY student_id
                           ORDER BY test_score DESC)
              FROM Gradebook)
            AS Student_Tests_Seq (student_id, test_score, score_seq)
     WHERE score_seq <= 5; 

    Your whole approach to SQL is fundamentally wrong. You newed to stop and educate yourself. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, May 26, 2012 3:37 AM