All geek questions in one place

How does MySQL UNION ALL run agregate functions?

I have SQL code that works exactly the way I want:

select 10 as number, "Checklist 10 Foo" as name, max(id), max(ts) as max_ts, callsign, max(time_hint_utc), count(*)
from checklist_10
union all
select 11 as number, "Checklist 11 Bar" as name, max(id), max(ts) as max_ts, callsign, max(time_hint_utc), count(*)
from checklist_11
union all
select 12 as number, "Checklist 12 Baz" as name, max(id), max(ts) as max_ts, callsign, max(time_hint_utc), count(*)
from checklist_12

group by callsign, number
order by max_ts

results:

number,name,max(id),max_ts,callsign,max(time_hint_utc),count(*)
10,Checklist 10 Foo,2,1486554484635,VRTEST,2017-02-08 12:21:32,2
11,Checklist 11 Bar,2,1486554490674,VRTEST,2017-02-08 12:21:39,2
12,Checklist 12 Baz,2,1486554496378,VRTEST,2017-02-08 12:21:44,2
12,Checklist 12 Baz,3,1486554496379,VRTEST2,2017-02-08 12:21:45,1

In particular, I see the correct results for max()and count(), i.e. maxand counteach combination of callsign/ number, and not separate selects.

How it works? Aggregate functions look as if they are at the table level selects, but they function as if they are functions above the results union all.

PS Sorry to ask a question when the only problem is my lack of understanding.

Update with table description:

mysql> describe checklist_10;
+---------------+--------------------------------------------------------------+------+-----+---------+----------------+
| Field         | Type                                                         | Null | Key | Default | Extra          |
+---------------+--------------------------------------------------------------+------+-----+---------+----------------+
| id            | bigint(20) unsigned                                          | NO   | PRI | NULL    | auto_increment |
| ts            | bigint(20) unsigned                                          | NO   |     | NULL    |                |
| callsign      | varchar(20)                                                  | NO   |     | NULL    |                |
| smg_id        | tinyint(3) unsigned                                          | NO   |     | NULL    |                |
| time          | int(11)                                                      | NO   |     | NULL    |                |
| time_hint_utc | datetime                                                     | NO   |     | NULL    |                |
| reason        | enum('UNKNOWN','PERIODIC','SHIFT','MENU','EVENT','DECLINED') | NO   |     | NULL    |                |
| foo0          | tinyint(1)                                                   | NO   |     | NULL    |                |
| foo1          | tinyint(1)                                                   | NO   |     | NULL    |                |
| foo2          | tinyint(1)                                                   | NO   |     | NULL    |                |
| foo3          | tinyint(1)                                                   | NO   |     | NULL    |                |
| foo4          | tinyint(1)                                                   | NO   |     | NULL    |                |
| foo5          | tinyint(1)                                                   | NO   |     | NULL    |                |
| foo6          | tinyint(1)                                                   | NO   |     | NULL    |                |
| foo7          | tinyint(1)                                                   | NO   |     | NULL    |                |
| foo8          | tinyint(1)                                                   | NO   |     | NULL    |                |
| foo9          | tinyint(1)                                                   | NO   |     | NULL    |                |
+---------------+--------------------------------------------------------------+------+-----+---------+----------------+
17 rows in set (0.00 sec)

mysql> describe checklist_11;
+---------------+--------------------------------------------------------------+------+-----+---------+----------------+
| Field         | Type                                                         | Null | Key | Default | Extra          |
+---------------+--------------------------------------------------------------+------+-----+---------+----------------+
| id            | bigint(20) unsigned                                          | NO   | PRI | NULL    | auto_increment |
| ts            | bigint(20) unsigned                                          | NO   |     | NULL    |                |
| callsign      | varchar(20)                                                  | NO   |     | NULL    |                |
| smg_id        | tinyint(3) unsigned                                          | NO   |     | NULL    |                |
| time          | int(11)                                                      | NO   |     | NULL    |                |
| time_hint_utc | datetime                                                     | NO   |     | NULL    |                |
| reason        | enum('UNKNOWN','PERIODIC','SHIFT','MENU','EVENT','DECLINED') | NO   |     | NULL    |                |
| bar0          | tinyint(1)                                                   | NO   |     | NULL    |                |
| bar1          | tinyint(1)                                                   | NO   |     | NULL    |                |
| bar2          | tinyint(1)                                                   | NO   |     | NULL    |                |
| bar3          | tinyint(1)                                                   | NO   |     | NULL    |                |
+---------------+--------------------------------------------------------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

