Rebuild a table from history records

Question

A small disaster happened to me on my PostgreSQL 8.4 database.

I follow visitors to my webpage in two tables:

History

+---------------+-------------+
| whereseenlast | varchar(50) |
| uniqid        | varchar(13) |
| whenseenlast  | timestamp   |
| bla1          | varchar(10) |
| bla2          | varchar(10) |
| bla3          | varchar(10) |
+---------------+-------------+

Sample data:

+-----------------+---+---------------------+----------+----------+----------+
| where1.html     | 1 | 2000-01-01 00:00:00 | somebla1 | somebla2 | somebla3 |
| where2.html     | 1 | 2005-05-05 05:06:07 | somebla1 | somebla2 | somebla3 |
| where12345.html | 1 | 2012-11-22 11:22:33 | somebla1 | somebla2 | somebla3 |
+-----------------+---+---------------------+----------+----------+----------+

Visitors

uniqid is primary key, each visitor can be found just once in this table

+---------------+----------------+
| wherseenfirst | varchar(50)    |
| whereseenlast | varchar(50)    |
| whenseenfirst | timestamp      |
| whenseenlast  | timestamp,     |
| uniqid        | varchar(13) PK |
| notes         | varchar(20)    |
| xyz           | varchar(20)    |
+---------------+----------------+

Sample data:

+-------------+-----------------+---------------------+---------------------+---+----------+----------+
| where1.html | where12345.html | 2000-01-01 00:00:00 | 2012-11-22 11:22:33 | 1 | somenote | somexyz  |
+-------------+-----------------+---------------------+---------------------+---+----------+----------+

Problem

I accidentally deleted the Visitors table and I have no backup. But I am sure it is easy for an expert to rebuild the Visitors table from the history with a query. I know I will not be able to restore the notes and xyz columns but that is not a big deal.

Any suggestions please?

Answer 1

Something like:

select uniqid,
       min(case when visit_seq = 1 then whenseenlast end) as whenseenfirst,
       min(case when visit_seq = 1 then whereseenlast end) as whereseenfirst,
       min(case when visit_seq = visits_thisuser then whenseenlast end) as whenseenlast,
       min(case when visit_seq = visits_thisuser then whereseenlast end) as whereseenlast
from (
  select uniqid,
         row_number() over(w) as visit_seq,
         count(*) over(w range between unbounded preceding and unbounded following) as visits_thisuser,
         whenseenlast, whereseenlast
  from history
  window w as (partition by uniqid order by whenseenlast)
) visit
group by visit.uniqid

http://sqlfiddle.com/#!11/fb6f8/14/0