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;