Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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

share|improve this answer
    
perfect solution... :D –  Paul Dec 12 '12 at 20:02
    
by the way, thank You... –  Paul Dec 12 '12 at 20:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.