Copy-first script
Window function for mest viste sider per dag
Rangerer sidevisninger per dag med ROW_NUMBER.
Forutsetninger
MySQL 8.0+, MySQL Workbench.
Window function for mest viste sider per dag
Rangerer sidevisninger per dag med ROW_NUMBER.
Kode
WITH daily_pages AS (
SELECT DATE(created_at) AS view_date, page_url, COUNT(*) AS views
FROM visitor_pageviews
GROUP BY DATE(created_at), page_url
), ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY view_date ORDER BY views DESC) AS rn
FROM daily_pages
)
SELECT view_date, page_url, views
FROM ranked
WHERE rn <= 10
ORDER BY view_date DESC, views DESC;Bruk
Scriptet er laget for copy-first arbeidsflyt. Kjør det først i et testmiljø eller mot en kopi av relevante tabeller.
Klar til bruk
Kodevindu
Koden er optimalisert for rask kopiering og videre tilpasning i eget prosjekt.
WITH daily_pages AS (
SELECT DATE(created_at) AS view_date, page_url, COUNT(*) AS views
FROM visitor_pageviews
GROUP BY DATE(created_at), page_url
), ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY view_date ORDER BY views DESC) AS rn
FROM daily_pages
)
SELECT view_date, page_url, views
FROM ranked
WHERE rn <= 10
ORDER BY view_date DESC, views DESC;