Início / dba / Perguntas / 301002
Accepted
JJM50
Asked: 2021-10-13 04:39:48 +0800 CST

Consulta para obter a soma de todas as entradas exclusivas em uma coluna

  • 772

Atualmente estou usando mysql para retornar a soma de todos os dados em uma coluna que tem a mesma palavra. Para isso estou usando a seguinte consulta:

SELECT SUM(CASE WHEN status_to = 'Draft' THEN 1 END) AS draft, 
SUM(CASE WHEN status_to = 'Unpublish' THEN 1 END) AS unpublish, 
SUM(CASE WHEN status_to = 'Publish' THEN 1 END) AS publish, 
SUM(CASE WHEN status_to = 'Action' THEN 1 END) AS action, 
SUM(CASE WHEN status_to = 'Unlisted' THEN 1 END) AS unlisted, 
SUM(CASE WHEN status_to = 'Sold' THEN 1 END) AS sold, 
SUM(CASE WHEN status_to = 'Let' THEN 1 END) AS let 
FROM `crm_logs`

Isso fornece a saída correta no meu banco de dados para todos os termos que especifiquei, mas agora descobri que há mais variáveis ​​de status no banco de dados além das especificadas acima. Então eu quero uma forma de ter a mesma funcionalidade dessa declaração, mas tornar a variável de status dinâmica.

Basicamente se esta for a consulta SUM(CASE WHEN status_to = 'Draft' THEN 1 END) AS draft, ambas as ocorrências de rascunho devem ser dinâmicas.

mysql case

3 respostas

  1. Escreva um procedimento armazenado que construa a consulta usando CONCAT()e a execute via PREPAREe EXECUTE.

    Ou construa a consulta em sua linguagem de aplicativo usando suas funções de string.

    Quanto à discussão na resposta do wiki , adiciono isso

    COALESCE(SUM(status_to = 'Draft'), 0)

    Mas isso só é necessário se status_topuder ser NULL. Caso contrário, isso funciona:

    SUM(status_to = 'Draft')
    • 2
  2. Best Answer

    Há uma maneira muito mais fácil de fazer o que você quer - eu estava pensando nisso ontem à noite - veja meu segundo violino aqui .

    Você pode fazer algo como o seguinte (todo o código abaixo está disponível no violino aqui ):

    CREATE TABLE test
(
  t_id INTEGER NOT NULL PRIMARY KEY,
  status_to VARCHAR (50)
);

    Em seguida, preencha-o:

    INSERT INTO test VALUES
(7, NULL), (8, 'Test_val_1'), (9, 'Test_val_2'), 
 (3, 'Publish'), (4,  'Action'),  (5, 'Sold'),  (6, 'Let'), (10, 'Draft'), (11, 'Unpublish'), 
(12, 'Publish'), (13, 'Action'), (14, 'Sold'), (15, 'Let'), (26, 'Draft'), (16, 'Unpublish'),
(17, 'Publish'), (18, 'Action'), (19, 'Sold'), (20, 'Let'), (27, 'Draft'), (21, 'Unpublish'),
(22, 'Publish'), (23, 'Action'), (24, 'Sold'), (25, 'Let'), (1,  'Draft'),  (2, 'Unpublish'),
(29, NULL), (30, NULL), (35, 'Test_val_1'), (31, 'Test_val_2'),
(45, 'Single_1'), (46, 'Single_2');

    Observe que adicionei 2 valores singleton ( Single_1e Single_2) para examinar como alguém, por exemplo, contaria os DISTINCTvalores que tinham mais de 1 registro em sua tabela ... é muito mais difícil com SQL "clássico" - com funções de janela, é muito fácil.

    A nova consulta "simples" é:

    SELECT
  COUNT(DISTINCT(status_to)) AS "Unique count"
FROM
  test;

    (Novo) resultado:

    Unique count
          10

    Observe a extrema diferença nos resultados EXPLAIN ANALYZEna parte inferior do violino para esta nova consulta e minha antiga!

    Esta consulta demonstra como você pode facilmente escolher os DISINCTvalores status_toque têm mais de um registro:

    SELECT COUNT(rn) OVER (PARTITION BY rn) AS "Unique count"
FROM
(
  SELECT
    t_id, status_to, 
    ROW_NUMBER() OVER (PARTITION BY status_to ORDER BY status_to) AS rn
  FROM test
  WHERE status_to IS NOT NULL
  ORDER BY status_to, t_id
) AS tab
WHERE rn > 1
GROUP BY status_to
ORDER BY status_to
LIMIT 1;

    Resultado:

    Unique count
           8

    8 = 10 (total) menos os 2 singletons!

    Também incluí este SQL - que permite contar facilmente (manualmente) o número de UNIQUEentradas.

    SELECT status_to, MAX(rn)
FROM
(
  SELECT
    t_id, status_to, 
    ROW_NUMBER() OVER (PARTITION BY status_to ORDER BY status_to) AS rn
  FROM test
  WHERE status_to IS NOT NULL
  ORDER BY status_to, t_id
) AS tab
WHERE rn > 1        <<==== ****
GROUP BY status_to
ORDER BY status_to;

    Resultado:

    status_to    MAX(rn)
  Action          4
  Draft           4
  Let             4
  Publish         4
  Sold            4
  Test_val_1      2
  Test_val_2      2
  Unpublish       4
8 rows              <<---- note 8 rows!

    Deixei mais alguns trechos no segundo violino - se você realmente quiser aprender sobre SQL, convido você a ver tanto o antigo quanto o novo e também tentar executar o código usando o PostgreSQL - é interessante!

    Finalmente, se você deseja construir dinamicamente uma linha (conforme sua pergunta original) com todos os seus DISTINCTvalores e seus COUNT()s, então você pode usar alguma forma de RECURSIVE CTE- mas isso é para outra pergunta!

    • 2

  3. jkavalik : Você deve fazer:

    select status_to, count(1) 
from crm_logs 
group by status_to

    Tem dados em linhas em vez de colunas, mas isso é um detalhe. Se você realmente precisar, isso também pode ser resolvido com SQL dinâmico, mas o group by ainda é melhor como uma consulta base.

    Akina : No MySQL você pode usar não SUM(CASE WHEN status_to = 'Draft' THEN 1 END)mas mais simples (e claro) SUM(status_to = 'Draft').

    Além disso, essa expressão produzirá zero em vez de NULL se nenhuma linha corresponder.

    count(status_to = 'Draft') contaria todos os valores não nulos , independentemente do valor. SUM(status_to = 'Draft')produzirá null somente quando nenhum status_tovalor não nulo estiver presente.

    Lennart : count(nullif(status_to = 'Draft',0)) lidaria com ambas as situações.

    • 0

relate perguntas