none
Average column RRS feed

  • 질문

  • Good Morning,

    I am wondering how I can  add a column to this SQL script that will calculate the AVG(PROC_TIME).

    If the PROC_NUMBER IS >= 10 Then the AVG should be grouped on MD_CODE, PROC_BLOCK_DESCRIPTION AND PROC_DESCRIPTION.

    If the PROC_NUMBER IS < 10 Then I need it to display the AVG grouped on just the PROC_BLOCK_DESCRIPTION AND PROC_DESCRIPTION.

    I am basically wanting a column that calculates the average procedure time per doctor, but if the doctor hasnt done 10 or more of a type of procedure then it will display the overall average across all the doctors for that procedure.

    My code is

    SELECT 
    HOSP_CODE, PROC_DATE, OPP_PRO_SPECIALTY, MD_CODE, GIVEN_NAME, SURNAME, PROC_BLOCK_DESCRIPTION, PROC_DESCRIPTION, PROC_TIME,
    ROW_NUMBER() OVER (PARTITION BY MD_CODE, PROC_BLOCK_DESCRIPTION, PROC_DESCRIPTION ORDER BY PROC_DATE) AS PROC_NUMBER
    FROM #PROCEDURES
    ORDER BY MD_CODE, PROC_BLOCK_DESCRIPTION, PROC_DESCRIPTION

    My output currently looks like this:

    2013년 6월 5일 수요일 오전 3:55

