mysql - valeurs - where count sql




Comment compter des valeurs distinctes qui satisfont toutes une condition dans MySQL? (6)

Per MemberIdentifier trouve le statut que vous considérez comme approprié, par exemple 'In Progress' gagne 'Complete' et 'Not Started' . 'Not Started' gagne 'Complete' . Utilisez l'agrégation conditionnelle pour cela.

select status, count(*)
from
(
  select 
    case when sum(status = 'In Progress') > 0 then 'In Progress'
         when sum(status = 'Not Started') > 0 then 'Not Started'
         else 'Complete'
    end as status
  from mytable
  group by memberidentifier
) statuses
group by status;

J'essaie d'écrire une requête pour trouver des valeurs distinctes dans un champ particulier, compter le nombre d'occurrences de cette valeur où pour toutes les instances de cette valeur particulière une autre valeur de colonne est satisfaite, puis afficher les résultats comme suit (plus d'explications suivre):

Exemple db:

RowId    Status       MemberIdentifier
-----    ------       ----------------
1       In Progress   111111111
2       Complete      123456789
3       Not Started   146782452
4       Complete      111111111
5       Complete      123456789
6       Not Started   146782452
7       Complete      111111111

Résultat désiré:

Status         MemberIdentifierCount 
------         ---------------------- 
Not Started    1
In Progress    1
Complete       1

Dans la requête ci-dessus, le nombre d'identifiants membres distincts avec un statut donné est compté et affiché. Si un identificateur de membre a deux lignes avec le statut 'Terminé' mais une avec le statut 'En cours', il est groupé et compté comme étant en cours (ie MemberIdentifier = 111111111). Pour qu'un MemberIdentifier soit groupé et considéré comme complet, toutes ses lignes doivent avoir le statut 'Complete' (ie, MemberIdentifier = 123456789). Toute idée serait appréciée (débutant MySQL).


SQL

SELECT AdjustedStatus AS Status,
       COUNT(*) AS MemberIdentifierCount
FROM
(SELECT IF(Status='Complete',
           IF(EXISTS(SELECT Status
                     FROM tbl t2
                     WHERE t2.Status = 'In Progress'
                       AND t2.MemberIdentifier = t1.MemberIdentifier),
              'In Progress',
              'Complete'),
           Status) AS AdjustedStatus,
        MemberIdentifier
 FROM tbl t1
 GROUP BY AdjustedStatus, MemberIdentifier) subq
GROUP BY AdjustedStatus;

Démonstration en ligne

http://rextester.com/FFGM6300

Explication

La première fonction IF() vérifie si le statut est "Complete" et si c'est le cas, vérifie l'existence d'un autre enregistrement avec le même MemberIdentifier mais avec un statut "In Progress": Ceci est fait via IF(EXISTS(SELECT...))) . S'il est trouvé, un statut de "En cours" est assigné au champ AdjustedStatus , sinon le paramètre AdjustedStatus est défini à partir de la valeur d' Status (non ajustée).

Avec le statut ajusté ayant été dérivé comme ceci pour chacune des lignes dans la table, GROUP BY le AdjustedStatus et MemberIdentifier afin d'obtenir toutes les combinaisons uniques de ces deux valeurs de champ. Ceci est ensuite transformé en subq - subq en subq . Agréger ensuite ( GROUP BY ) le AdjustedStatus et compter le nombre d'occurrences, c'est-à-dire le nombre d' MemberIdentifier uniques pour chacun.


Si l'ordre de priorité pour le status est

 Not Started
 In Progress
 Complete

Nous pouvons utiliser un raccourci ...

   SELECT t.memberIdentifier
        , MAX(t.status) AS status
     FROM mytable t
    GROUP BY t.MemberIdentifier

Cela nous donne l' memberIdentifier distinct memberIdentifier .

S'il y a des lignes pour un membre qui a des lignes dans les états 'In Progress' et 'Complete' , la requête retournera 'In Progress' comme état.

Nous obtiendrons le statut 'Complete' retourné pour un membre seulement si ce membre n'a pas de ligne avec un statut supérieur à 'Complete' .

Pour obtenir des comptes à partir de ce résultat, nous pouvons référencer cette requête en tant que vue en ligne:

 SELECT q.status
      , COUNT(q.memberIdentifier) 
   FROM ( 
          SELECT t.memberIdentifier
               , MAX(t.status) AS status
            FROM mytable t
           GROUP BY t.MemberIdentifier
        ) q
  ORDER BY q.status

Pensez à cela si ... MySQL exécute la requête entre les parenthèses en premier (MySQL appelle cela une "table dérivée".) Les résultats de la requête sont un ensemble de lignes qui peuvent être interrogées comme une table.

