USE svwiki_p;

/*

SELECT SUBSTR(rev_timestamp,9,2) AS Timme, 

CAST(SUM(DATE_FORMAT(rev_timestamp,'%w')=1) AS INTEGER) AS Måndag,

CAST(SUM(DATE_FORMAT(rev_timestamp,'%w')=2) AS INTEGER) AS Tisdag,

CAST(SUM(DATE_FORMAT(rev_timestamp,'%w')=3) AS INTEGER) AS Onsdag,

CAST(SUM(DATE_FORMAT(rev_timestamp,'%w')=4) AS INTEGER) AS Torsdag,

CAST(SUM(DATE_FORMAT(rev_timestamp,'%w')=5) AS INTEGER) AS Fredag,

CAST(SUM(DATE_FORMAT(rev_timestamp,'%w')=6) AS INTEGER) AS Lördag,

CAST(SUM(DATE_FORMAT(rev_timestamp,'%w')=0) AS INTEGER) AS Söndag,

(CONCAT(COUNT(*),';')) AS Totalt FROM revision

WHERE rev_timestamp >= DATE_ADD(NOW(),INTERVAL -1 MONTH) 

GROUP BY Timme

ORDER BY Timme;

*/

SELECT SUBSTR(rc_timestamp,9,2) AS Timme, 

CAST(SUM(DATE_FORMAT(rc_timestamp,'%w')=1) AS INTEGER) AS Måndag,

CAST(SUM(DATE_FORMAT(rc_timestamp,'%w')=2) AS INTEGER) AS Tisdag,

CAST(SUM(DATE_FORMAT(rc_timestamp,'%w')=3) AS INTEGER) AS Onsdag,

CAST(SUM(DATE_FORMAT(rc_timestamp,'%w')=4) AS INTEGER) AS Torsdag,

CAST(SUM(DATE_FORMAT(rc_timestamp,'%w')=5) AS INTEGER) AS Fredag,

CAST(SUM(DATE_FORMAT(rc_timestamp,'%w')=6) AS INTEGER) AS Lördag,

CAST(SUM(DATE_FORMAT(rc_timestamp,'%w')=0) AS INTEGER) AS Söndag,

(CONCAT(COUNT(*),';')) AS Totalt FROM recentchanges

WHERE (rc_timestamp >= DATE_ADD(NOW(),INTERVAL -28 DAY)) AND rc_bot = 0 AND rc_type < 5

GROUP BY Timme

ORDER BY Timme;

​

/*

SELECT COUNT(*) FROM revision WHERE rev_timestamp >= DATE_ADD(NOW(),INTERVAL -28 DAY);

*/

/*

SELECT ABS(gt_lat - cen_gt_lat) + ABS(gt_lon - cen_gt_lon) AS dist, 

       gt_lat, gt_lon, gt_page_id, gt_name,

       (SELECT page_title FROM page WHERE page_id = gt_page_id) AS title,

       gt_country,gt_region FROM geo_tags,

       (SELECT AVG(gt_lat) AS cen_gt_lat,AVG(gt_lon) AS cen_gt_lon FROM geo_tags

        WHERE gt_country='SE') AS z

       WHERE gt_country='SE'

       ORDER BY dist DESC

       LIMIT 100;    

*/