1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
|
DROP TABLE stats_site_all;
DROP TABLE stats_site_last_30;
DROP TABLE stats_project_all;
DROP TABLE stats_project_developers_last30;
DROP TABLE stats_project_last_30;
CREATE VIEW stats_project_vw AS
SELECT spd.group_id,
spd.month,
spd.day,
spd.developers,
spm.ranking AS group_ranking,
spm.percentile AS group_metric,
salbg.count AS logo_showings,
fdga.downloads,
sasbg.count AS site_views,
ssp.pages AS subdomain_views,
(coalesce(sasbg.count,0) + coalesce(ssp.pages,0))::int AS page_views,
sp.file_releases,
sp.msg_posted,
sp.msg_uniq_auth,
sp.bugs_opened,
sp.bugs_closed,
sp.support_opened,
sp.support_closed,
sp.patches_opened,
sp.patches_closed,
sp.artifacts_opened,
sp.artifacts_closed,
sp.tasks_opened,
sp.tasks_closed,
sp.help_requests,
scg.checkouts AS cvs_checkouts,
scg.commits AS cvs_commits,
scg.adds AS cvs_adds
FROM stats_project_developers spd
LEFT JOIN stats_project sp USING (month,day,group_id)
LEFT JOIN stats_project_metric spm USING (month,day,group_id)
LEFT JOIN stats_cvs_group scg USING (month,day,group_id)
LEFT JOIN stats_agg_site_by_group sasbg USING (month,day,group_id)
LEFT JOIN stats_agg_logo_by_group salbg USING (month,day,group_id)
LEFT JOIN stats_subd_pages ssp USING (month,day,group_id)
LEFT JOIN frs_dlstats_group_vw fdga USING (month,day,group_id)
;
CREATE VIEW stats_project_all_vw AS
SELECT group_id,
AVG(developers)::int AS developers,
AVG(group_ranking)::int AS group_ranking,
AVG(group_metric)::float AS group_metric,
SUM(logo_showings) AS logo_showings,
SUM(downloads) AS downloads,
SUM(site_views) AS site_views,
SUM(subdomain_views) AS subdomain_views,
SUM(page_views) AS page_views,
SUM(file_releases) AS file_releases,
SUM(msg_posted) AS msg_posted,
AVG(msg_uniq_auth)::int AS msg_uniq_auth,
SUM(bugs_opened) AS bugs_opened,
SUM(bugs_closed) AS bugs_closed,
SUM(support_opened) AS support_opened,
SUM(support_closed) AS support_closed,
SUM(patches_opened) AS patches_opened,
SUM(patches_closed) AS patches_closed,
SUM(artifacts_opened) AS artifacts_opened,
SUM(artifacts_closed) AS artifacts_closed,
SUM(tasks_opened) AS tasks_opened,
SUM(tasks_closed) AS tasks_closed,
SUM(help_requests) AS help_requests,
SUM(cvs_checkouts) AS cvs_checkouts,
SUM(cvs_commits) AS cvs_commits,
SUM(cvs_adds) AS cvs_adds
FROM stats_project_months
GROUP BY group_id;
CREATE VIEW stats_site_vw AS
SELECT p.month,
p.day,
sspbd.site_page_views,
SUM(p.downloads) AS downloads,
SUM(p.subdomain_views) AS subdomain_views,
SUM(p.msg_posted) AS msg_posted,
SUM(p.bugs_opened) AS bugs_opened,
SUM(p.bugs_closed) AS bugs_closed,
SUM(p.support_opened) AS support_opened,
SUM(p.support_closed) AS support_closed,
SUM(p.patches_opened) AS patches_opened,
SUM(p.patches_closed) AS patches_closed,
SUM(artifacts_opened) AS artifacts_opened,
SUM(artifacts_closed) AS artifacts_closed,
SUM(p.tasks_opened) AS tasks_opened,
SUM(p.tasks_closed) AS tasks_closed,
SUM(p.help_requests) AS help_requests,
SUM(p.cvs_checkouts) AS cvs_checkouts,
SUM(p.cvs_commits) AS cvs_commits,
SUM(p.cvs_adds) AS cvs_adds
FROM stats_project_vw p, stats_site_pages_by_day sspbd
WHERE p.month=sspbd.month AND p.day=sspbd.day
GROUP BY p.month, p.day, sspbd.site_page_views;
CREATE VIEW stats_site_all_vw AS
SELECT
SUM(site_page_views) AS site_page_views,
SUM(downloads) AS downloads,
SUM(subdomain_views) AS subdomain_views,
SUM(msg_posted) AS msg_posted,
SUM(bugs_opened) AS bugs_opened,
SUM(bugs_closed) AS bugs_closed,
SUM(support_opened) AS support_opened,
SUM(support_closed) AS support_closed,
SUM(patches_opened) AS patches_opened,
SUM(patches_closed) AS patches_closed,
SUM(artifacts_opened) AS artifacts_opened,
SUM(artifacts_closed) AS artifacts_closed,
SUM(tasks_opened) AS tasks_opened,
SUM(tasks_closed) AS tasks_closed,
SUM(help_requests) AS help_requests,
SUM(cvs_checkouts) AS cvs_checkouts,
SUM(cvs_commits) AS cvs_commits,
SUM(cvs_adds) AS cvs_adds
FROM stats_site_months;
|
