MySQL GROUP BY 두 열

MySQL GROUP BY 두 열

---

여기서는 각 테이블에 하나씩 여러 열로 그룹화하려고합니다.
현재 포트폴리오와 현금을 합산하여 각 고객의 최고 포트폴리오 가치를 찾고 싶지만 고객은 둘 이상의 포트폴리오를 가질 수 있으므로 각 고객에 대한 최고 포트폴리오가 필요합니다.

현재 아래 코드를 사용하면 각 상위 포트폴리오에 대해 동일한 클라이언트를 여러 번 받고 있습니다 (클라이언트 ID별로 그룹화되지 않음).

SELECT clients.id, clients.name, portfolios.id, SUM ( portfolios.portfolio +  portfolios.cash ) AS total
FROM clients, portfolios
WHERE clients.id = portfolios.client_id
GROUP BY portfolios.id, clients.id
ORDER BY total DESC
LIMIT 30 

---

먼저 테스트 데이터를 만들어 보겠습니다.

create table client (client_id integer not null primary key auto_increment,
                     name varchar(64));
create table portfolio (portfolio_id integer not null primary key auto_increment,
                        client_id integer references client.id,
                        cash decimal(10,2),
                        stocks decimal(10,2));
insert into client (name) values ('John Doe'), ('Jane Doe');
insert into portfolio (client_id, cash, stocks) values (1, 11.11, 22.22),
                                                       (1, 10.11, 23.22),
                                                       (2, 30.30, 40.40),
                                                       (2, 40.40, 50.50);

포트폴리오 ID가 필요하지 않은 경우 간단합니다.

select client_id, name, max(cash + stocks)
from client join portfolio using (client_id)
group by client_id

+-----------+----------+--------------------+
| client_id | name     | max(cash + stocks) |
+-----------+----------+--------------------+
|         1 | John Doe |              33.33 | 
|         2 | Jane Doe |              90.90 | 
+-----------+----------+--------------------+

포트폴리오 ID가 필요하기 때문에 상황이 더 복잡해집니다. 단계적으로 해봅시다. 먼저 각 클라이언트에 대한 최대 포트폴리오 값을 반환하는 하위 쿼리를 작성합니다.

select client_id, max(cash + stocks) as maxtotal
from portfolio
group by client_id

+-----------+----------+
| client_id | maxtotal |
+-----------+----------+
|         1 |    33.33 | 
|         2 |    90.90 | 
+-----------+----------+

그런 다음 포트폴리오 테이블을 쿼리하되 이전 하위 쿼리에 조인을 사용하여 총 값이 클라이언트에 대해 최대 값 인 포트폴리오 만 유지합니다.

 select portfolio_id, cash + stocks from portfolio 
 join (select client_id, max(cash + stocks) as maxtotal 
       from portfolio
       group by client_id) as maxima
 using (client_id)
 where cash + stocks = maxtotal

+--------------+---------------+
| portfolio_id | cash + stocks |
+--------------+---------------+
|            5 |         33.33 | 
|            6 |         33.33 | 
|            8 |         90.90 | 
+--------------+---------------+

마지막으로 각 클라이언트의 이름을 포함하기 위해 클라이언트 테이블에 조인 할 수 있습니다.

select client_id, name, portfolio_id, cash + stocks
from client
join portfolio using (client_id)
join (select client_id, max(cash + stocks) as maxtotal
      from portfolio 
      group by client_id) as maxima
using (client_id)
where cash + stocks = maxtotal

+-----------+----------+--------------+---------------+
| client_id | name     | portfolio_id | cash + stocks |
+-----------+----------+--------------+---------------+
|         1 | John Doe |            5 |         33.33 | 
|         1 | John Doe |            6 |         33.33 | 
|         2 | Jane Doe |            8 |         90.90 | 
+-----------+----------+--------------+---------------+

John Doe에 대해 총액이 정확히 동일한 두 개의 포트폴리오가 있으므로 두 행을 반환합니다. 이를 방지하고 임의의 상위 포트폴리오를 선택하려면 GROUP BY 절에 태그를 지정하십시오.

select client_id, name, portfolio_id, cash + stocks
from client
join portfolio using (client_id)
join (select client_id, max(cash + stocks) as maxtotal
      from portfolio 
      group by client_id) as maxima
using (client_id)
where cash + stocks = maxtotal
group by client_id, cash + stocks

+-----------+----------+--------------+---------------+
| client_id | name     | portfolio_id | cash + stocks |
+-----------+----------+--------------+---------------+
|         1 | John Doe |            5 |         33.33 | 
|         2 | Jane Doe |            8 |         90.90 | 
+-----------+----------+--------------+---------------+

---

그룹 기준에서 Concat을 사용하면 작동합니다.

SELECT clients.id, clients.name, portfolios.id, SUM ( portfolios.portfolio + portfolios.cash ) AS total
FROM clients, portfolios
WHERE clients.id = portfolios.client_id
GROUP BY CONCAT(portfolios.id, "-", clients.id)
ORDER BY total DESC
LIMIT 30

참고 URL : https://stackoverflow.com/questions/2183373/mysql-group-by-two-columns