Professional Web Applications Themes

STD of multiple columns - MySQL

Dear Mysql-ians, I want to calculate the standard deviation of data that are in multiple columns. I know how to calculate the STD of 1 column (e.g. X1 of table_X) using: SELECT STD(X1) FROM table_X; but I want to calculate now the STD of the union of data of columns (e.g. X1, X2, ..., X100 of table_X). Does anyone has any suggestion on how to do that? I hoped something as SELECT STD(X1,X2,...,X100) FROM table_X existed, but apparently it does not. Thanks for your suggestions in advance! Kind regards, Stef...

  1. #1

    Default STD of multiple columns

    Dear Mysql-ians,

    I want to calculate the standard deviation of data that are in multiple
    columns. I know how to calculate the STD of 1 column (e.g. X1 of
    table_X) using:

    SELECT STD(X1) FROM table_X;

    but I want to calculate now the STD of the union of data of columns
    (e.g. X1, X2, ..., X100 of table_X).

    Does anyone has any suggestion on how to do that? I hoped something as
    SELECT STD(X1,X2,...,X100) FROM table_X existed, but apparently it does
    not.

    Thanks for your suggestions in advance!

    Kind regards,
    Stef

    stefaan.lhermitte@agr.kuleuven.ac.be Guest

  2. #2

    Default Re: STD of multiple columns

    Thanks for your help!

    If I look at my design, it looks like:
    (table) id, obs_time1, obs_time2, ..., obs_time100
    where:
    obs_timeX = observation at "time X"

    I have over 2 million records (with different unique id's) for this
    table, and I want to create the STD of all observations of 1 id through
    time.

    If I understand your design well, you suggest to reform it towards:
    (table_1) studentid + other info
    (table_2) studentid obs_time obs_value
    where:
    obs_time = "time X" of obs_timeX
    obs_value = value of obs_timeX with corresponding "time X"

    If I am correct, I will get a very long table_2 since I create 2
    millions (ids) *100 records (for every obs_time). Don't I create much
    more redundant information then (having only 1 row of obs_timeX per id
    and having unique id's)?

    I hope i made myself clear?

    Thanks again for your help!

    Regards
    Stef

    stefaan.lhermitte@agr.kuleuven.ac.be Guest

  3. #3

    Default Re: STD of multiple columns

    [email]stefaan.lhermitteagr.kuleuven.ac.be[/email] wrote:
    > Dear Mysql-ians,
    >
    > I want to calculate the standard deviation of data that are in multiple
    > columns. I know how to calculate the STD of 1 column (e.g. X1 of
    > table_X) using:
    >
    > SELECT STD(X1) FROM table_X;
    >
    > but I want to calculate now the STD of the union of data of columns
    > (e.g. X1, X2, ..., X100 of table_X).
    >
    > Does anyone has any suggestion on how to do that? I hoped something as
    > SELECT STD(X1,X2,...,X100) FROM table_X existed, but apparently it does
    > not.
    >
    > Thanks for your suggestions in advance!
    >
    > Kind regards,
    > Stef
    >
    Stef,

    I don't *think* it's possible from your current design.

    Perhaps a redesign is in order. Having 100 columns containing basically the
    same information is not a good design.

    For instance, in the case of student test scores - you could do something like:

    (table) studentid name test1scrore test2score test3score test4score

    (Of course there would be more info)

    A better design would be:

    (table 1) studentid name

    (table 2) studentid testid score

    Such a design is more versatile - and cures your problem along the way.


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  4. #4

    Default Re: STD of multiple columns

    [email]stefaan.lhermitteagr.kuleuven.ac.be[/email] wrote:
    > Thanks for your help!
    >
    > If I look at my design, it looks like:
    > (table) id, obs_time1, obs_time2, ..., obs_time100
    > where:
    > obs_timeX = observation at "time X"
    >
    > I have over 2 million records (with different unique id's) for this
    > table, and I want to create the STD of all observations of 1 id through
    > time.
    >
    > If I understand your design well, you suggest to reform it towards:
    > (table_1) studentid + other info
    > (table_2) studentid obs_time obs_value
    > where:
    > obs_time = "time X" of obs_timeX
    > obs_value = value of obs_timeX with corresponding "time X"
    >
    > If I am correct, I will get a very long table_2 since I create 2
    > millions (ids) *100 records (for every obs_time). Don't I create much
    > more redundant information then (having only 1 row of obs_timeX per id
    > and having unique id's)?
    >
    > I hope i made myself clear?
    >
    > Thanks again for your help!
    >
    > Regards
    > Stef
    >
    Stef,

    Yep, that's exactly what I'm suggesting. Do some reading up on "Database
    Normalization" - it can help you understand why this is potentially a better
    solution.

    And yes, the new table will be quite long. But your existing table is quite
    wide! 200M rows (the max you could have) isn't as different than what you have
    now - 2M rows with > 100 columns in each row.

    Also, as you normalize your tables, you can potentially have more, if the
    majority of the fields are filled. But you may also have less, if only a small
    number are filled. And normalizing your tables makes things more flexible.



    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  5. #5

    Default Re: STD of multiple columns

    Thanks for your suggestion! I will try to reorganize my data.

    I was thinking of making a query to reorganize my data.
    E.g.:
    (SELECT id, "name(obs_time1)" AS obs_time, obs_time1 AS obs_value FROM
    table_1)
    UNION
    (SELECT id, "name(obs_time2)" AS obs_time, obs_time2 AS obs_value FROM
    table_1)
    UNION
    .......
    UNION
    (SELECT id, "name(obs_time100)" AS obs_time, obs_time100 AS obs_value
    FROM table_1)
    ORDER BY id

    with:
    "name(obs_timeX)"= the name of my columns I now use to extract
    obs_timeX

    Hopefully that will work!

    Regards,
    Stef

    stefaan.lhermitte@agr.kuleuven.ac.be Guest

  6. #6

    Default Re: STD of multiple columns


    Stef,

    do you really want to run a union of 100 select on a table with 2
    millions records ?!?

    Actually I don't see where is the problem, why dont' you just apply the
    function std to each single column, select std(v1), std(v2), ... ? Why
    do you feel you need a multivariate function?

    -tom

    [email]stefaan.lhermitteagr.kuleuven.ac.be[/email] ha scritto:
    > Thanks for your suggestion! I will try to reorganize my data.
    >
    > I was thinking of making a query to reorganize my data.
    > E.g.:
    > (SELECT id, "name(obs_time1)" AS obs_time, obs_time1 AS obs_value FROM
    > table_1)
    > UNION
    > (SELECT id, "name(obs_time2)" AS obs_time, obs_time2 AS obs_value FROM
    > table_1)
    > UNION
    > ......
    > UNION
    > (SELECT id, "name(obs_time100)" AS obs_time, obs_time100 AS obs_value
    > FROM table_1)
    > ORDER BY id
    >
    > with:
    > "name(obs_timeX)"= the name of my columns I now use to extract
    > obs_timeX
    >
    > Hopefully that will work!
    >
    > Regards,
    > Stef
    tommaso.gastaldi@uniroma1.it Guest

  7. #7

    Default Re: STD of multiple columns

    Thanx Jerry. I followed your advice and it worked wonderfully!

    stefaan.lhermitte@agr.kuleuven.ac.be Guest

Similar Threads

  1. Datagrid sorting on multiple columns
    By guygousse in forum Macromedia Flash Data Integration
    Replies: 0
    Last Post: November 19th, 04:17 AM
  2. Datagrid in multiple columns
    By Arr in forum Macromedia Flash Data Integration
    Replies: 0
    Last Post: July 20th, 03:35 PM
  3. Total of multiple columns in datagrid
    By Ian Cook in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: April 19th, 11:15 PM
  4. help w/ multiple columns in NSTableView
    By Willian Irving Zumwalt in forum Mac Programming
    Replies: 2
    Last Post: September 23rd, 03:05 AM
  5. Multiple index columns (keys)
    By Tony in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: August 25th, 04:34 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139