...
SELECT * FROM crosstab(
$$SELECT
date_part('week',submitted)::int,
date_part('isodow',submitted)::int as weekday,
sum((duration+500)/1000) as seconds
FROM weave.wv_statistics
group by 1,2
order by 1$$,
$$ SELECT m FROM generate_series(1,7) m $$
) as ( week int, "Måndag" int, "Tisdag" int, "Onsdag" int, "Torsdag" int, "Fredag" int, "Lördag" int, "Söndag" int );
List with categorised requests
SELECT
--date(submitted),
case
when request like '%entity%' or request like '%Entity%' then 'Edit'
when substr(request,1,4) like 'data' or request like '%ExecuteData%' then 'Data'
when request like '%edit%' or request like '%Edit%' then 'Edit'
when request like '%dms%' or request like '%Dms%' then 'Dokument'
when request like '%select%' or request like '%Select%' then 'Urval'
when request like '%index%' or request like '%Index%' then 'Dokument'
when request like '%search%' or request like '%Search%' then 'Dokument'
when substr(request,1,5) like 'admin' then 'Admin'
when request like '%report%' or request like '%Report%' then 'Rapporter'
when request like '%list%' or request like '%List%' then 'Listor'
when request like '%map%' or request like '%Map%' then 'Kartor'
when request like '%export%' or request like '%Export%' then 'Export'
else 'Övrigt'
end,
count(request), sum((duration+500)/1000) as seconds
FROM weave.wv_statistics
--where request = 'com.cohga.ExecuteReport'
group by 1
order by 3 desc;
