Compter le nombre d'occurrences entre deux comptes

Bonjour à tous,

Je viens vers vous car j’ai une question quant à la possibilité de réaliser une query en SQL (je pense que oui mais je ne visualise pas trop comment).

J’ai une table assez classique où il y a un account_id et un account_id_receiver avec chaque ligne qui représente une transaction.

J’aimerais dénombrer les comptes qui font le plus de transactions entre eux. Il faudrait calculer les transactions réaliser "dans les deux sens". Par exemple si j’ai la table suivante :

Il faudrait additionner les transactions suivantes :

• une transaction 1 vers 2
• deux transactions 2 vers 1

J’arrive sans souci à compter du account_id vers account_id_receiver mais je ne saurai pas les additionner par la suite.

Est-ce déjà possible à réaliser et si oui je veux bien quelques pistes ?

Excellente journée à vous

J’arrive sans souci à compter du account_id vers account_id_receiver mais je ne saurai pas les additionner par la suite.

Tu peux nous montrer ta requête actuelle ?

Il faudrait additionner les transactions suivantes :

• une transaction 1 vers 2
• deux transactions 2 vers 1

Cela revient à dire que tu veux filtrer sur les lignes qui respectent ces deux conditions :

• account_id est égal à 1 ou égal à 2
• account_id_receiver est égal à 1 ou égal 2

En SQL, cela peut se traduire comme cela :

select * from tx where
  account_id in (1, 2) and
  account_id_receiver in (1, 2);

-- |1|2|10.0
-- |2|1|15.0
-- |2|1|12.0

Et on peut bien-sûr faire la somme dessus :

select sum(amount) from tx where
  account_id in (1, 2) and
  account_id_receiver in (1, 2);

-- 37.0

Et compter le nombre de transactions entre 1 et 2 :

select count(*) from tx where
  account_id in (1, 2) and
  account_id_receiver in (1, 2);

-- 3

Le in (...) permet de dire « égal à l’un de ces termes », ça permet d’éviter d’alourdir les clauses avec des and et des or.

Salut,
J’imagine que le couple (1,2) et (2,1) n’est qu’un exemple et que l’OP souhaite avoir tous les échanges possibles non ?

Question pour @Emphy : quel est ton SGBD ?

Hello à vous deux,

Tout d’abord merci pour vos réponses !

Tu peux nous montrer ta requête actuelle ?

Oui la voici :

SELECT account_id, account_id_receiver, COUNT(*)
FROM transactions
GROUP BY account_id, account_id_receiver
ORDER BY COUNT(*) DESC

J’obtiens ainsi le tableau suivant :

@Angelo : en effet, j’aurais besoin de le faire pour tous les échanges possibles !

Il s’agit d’un exercice que je dois rendre donc il n’y a aucune précision sur le SGBD. Peut-être que ai-je mal interprété la consigne : "Compter les utilisateurs qui ont fait le plus de transactions entre eux (dans un sens ou dans l’autre)"

Merci déjà pour vos premières réponses

Petite précision : les fonction min et max peuvent avoir des noms différents selon la DB que tu utilises, d’où la question d’Angelo je pense.

J’avoue que je pensais plus à CONCAT (tout en ne sachant pas comment ordonner derrière ) qui est spécifique à mysql il me semble
A priori min et max sont bien générique

Merci pour l’astuce d’ailleurs !

Rebonjour !

Top c’est exactement ce que je cherchais

Je t’avoue que je n’y avais pas du tout pensé alors que c’est très logique une fois qu’on l’a vu… Merci pour l’astuce, je retiendrai pour la suite

Bonne journée à vous !

SELECT account_id, account_id_receiver, COUNT(*)
FROM transactions
GROUP BY min(account_id, account_id_receiver), max(account_id, account_id_receiver)
ORDER BY COUNT(*) DESC;

Cette requête marche ? ?

Sur l’exemple avec les users 1 et 2, il va afficher 1;2;count() ou bien 2;1;count()

Dites moi quel SGBD accepte cette requête … pour que je ne l’utilise jamais.

Cette requête me semble beaucoup moins ambigue :

SELECT min(account_id, account_id_receiver), max(account_id, account_id_receiver), COUNT(*)
FROM transactions
GROUP BY min(account_id, account_id_receiver), max(account_id, account_id_receiver)
ORDER BY COUNT(*) DESC;

La 2ème remarque que je voulais faire, c’est que min() et max() risquent de ne pas être acceptées dans ce contexte sur tous les SGBD.

D’autres SGBD vont fonctionner avec Least() et Greatest().

Un contournement, pour que ça fonctionne systématiquement :

Pour avoir un truc qui marche toujours, remplacer min(a,b) par (a+b-abs(a-b))/2 et remplacer max(a,b) par (a+b+abs(a-b))/2

Ce dernier point, c’est pour le fun… je vois bien que c’est particulièrement lourd… et inutile si tu ne comptes pas changer de SGBD.

@elegance la requête marche avec SQLite. Je n’ai pas essayé avec PostgreSQL (qui utilise GREATEST et LEAST)

En l’occurrence c’est pour l’exercice de l’OP seulement. Dans une vraie BD d’entreprise, je pense qu’il faudrait carrément revoir le schéma si ce genre de requêtes est la norme.

Je ne recommande pas particulièrement ce genre d’acrobaties dans une DB de production.

Si on devait partir sur une solution vraiment pérenne avec la contrainte ne pas pouvoir restructurer le schéma je pense que je procéderais ainsi : pour chaque couple, dériver une clef qui est la même aussi bien pour (x, y) que pour (y, x ) et qui leur est unique. Une computed column peut s’avérer utile si la DB les supporte. Cette clef peut-être représentée de diverses manières : une string qui comporte les deux ID ordonnés concaténés (ex. : "x-y"), ou pour être très original : le produit de deux ID qui doivent alors être des nombres premiers (ex. : 34 pour (2, 17) et (17, 2)).

Au moment de la recherche des transactions bidirectionnelles au sein d’un couple, il suffit de filtrer sur cette clef dérivée. Le GROUP BY inclus. Si le nombre de transactions augmente énormément, ajouter un index sur cette clef pour optimiser les groupement et les recherches.