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

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;

  ViewVC Help
Powered by ViewVC 1.1.26