10 Replies Latest reply: Mar 2, 2013 4:43 PM by Frank Kulash RSS

    Students T Critical Value

    spalato76
      Hi All,

      I'm trying to calculate 90/95% confidence intervals on data that I have. Oracle doesn't seem to be able to give me the Critical T-Values from the Student's T Table given N-1 degrees of freedom and a given confidence level, ie 90 or 95%. I've managed to create the student's t table in Oracle, I've named the table STUDENTS_T_TABLE_REF. Below is a small sample of the table:
      DEGREE_FREEDOM     CONFIDENCE_LVL     CRITICAL_T_VALUE
      1     90     6.3138
      2     90     2.92
      3     90     2.3534
      4     90     2.1319
      5     90     2.015
      6     90     1.9432
      7     90     1.8946
      8     90     1.8595
      9     90     1.8331
      10     90     1.8124
      11     90     1.7959
      12     90     1.7823
      13     90     1.7709
      14     90     1.7613
      1     95     12.7065
      2     95     4.3026
      3     95     3.1824
      4     95     2.7764
      5     95     2.5706
      6     95     2.4469
      7     95     2.3646
      8     95     2.306
      9     95     2.2621
      10     95     2.2282
      11     95     2.201
      12     95     2.1788
      13     95     2.1604
      14     95     2.1448
      I want to create a function or procedure that looks up the Critical T Value from this table given a user inputted confidence level, ie 95%, given N-1 degrees of freedom where N = Sample size. The sample sizes will be pulled from aggregate tables I create. So for instance I have a table called AGGREGATE_DATA_SAMPLES and within this table I have a column called "SAMPLE_SIZE". What I would like to have is an oracle function or procedure that allows the user to specify the following as input parameters:

      1. Name of the column in the aggregate table that contains the sample sizes. The sample size inputted is substracted by 1 to get degrees of freedom.
      2. Confidence Level, ie 95.

      This function/procedure in turn outputs or returns the Critical T Value from the STUDENTS_T_TABLE_REF table. So for example I'll name of the function/procedure CRITICAL_T and call it by inputting the parameters above so it would look like, CRITICAL_T(sample_size,95) and it would return, for every row in my AGGREGATE_DATA_SAMPLES table the critical t value. So if I had 3 rows in my AGGREGATE_DATA_SAMPLES table, it would return the critical t given the sample size and confidence level of 95%.
       Select sample_size, critical_t(sample_size,95) from AGGREGATE_DATA_SAMPLES; 
      
      SAMPLE_SIZE   CRITICAL_T_VALUE (computed)
      9                  2.306
      14                 2.1604
      4                  3.1824
      Hope this makes sense. I'm using Oracle 10g version. Any help would be appreciated.

      Thanks,
      Ed
        • 1. Re: Students T Critical Value
          Christine Schnittker
          Hi,
          if I understand you correctly, you don't need to compute anything, just look it up, and you can do that with a join:
          select ds.sample_size, 
                 sr.critical_t_value
            from aggregate_data_samples ds,
                 students_t_table_ref sr
           where sr.confidence_level = 95
                 and sr.degree_freedom = ds.sample_size-1;
          //Tine
          • 2. Re: Students T Critical Value
            spalato76
            Hi Christine,

            Thanks for the response. Yes, I can do this as a join but I would like it setup as a function or procedure if possible.

            Ed
            • 3. Re: Students T Critical Value
              Frank Kulash
              Hi, Ed,
              spalato76 wrote:
              Hi All,

              I'm trying to calculate 90/95% confidence intervals on data that I have. Oracle doesn't seem to be able to give me the Critical T-Values from the Student's T Table given N-1 degrees of freedom and a given confidence level, ie 90 or 95%.
              Are you sure none of the built-in statistical functions do what you you want? If one of them is anywehere close to what you want, it would probably be easier to build on that rather than start from scratch.
              I've managed to create the student's t table in Oracle, I've named the table STUDENTS_T_TABLE_REF. Below is a small sample of the table:
              DEGREE_FREEDOM     CONFIDENCE_LVL     CRITICAL_T_VALUE
              1     90     6.3138
              2     90     2.92
              ...
              14     95     2.1448
              Post CREATE TABLE and INSERT statements. You don't have to post INSERT statements for the entire table, just enough to test the function.
              >
              >
              I want to create a function or procedure that looks up the Critical T Value from this table given a user inputted confidence level, ie 95%, given N-1 degrees of freedom where N = Sample size. The sample sizes will be pulled from aggregate tables I create. So for instance I have a table called AGGREGATE_DATA_SAMPLES and within this table I have a column called "SAMPLE_SIZE". What I would like to have is an oracle function or procedure that allows the user to specify the following as input parameters:

              1. Name of the column Do you really want to pass the name of the column? That would require dynamic SQL. Can't you derive what values you need from the column (using built-in functions like COUNT) first, and then just call the function with those values as arguments? It will make the function much simpler and more robust.
              in the aggregate table that contains the sample sizes. The sample size inputted is substracted by 1 to get degrees of freedom.
              2. Confidence Level, ie 95.

              This function/procedure in turn outputs or returns the Critical T Value from the STUDENTS_T_TABLE_REF table. So for example I'll name of the function/procedure CRITICAL_T and call it by inputting the parameters above so it would look like, CRITICAL_T(sample_size,95) and it would return, for every row in my AGGREGATE_DATA_SAMPLES table the critical t value. So if I had 3 rows in my AGGREGATE_DATA_SAMPLES table, it would return the critical t given the sample size and confidence level of 95%.
               Select sample_size, critical_t(sample_size,95) from AGGREGATE_DATA_SAMPLES; 
              
              SAMPLE_SIZE   CRITICAL_T_VALUE (computed)
              9                  2.306
              14                 2.1604
              4                  3.1824
              Hope this makes sense. I'm using Oracle 10g version. Any help would be appreciated.
              It would help a lot if you posted CREATE TABLE and INSERT statements for both aggregate_data_samples and students_t_table_ref (again, just enough to calculate the results you posted from the query above). Explain, step by step, how you get the results you want from those tables. Not all of the people who want to help you know a lot about statistics.
              • 4. Re: Students T Critical Value
                Christine Schnittker
                Hi Ed,

                why? ;) If you use it in a select like the one you posted, the only thing you'll gain is a loss of performance.

                But it is possible - now you have the select, you can easily create a function as a wrapper around it which takes said in parameters and returns that which gets selected (has to be a function, not a procedure, since procedures cannot be called from sql). Give it a try ;)

                //Tine
                • 5. Re: Students T Critical Value
                  ranit B
                  Yes, I can do this as a join
                  Any specific reason for this?
                  Why do you want to complicate things (by creating an extra object - proc/func) when it can be done with a simple query
                  but I would like it setup as a function or procedure if possible.
                  Did you try doing this?
                  I guess shouldn't be a big deal... Could you please show your efforts?
                  • 6. Re: Students T Critical Value
                    spalato76
                    Hi Frank,

                    Thanks for the reply. Sorry for not creating the create and insert statements....I figured it would not be necessary. As far the built in statistical tests are concerned...the t tests provide you with the observed T value but not the critical t which is needed to multiple by the standard error to get the confidence interval. I can calculate the standard error, just need to multiply it by the critical t so what I really want is just a simple function to lookup the critical t value given the n-1 degrees of freedom and confidence level. Perhaps I've complicated it too much....all I need is a function that provides the critical t value given the degrees of freedom and confidence level:
                    create table students_t_table_ref (
                    degrees_freedom   number,
                    confidence_lvl    number,
                    critical_t_value  number
                    );
                    
                    insert into students_t_table_ref values(1,90,6.3138);
                    insert into students_t_table_ref values(2,90,2.92);
                    insert into students_t_table_ref values(3,90,2.3534);
                    insert into students_t_table_ref values(4,90,2.1319);
                    insert into students_t_table_ref values(5,90,2.015);
                    insert into students_t_table_ref values(6,90,1.9432);
                    insert into students_t_table_ref values(7,90,1.8946);
                    insert into students_t_table_ref values(8,90,1.8595);
                    insert into students_t_table_ref values(9,90,1.8331);
                    insert into students_t_table_ref values(10,90,1.8124);
                    insert into students_t_table_ref values(11,90,1.7959);
                    insert into students_t_table_ref values(12,90,1.7823);
                    insert into students_t_table_ref values(13,90,1.7709);
                    insert into students_t_table_ref values(14,90,1.7613);
                    insert into students_t_table_ref values(1,95,12.7065);
                    insert into students_t_table_ref values(2,95,4.3026);
                    insert into students_t_table_ref values(3,95,3.1824);
                    insert into students_t_table_ref values(4,95,2.7764);
                    insert into students_t_table_ref values(5,95,2.5706);
                    insert into students_t_table_ref values(6,95,2.4469);
                    insert into students_t_table_ref values(7,95,2.3646);
                    insert into students_t_table_ref values(8,95,2.306);
                    insert into students_t_table_ref values(9,95,2.2621);
                    insert into students_t_table_ref values(10,95,2.2282);
                    insert into students_t_table_ref values(11,95,2.201);
                    insert into students_t_table_ref values(12,95,2.1788);
                    insert into students_t_table_ref values(13,95,2.1604);
                    insert into students_t_table_ref values(14,95,2.1448);
                    Ed

                    Edited by: spalato76 on Mar 2, 2013 12:02 PM
                    • 7. Re: Students T Critical Value
                      ranit B
                      Not tested
                      create or replace function ctv(ss number,cl number)
                      return number
                      as
                      v_ret number;
                      begin
                      
                      select critical_t_value 
                      into v_ret
                      from students_t_table_ref
                      where
                           degrees_freedom = ss
                           AND confidence_lvl = cl;
                      
                      return v_ret;
                      
                      end ctv;
                      • 8. Re: Students T Critical Value
                        Frank Kulash
                        Hi,

                        So you just want a function that subtracts 1 from one of its arguments, and then looks up something in a table?
                        Here's a function that does that:
                        CREATE OR REPLACE FUNCTION  critical_t
                        (   in_sample_size     IN   NUMBER
                        ,   in_confidence_lvl  IN   NUMBER
                        )
                        RETURN NUMBER
                        IS
                            return_val  NUMBER;
                        BEGIN
                            SELECT  MIN (critical_t_value)
                            INTO    return_val
                            FROM    students_t_table_ref
                            WHERE   degrees_freedom  = in_sample_size - 1
                            AND         confidence_lvl   = in_confidence_lvl;
                        
                            RETURN  return_val;
                        END  critical_t;
                        /
                        SHOW ERRORS
                        As others have said, it's easy enough to do with a join.
                        A function is more convenient. There's nothing wrong with using a function for this when speed isn't important.
                        • 9. Re: Students T Critical Value
                          spalato76
                          Thanks Frank.

                          If I wanted to extend the concept of the function below....and let the sample size be pulled from a column in another table, how could I do this. The function below is great if you are manually inputting the sample size but if you have an aggregate table with many sample sizes you need a critical t value for...how can I do this? Below is the code to create the aggregate sample sizes table:
                          create table aggregate_sample_sizes (
                          sample_id number,
                          sample_size number
                          );
                          
                          insert into aggregate_sample_sizes values(1,6);
                          insert into aggregate_sample_sizes values(2,5);
                          insert into aggregate_sample_sizes values(3,6);
                          insert into aggregate_sample_sizes values(4,17);
                          insert into aggregate_sample_sizes values(5,3);
                          insert into aggregate_sample_sizes values(6,20);
                          insert into aggregate_sample_sizes values(7,19);
                          insert into aggregate_sample_sizes values(8,18);
                          insert into aggregate_sample_sizes values(9,9);
                          insert into aggregate_sample_sizes values(10,11);
                          insert into aggregate_sample_sizes values(11,11);
                          insert into aggregate_sample_sizes values(12,6);
                          insert into aggregate_sample_sizes values(13,2);
                          insert into aggregate_sample_sizes values(14,9);
                          Thanks,
                          Ed
                          • 10. Re: Students T Critical Value
                            Frank Kulash
                            Hi,

                            Thanks for posting the table. Don't forget to post the results you want from that table (and whatever other inputs may be needed, such as confidence_lvl=95), and a step-by-step explanation of how you get those results from that table (and the students_t_table_ref table).