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