mysql> describe checklist_12;
+---------------+--------------------------------------------------------------+------+-----+---------+----------------+
| Field         | Type                                                         | Null | Key | Default | Extra          |
+---------------+--------------------------------------------------------------+------+-----+---------+----------------+
| id            | bigint(20) unsigned                                          | NO   | PRI | NULL    | auto_increment |
| ts            | bigint(20) unsigned                                          | NO   |     | NULL    |                |
| callsign      | varchar(20)                                                  | NO   |     | NULL    |                |
| smg_id        | tinyint(3) unsigned                                          | NO   |     | NULL    |                |
| time          | int(11)                                                      | NO   |     | NULL    |                |
| time_hint_utc | datetime                                                     | NO   |     | NULL    |                |
| reason        | enum('UNKNOWN','PERIODIC','SHIFT','MENU','EVENT','DECLINED') | NO   |     | NULL    |                |
| baz0          | tinyint(1)                                                   | NO   |     | NULL    |                |
| baz1          | tinyint(1)                                                   | NO   |     | NULL    |                |
| baz2          | tinyint(1)                                                   | NO   |     | NULL    |                |
| baz3          | tinyint(1)                                                   | NO   |     | NULL    |                |
| baz4          | tinyint(1)                                                   | NO   |     | NULL    |                |
+---------------+--------------------------------------------------------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)

Data:

mysql> select * from checklist_10;
+----+---------------+----------+--------+------------+---------------------+----------+------+------+------+------+------+------+------+------+------+------+
| id | ts            | callsign | smg_id | time       | time_hint_utc       | reason   | foo0 | foo1 | foo2 | foo3 | foo4 | foo5 | foo6 | foo7 | foo8 | foo9 |
+----+---------------+----------+--------+------------+---------------------+----------+------+------+------+------+------+------+------+------+------+------+
|  1 | 1486554385343 | VRTEST   |      7 | 1486556393 | 2017-02-08 12:19:53 | PERIODIC |    1 |    1 |    0 |    0 |    1 |    1 |    0 |    1 |    0 |    0 |
|  2 | 1486554484635 | VRTEST   |      7 | 1486556492 | 2017-02-08 12:21:32 | SHIFT    |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    1 |    0 |    0 |
+----+---------------+----------+--------+------------+---------------------+----------+------+------+------+------+------+------+------+------+------+------+
2 rows in set (0.00 sec)

mysql> select * from checklist_11;
+----+---------------+----------+--------+------------+---------------------+----------+------+------+------+------+
| id | ts            | callsign | smg_id | time       | time_hint_utc       | reason   | bar0 | bar1 | bar2 | bar3 |
+----+---------------+----------+--------+------------+---------------------+----------+------+------+------+------+
|  1 | 1486554457077 | VRTEST   |      7 | 1486556465 | 2017-02-08 12:21:05 | PERIODIC |    0 |    0 |    0 |    0 |
|  2 | 1486554490674 | VRTEST   |      7 | 1486556499 | 2017-02-08 12:21:39 | SHIFT    |    1 |    1 |    1 |    1 |
+----+---------------+----------+--------+------------+---------------------+----------+------+------+------+------+
2 rows in set (0.00 sec)

mysql> select * from checklist_12;
+----+---------------+----------+--------+------------+---------------------+----------+------+------+------+------+------+
| id | ts            | callsign | smg_id | time       | time_hint_utc       | reason   | baz0 | baz1 | baz2 | baz3 | baz4 |
+----+---------------+----------+--------+------------+---------------------+----------+------+------+------+------+------+
|  1 | 1486554476903 | VRTEST   |      7 | 1486556485 | 2017-02-08 12:21:25 | PERIODIC |    1 |    1 |    1 |    1 |    1 |
|  2 | 1486554496378 | VRTEST   |      7 | 1486556504 | 2017-02-08 12:21:44 | SHIFT    |    1 |    1 |    1 |    1 |    1 |
|  3 | 1486554496379 | VRTEST2  |      7 | 1486556505 | 2017-02-08 12:21:45 | SHIFT    |    1 |    1 |    1 |    1 |    1 |
+----+---------------+----------+--------+------------+---------------------+----------+------+------+------+------+------+
3 rows in set (0.00 sec)

