Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009

    Unanswered: Interleave / Prevailing Values Via PostgreSql

    Say we had two tables with a value matched with a time stamp

    TABLE 1

    TI X1
    13 27
    17 19
    22 33

    Table 2
    TI Y1
    14 122
    19 132

    We want to combine these tables in a way in which gives us the following output that describes at time 13 what is the prevailing value of Y1. Additionally it allows us to calculate the difference between consecutive row values of time, such as 14-13.

    Combined Table (Achievable in SAS via interleave (for fill down) and first obs=2 (for duration), but we can't figure it out in SQL)

    TI X1 Y1 duration
    13 27 null 14-13=1
    14 27 122 17-14=3
    17 19 122 19-17=2
    19 19 132 22-19=3
    22 33 132

  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    What is the field you want to use to join the tables?

    i.e., if TI is the field used to join the tables, there would have to be a value of 13 in both tables to be able to join them. With the values you've shown, you could not KNOW the value of Y1 at time 13. Possible, you might be able to interpolate a value, but, you wouldn't know if it was correct or not.

    It APPEARS that you assume that, as time increments in either table, you assume that the value in the other table would remain unchanged.

    keep in mind that SQL is a set-based language. The order that a given row is presented is not guaranteed, unless you proved a specific sort order. And, it really had no easy mechanism to compare values from one row to another.

    As a first 'rough draft,' take a look at a union to merge the data from the two tables. Something along the lines of this:
    Select TI, X1, NULL as Y1 from TABLE 1
    Select TI, NULL as X1, Y1 from Table 2
    Last edited by loquin; 01-19-09 at 00:50.
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

  3. #3
    Join Date
    Jan 2009
    Thanks Loquin. In the context that we are dealing with it is accurate to assume that the value seen at time 13 remains at time 14 even if there is no update since comparing the two sets of values from different simultaneous systems. Is there a way to create a 'prevailing' value just using sql? We use SAS to do this currently but would prefer sql.

    As for the rows -- that makes sense to me.. looks like we will need to keep an alternative programming language to do that.

    Thanks for the union bit, we need the previous values to fill down..which has been tricky in SQL.

Posting Permissions

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