ti-enxame.com

Contando valores nulos como valor exclusivo

Preciso contar valores diferentes em uma coluna, como:

Hours
1
1
2
null
null
null

O resultado deve ser: . Minha consulta é:

select count(distinct hour) from hours;

mas retorna: 2. Testei também:

select count(*) from hours group by hour

mas retorna três linhas:

(1) 3
(2) 2
(3) 1

Como posso contar valores nulos como 1 valor e usar distintos para evitar contar valores repetidos?

Estou aprendendo SQL avançado, eles querem esses requisitos para todas as soluções:

Tente minimizar o número de subconsultas necessárias para resolver a consulta. Além disso, você não tem permissão para usar as seguintes construções:

  • SELECT no FROM ou SELECT. Você tem permissão para ter subconsultas (SELECT no WHERE ou HAVING)
  • Combinações de funções de agregação, como COUNT (COUNT. ..)), SUM (COUNT. ..)) e similares.
  • UNIÃO se você puder evitá-lo.
  • Funções não padrão (como NVL)
  • CASO
18
David
select  count(distinct col1) + count(distinct case when col1 is null then 1 end)
from    YourTable
23
Andomar

se hour for um número, se ele puder ser apenas um número inteiro:

select count(distinct coalesce(hour, 0.1)) cnt from test;

caso contrário, se puder ser qualquer ponto flutuante, altere NULL para uma string de caracteres.

por exemplo

select count(distinct coalesce(to_char(hour), 'a')) cnt from test;
17
user1788325
select 
   count(0) 
from
  (
      select distinct hour from hours
  )

SqlFiddle

8
Egor Skriptunoff
SELECT
      ( SELECT COUNT(DISTINCT hour)
        FROM hours
      )
    + CASE WHEN EXISTS
           ( SELECT *
             FROM hours
             WHERE hour IS NULL
           )
        THEN 1 
        ELSE 0
      END
   AS result
FROM dual ;
2
ypercubeᵀᴹ

talvez

    select count(distinct hour||' ') from hours;

vai fazer?

2
Andres
select count(distinct nvl(hour,0)) from hours;
2
Giampaolo

Eu diria que seus requisitos são bastante bizarros, já que você tem quase certeza de obter uma consulta mais eficiente simplesmente usando NVL(), COALESCE() ou CASE. No entanto, consegui obter o resultado certo (e lidar com a presença ou ausência de NULL values) usando apenas subconsultas. Eu não consegui fazer isso sem usar uma subconsulta na cláusula FROM ainda.

SQL Fiddle

Consulta 1 :

SELECT nnh.not_null_hours + nh.null_hours
FROM (
  SELECT COUNT(DISTINCT t.hour) not_null_hours
  FROM example_table t
) nnh
CROSS JOIN (
  SELECT 1 null_hours
  FROM dual
  WHERE EXISTS (
    SELECT 1
    FROM example_table t
    WHERE t.hour IS NULL
  )
  UNION ALL
  SELECT 0 null_hours
  FROM dual
  WHERE NOT EXISTS (
    SELECT 1
    FROM example_table t
    WHERE t.hour IS NULL
  )
) nh

Resultados:

| NNH.NOT_NULL_HOURS+NH.NULL_HOURS |
------------------------------------
|                                3 |

Isso vai exigir muito esforço para lidar com os requisitos. Uma opção muito mais simples é usar NVL e, em seguida, uma das duas opções simples ...:

  1. Usar TO_CHAR para converter os valores não NULL no tipo de dados VARCHAR2 e NVL para converter NULL valores no VARCHAR2 'NULL' ou
  2. Basta usar NVL com um número mágico que você sabe que nunca estará presente no conjunto de resultados (ou seja, devido a restrições na tabela).

Consulta 1:

SELECT 
  COUNT(DISTINCT NVL(TO_CHAR(hour), 'NULL')) using_to_char_null
, COUNT(DISTINCT NVL(hour, -1)) using_magic_number
FROM example_table

Resultados:

| USING_TO_CHAR_NULL | USING_MAGIC_NUMBER |
-------------------------------------------
|                  3 |                  3 |
1
Ben

A resposta de Andres é a que atende aos requisitos perfeitamente e sem usar nenhuma função além de COUNT:

select count(distinct hour||' ') from hours;

eu estava procurando a mesma coisa para outro propósito (eu poderia usar qualquer coisa), mas não me pareceu correto ou eficiente até que eu vi esse, obrigado Andrés, uma solução tão simples e poderosa.

1
arana

O mais próximo que pude ajustar os critérios especificados é este: ( SQL Fiddle )

Consulta 1 :

SELECT COUNT(*)
FROM example_table t1
WHERE t1.ROWID IN (
  SELECT MAX(t2.ROWID)
  FROM example_table t2
  GROUP BY t2.hour
)

Resultados:

| COUNT(*) |
------------
|        3 |

Não tenho certeza se a pseudocoluna ROWID é permitida, dadas as outras restrições, mas funciona e manipula normalmente NULL valores. Eu não acho que o ROWID exista fora do Oracle, então isso provavelmente vai contra o espírito da pergunta, mas se encaixa nos critérios listados pelo menos.

0
Ben

Provavelmente, a maneira mais fácil é usar DUMP:

SELECT COUNT(DISTINCT DUMP(hour)) AS distinct_count
FROM hours;

Saída: 3

Demonstração do DBFiddle

0
Lukasz Szozda