Usuário:Danilo.mac/Temp
Aspeto
Consulta sobre a quantidade de edições totais, de IPs e revertidas por hora de 19h de 25 de junho até 18h de 27 de junho de 2013:
SELECT
SUBSTR(rc_timestamp, 1, 10) AS HORA,
SUM(CASE rc_bot WHEN 1 THEN 0 ELSE 1 END) as TOTAL,
SUM(rc_user_text REGEXP "^[0-9.]+$") AS IP,
SUM(CASE WHEN rc_comment LIKE "[[WP:REV|%" THEN 1 ELSE 0 END) AS REV
FROM recentchanges
WHERE rc_namespace = 0
GROUP BY HORA
ORDER BY rc_id DESC
LIMIT 48;
+------------+-------+------+------+ | HORA | TOTAL | IP | REV | +------------+-------+------+------+ | 2013062718 | 301 | 108 | 15 | | 2013062717 | 801 | 260 | 34 | | 2013062716 | 728 | 183 | 4 | | 2013062715 | 620 | 210 | 10 | | 2013062714 | 556 | 227 | 17 | | 2013062713 | 487 | 208 | 15 | | 2013062712 | 577 | 159 | 10 | | 2013062711 | 382 | 79 | 10 | | 2013062710 | 228 | 64 | 3 | | 2013062709 | 194 | 26 | 1 | | 2013062708 | 361 | 44 | 0 | | 2013062707 | 318 | 30 | 1 | | 2013062706 | 206 | 47 | 0 | | 2013062705 | 202 | 38 | 5 | | 2013062704 | 287 | 83 | 8 | | 2013062703 | 363 | 112 | 16 | | 2013062702 | 362 | 135 | 23 | | 2013062701 | 459 | 127 | 50 | | 2013062700 | 481 | 176 | 39 | | 2013062623 | 407 | 145 | 53 | | 2013062622 | 610 | 181 | 78 | | 2013062621 | 706 | 188 | 76 | | 2013062620 | 566 | 162 | 15 | | 2013062619 | 617 | 217 | 35 | | 2013062618 | 744 | 206 | 79 | | 2013062617 | 587 | 178 | 31 | | 2013062616 | 527 | 162 | 10 | | 2013062615 | 564 | 149 | 8 | | 2013062614 | 656 | 174 | 21 | | 2013062613 | 478 | 145 | 32 | | 2013062612 | 486 | 185 | 2 | | 2013062611 | 277 | 75 | 1 | | 2013062610 | 207 | 59 | 0 | | 2013062609 | 216 | 28 | 0 | | 2013062608 | 195 | 38 | 3 | | 2013062607 | 166 | 28 | 6 | | 2013062606 | 152 | 35 | 3 | | 2013062605 | 185 | 30 | 0 | | 2013062604 | 230 | 58 | 7 | | 2013062603 | 274 | 102 | 15 | | 2013062602 | 353 | 160 | 2 | | 2013062601 | 388 | 121 | 42 | | 2013062600 | 565 | 210 | 53 | | 2013062523 | 790 | 229 | 55 | | 2013062522 | 580 | 155 | 42 | | 2013062521 | 677 | 164 | 48 | | 2013062520 | 736 | 220 | 76 | | 2013062519 | 732 | 256 | 91 | +------------+-------+------+------+ 48 rows in set (3.11 sec)