MariaDB 스테이트먼트의 UNION 조항을 대체하는 방법에 대한 생각

MariaDB 스테이트먼트의 UNION 조항을 대체하는 방법에 대한 생각

(테스트 테이블을 작성하기 위한 코드는 이 메시지 하단에 있습니다).

2개의 테이블:

SELECT * FROM test_users;
+----+-------+
| ID | name  |
+----+-------+
|  1 | Tom   |
|  2 | Wendy |
|  3 | Fred  |
|  4 | Sandy |
+----+-------+
4 rows in set (0.000 sec)

SELECT * FROM test_hours;
+----+----------+------+-------+
| ID | users_ID | year | hours |
+----+----------+------+-------+
|  1 |        1 | 2018 |     3 |
|  2 |        1 | 2018 |     5 |
|  3 |        1 | 2019 |     7 |
|  4 |        1 | 2019 |     2 |
|  5 |        1 | 2019 |     9 |
|  6 |        1 | 2020 |     5 |
|  7 |        1 | 2020 |     9 |
|  8 |        2 | 2018 |     7 |
|  9 |        2 | 2018 |    11 |
| 10 |        2 | 2018 |     8 |
| 11 |        2 | 2019 |    10 |
| 12 |        2 | 2019 |    12 |
| 13 |        3 | 2018 |     4 |
| 14 |        3 | 2018 |     1 |
| 15 |        3 | 2018 |    15 |
| 16 |        3 | 2020 |    10 |
| 17 |        3 | 2020 |    12 |
| 18 |        4 | 2019 |     7 |
| 19 |        4 | 2019 |    11 |
| 20 |        4 | 2020 |     4 |
| 21 |        4 | 2020 |     6 |
+----+----------+------+-------+
21 rows in set (0.000 sec)

사용자별 및 연도별 시간 요약을 얻기 위해 간단한 가입을 사용할 수 있습니다.

SELECT name, year, SUM(hours) 
FROM test_hours 
JOIN test_users
  ON users_ID = test_users.ID
GROUP BY users_ID, year;
+-------+------+------------+
| name  | year | SUM(hours) |
+-------+------+------------+
| Tom   | 2018 |          8 |
| Tom   | 2019 |         18 |
| Tom   | 2020 |         14 |
| Wendy | 2018 |         26 |
| Wendy | 2019 |         22 |
| Fred  | 2018 |         20 |
| Fred  | 2020 |         22 |
| Sandy | 2019 |         18 |
| Sandy | 2020 |         10 |
+-------+------+------------+
9 rows in set (0.001 sec)

1년만 원하는 경우 다음을 수행할 수 있습니다.

SELECT name, year, SUM(hours) 
FROM test_hours 
JOIN test_users
  ON users_ID = test_users.ID
WHERE year = 2020
GROUP BY users_ID, year;
+-------+------+------------+
| name  | year | SUM(hours) |
+-------+------+------------+
| Tom   | 2020 |         14 |
| Fred  | 2020 |         22 |
| Sandy | 2020 |         10 |
+-------+------+------------+
3 rows in set (0.000 sec)

웬디는 2020시간이 없어서 자퇴했어요.하지만 내가 정말 원하는 건

+--------+------+------------+
| name   | year | SUM(hours) |
+--------+------+------------+
| Tom    | 2020 |         14 |
| Wendy  | 2020 |          0 |
| Fred   | 2020 |         22 |
| Sandy  | 2020 |         10 |
+--------+------+------------+

UNION 조항으로 할 수 있습니다.

SELECT name, year, SUM(hours) 
FROM test_hours 
JOIN test_users
  ON users_ID = test_users.ID
WHERE year = 2020
GROUP BY users_ID, year

UNION

SELECT DISTINCT name, 2020, 0 
FROM test_hours 
JOIN test_users
  ON users_ID = test_users.ID
WHERE NOT EXISTS (
    SELECT *
    FROM test_hours
    WHERE users_ID = test_users.ID AND year = 2020);
