Contagem e sub-contagem na mesma pesquisa
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
2 answers
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
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);