bd_agg_examples

SELECT COUNT(*) AS NumberOfPosts 
FROM POST;

+---------------+
| NumberOfPosts |
+---------------+
|            12 |
+---------------+
1 row in set (0.01 sec)

Contagem de utilizadores com telefone definido (não-nulo)

SELECT Login, Phone 
FROM USER;

+------------+-----------+
| Login      | Phone     |
+------------+-----------+
| joao.pinto |      NULL |
| semedo     | 223774327 |
| maria      | 939939939 |
| pedro      |      NULL |
| mendocas   |      NULL |
| eva        |      NULL |
| pedro2     | 213884445 |
| batman     |      NULL |
| catwoman   |      NULL |
| ziggy      |      NULL |
| lady.z     |      NULL |
+------------+-----------+
11 rows in set (0.01 sec)

SELECT COUNT(Phone) AS N 
FROM USER;

+---+
| N |
+---+
| 3 |
+---+
1 row in set (0.00 sec)

SELECT COUNT(*) AS N 
FROM USER 
WHERE Phone IS NOT NULL;

+---+
| N |
+---+
| 3 |
+---+
1 row in set (0.00 sec)

Determinar tamanho máximo do conteúdo de todos os "posts"

SELECT Content, LENGTH(Content) AS Size
FROM POST 
ORDER BY Size;

+----------------------------------------------------------------------+------+
| Content                                                              | Size |
+----------------------------------------------------------------------+------+
| Joker, you are dead!                                                 |   20 |
| Catwoman, I love you!                                                |   21 |
| I guess nobody is online!                                            |   25 |
| ... we ran away on the Joker-mobile!                                 |   36 |
| O meu coração só tem uma cor: azul e branco.                         |   44 |
| Comigo, ou sem-migo, o Porto vai ser campeão!                        |   45 |
| Nothing to do except posting ... anyone there?                       |   46 |
| Happy new year star-gazers! 2020 will rock ...                       |   46 |
| Batman is a troll! Joker is my sweetheart these days!                |   53 |
| Joker is a busy man, he has no time for social networking.           |   58 |
| Não foi nada de especial, chutei com o pé que estava mais a mão!     |   64 |
| Joker and I went out last night ... but pesky Batman had to show up. |   68 |
+----------------------------------------------------------------------+------+
12 rows in set (0.01 sec)

SELECT MAX(LENGTH(Content)) AS MaxSize
FROM POST;

+---------+
| MaxSize |
+---------+
|      68 |
+---------+
1 row in set (0.00 sec)

Obter tamanho mínimo, máximo, e médio do conteúdo de todos os "posts"

SELECT MIN(LENGTH(Content)) AS MinSize,
       MAX(LENGTH(Content)) AS MaxSize,
       AVG(LENGTH(Content)) AS AvgSize
FROM POST;

+---------+---------+---------+
| MinSize | MaxSize | AvgSize |
+---------+---------+---------+
|      20 |      68 | 43.8333 |
+---------+---------+---------+
1 row in set (0.01 sec)

Agregações simples com agrupamento

Obter número de "posts" por utilizador

SELECT Author, Num 
FROM POST
ORDER BY Author, Num;

+--------+-----+
| Author | Num |
+--------+-----+
|      1 |   1 |
|      1 |   2 |
|      1 |   3 |
|      2 |   4 |
|      2 |   5 |
|      8 |   6 |
|      8 |   7 |
|      9 |   8 |
|      9 |   9 |
|      9 |  10 |
|      9 |  11 |
|     10 |  12 |
+--------+-----+
12 rows in set (0.00 sec)

SELECT Author,COUNT(*) AS NumberOfPosts
FROM POST
GROUP BY Author
ORDER BY Author;

+--------+---------------+
| Author | NumberOfPosts |
+--------+---------------+
|      1 |             3 |
|      2 |             2 |
|      8 |             2 |
|      9 |             4 |
|     10 |             1 |
+--------+---------------+
5 rows in set (0.00 sec)

Obter número de "posts" por utilizador, para utilizadores com mais de 2 posts

SELECT Author,COUNT(*) AS NumberOfPosts
FROM POST
GROUP BY Author
HAVING NumberOfPosts > 2
ORDER BY Author;

+--------+---------------+
| Author | NumberOfPosts |
+--------+---------------+
|      1 |             3 |
|      9 |             4 |
+--------+---------------+
2 rows in set (0.00 sec)

Determinar que utilizador escreveu mais "posts" e quantos escreveu

SELECT Author,COUNT(*) AS NumberOfPosts
FROM POST
GROUP BY Author
ORDER BY NumberOfPosts DESC
LIMIT 1;

+--------+---------------+
| Author | NumberOfPosts |
+--------+---------------+
|      9 |             4 |
+--------+---------------+
1 row in set (0.00 sec)

Determinar número de posts e data mais recente de "post" por utilizador

SELECT Author, Num, Creation
FROM POST
ORDER BY Author, Num;

+--------+-----+---------------------+
| Author | Num | Creation            |
+--------+-----+---------------------+
|      1 |   1 | 2020-01-13 12:31:59 |
|      1 |   2 | 2020-02-01 23:50:59 |
|      1 |   3 | 2020-02-13 12:31:59 |
|      2 |   4 | 2019-12-23 23:21:29 |
|      2 |   5 | 2019-12-24 05:00:00 |
|      8 |   6 | 2020-01-01 11:23:56 |
|      8 |   7 | 2020-02-10 23:00:56 |
|      9 |   8 | 2020-02-10 23:41:00 |
|      9 |   9 | 2020-02-17 23:51:00 |
|      9 |  10 | 2020-02-17 23:51:12 |
|      9 |  11 | 2020-02-18 17:00:30 |
|     10 |  12 | 2019-12-31 23:59:59 |
+--------+-----+---------------------+
12 rows in set (0.00 sec)

SELECT Author,
       COUNT(*) AS NumberOfPosts,
       MAX(Creation) AS LatestPost
FROM POST
GROUP BY Author
ORDER BY Author;

+--------+---------------+---------------------+
| Author | NumberOfPosts | LatestPost          |
+--------+---------------+---------------------+
|      1 |             3 | 2020-02-13 12:31:59 |
|      2 |             2 | 2019-12-24 05:00:00 |
|      8 |             2 | 2020-02-10 23:00:56 |
|      9 |             4 | 2020-02-18 17:00:30 |
|     10 |             1 | 2019-12-31 23:59:59 |
+--------+---------------+---------------------+

Determinar número de posts e data mais recente de "post" por utilizador, apenas para utilizadores com com "post" mais recente feito em 2020

SELECT Author,
       COUNT(*) AS NumberOfPosts,
       MAX(Creation) AS LatestPost
FROM POST
GROUP BY Author
HAVING YEAR(LatestPost) = 2020
ORDER BY Author;

+--------+---------------+---------------------+
| Author | NumberOfPosts | LatestPost          |
+--------+---------------+---------------------+
|      1 |             3 | 2020-02-13 12:31:59 |
|      8 |             2 | 2020-02-10 23:00:56 |
|      9 |             4 | 2020-02-18 17:00:30 |
+--------+---------------+---------------------+
3 rows in set (0.01 sec)

"Livro das Caras" - uso de funções de agregação

Agregações simples

Contagem de registos