Nous pourrions faire un COUNT(DISTINCT q.memberIdentifier) ou, en supposant que memberIdentifier est garanti non-NULL, nous pourrions faire COUNT(1) ou SUM(1) et obtenir un résultat équivalent. (Le GROUP BY dans la vue inline nous garantit que memberIdentifier sera unique.)

Dans le cas plus général, où nous n'avons pas un raccourci pratique de l'ordre alphabétique pour la préséance du statut ... nous pourrions utiliser une expression qui renvoie des valeurs qui sont "dans l'ordre". Cela rend la requête un peu plus compliquée, mais cela fonctionnerait de la même manière.

Nous pourrions remplacer t.status avec quelque chose comme ceci:

  CASE t.status
  WHEN 'Complete'    THEN 1
  WHEN 'In Progress' THEN 2
  WHEN 'Not Started' THEN 3
  ELSE 4
  END AS `status_priority`

Et remplacez q.status par quelque chose d'inverse, à convertir en chaînes:

  CASE q.status_priority
  WHEN 1 THEN 'Complete'
  WHEN 2 THEN 'In Progress'
  WHEN 3 THEN 'Not Started'
  ELSE NULL
  END AS `status`

Nous aurions besoin de décider comment nous gérerions les valeurs de statut qui ne sont pas l'un des trois ... sont ceux qui seront ignorés, traités comme une priorité plus ou moins élevée que les autres. (Un cas de test serait des lignes avec status = 'Unknown' et des lignes avec status = 'Abracadabra .


utilisez le code suivant pour obtenir le statut de MemberIdentifier

select MemberIdentifier
,case 
when total = cn then 'Complete' 
when total < cn then 'In Progress' 
when total is null then 'Not Started' END as Fstatus
 from 
(
select sum(stat) total,MemberIdentifier,(select count(MemberIdentifier) as cnt from tbldata t1
     where t1.MemberIdentifier = C.MemberIdentifier
     group by MemberIdentifier) as cn
from (
select MemberIdentifier,case status when 'In Progress' then -1 
                                    when 'Complete' Then 1 
                                    when 'Not Started' then null End as Stat from tbldata 
 ) C
 group by MemberIdentifier

 ) as f1

utilisez le code suivant pour obtenir le compte de MemberIdentifiers dans un état particulier.

Select count(fstatus) counts,fstatus from (
select MemberIdentifier
,case when total = cn then 'Complete' 
      when total < cn then 'In Progress' 
      when total is null then 'Not Started' END as Fstatus
 from 
(
select sum(stat) total,MemberIdentifier,(select count(MemberIdentifier) as cnt from tbldata t1
     where t1.MemberIdentifier = C.MemberIdentifier
     group by MemberIdentifier) as cn
from (
select MemberIdentifier
,case status when 'In Progress' then -1 when 'Complete' Then 1 when 'Not Started' then null End as Stat from tbldata 
 ) C
 group by MemberIdentifier

 ) as f1

 ) f2 group by fstatus
sortie:
counts  fstatus
1       Complete
1       In Progress
1       Not Started

Une autre façon d'utiliser une table spécifique pour configurer l'ordre (carte à la puissance de deux entiers).

Ce mappage permet à bit_or aggregate de simplement transposer des données.

http://rextester.com/edit/ZSG98543

-- Table bit_progression to determine priority

CREATE TABLE bit_progression (bit_status int PRIMARY KEY, Status VARCHAR(255));
INSERT INTO bit_progression (bit_status, Status)
VALUES
(1,       'Not Started'),  
(2,       'Complete'   ),      
(4,       'In Progress');

select
    Status,
    count(*)
from
    (
    select
         MemberIdentifier,max(bit_status) bit_status
    from
        tbl natural join bit_progression
    group by
        MemberIdentifier
    ) Maxi natural join bit_progression
group by
    Status
;

produire

Status  count(*)

1   Complete    1
2   In Progress 1
3   Not Started 1

Supplémentaire :

select
    MemberIdentifier,
    bit_or(bit_status) bits_status,
    case when bit_or(bit_status) & 4 = 4 then true end as withStatusInProgress,
    case when bit_or(bit_status) & 2 = 2 then true end as withStatusComplete,
    case when bit_or(bit_status) & 1 = 1 then true end as withStatusNotStarted
from
    tbl natural join bit_progression
group by
    MemberIdentifier
;

le produire:

MemberIdentifier bits_status    withStatusInProgress    withStatusComplete  withStatusNotStarted

111111111   6   1       1       NULL
123456789   2   NULL    1       NULL
146782452   1   NULL    NULL    1

SELECT max_status AS Status
     , COUNT(*) AS ct
    FROM (
        SELECT MAX(Status) AS max_status
            FROM tbl
            GROUP BY MemberIdentifier
         ) AS a
    GROUP BY max_status;

Cela profite de la façon dont ces chaînes se comparent: "En cours"> "Complète". Ce faisant, il fait des choses aléatoires à tout autre membre avec plusieurs statuts.





distinct