Contagem e sub-contagem na mesma pesquisa

Tenho duas mesas: membros, ordens.

Members: MemberID, DateCreated
Orders:  OrderID, DateCreated, MemberID

Eu quero descobrir o número de novos membros em um determinado mês dividido em número de grupos de ordem, por exemplo. 5+, 4, 3, 2, 1, 0

eu tenho a consulta para trabalhar o número de ordens para um membro, mas como posso obter estes valores em uma consulta?

SELECT
  COUNT(o.orderid) AS Purchases
FROM
  members m
  LEFT JOIN orders o ON o.memberid = m.memberid
    AND MONTH(o.DateCreated) = 8
WHERE
  MONTH(m.DateCreated) = 8
GROUP BY
  m.memberid
ORDER BY
  COUNT(o.orderid) DESC
Author: Jordan Running, 2010-09-13
Source

2 answers

Há algumas maneiras de fazer isto, algumas das quais podem ser bastante complicadas. É assim que eu faria, concentrando-me na parte do novo membro e não na parte da contagem. 
  SELECT COUNT(M.MemberID),
         (SELECT COUNT(*) FROM Orders O WHERE O.MemberId = M.MemberId AND O.DateCreated BETWEEN '2010-08-01' AND DATE_ADD('2010-08-01', INTERVAL 1 MONTH)) AS num_orders
    FROM Members M
   WHERE M.DateCreated BETWEEN '2010-08-01' AND DATE_ADD('2010-08-01', INTERVAL 1 MONTH)
GROUP BY num_orders

