Results 1 to 11 of 11
  1. #1
    Join Date
    May 2014
    Posts
    20

    Exclamation Unanswered: join dat from multiple rows to one with same timestamp

    Hi,
    I am working with a large set of data from multiple sensors being recorded as
    ID Time sensorid scaled_val unscaled_val
    1 2014-04-04 23:48:40 43 -187.503581 -0.0000000603199
    2 2014-04-04 23:48:40 44 -187.497976 0.00000003409386
    3 2014-04-04 23:48:40 45 -187.5 0
    4 2014-04-04 23:48:40 46 -187.500779 -0.00000001311302
    5 2014-04-04 23:48:40 51 0 0
    6 2014-04-04 23:48:40 52 0 0
    7 2014-04-04 23:48:40 53 0 0
    8 2014-04-04 23:48:40 54 0 0
    9 2014-04-04 23:48:41 16 -40.084246 0.003993
    10 2014-04-04 23:48:41 17 -84.999852 0.00000001311302

    4761 2014-04-04 23:50:56 43 -187.506229 -0.0000001049042
    4762 2014-04-04 23:50:56 44 -187.500779 -0.00000001311302
    4763 2014-04-04 23:50:56 45 -187.50327 -0.0000000550747
    4764 2014-04-04 23:50:56 46 -187.502491 -0.00000004196167
    4765 2014-04-04 23:50:56 51 0 0
    4766 2014-04-04 23:50:56 52 0 0
    4767 2014-04-04 23:50:56 53 0 0
    4768 2014-04-04 23:50:56 54 0 0
    4769 2014-04-04 23:50:57 16 -40.085868 0.003992
    4770 2014-04-04 23:50:57 17 -85.00003 -0.000000002622605

    In order to analysis the data effectively I want to restructure the data in view which shows all data with one timestamp in a row and ordered by the sensorid.
    This I way I can export select to excel to do my analysis for a range of days with all the sensor data in columns. like
    data | sensor id|unscaled_val|scaled_val|sensorid|unscaled_val|s caled|val and so on till all sensors are incorporate. if some sensor data is missing I want to have null value in those columns. so each cols will have unique sensor data only.

    I am a noob and tried joins and other things but cant figureout. Please help experts.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want to see more concrete expected resuls from your sample data.
    For example:
    (1) Name appropriately for all result columns.
    (2) Show data values for all columns.

    An example I could imagine was like...
    time | sensorid_1 | unscaled_val_1 | scaled_val_1 | sensorid_2 | unscaled_val_2 | s caled_val_2 | sensorid_3 | unscaled_val_3 | scaled_val_3 | ... | sensorid_10 | unscaled_val_10 | scaled_val_10
    2014-04-04 23:48:40 | 43 | -0.0000000603199 | -187.503581 | 44 | 0.00000003409386 | -187.497976 | 45 | 0 | -187.5 | ... | 17 | 0.00000001311302 | -84.999852
    2014-04-04 23:50:56 | 43 | -0.0000001049042 | -187.506229 | 44 | -0.00000001311302 | -187.500779 | 45 | -0.0000000550747 | -187.50327 | ... | null | null | null
    2014-04-04 23:50:57 | null | null | null | null | null | null | null | null | null | ... | 17 | -0.000000002622605 | -85.00003

    Was that right?
    Or, did you expected different results?

    ... if some sensor data is missing I want to have null value in those columns. ...
    What did you mean by "some sensor data is missing"?
    Please show some more concrete sample data and expected results from the data.
    Last edited by tonkuma; 05-30-14 at 10:52. Reason: Split second row of my example of expected resuls into two rows.

  3. #3
    Join Date
    May 2014
    Posts
    20
    Thanks tonkuma,

    The gaps might be as I added few sensor id after May 9th and thus is we transpose them to columns 1 thourgh 66( I have 66 sensor id) many of them a redundant now. TO maintain the old data and logic I did not dump the redundant sensorid's. so the query should populate all the set of recording in a row and hence might have no records on certain data(i assume this will be NULL values from db point of view)
    for the 1 point you are correct the desired way is like what you understood.

    time | sensorid_1 | unscaled_val_1 | scaled_val_1 | sensorid_2 | unscaled_val_2 | scaled_val_2 | sensorid_3 | unscaled_val_3 | scaled_val_3 | ... | sensorid_10 | unscaled_val_10 | scaled_val_10......till the last sensorid_66|unscaled_val_66 | scaled_val_66
    2014-04-04 23:48:40 | 43 | -0.0000000603199 | -187.503581 | 44 | 0.00000003409386 | -187.497976 | 45 | 0 | -187.5 | ... | 17 | 0.00000001311302 | -84.999852
    2014-04-04 23:50:56 | 43 | -0.0000001049042 | -187.506229 | 44 | -0.00000001311302 | -187.500779 | 45 | -0.0000000550747h | -187.50327 | ... | 17 | -0.000000002622605 | -85.00003

    point 2:- show data values for all columns. Yes you understood correct.

    I would appreciate is you can also suggest how to skip few sensordata is they are not meaningful anymore. like skipping senors 47 to 62 either in the same query or view.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Some questions:

    (1) How much differences in times are considered as same?
    For exampe,
    you might want to display in one line
    "1 2014-04-04 23:48:40 ..." and "9 2014-04-04 23:48:41 ..." (1 second difference)
    or
    "4761 2014-04-04 23:50:56 ..." and "4769 2014-04-04 23:50:57 ..." (1 second difference)


    (2) If relationship of result columns(i.e. sensorid_1, sensorid_2, sensorid_3, so on ...) and sensorid values in source table(i.e. 43, 44, 45, ...) are fixed,

    (2-1) could you show the relationship of them like...
    sensorid_xx(column name in results) , sensorid value(in source table)
    sensorid_1 , 43
    sensorid_2 , 44
    sensorid_3 , 45
    ...
    sensorid_10 , 17
    ...
    ...
    sensorid_66 , ??

    and what was the rationale to relate them in such ways?

    (2-2) if the relationships are fixed,
    how about suffix the name of the result columns by sensorid, like...

    time | unscaled_val_43 | scaled_val_43 | unscaled_val_44 | scaled_val_44 | unscaled_val_45 | scaled_val_45 | ... | unscaled_val_17 | scaled_val_17 | ...

    (2-3) If (2-2) was worth to consider, isn't it better to sequence the columns by the order of sensorid?

    time | unscaled_val_16 | scaled_val_16 | unscaled_val_17 | scaled_val_17 | ... | unscaled_val_43 | scaled_val_43 | ... | unscaled_val_51 | scaled_val_51 | ...



    Although, I couldn't understand the sentence
    I would appreciate is you can also suggest how to skip few sensordata is they are not meaningful anymore. like skipping senors 47 to 62 either in the same query or view.
    First of all, I hope you to answer the questions (1), (2-1), (2-2) and (2-3).

  5. #5
    Join Date
    May 2014
    Posts
    20

    Question

    Thanks again Tonkuma,

    (1.) The is being collected every second but not always as few sensors are counting and event so they might not have any increment to be reported so a select unique timestamp will give us the row index. I did not get why you have numbers in front of the data like 1, 4761, etc.(as it will change the data format for further analysis).
    (2).& (2-2) the relation is fixed (1-1, 2-2, 3-3,...47-47, ..66-66) as it the connecting link between the definition table and data table for each sensor. the id '47' will be used to filter information.
    so a table like this works for me
    time | unscaled_val_43 | scaled_val_43 | unscaled_val_44 | scaled_val_44 | unscaled_val_45 | scaled_val_45 | ... | unscaled_val_17 | scaled_val_17 |
    (2-1) is not required as they are fixed 1-1, 2-2 etc so not math is required but tge table name does not have scaled_val_(id) but instead its only 'scaled_val'(no id)
    But I dont know how to incorporate the correct id in the column name as you suggested.

    (In the last part i meant in case we want to skip some sensor data. how will the script be different. like unscaled_val, scaled_val_1, unscaled_val_3, scaled_val_3( so we can skp those data that are meaningless between some intervals. But this would be a different script then the one on the top.)

    Thanks a ton once again.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I half understood(and half confused) your description of requirements.

    Anyway,
    I want to return to your original sample data, which were...
    ID Time sensorid scaled_val unscaled_val
    1 2014-04-04 23:48:40 43 -187.503581 -0.0000000603199
    2 2014-04-04 23:48:40 44 -187.497976 0.00000003409386
    3 2014-04-04 23:48:40 45 -187.5 0
    4 2014-04-04 23:48:40 46 -187.500779 -0.00000001311302
    5 2014-04-04 23:48:40 51 0 0
    6 2014-04-04 23:48:40 52 0 0
    7 2014-04-04 23:48:40 53 0 0
    8 2014-04-04 23:48:40 54 0 0
    9 2014-04-04 23:48:41 16 -40.084246 0.003993
    10 2014-04-04 23:48:41 17 -84.999852 0.00000001311302

    4761 2014-04-04 23:50:56 43 -187.506229 -0.0000001049042
    4762 2014-04-04 23:50:56 44 -187.500779 -0.00000001311302
    4763 2014-04-04 23:50:56 45 -187.50327 -0.0000000550747
    4764 2014-04-04 23:50:56 46 -187.502491 -0.00000004196167
    4765 2014-04-04 23:50:56 51 0 0
    4766 2014-04-04 23:50:56 52 0 0
    4767 2014-04-04 23:50:56 53 0 0
    4768 2014-04-04 23:50:56 54 0 0
    4769 2014-04-04 23:50:57 16 -40.085868 0.003992
    4770 2014-04-04 23:50:57 17 -85.00003 -0.000000002622605
    in that,
    ID = 1-8 had Time=2014-04-04 23:48:40
    ID = 9-10 had Time=2014-04-04 23:48:41
    ID = 4761-4768 had Time=2014-04-04 23:50:56
    ID = 4769-4770 had Time=2014-04-04 23:50:57

    But, you might want to get the results in which ID= 1-10 in one row and ID= 4761-4770 in another one row.
    Is my guess right?

  7. #7
    Join Date
    May 2014
    Posts
    20
    Thanks,
    No I would like to have same row with single time and date so
    date1, time1 ...sensor1 val1.................till sensor47
    date 2, time 2.....sensor1 val1.................till sensor47so each date and time combinations will be unique and have one row with the data from all sensors.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want to confirm some points again.

    (1) Do you want 4 rows from your sample data?
    Like this...
    Code:
    Date_time           | unscaled_val_1 | scaled_val_1 | unscaled_val_2 | scaled_val_2 | ...  | unscaled_val_47 | scaled_val_47
    2014-04-04 23:48:40 | /* values for ID = 1-8 */
    2014-04-04 23:48:41 | /* values for ID = 9-10 */
    2014-04-04 23:50:56 | /* values for ID = 4761-4768 */
    2014-04-04 23:50:57 | /* values for ID = 4769-4770 */
    Because you wrote
    Quote Originally Posted by rkbest View Post
    ... I would like to have same row with single time and date so
    ...
    ...so each date and time combinations will be unique and have one row with the data from all sensors.

    (2) Why till sensor47?
    And, not till 66?

    You wrote
    Quote Originally Posted by rkbest View Post
    ...
    The gaps might be as I added few sensor id after May 9th and thus is we transpose them to columns 1 thourgh 66( I have 66 sensor id) many of them a redundant now.
    ...
    for the 1 point you are correct the desired way is like what you understood.

    time | sensorid_1 | unscaled_val_1 | scaled_val_1 | sensorid_2 | unscaled_val_2 | scaled_val_2 | sensorid_3 | unscaled_val_3 | scaled_val_3 | ... | sensorid_10 | unscaled_val_10 | scaled_val_10......till the last sensorid_66|unscaled_val_66 | scaled_val_66
    2014-04-04 23:48:40 | 43 | -0.0000000603199 | -187.503581 | 44 | 0.00000003409386 | -187.497976 | 45 | 0 | -187.5 | ... | 17 | 0.00000001311302 | -84.999852
    2014-04-04 23:50:56 | 43 | -0.0000001049042 | -187.506229 | 44 | -0.00000001311302 | -187.500779 | 45 | -0.0000000550747h | -187.50327 | ... | 17 | -0.000000002622605 | -85.00003

  9. #9
    Join Date
    May 2014
    Posts
    20
    Thanks
    I am sorry my response to the point are
    1. 1 row for all data with same timestamp, i.e. the data should have time-stamp as the unique key and the data for all(1-66) sensors corresponding to that time in that row.
    Date_time | unscaled_val_1 | scaled_val_1 | unscaled_val_2 | scaled_val_2 | ... | unscaled_val_66 | scaled_val_66
    2014-04-04 23:48:40 | /* values for sensorID = 1-66 */
    2014-04-04 23:48:41 | /* values for sensorID =1-66 */
    2014-04-04 23:50:56 | /* values for sensorID = 1-66 */
    2014-04-04 23:50:57 | /* values for sensorID = 1-66 */

    2. True all the sensors 1-66, I mentioned 47 as the sample only showed 47 and I did not want to confuse and can modify script to include the rest sensors data.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Code:
    SELECT date_time
         , MAX( CASE sensorid WHEN  1 THEN unscaled_val END ) AS unscaled_val_1
         , MAX( CASE sensorid WHEN  1 THEN scaled_val   END ) AS scaled_val_1
         , MAX( CASE sensorid WHEN  2 THEN unscaled_val END ) AS unscaled_val_2
         , MAX( CASE sensorid WHEN  2 THEN scaled_val   END ) AS scaled_val_2
         , MAX( CASE sensorid WHEN  3 THEN unscaled_val END ) AS unscaled_val_3
         , MAX( CASE sensorid WHEN  3 THEN scaled_val   END ) AS scaled_val_3
    /* ... */
         , MAX( CASE sensorid WHEN 10 THEN unscaled_val END ) AS unscaled_val_10
         , MAX( CASE sensorid WHEN 10 THEN scaled_val   END ) AS scaled_val_10
    /* ... */
    /* ... */
         , MAX( CASE sensorid WHEN 66 THEN unscaled_val END ) AS unscaled_val_66
         , MAX( CASE sensorid WHEN 66 THEN scaled_val   END ) AS scaled_val_66
     FROM  <name of your sensor data table>
     GROUP BY
           date_time
    ;

  11. #11
    Join Date
    May 2014
    Posts
    20
    Thanks a lot,

    my server is down since few days and I will check this suggested code asap. Thanks tonkuma

Tags for this Thread

Posting Permissions

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