lundi 20 avril 2015

SQL Update Loop Assistance

I am having issues working out an SQL function in Postgresql. I have managed to get this done in python but it takes a very long time on a table with millions of records.

What I have is a 'example_table' that is structured as follows with data that resembles the sample below:

Example Table
|id |version |valid_from          |valid_to              |time_valid
1  | 1       |2010-03-21 19:00:00 | 2010-03-21 19:00:00   | NULL
1   | 2       |2011-02-02 09:00:00 | 2011-02-02 09:00:00  | NULL
1   | 3       |2012-04-20 15:00:00 | 2012-04-20 15:00:00  | NULL
2   | 1       |2012-07-02 04:00:00 | 2012-07-02 04:00:00  | NULL
3   | 1       |2011-05-05 05:00:00 | 2011-05-05 05:00:00  | NULL`

As you can see I have 3 records with id "1" and each are a corresponding version (i.e, 1:3 in this case)

I would like to update the versions 2 and 1 by setting 'valid_to' equal to the 'valid_from' value in later version.

Updated Table
|id |version |valid_from          |valid_to              |time_valid
1  | 1        |2010-03-21 19:00:00 | **2011-02-02 09:00:00**   | **Some Time**
1   | 2       |**2011-02-02 09:00:00** | **2012-04-20 15:00:00**  | **Some Time**
1   | 3       |**2012-04-20 15:00:00** | 2012-04-20 15:00:00  | NULL
2   | 1       |2012-07-02 04:00:00 | 2012-07-02 04:00:00  | NULL
3   | 1       |2011-05-05 05:00:00 | 2011-05-05 05:00:00  | NULL

Some records will have many versions while others may not have any (only one). It would also be convenient to calculate a time_valid field at the same time, which I assume is done by just subtracting the valid_to from the valid_from timestamps. Again I have millions upon millions of records and multiple tables I need to to this to - so faster is indeed better.

Many thanks for any working code examples!

Aucun commentaire:

Enregistrer un commentaire