Eu fiz a pesquisa com as datas porque seria mais rápido (poderia fazer uso de um índice enquanto {[[2]} faria sempre uma verificação completa da tabela, mas você pode alterá-lo de volta se realmente precisar de todas as ordens/membros de um determinado mês).

Editar: Esqueci-me de tratar da parte 5+ da pergunta. aqui está uma opção para isso: 

  SELECT COUNT(M.MemberID),
         (SELECT IF(COUNT(*) >= 5, '5+', COUNT(*)) FROM Orders O WHERE O.MemberId = M.MemberId AND O.DateCreated BETWEEN '2010-08-01' AND DATE_ADD('2010-08-01', INTERVAL 1 MONTH)) AS num_orders
    FROM Members M
   WHERE M.DateCreated BETWEEN '2010-08-01' AND DATE_ADD('2010-08-01', INTERVAL 1 MONTH)
GROUP BY num_orders
 1
Author: Rob Van Dam, 2010-09-13 05:21:33

Terá de usar sub-consultas na cláusula FROM, ou uma série de instruções com antes da instrução SELECT principal (se o seu SGBD suportar essa notação). Você também vai precisar corrigir suas consultas para que você não informe sobre as pessoas que aderiram em agosto de 2009, bem como aqueles que aderiram em agosto de 2010.

Resposta Mais Simples

A' resposta mais difícil ' abaixo é a consulta original muito alterada, e deixei-a porque mostra como desenvolvi a resposta. Seguinte a resposta é mais simples; ela alavanca o fato de que a contagem(coluna) retorna 0 se não houver valores não nulos na coluna a ser contada.

Utiliza uma tabela de base para controlar os agregados que devem aparecer: 

CREATE TEMP TABLE BaseCounts
(
    NumOrders CHAR(2) NOT NULL PRIMARY KEY
);
INSERT INTO BaseCounts VALUES("0 ");
INSERT INTO BaseCounts VALUES("1 ");
INSERT INTO BaseCounts VALUES("2 ");
INSERT INTO BaseCounts VALUES("3 ");
INSERT INTO BaseCounts VALUES("4 ");
INSERT INTO BaseCounts VALUES("5+");

SELECT B.NumOrders, COUNT(N.MemberID) AS NumNewMembers
  FROM BaseCounts AS B LEFT OUTER JOIN
       (SELECT MemberID, CASE WHEN NumOrders < 5
                         THEN CAST(NumOrders AS CHAR(2))
                         ELSE "5+" END AS NumOrders
          FROM (SELECT M.MemberID, COUNT(O.OrderID) AS NumOrders
                  FROM Members AS M LEFT OUTER JOIN Orders AS O
                    ON M.MemberID = O.MemberID AND
                       YEAR(O.DateCreated) = 2010 AND MONTH(O.DateCreated) = 8
                 WHERE YEAR(M.DateCreated) = 2010 AND MONTH(M.DateCreated) = 8
                 GROUP BY M.MemberID
               ) AS NMO
       ) AS N
    ON B.NumOrders = N.NumOrders
 GROUP BY B.NumOrders
 ORDER BY B.NumOrders;

A notação da tabela CREATE TEMP é para o servidor dinâmico IBM Informix (versão 11.50 usada para testar). A tabela desaparece no final da sessão (ou quando explicitamente abandonada), e é privada para uma sessão. Pode ser uma tabela de base permanente (largue a palavra-chave TEMP) instead.

A sub-consulta marcada NMO (para novas encomendas de membros) é muito importante. A condição do filtro em O.DateCreated deve aparecer na cláusula sobre e não na cláusula onde; caso contrário, não obterá as contagens zero que são necessárias. A notação de contagem (coluna) é usada duas vezes.

A explicação na resposta mais difícil mostrada abaixo irá ajudá-lo a compreender detalhes não explicados nesta resposta mais simples. Embora "mais simples", não a consideraria "simples". A resposta completa mostra a importância de iterar seu projeto; Eu não poderia ter produzido a resposta mais simples sem ter passado pelo esforço de produzir o mais difícil.

Resposta Mais Difícil

Este foi o desenvolvimento original da resposta. Creio que ainda tem utilidade para mostrar como abordei o problema. Com isso como uma base, era uma matéria relativamente simples remover o material extra ao desenvolver a resposta mais simples acima.

Contando o os zeros também são surpreendentemente difíceis, como é fazer " 5+". Então, vamos resolver isto por etapas.

Novos membros com 1 ou mais aquisições 

SELECT M.MemberID, COUNT(*) AS NumOrders
  FROM Members AS M JOIN Orders AS O ON M.MemberID = O.MemberID
 WHERE YEAR(M.DateCreated) = 2010 AND MONTH(M.DateCreated) = 8
   AND YEAR(O.DateCreated) = 2010 AND MONTH(O.DateCreated) = 8
 GROUP BY M.MemberID

Chama a essa lista " NZO "(para "ordens não-zero"). Note - se que uma saída à esquerda atribuiria as pessoas ao grupo '1' mesmo que não fizessem encomendas - não o resultado desejado.

Novos membros com 0 compras 

SELECT M.MemberID, 0 AS NumOrders
  FROM Members AS M
 WHERE YEAR(M.DateCreated) = 2010 AND MONTH(M.DateCreated) = 8
   AND NOT EXISTS (SELECT * FROM Orders AS O
                    WHERE YEAR(O.DateCreated) = 2010
                      AND MONTH(O.DateCreated) = 8
                      AND O.MemberID = M.MemberID
                  )
Isso é uma pergunta desagradável por causa da sub-consulta correlacionada, mas evita referenciar o NZO. Uma alternativa seria: encontrar a lista dos membros que aderiram no mês de referência, e subtrair dessa lista a lista de membros com 1 ou mais ordens (NZO).

Chama a essa lista " WZO "(para "com ordens nulas").

É evidente que a NZO e a WZO não têm membros em comum - A União ou a União, todas elas, apresentam uma lista de novos membros e o número de encomendas que efectuaram.

Novos membros nas seis categorias 

SELECT MemberID, CAST(NumOrders AS CHAR(2)) AS NumOrders
  FROM WZO
UNION
SELECT MemberID, CASE WHEN NumOrders < 5 THEN CAST(NumOrders AS CHAR(2))
                 ELSE "5+" END AS NumOrders
  FROM NZO

Os moldes resolvem um problema com os tipos aqui - a coluna dos numeradores é um numérico digite e o resultado precisa ser uma string.

Chame esta lista NMC (novos membros em categorias).

Resuma os resultados 

SELECT NumOrders, COUNT(*) AS NumNewMembers
  FROM NMC
 GROUP BY NumOrders
 ORDER BY NumOrders;

Penúltimo pedido

Reunir os vários bits e peças acima-e obter os bits certos nos locais certos-produz a seguinte consulta: 

SELECT NumOrders, COUNT(*) AS NumNewMembers
  FROM (SELECT MemberID, CAST(NumOrders AS CHAR(2)) AS NumOrders
          FROM (SELECT M.MemberID, 0 AS NumOrders
                  FROM Members AS M
                 WHERE YEAR(M.DateCreated) = 2010 AND MONTH(M.DateCreated) = 8
                   AND NOT EXISTS (SELECT * FROM Orders AS O
                                    WHERE YEAR(O.DateCreated) = 2010
                                      AND MONTH(O.DateCreated) = 8
                                      AND O.MemberID = M.MemberID
                                  )
               ) AS WZO
        UNION
        SELECT MemberID, CASE WHEN NumOrders < 5
                         THEN CAST(NumOrders AS CHAR(2))
                         ELSE "5+" END AS NumOrders
          FROM (SELECT M.MemberID, COUNT(*) AS NumOrders
                  FROM Members AS M JOIN Orders AS O ON M.MemberID = O.MemberID
                 WHERE YEAR(M.DateCreated) = 2010 AND MONTH(M.DateCreated) = 8
                   AND YEAR(O.DateCreated) = 2010 AND MONTH(O.DateCreated) = 8
                 GROUP BY M.MemberID
               ) AS NZO
       ) AS NMC
 GROUP BY NumOrders
 ORDER BY NumOrders;

Essa consulta completa foi executada com sucesso contra o servidor dinâmico IBM Informix 11,50. Para a amostra de dados que eu gerei (ver abaixo), eu tenho o resultado: 

numorders   numnewmembers
CHAR(2)     DECIMAL(15,0)
0           1
1           1
2           1
3           1
4           1
5+          2
O esquema geral de como construir a consulta fragmentada deve ajudá-lo a projetar suas próprias consultas no futuro. Em particular, você pode validar os diferentes segmentos da consulta enquanto vai. Você pode achar mais fácil trabalhar a data criando os primeiros e Últimos Dias do mês em que você está interessado, e então executando a consulta para esses intervalos-o que também é mais flexível porque poderia fazer quartos ou meio meses ou períodos que se estendem por dois mês. Note também que se não houver novos membros que coloquem, digamos, 2 encomendas no mês em que se juntam, então não haverá fila no resultado. É possível resolver esse problema-não é fácil resolver esse problema.

Lidar com "nenhum novo membro fez n compras"

Há provavelmente várias maneiras de obter uma linha com uma contagem zero para os itens em falta. A técnica que eu tendem a usar é criar uma tabela que contém as linhas que eu quero aparecer, algo como isto-onde eu criei tabelas temporárias para manter o resultado de cada uma das expressões nomeadas na parte principal da resposta. Esta é uma variante da tabela BaseCounts mostrada na resposta mais simples; essa versão não precisava da coluna NumNewMembers enquanto que esta versão precisa. 
CREATE TEMP TABLE BaseCounts
(
    NumOrders CHAR(2) NOT NULL,
    NumNewMembers DECIMAL(15,0) NOT NULL
);

INSERT INTO BaseCounts VALUES("0 ", 0);
INSERT INTO BaseCounts VALUES("1 ", 0);
INSERT INTO BaseCounts VALUES("2 ", 0);
INSERT INTO BaseCounts VALUES("3 ", 0);
INSERT INTO BaseCounts VALUES("4 ", 0);
INSERT INTO BaseCounts VALUES("5+", 0);

SELECT NumOrders, MAX(NumNewMembers) AS NumNewMembers
  FROM (SELECT * FROM BaseCounts
        UNION
        SELECT NumOrders, COUNT(*) AS NumNewMembers
          FROM NMC
         GROUP BY NumOrders
        )
 GROUP BY NumOrders
 ORDER BY NumOrders;

A segunda questão na União na cláusula FROM é a resposta "final" anterior, utilizando uma tabela temporária para os resultados intermédios.

Pergunta final

Quando escrito para evitar a tabela temp, a consulta torna-se: 

SELECT NumOrders, MAX(NumNewMembers)
  FROM (SELECT * FROM BaseCounts
        UNION
        SELECT NumOrders, COUNT(*) AS NumNewMembers
          FROM (SELECT MemberID, CAST(NumOrders AS CHAR(2)) AS NumOrders
                  FROM (SELECT M.MemberID, 0 AS NumOrders
                          FROM Members AS M
                         WHERE YEAR(M.DateCreated) = 2010 AND MONTH(M.DateCreated) = 8
                           AND NOT EXISTS (SELECT * FROM Orders AS O
                                            WHERE YEAR(O.DateCreated) = 2010
                                              AND MONTH(O.DateCreated) = 8
                                              AND O.MemberID = M.MemberID
                                          )
                       ) AS WZO
                UNION
                SELECT MemberID, CASE WHEN NumOrders < 5
                                 THEN CAST(NumOrders AS CHAR(2))
                                 ELSE "5+" END AS NumOrders
                  FROM (SELECT M.MemberID, COUNT(*) AS NumOrders
                          FROM Members AS M JOIN Orders AS O ON M.MemberID = O.MemberID
                         WHERE YEAR(M.DateCreated) = 2010 AND MONTH(M.DateCreated) = 8
                           AND YEAR(O.DateCreated) = 2010 AND MONTH(O.DateCreated) = 8
                         GROUP BY M.MemberID
                       ) AS NZO
               ) AS NMC
         GROUP BY NumOrders
       )
 GROUP BY NumOrders
 ORDER BY NumOrders;

Ao usar o conjunto de dados modificado, obtenho o resultado: 

NumOrders    NumNewMembers
CHAR(2)      DECIMAL(15,0)
0            1
1            1
2            1
3            0
4            2
5+           2

Alguns DBMS fornecem outras formas, possivelmente mais convenientes, de criar valores de tabela como a tabela BaseCounts.

Uma técnica alternativa que pode ser considerada é algum tipo de junção externa usando 'COUNT(column)' em vez de 'COUNT(*)'. Quando você usa ' COUNT (column)', a consulta só conta as linhas com um valor não nulo para 'column', de modo que uma junção externa que gera um null em 'column' dará 'COUNT (column)' de zero para o null. No entanto, você ainda precisa de uma lista de referência de algum lugar das linhas que devem aparecer na saída para que você possa determinar quando algo está faltando do conjunto de dados. Isto é fornecido pela tabela BaseCounts na minha exposição.

Com cláusula

Também, como observado no topo, a norma SQL e alguns DBMS fornecem uma cláusula que permite criar resultados intermédios nomeados que podem então ser usados em a pergunta final (ou, na verdade, mais tarde na Cláusula com): 

WITH <name1> AS (<query1>),
     <name2>(<named-columns>) AS (<query2>),
     ...
SELECT ... FROM <name1> JOIN <name2> ON ...

Usando isto, poderíamos escrever o seguinte (não testado) SQL: 

WITH NZO AS (
        SELECT M.MemberID, COUNT(*) AS NumOrders
          FROM Members AS M JOIN Orders AS O ON M.MemberID = O.MemberID
         WHERE YEAR(M.DateCreated) = 2010 AND MONTH(M.DateCreated) = 8
           AND YEAR(O.DateCreated) = 2010 AND MONTH(O.DateCreated) = 8
         GROUP BY M.MemberID),
     WZO AS (
        SELECT M.MemberID, 0 AS NumOrders
          FROM Members AS M
         WHERE YEAR(M.DateCreated) = 2010 AND MONTH(M.DateCreated) = 8
           AND NOT EXISTS (SELECT * FROM Orders AS O
                            WHERE YEAR(O.DateCreated) = 2010
                              AND MONTH(O.DateCreated) = 8
                              AND O.MemberID = M.MemberID
                          )),
     NMC AS (
        SELECT MemberID, CAST(NumOrders AS CHAR(2))
          FROM WZO
        UNION
        SELECT MemberID, CASE WHEN NumOrders < 5
                         THEN CAST(NumOrders AS CHAR(2))
                         ELSE "5+" END AS NumOrders
          FROM NZO),
     NZC AS (
        SELECT NumOrders, COUNT(*) AS NumNewMembers
          FROM NMC
         GROUP BY NumOrders)
SELECT NumOrders, MAX(NumNewMembers)
  FROM (SELECT * FROM NZC
        UNION
        SELECT * FROM BaseCounts
       )
 GROUP BY NumOrders
 ORDER BY NumOrders;

Dados Da Amostra

Tabelas 

CREATE TABLE Members
(
    MemberID INTEGER NOT NULL PRIMARY KEY,
    DateCreated DATE NOT NULL
);

CREATE TABLE Orders
(
    OrderID INTEGER NOT NULL PRIMARY KEY,
    DateCreated DATE NOT NULL,
    MemberID INTEGER NOT NULL REFERENCES Members
);

Membros 

INSERT INTO Members VALUES(1, '2009-08-03');
INSERT INTO Members VALUES(2, '2010-08-03');
INSERT INTO Members VALUES(3, '2010-08-05');
INSERT INTO Members VALUES(4, '2010-08-13');
INSERT INTO Members VALUES(5, '2010-08-15');
INSERT INTO Members VALUES(6, '2010-08-23');
INSERT INTO Members VALUES(7, '2010-08-23');
INSERT INTO Members VALUES(8, '2010-08-23');
INSERT INTO Members VALUES(9, '2010-09-03');

Ordens 

INSERT INTO Orders VALUES(11, '2010-08-03', 1);

INSERT INTO Orders VALUES(33, '2010-08-03', 3);

INSERT INTO Orders VALUES(44, '2010-08-05', 4);
INSERT INTO Orders VALUES(45, '2010-08-06', 4);

INSERT INTO Orders VALUES(56, '2010-08-11', 5);
INSERT INTO Orders VALUES(57, '2010-08-13', 5);
INSERT INTO Orders VALUES(58, '2010-08-23', 5);
--For testing 0 members with 3 orders (and 2 with 4 orders), add:
--INSERT INTO Orders VALUES(51, '2010-08-09', 5);

INSERT INTO Orders VALUES(61, '2010-08-05', 6);
INSERT INTO Orders VALUES(62, '2010-08-15', 6);
INSERT INTO Orders VALUES(63, '2010-08-15', 6);
INSERT INTO Orders VALUES(64, '2010-08-25', 6);

INSERT INTO Orders VALUES(71, '2010-08-03', 7);
INSERT INTO Orders VALUES(72, '2010-08-03', 7);
INSERT INTO Orders VALUES(73, '2010-08-03', 7);
INSERT INTO Orders VALUES(74, '2010-08-03', 7);
INSERT INTO Orders VALUES(75, '2010-08-03', 7);

INSERT INTO Orders VALUES(81, '2010-08-03', 8);
INSERT INTO Orders VALUES(82, '2010-08-03', 8);
INSERT INTO Orders VALUES(83, '2010-08-03', 8);
INSERT INTO Orders VALUES(84, '2010-08-03', 8);
INSERT INTO Orders VALUES(85, '2010-08-03', 8);
INSERT INTO Orders VALUES(86, '2010-08-03', 8);

INSERT INTO Orders VALUES(91, '2010-09-03', 9);
 4
Author: Jonathan Leffler, 2010-10-21 23:42:55