Error 1054:

mysql> select 10 as number, "Checklist 10 Foo" as name, max(id), max(ts) as max_ts, callsign, max(time_hint_utc), count(*)
    -> from checklist_10
    -> union all
    -> select 11 as number, "Checklist 11 Bar" as name, max(id), max(ts) as max_ts, callsign, max(time_hint_utc), count(*)
    -> from checklist_11
    -> union all
    -> select 12 as number, "Checklist 12 Baz" as name, max(id), max(ts) as max_ts, callsign, max(time_hint_utc), count(*)
    -> from checklist_12
    -> 
    -> group by callsign, number
    -> order by max_ts;
+--------+------------------+---------+---------------+----------+---------------------+----------+
| number | name             | max(id) | max_ts        | callsign | max(time_hint_utc)  | count(*) |
+--------+------------------+---------+---------------+----------+---------------------+----------+
|     10 | Checklist 10 Foo |       2 | 1486554484635 | VRTEST   | 2017-02-08 12:21:32 |        2 |
|     11 | Checklist 11 Bar |       2 | 1486554490674 | VRTEST   | 2017-02-08 12:21:39 |        2 |
|     12 | Checklist 12 Baz |       2 | 1486554496378 | VRTEST   | 2017-02-08 12:21:44 |        2 |
|     12 | Checklist 12 Baz |       3 | 1486554496379 | VRTEST2  | 2017-02-08 12:21:45 |        1 |
+--------+------------------+---------+---------------+----------+---------------------+----------+
4 rows in set (0.00 sec)
+4
source share
2

, . ,

, GROUP BY, .

, , , group by ( , , , , ) group by, .

, MySQL

(select 10 as number, "Checklist 10 Foo" as name, max(id), max(ts) as max_ts, 
    callsign, max(time_hint_utc), count(*)
from checklist_10)

union all

(select 11 as number, "Checklist 11 Bar" as name, max(id), max(ts) as max_ts, 
   callsign, max(time_hint_utc), count(*)
from checklist_11)

union all

(select 12 as number, "Checklist 12 Baz" as name, max(id), max(ts) as max_ts, 
   callsign, max(time_hint_utc), count(*)
from checklist_12
group by callsign, number)

order by max_ts

, . callsign checklist_10 checklist_11. , . , , number number1 . , group by number.

, , only_full_group_by, , .

+2

, group by selects, order by union all .

, group by :

mysql> select 10 as number, "Checklist 10 Foo" as name, max(id), max(ts) as max_ts, callsign, max(time_hint_utc), count(*)
    -> from checklist_10
    -> group by callsign
    -> 
    -> union all
    -> 
    -> select 11 as number, "Checklist 11 Bar" as name, max(id), max(ts) as max_ts, callsign, max(time_hint_utc), count(*)
    -> from checklist_11
    -> group by callsign
    -> 
    -> union all
    -> 
    -> select 12 as number, "Checklist 12 Baz" as name, max(id), max(ts) as max_ts, callsign, max(time_hint_utc), count(*)
    -> from checklist_12
    -> group by callsign
    -> 
    -> order by max_ts
    -> ;
+--------+------------------+---------+---------------+----------+---------------------+----------+
| number | name             | max(id) | max_ts        | callsign | max(time_hint_utc)  | count(*) |
+--------+------------------+---------+---------------+----------+---------------------+----------+
|     10 | Checklist 10 Foo |       2 | 1486554484635 | VRTEST   | 2017-02-08 12:21:32 |        2 |
|     11 | Checklist 11 Bar |       2 | 1486554490674 | VRTEST   | 2017-02-08 12:21:39 |        2 |
|     12 | Checklist 12 Baz |       2 | 1486554496378 | VRTEST   | 2017-02-08 12:21:44 |        2 |
|     12 | Checklist 12 Baz |       3 | 1486554496379 | VRTEST2  | 2017-02-08 12:21:45 |        1 |
|     10 | Checklist 10 Foo |       3 | 1486554496380 | VRTEST2  | 2017-02-08 12:21:46 |        1 |
+--------+------------------+---------+---------------+----------+---------------------+----------+
5 rows in set (0.00 sec)
0

Source: https://habr.com/ru/post/1669171/

More articles:


All Articles