/[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

Contents of /data/stats/stats.sql

Parent Directory Parent Directory | Revision Log Revision Log


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

1 -- 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
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