/[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 246 - (show annotations)
Mon Oct 13 12:30:59 2008 UTC (14 years, 3 months ago) by dpavlin
File size: 1061 byte(s)
convertor from SQL query to JSON for Exhibit

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;

  ViewVC Help
Powered by ViewVC 1.1.26