L'utilisation efficace de SQL GROUP BY, SUM, COUNT

J'ai une table de vente de produits qui peuvent se présenter comme suit:

product   | amount   | ptype    | pdate      
p1        | 1.00     | sale     | 01/01
p1        | 2.00     | base     | 01/02
p2        | 1.50     | sale     | 02/03
p3        | 5.25     | base     | 10/10

et je voudrais construire un tableau qui montre un produit par ligne, la somme des montants, si le produit est unique de montrer le type d'autre de montrer le type de 'VAR', si le produit est unique afficher la date à laquelle le reste de la voir comme NULL. De sorte que le résultat se présente comme suit:

product   | total    | ptype    | pdate      
p1        | 3.00     | VAR      | (NULL)
p2        | 1.50     | sale     | 02/03
p3        | 5.25     | base     | 10/10

Je suis d'accomplir le résultat que j'ai besoin en procédant comme suit:

SELECT DISTINCT product
,(SELECT SUM(amount) FROM T as b GROUP BY b.product HAVING a.product = b.product ) as total
,(SELECT CASE WHEN COUNT(*) = 1 THEN a.ptype  ELSE 'VAR' END from T as b GROUP BY b.product HAVING a.product = b.product) as ptype
,(SELECT CASE WHEN COUNT(*) = 1 THEN a.pdate  ELSE NULL END from T as b GROUP BY b.product HAVING a.product = b.product) as pdate
FROM T as a

Mais je voudrais savoir si il existe un moyen plus efficace qui accomplit le même résultat.

OriginalL'auteur Daniel Vaca | 2012-06-29

  1. 6

    Il n'est pas nécessaire d'utiliser toute forme de sous-requête ou la vue intégrée. Selon le degré de sophistication du moteur de base de données, ces constructions pourraient nuire aux performances.

    Ici est ce que vous avez demandé, et il doit de manière fiable, par la suite, avec un seul balayage de la table, même les plus primitifs du moteur SQL.

    select product,
       sum(amount) as amount,
       case when count(*)=1 then min(ptype) else 'VAR' end as ptype,
       case when count(*)=1 then min(pdate) else null end as pdate
  from T
 group by product

    La suite n'est pas exactement ce que vous avez demandé, mais je pense qu'il peut être plus proche de ce que vous êtes réellement à la recherche pour. Il signale uniquement ptype comme le VAR ou d'une mise à jour comme NULL si il y a plusieurs valeurs distinctes qui composent l'agrégat.

    J'ai ajouté un pcount colonne, de sorte que vous pouvez toujours identifier singulet agrégats, même si les deux ptype et mise à jour ne sont pas nulll.

    select product,
       sum(amount) as amount,
       count(*) as pcount,
       case when count(distinct ptype)=1 then min(ptype) else 'VAR' end as ptype,
       case when count(distinct pdate)=1 then min(pdate) else null end as pdate
  from T
 group by product

    OriginalL'auteur dbenham

  2. 1

    Si vous êtes en cours d'exécution contre des majeur (base de données tels que SQL Server), l'optimiseur de requête sera probablement prendre soin de la plupart de l'optimisation sur votre nom. Cela dit, vous pouvez le faire assez facilement avec une sous-requête qui a un seul groupe. Voici un exemple qui fonctionne, et vous pouvez jouer avec:

    http://sqlfiddle.com/#!3/f2e05/19/1

    Ma requête n'a à peu près la même que la première requête offert par @dbenham; j'ai fait la même observation qu'il pourrait ne pas être ce que vous voulez vraiment. Je vais upvote cette réponse. 🙂

    OriginalL'auteur Mark Stafford - MSFT

  3. -1

    Essayer ce morceau de code:

    SELECT DISTINCT product, x.total,
IF(COUNT(x.ptype) > 1, 'VAR', x.ptype) AS ptype,
IF(COUNT(x.pdate) > 1, NULL, x.pdate) AS pdate
FROM (SELECT DISTINCT product, SUM(amount) AS total, ptype, pdate FROM table GROUP BY product) AS x
JOIN table ON x.product = table.product
GROUP BY x.product;

    Espérons que cela fonctionne.

    Ce SQL ne fonctionne pas sur SQL Server; ce n'est pas sûr d'autres moteurs de base de données.
    Dans Sybase, cela me donne un message d'erreur indiquant que "ptype" et "mise à jour" doit également figurer dans le GROUPE.
    Wow, je me suis pris avec mon pantalon vers le bas. Complètement oublié que pour un groupe de fonctions dont vous avez besoin pour réellement groupe de la table.

    OriginalL'auteur Palladium