Usuário:Danilo.mac/Temp

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)