/[A3C]/data/stats/stats.sql
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Annotation of /data/stats/stats.sql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 247 - (hide annotations)
Mon Oct 13 13:48:00 2008 UTC (14 years, 3 months ago) by dpavlin
File size: 1608 byte(s)
monthly visits, changes

1 dpavlin 246 -- RT #4608: kori┬╣tenje instanci po ┬╣kolama
2    
3     drop schema stats cascade ;
4     create schema stats;
5    
6     create table stats.visits (
7     instance text not null,
8     visits int not null,
9     primary key(instance)
10     );
11    
12     \copy stats.visits from 'visits.tsv'
13    
14     -- \echo top 10 schools by visits
15     -- select * from stats.visits order by visits desc limit 10 ;
16    
17     create table stats.changes (
18     instance text not null,
19     changes int not null,
20     primary key(instance)
21     );
22     \copy stats.changes from 'changes.tsv'
23    
24     -- \echo top 10 schools by changes
25     -- select * from stats.changes order by changes desc limit 10 ;
26    
27     create view stats.instance_organization as
28     select
29     stats.visits.instance as id,
30     'http://' || hrEduOrgUrl as uri,
31     o as label,
32     l as town,
33     postalAddress,
34     telephoneNumber,
35     facsimileTelephoneNumber,
36     stats.visits.visits,
37     stats.changes.changes
38     from stats.visits
39     left outer join hr_edu_orgs on cn = instance
40     left outer join stats.changes on stats.changes.instance = stats.visits.instance
41     where hrEduOrgUrl is not null
42     order by l, o
43     ;
44    
45     select * from stats.instance_organization limit 1;
46 dpavlin 247
47     create table stats.monthly_visits (
48     instance text not null,
49     visits int not null,
50     month int not null,
51     first timestamp,
52     last timestamp,
53     primary key(instance,month)
54     );
55    
56     \copy stats.monthly_visits from 'monthly-visits.tsv'
57    
58     select * from stats.monthly_visits limit 1;
59    
60     create table stats.monthly_changes (
61     instance text not null,
62     changes int not null,
63     month int not null,
64     first timestamp,
65     last timestamp,
66     primary key(instance,month)
67     );
68    
69     \copy stats.monthly_changes from 'monthly-changes.tsv'
70    
71     select * from stats.monthly_changes limit 1;

  ViewVC Help
Powered by ViewVC 1.1.26