Can you explain the behaviour of aggregations with inner join

user_400048 0
Viewed 41

I'm trying to understand the behaviour of aggregations when the columns being aggregated are from tables in the join clauses.

So the scenarios I setup is this: Imagine some struct with one to many properties

class X {
  String[] str;
  int ints[];
  float floats[];
}

This then gets represented by two tables

CREATE TABLE count_test.aggs (
    inst varchar(100) NULL,
    id varchar(100) NULL
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;

CREATE TABLE count_test.scalars (
    inst varchar(100) NULL,
    id varchar(100) NULL,
    str varchar(100) NULL,
    `int` INT NULL,
    `float` FLOAT NULL
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;

I insert some data:

INSERT INTO aggs VALUES 
("inst1", "id1"),
("inst2", "id2"),
("inst3", "id3"),
("inst4", "id4"),
("inst5", "id5");

INSERT INTO scalars VALUES
('inst1', 'id1', 'str1', 1, 1.1),
('inst1', 'id1', 'str2', 2, 1.2),
('inst1', 'id1', 'str3', 3, 1.3),
('inst1', 'id1', 'str4', 4, 1.4),
('inst1', 'id1', 'str5', 5, 1.5),

('inst2', 'id2', NULL, 5, 2.1),
('inst2', 'id2', NULL, 6, 2.2),
('inst2', 'id2', NULL, 7, 2.3),
('inst2', 'id2', NULL, 7, 2.4),
('inst2', 'id2', 'str6', 8, 2.5),

('inst3', 'id3', NULL, 8, 3.1),
('inst3', 'id3', NULL, 9, 3.2),
('inst3', 'id3', NULL, 10, 3.3),
('inst3', 'id3', NULL, 11, 3.4),
('inst3', 'id3', 'str7', 12, 3.5),

('inst4', 'id4', 'str8', 13, 4.1),
('inst4', 'id4', 'str9', 14, 4.2),
('inst4', 'id4', 'str10', 15, 4.3),
('inst4', 'id4', NULL, 16, 4.4),
('inst4', 'id4', 'str7', 17, 4.5),

('inst4', 'id4', 'str8', 13, 4.1),
('inst4', 'id4', 'str9', 14, 4.2),
('inst4', 'id4', 'str10', 15, 4.3),
('inst4', 'id4', NULL, 16, 4.4),
('inst4', 'id4', 'str11', 17, 4.5),

('inst5', 'id5', 'str12', 18, 5.1),
('inst5', 'id5', 'str13', 19, 5.2),
('inst5', 'id5', 'str13', 20, 5.3),
('inst5', 'id5', 'str14', 21, 5.4),
('inst5', 'id5', 'str15', 22, 5.5);

And then the query I thought I'd write to find the count, sum, avg etc is

SELECT COUNT(s0.`str`) AS h1_c10,
 COUNT(s1.`str`) AS h1_c11,
 SUM(s2.`int`) AS h1_s10,
 SUM(s3.`float`) AS h1_s11,
 AVG(s4.`float`) AS h1_a10,
 MAX(s5.`float`) AS h1_m10,
 MIN(s6.`float`) AS h1_m11
FROM aggs h1
 INNER JOIN scalars s0 ON h1.inst = s0.inst AND h1.id = s0.id 
 INNER JOIN scalars s1 ON h1.inst = s1.inst AND h1.id = s1.id
 INNER JOIN scalars s2 ON h1.inst = s2.inst AND h1.id = s2.id
 INNER JOIN scalars s3 ON h1.inst = s3.inst AND h1.id = s3.id
 INNER JOIN scalars s4 ON h1.inst = s4.inst AND h1.id = s4.id
 INNER JOIN scalars s5 ON h1.inst = s5.inst AND h1.id = s5.id
 INNER JOIN scalars s6 ON h1.inst = s6.inst AND h1.id = s6.id
WHERE h1.inst = "inst1"
GROUP BY h1.inst, h1.id

The result in MariaDB is: Query result

My instinct here is that the cartesian product of h1 X s0 X s1...X s6 is being done but I don't have much more than that. What I wanted, expected even, was 5 for the count since h1.inst = 'inst1' in the query.

Can someone help me understand why this is the result and how this should be written to count, sum only the rows from scalars where inst = 'inst1' or whatever the constraint is?

mariadb mysql
1 Answers

You can do it with only 1 join:

SELECT COUNT(s.`str`) AS h1_c10,
       COUNT(s.`str`) AS h1_c11,
       SUM(s.`int`) AS h1_s10,
       SUM(s.`float`) AS h1_s11,
       AVG(s.`float`) AS h1_a10,
       MAX(s.`float`) AS h1_m10,
       MIN(s.`float`) AS h1_m11
FROM aggs a INNER JOIN scalars s 
ON a.inst = s.inst AND a.id = s.id 
WHERE a.inst = 'inst1'
GROUP BY a.inst, a.id

But actually, for this sample data, all you need is to aggregate in scalars only:

SELECT COUNT(s.`str`) AS h1_c10,
       COUNT(s.`str`) AS h1_c11,
       SUM(s.`int`) AS h1_s10,
       SUM(s.`float`) AS h1_s11,
       AVG(s.`float`) AS h1_a10,
       MAX(s.`float`) AS h1_m10,
       MIN(s.`float`) AS h1_m11
FROM scalars s 
WHERE s.inst = 'inst1'
GROUP BY s.inst, s.id

See the demo.
Results:

> h1_c10 | h1_c11 | h1_s10 | h1_s11 | h1_a10 | h1_m10 | h1_m11
> -----: | -----: | -----: | -----: | -----: | -----: | -----:
>      5 |      5 |     15 |    6.5 |    1.3 |    1.5 |    1.1
user_104988280
Related