1
votes

Comment compter plusieurs colonnes dans SQL (Oracle) avec des critères?

Je travaille sur une passerelle SMS qui contient plusieurs services SMS facturés avec des numéros différents,
chaque SMS envoyé au client a 4 statuts comme ci-dessous ( transféré, livré, expiré, livraison échouée )

Maintenant, j'ai le premier tableau ci-dessous pour le système de charge avec les détails ci-dessous ( TABLE-A )

et ci-dessous ( TABLE-B ) qui contiennent l'état de chaque SMS envoyé avec son identifiant

Voici mon résultat final attendu pour prévoir les détails de chaque service sms:

Au début, je pensais que c'était facile, il me suffisait d'utiliser COUNT (Case when ...)
mais dans mon cas, j'ai des milliers de numéros de SMS (services), donc si j'utilise cette approche, ce sera comme ça: -

COUNT(CASE WHEN a.SMS_SHORT_CODE='1111' AND B.STATUS='forwarded' )
COUNT(CASE WHEN a.SMS_SHORT_CODE='1111' AND B.STATUS='delivered' )
COUNT(CASE WHEN a.SMS_SHORT_CODE='1111' AND B.STATUS='expired' )
COUNT(CASE WHEN a.SMS_SHORT_CODE='1111' AND B.STATUS='delivery failed' )
COUNT(CASE WHEN a.SMS_SHORT_CODE='5000' AND B.STATUS='forwarded' )
COUNT(CASE WHEN a.SMS_SHORT_CODE='5000' AND B.STATUS='delivered' )
COUNT(CASE WHEN a.SMS_SHORT_CODE='5000' AND B.STATUS='expired' )
COUNT(CASE WHEN a.SMS_SHORT_CODE='5000' AND B.STATUS='delivery failed' )
...
...
...
...
...
...
...

L'approche ci-dessus n'est pas pratique quand vous avez de nombreux services notent également que CASE ne peut gérer que 250 conditions?

Alors, quelle est la meilleure approche pour faire jointure externe gauche pour (Tableau A) sur (Tableau B) en utilisant le SMS-ID et compter chaque état SMS et le prévoir comme ci-dessous?

sql oracle pivot count

0 commentaires

3 Réponses :

1
votes

Veuillez utiliser la requête ci-dessous,

select 
A.SMS_SHORT_CODE,
case when status = 'forwaded' then count(status ) end as count_of_forwaded,
case when status = 'delivered' then count(status ) end as count_of_status,
case when status = 'expired' then count(status ) end as count_of_expired,
case when status = 'delivery failed' then count(status ) end as count_of_delivery_failed
from TABLEA A
inner join TABLEB B 
on (A.SMS_ID = B.SMS_ID)
group by A.SMS_SHORT_CODE, status ;

0 commentaires

1
votes

Vous pouvez utiliser PIVOT code> ( introduit dans la version Oracle 11g ) pour ces colonnes status :

SELECT sms_short_code, 
       COUNT_OF_forwarded, 
       COUNT_OF_delivered, 
       COUNT_OF_expired,
       COUNT_OF_delivery_failed
  FROM tableB
 PIVOT 
 (
  COUNT(*) FOR status IN ( 'forwarded'       AS COUNT_OF_forwarded,
                           'delivered'       AS COUNT_OF_delivered,
                           'expired'         AS COUNT_OF_expired,
                           'delivery failed' AS COUNT_OF_delivery_failed )
 )

par exemple il suffit d'utiliser TableB .

Démo

2 commentaires

merci Barbaros Özhan, je vais essayer, mais j'ai besoin de la jointure externe gauche de la table avec la table A car certains sms sont gratuits, le TABLEAU A contient uniquement les SMS chargés, c'est pourquoi j'utilise la jointure.

vous êtes le bienvenu @ OsamaAl-Banna, mais vous devez étendre le jeu de sortie comme vous le souhaitez et élaborer davantage la question en la modifiant.

1
votes

Je suggérerais une agrégation conditionnelle:

select b.SMS_SHORT_CODE,
       sum(case when status = 'forwaded' then 1 else 0 end) as count_of_forwaded,
       sum(case when status = 'delivered' then 1 else 0 end) as count_of_status,
       sum(case when status = 'expired' then 1 else 0 end) as count_of_expired,
       sum(case when status = 'delivery failed' then 1 else 0 end) as count_of_delivery_failed
from TABLEB b
group by b.SMS_SHORT_CODE ;

Notez qu'aucune JOIN n'est nécessaire. Toutes les données que vous souhaitez agréger se trouvent dans TABLEB.

3 commentaires

merci, je vais essayer, mais j'ai besoin de la jointure externe gauche de la table avec la table A car certains sms sont gratuits, la TABLE A ne contient que les SMS facturés, c'est pourquoi j'utilise la jointure.

@ OsamaAl-Banna. . . C'est bon. Vous pouvez utiliser une jointure gauche . Mais ce n'est pas nécessaire pour la question que vous avez posée .

J'ai choisi cette approche car plus simple et travaillant sur d'autres scénarios et moins complexes pour moi, je pense que j'ai besoin d'apprendre le pivot aussi.