MS SQL Server
  1. MS SQL Server
  3. Comptage de l'occurrence lors de la modification de la valeur NULL à 0
Intereting Posts
T-SQL Puzzler – Dépendances d'objects rampants Comment calculer la sum différentielle par groupe de valeurs en fonction de l'ordre Comment utiliser le package SSIS dans l'application C # sans installer SSIS sur le server, est-ce possible? Améliorer les performances de l'extraction de requêtes à partir de View Comment faire pour que les valeurs d'une table SQL soient les en-têtes d'une vue? Renvoyer une seule ligne pour une fonction d'agrégat par période et user row_number () Grouper par? SQL Enlève le dernier caractère si un certain existe dans une string Données d'export SQL à partir d'une requête à utiliser Tilde (~) Génération automatique de formulaires VB.NET à l'aide de SQL Server 2008 Dans une instruction SELECT (MS SQL), comment découper une string Code pour créer et se connecter à une database SQL Server: Quel est le problème avec? Réinitialiser manuellement les colonnes IDENTITY Dans SQL Server 2005, est-il possible de définir le niveau d'isolation des transactions user par user? Boîte de dialog de conversation de SQL Server Service Broker Queue

Comptage de l'occurrence lors de la modification de la valeur NULL à 0

J'ai une table comme celle-ci (key primaire omise pour plus de clarté): 

events: itemId eventType ----------------- 100 1 101 1 101 2 102 2 102 2

Il y a d'autres types d'events mais je me soucie seulement des 1 et 2. Je veux find le nombre de ces eventType s par itemId , mais j'ai aussi besoin d'un moyen d'effectuer facilement des calculs sur les résultats. Par exemple, je veux une sortie comme ceci: 

 itemId ones twos onesPct twosPct ------------------------------------- 100 1 0 1.0 0.0 101 1 1 0.5 0.5 102 0 2 0.0 1.0

Dans mon application actuelle, les maths que j'effectue sont beaucoup plus complexes que les pourcentages. Le dialecte est T-SQL. Donc maintenant j'ai une requête comme celle-ci; Je ne suis pas très bon avec SQL et donc le meilleur que j'ai trouvé était: 

 SELECT COALESCE(onest.itemId,twost.itemId) itemId, COALESCE(onest.n,0) ones, COALESCE(twost.n,0) twos, 1.0*COALESCE(onest.n,0) / (COALESCE(onest.n,0) + COALESCE(twost.n,0)) onesPct, 1.0*COALESCE(twost.n,0) / (COALESCE(onest.n,0) + COALESCE(twost.n,0)) twosPct FROM (SELECT itemId, COUNT(*) n FROM events WHERE eventType = 1 GROUP BY itemId) onest FULL OUTER JOIN (SELECT itemId, COUNT(*) n FROM events WHERE eventType = 2 GROUP BY itemId) twost ON onest.itemId = twost.itemId

Cela fonctionne, sauf que tous les COALESCE dans les équations deviennent extrêmement lourd. J'ai donc deux questions:

  1. Le plus important: existe-t-il un moyen de convertir les NULL de la jointure externe à 0 à un niveau plus global, de sorte que je n'ai pas besoin d'écrire par exemple COALESCE(onest.n,0) partout? D'après ce que j'ai vu jusqu'ici, je ne suis pas autorisé à utiliser des alias de colonne dans d'autres spécifications de colonnes, donc je ne peux pas faire par exemple (1.0 * ones / (ones + twos)) onestPct dans la requête ci-dessus est un moyen de le faire)?

  2. Y a-t-il une requête plus courte qui peut accomplir tout cela? Cela semble être un gâchis de sous-requêtes.

Je ne me soucie pas trop de la performance, la tâche principale est l'extraction de données et l'parsing.

Ici, c'est sur SQLFiddle .

Aussi, mes excuses pour le titre pauvre, je n'ai pas pu find un bon résumé.

Je sais que vous avez dit que ce que vous faites est plus compliqué que les pourcentages, mais je pense que la key est simplement une sous-requête.

Faites votre travail de groupage en premier. Lorsque cela est fait, utilisez cela comme source d'une requête dans laquelle vous faites le calcul réel. Et bien sûr, si c'est trop complexe, vous pouvez envisager de placer cette sous-requête dans une table temporaire.

Donc, voici ce que je ferais si c'était juste datatables que vous avez fournies. 

 select itemid, ones, twos, ones + twos as total, 100 * (round(ones / (ones + twos), 4)) as ones_perc, 100 * (round(twos / (ones + twos), 4)) as twos_perc from (select itemid, sum(case when eventtype = 1 then 1 else 0 end) as ones, sum(case when eventtype = 2 then 1 else 0 end) as twos from a group by itemid)b

Vous pouvez utiliser des fonctions d'agrégat ( SUM() ) sur CASE ce qui simplifie l'set de la requête: 

 select itemId, sum(case eventType when 1 then 1 else 0 end) ones, sum(case eventType when 2 then 1 else 0 end) twos, convert(float, sum(case eventType when 1 then 1 else 0 end)) / count(eventType) onesPct, convert(float, sum(case eventType when 2 then 1 else 0 end)) / count(eventType) twosPct from events group by itemId

Démo de Fiddle SQL

S'il existe des éléments qui n'ont aucun événement, vous devez les exclure ou gérer le calcul du pourcentage séparément car la requête ci-dessus entraînera une division par zéro. 

 select itemId,ones,twos,ones/c onesPct,twos/c twosPct from ( select itemId,sum(2-eventType) ones,sum(eventType-1) twos,sum(1.0) c from events where eventType in (1,2) group by itemId ) e