+-------+------+------------+
| name  | year | SUM(hours) |
+-------+------+------------+
| Tom   | 2020 |         14 |
| Fred  | 2020 |         22 |
| Sandy | 2020 |         10 |
| Wendy | 2020 |          0 |
+-------+------+------------+
4 rows in set (0.001 sec)

하지만 더 좋은 방법이 없을까 생각하고 있습니다.완전한 SQL 스테이트먼트에 이미 많은 UNION 조항이 포함되어 있기 때문에 어떻게 하면 그것들을 제거할 수 있을지 고민하고 있습니다.

이거 어떻게 하는지 모르겠어요.

좋은 생각 있어요?

CREATE DATABASE IF NOT EXISTS test_db;
USE test_db;

DROP TABLE IF EXISTS test_hours;
CREATE TABLE test_hours (
  ID int(10) AUTO_INCREMENT PRIMARY KEY,
  users_ID int(10),
  year int(4),
  hours int(4)
);

DROP TABLE IF EXISTS test_users;
CREATE TABLE test_users (
  ID int(10),
  name varchar(60) 
);

INSERT INTO test_users (ID, name) VALUES
  (1, 'Tom'), (2, 'Wendy'), (3, 'Fred'), (4, 'Sandy');

INSERT INTO test_hours (users_ID, year, hours) VALUES
  (1, 2018, 3), (1, 2018, 5), (1, 2019, 7), (1, 2019, 2), (1, 2019, 9), (1, 2020, 5), (1, 2020, 9),
  (2, 2018, 7), (2, 2018, 11), (2, 2018, 8), (2, 2019, 10), (2, 2019, 12),
  (3, 2018, 4), (3, 2018, 1), (3, 2018, 15), (3, 2020, 10), (3, 2020, 12),
  (4, 2019, 7), (4, 2019, 11), (4, 2020, 4), (4, 2020, 6);

가장 간단한 방법은 조건부 집약을 사용하는 것입니다.

SELECT u.name, 2020 as year,
       SUM(CASE WHEN h.year = 2020 THEN h.hours ELSE 0 END) 
FROM test_hours h JOIN
     test_users u
     ON h.users_ID = u.ID
GROUP BY u.ID;

주의: 를 변경했습니다.GROUP BY사용하다test_users표가 어디에 있는지ID아마 프라이머리 키일 것입니다.따라서 다음과 같은 다른 컬럼을 선택해도 괜찮습니다.name같은 테이블에서요

다른 방법으로는LEFT JOIN:

SELECT u.name, 2020 as year,
       SUM(h.hours)
FROM test_users u LEFT JOIN
     test_hours h
     ON h.users_ID = u.ID AND
        h.year = 2020
GROUP BY u.id, u.name;

데이터 집합에서 특정 연도(또는 일 또는 시간)가 누락되어 있고 0을 표시하려는 경우에도 해당 항목의 목록을 원하는 일반적인 문제의 지나치게 복잡한 예가 있습니다.

일반적인 해결책은 가능한 모든 값을 표로 시작하는 것입니다.이것은 아마도 데이터와 독립적으로 계산되어야 할 것이다.MariaDB에는 특히 편리한 방법이 있습니다.

SELECT num AS 'year'
    FROM seq_2000_to_2100  -- so you don't have to change this
    WHERE num BETWEEN 2018 AND 2020  -- unclear on what range you want
    -- You could do MIN(year) to MAX(year) from test_hours

그 후 다음과 같이 사용합니다.

SELECT u.name,
       year,
       COALESCE(SUM(h.hours), 0) AS tot_hours  -- Note COALESCE to avoid NULL
    FROM ( ( the above query )
           LEFT JOIN test_hours USING(year)  -- note LEFT
         )
    JOIN users ...
    WHERE ...
    

언급URL : https://stackoverflow.com/questions/65765465/thoughts-on-how-to-replace-union-clause-in-mariadb-statement