Post reply

PERCENTILE_DISC for median calculation

  • December 18, 2018 at 8:20 pm

    #2016778

    valeryk2000 - Tuesday, December 18, 2018 8:09 PM

    Jeff Moden - Tuesday, December 18, 2018 7:59 PM

    valeryk2000 - Tuesday, December 18, 2018 11:00 AM

    Jeff
    what does 'v' means in this code?

    ... SELECT SomeInt,SomeDecimal
    FROM (VALUES (1,1),(2,2),(3,3),(4,4)) v (SomeInt,SomeDecimal)

    It's the alias-name for the derived table of data formed by the VALUES clause.  If I were to use that alias in the code above, it would look like this...

     SELECT v.SomeInt, v.SomeDecimal
       FROM (VALUES (1,1),(2,2),(3,3),(4,4)) v (SomeInt,SomeDecimal)

    Then why field names in parentheses after v?

    Because the VALUES clause is being used as a derived table in a FROM clause and that requires column names to qualify as a derived table.  Rumor has it that you can assign the column names within the first row of the VALUEs clause but I've not tried it because doing it the way I did it in the code looks cleaner to me.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 18, 2018 at 8:21 pm

    #2016779

    valeryk2000 - Tuesday, December 18, 2018 8:12 PM

    Jeff Moden - Tuesday, December 18, 2018 7:55 PM

    valeryk2000 - Tuesday, December 18, 2018 10:43 AM

    Jeff I created the script - it is 17 Mb - 45 000 records, table with 5 fields. Not sure that we need to continue at this point - we have the answer to the original question. We will test different codes. And not sure that you have time and intention to further drill. 
    A?

    That's a bit large for this forum.  Glad you didn't attach it as a spreadsheet.

    If you post the table schema (the CREATE TABLE code for the table) and also identify which column you're trying to get the median for and whether it is a median for the whole table or you want more than one median based on some grouping, I can generate some fake data pretty quickly.  It won't take long to do a comparison after that to see what's going on.

    Ok. Tomorrow I'll send you a much smaller script with table schema and data

    That'll work.  Thanks.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 19, 2018 at 7:58 am

    #2016823

    Here is the data

  • December 19, 2018 at 8:36 am

    #2016828

    Sorry, I tried to attach .sql

  • December 19, 2018 at 8:47 am

    #2016829

    Which column are you taking the Median of?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 19, 2018 at 8:53 am

    #2016831

    Each column has its own median.

  • December 19, 2018 at 9:09 am

    #2016834

    Ah... OK.  What is the clustered index on the table and, if present, what are the non-clustered indexes?  I'm assuming, of course, that this table has a whole lot more columns.  Can you post the definition of the real table you're trying to use along with the index definitions?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 19, 2018 at 9:36 am

    #2016841

    Base table ED_Table: Just one clustered.
    For calculations:
    vw_ED_Time_Calc

  • December 19, 2018 at 10:06 am

    #2016851

    Not sure what the view is used for but what you posted appears to have been truncated.  There's no FROM clause.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 19, 2018 at 10:17 am

    #2016855

    FROM tblEDData
    Sorry

  • December 19, 2018 at 10:19 am

    #2016856

    Jeff Moden - Wednesday, December 19, 2018 10:06 AM

    Not sure what the view is used for but what you posted appears to have been truncated.  There's no FROM clause.

    View contains calculations of time difference between stages of ED visit.

  • December 19, 2018 at 1:48 pm

    #2016884

    Jeff Moden - Tuesday, December 18, 2018 8:20 PM

    valeryk2000 - Tuesday, December 18, 2018 8:09 PM

    Jeff Moden - Tuesday, December 18, 2018 7:59 PM

    valeryk2000 - Tuesday, December 18, 2018 11:00 AM

    Jeff
    what does 'v' means in this code?

    ... SELECT SomeInt,SomeDecimal
    FROM (VALUES (1,1),(2,2),(3,3),(4,4)) v (SomeInt,SomeDecimal)

    It's the alias-name for the derived table of data formed by the VALUES clause.  If I were to use that alias in the code above, it would look like this...

     SELECT v.SomeInt, v.SomeDecimal
       FROM (VALUES (1,1),(2,2),(3,3),(4,4)) v (SomeInt,SomeDecimal)

    Then why field names in parentheses after v?

    Because the VALUES clause is being used as a derived table in a FROM clause and that requires column names to qualify as a derived table.  Rumor has it that you can assign the column names within the first row of the VALUEs clause but I've not tried it because doing it the way I did it in the code looks cleaner to me.

    The rumors appear to be wrong.  I see no alternate way to assign column aliases when using a table value constructor.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • December 21, 2018 at 6:53 pm

    #2017149

    drew.allen - Wednesday, December 19, 2018 1:48 PM

    Jeff Moden - Tuesday, December 18, 2018 8:20 PM

    valeryk2000 - Tuesday, December 18, 2018 8:09 PM

    Jeff Moden - Tuesday, December 18, 2018 7:59 PM

    valeryk2000 - Tuesday, December 18, 2018 11:00 AM

    Jeff
    what does 'v' means in this code?

    ... SELECT SomeInt,SomeDecimal
    FROM (VALUES (1,1),(2,2),(3,3),(4,4)) v (SomeInt,SomeDecimal)

    It's the alias-name for the derived table of data formed by the VALUES clause.  If I were to use that alias in the code above, it would look like this...

     SELECT v.SomeInt, v.SomeDecimal
       FROM (VALUES (1,1),(2,2),(3,3),(4,4)) v (SomeInt,SomeDecimal)

    Then why field names in parentheses after v?

    Because the VALUES clause is being used as a derived table in a FROM clause and that requires column names to qualify as a derived table.  Rumor has it that you can assign the column names within the first row of the VALUEs clause but I've not tried it because doing it the way I did it in the code looks cleaner to me.

    The rumors appear to be wrong.  I see no alternate way to assign column aliases when using a table value constructor.

    Drew

    Perfect.  Thanks for the confirmation that the rumors are wrong and just as well... I wouldn't have done it differently if the rumors were right.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 13 posts - 31 through 42 (of 42 total)

You must be logged in to reply to this topic. Login to reply