답변


  • WITH CTE AS
    (SELECT 
    HOSP_CODE, PROC_DATE, OPP_PRO_SPECIALTY, MD_CODE, GIVEN_NAME, SURNAME, PROC_BLOCK_DESCRIPTION, PROC_DESCRIPTION, PROC_TIME,
    ROW_NUMBER() OVER (PARTITION BY MD_CODE, PROC_BLOCK_DESCRIPTION, PROC_DESCRIPTION ORDER BY PROC_DATE) AS PROC_NUMBER,
    COUNT(*) OVER(PARTITION BY MD_CODE, PROC_BLOCK_DESCRIPTION, PROC_DESCRIPTION) AS NBR_PROCS,
    AVG(PROC_TIME) OVER(PARTITION BY MD_CODE, PROC_BLOCK_DESCRIPTION, PROC_DESCRIPTION) AS AVG_BY_MD,
    AVG(PROC_TIME) OVER(PARTITION BY PROC_BLOCK_DESCRIPTION, PROC_DESCRIPTION) AS AVG_BY_PROC
    FROM #PROCEDURES)
    SELECT 
    HOSP_CODE, PROC_DATE, OPP_PRO_SPECIALTY, MD_CODE, GIVEN_NAME, SURNAME, PROC_BLOCK_DESCRIPTION, PROC_DESCRIPTION, PROC_TIME,
    ROW_NUMBER() OVER (PARTITION BY MD_CODE, PROC_BLOCK_DESCRIPTION, PROC_DESCRIPTION ORDER BY PROC_DATE) AS PROC_NUMBER,
    CASE WHEN NBR_PROCS < 10 THEN AVG_BY_PROC ELSE AVG_BY_MD END AS AVG_PROC_TIME
    FROM CTE
    ORDER BY MD_CODE, PROC_BLOCK_DESCRIPTION, PROC_DESCRIPTION

    Tom


    • 답변으로 표시됨 lrj1985 2013년 6월 5일 수요일 오전 6:31
    • 편집됨 Tom Cooper 2013년 6월 7일 금요일 오후 11:03 Removed comment that solution only works on SQL 2012
    2013년 6월 5일 수요일 오전 6:06

모든 응답


  • WITH CTE AS
    (SELECT 
    HOSP_CODE, PROC_DATE, OPP_PRO_SPECIALTY, MD_CODE, GIVEN_NAME, SURNAME, PROC_BLOCK_DESCRIPTION, PROC_DESCRIPTION, PROC_TIME,
    ROW_NUMBER() OVER (PARTITION BY MD_CODE, PROC_BLOCK_DESCRIPTION, PROC_DESCRIPTION ORDER BY PROC_DATE) AS PROC_NUMBER,
    COUNT(*) OVER(PARTITION BY MD_CODE, PROC_BLOCK_DESCRIPTION, PROC_DESCRIPTION) AS NBR_PROCS,
    AVG(PROC_TIME) OVER(PARTITION BY MD_CODE, PROC_BLOCK_DESCRIPTION, PROC_DESCRIPTION) AS AVG_BY_MD,
    AVG(PROC_TIME) OVER(PARTITION BY PROC_BLOCK_DESCRIPTION, PROC_DESCRIPTION) AS AVG_BY_PROC
    FROM #PROCEDURES)
    SELECT 
    HOSP_CODE, PROC_DATE, OPP_PRO_SPECIALTY, MD_CODE, GIVEN_NAME, SURNAME, PROC_BLOCK_DESCRIPTION, PROC_DESCRIPTION, PROC_TIME,
    ROW_NUMBER() OVER (PARTITION BY MD_CODE, PROC_BLOCK_DESCRIPTION, PROC_DESCRIPTION ORDER BY PROC_DATE) AS PROC_NUMBER,
    CASE WHEN NBR_PROCS < 10 THEN AVG_BY_PROC ELSE AVG_BY_MD END AS AVG_PROC_TIME
    FROM CTE
    ORDER BY MD_CODE, PROC_BLOCK_DESCRIPTION, PROC_DESCRIPTION

    Tom


    • 답변으로 표시됨 lrj1985 2013년 6월 5일 수요일 오전 6:31
    • 편집됨 Tom Cooper 2013년 6월 7일 금요일 오후 11:03 Removed comment that solution only works on SQL 2012
    2013년 6월 5일 수요일 오전 6:06
  • Hi Tom,

    I didn't read the problem closely, but I do not see what is specific to SQL 2012 in your solution? Since I need to scroll to see the whole code, it is not obvious to me. 

    Can you tell and also explain your solution?


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


    My blog


    My TechNet articles

    2013년 6월 7일 금요일 오후 9:27
    중재자
  • 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. Learn how to 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. Now it is time to start guessing and typing! Thanks for the extra work :(  I will make up my own then. 

    >> I am basically wanting a column that calculates the average procedure time per doctor, but if the doctor has not done 10 or more of a type of procedure then it will display the overall average across all the doctors for that procedure. << 

    WITH Proc_Doc 
    (doctor_id, proc_code, proc_cnt, doc_proc_avg, hospital_proc_avg)
    AS
    (SELECT doctor_id, proc_code, 
            COUNT(), AVG(proc_duration),
            AVG(proc_duration) OVER (proc_code)
       FROM Procedure_Log
      GROUP BY doctor_id)

    SELECT doctor_id, proc_code,
           CASE WHEN proc_cnt > 10
                THEN doc_proc_avg
                ELSE hospital_proc_avg END
           AS proc_duration_avg
      FROM Proc_Doc;

    The CTE builds the average and count for the (doctor, procedure) pairs. The windowed expression is the hospital for each procedure. The main SELECT uses a CASE to pick which one to display for a given doctor.  

    Untested, since we also had no sample data. 

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

    2013년 6월 7일 금요일 오후 10:48
  • Hi Naomi.

    You are right, that query doesn't need SQL 2012.  I'm not sure what I was thinking when I wrote that.  It just uses aggregate functions OVER(PARTITION BY ...) which has worked since 2005.  I have removed the SQL 2012 comment from the answer.  Thanks for catching this.

    And it works by just having the cte compute the average over (partition by procedure, doctor), the average over(partition by procedure) and the count over(partition by procedure, doctor).  Then in the final select, there is a case statements that chooses the average over (partition by procedure, doctor) if the count over(partition by procedure, doctor) for this particular procedure and doctor is >= 10, otherwise it chooses the average over(partition by procedure).  So, the final output shows the average time for this particular doctor doing this particular procedure if this doctor has done this procedure 10 or more times.  If the doctor hasn't, then the average shown is the average for this procedure for all doctors.

    Tom

    2013년 6월 7일 금요일 오후 11:15