...
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 lower(request) like '%entity%' then 'Edit'
when lower(request) like '%executedata%' then 'Data'
when lower(request) like '%edit%' then 'Edit'
when lower(request) like '%dms%' then 'Dokument'
when lower(request) like '%select%' then 'Urval'
when lower(request) like '%index%' then 'Snabbsök'
when lower(request) like '%search%' then 'Sökningar'
when lower(request) like '%admin%' then 'Admin'
when lower(request) like '%report%' then 'Rapporter'
when lower(request) like '%list%' then 'Listor'
when lower(request) like '%map%' then 'Kartor'
when lower(request) like '%export%' then 'Export'
else 'Övrigt'
end as kategori,
count(request), sum((duration+500)/1000) as seconds, sum((duration+500)/1000) * interval '1 second' as hms
FROM weave.wv_statistics
--where date(submitted) = date(now())
group by 1
order by 3 desc;
