MySQL GROUP BY deux colonnes

Question

J'essaie de regrouper par plusieurs colonnes ici - une sur chaque table.
Il s'agit d'un scénario dans lequel je veux trouver la valeur du portefeuille le plus élevé pour chaque client en additionnant leur portefeuille actuel et leurs liquidités, mais un client peut avoir plus d'un portefeuille, et j'ai donc besoin du portefeuille le plus élevé pour chaque client.

Pour l'instant, avec le code ci-dessous, j'obtiens les mêmes clients plusieurs fois pour chacun de leurs principaux portefeuilles (il n'y a pas de regroupement par identifiant de client).

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

Answer 1

D'abord, faisons quelques données de test :

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);

Si vous n'aviez pas besoin de l'ID du portefeuille, ce serait facile :

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 | 
+-----------+----------+--------------------+

Comme vous avez besoin de l'ID du portefeuille, les choses se compliquent. Faisons-le par étapes. D'abord, nous allons écrire une sous-requête qui renvoie la valeur maximale du portefeuille pour chaque client :

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

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

Ensuite, nous interrogerons la table des portefeuilles, mais en utilisant une jointure à la sous-requête précédente afin de ne garder que les portefeuilles dont la valeur totale est maximale pour le client :

 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 | 
+--------------+---------------+

Enfin, nous pouvons joindre la table des clients (comme vous l'avez fait) afin d'inclure le nom de chaque client :

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 | 
+-----------+----------+--------------+---------------+

Notez que cela renvoie deux lignes pour Jean Dupont car il a deux portefeuilles avec exactement la même valeur totale. Pour éviter cela et choisir un portefeuille supérieur arbitraire, utilisez une clause 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 | 
+-----------+----------+--------------+---------------+