Самый быстрый способ подсчета отличительных значений в столбце, включая значения NULL

Операция Transact-Sql Count Distinct подсчитывает все ненулевые значения в столбце. Мне нужно подсчитать количество различных значений для каждого столбца в наборе таблиц, включая нулевые значения (поэтому, если в столбце есть нуль, результат должен быть (Select Count(Distinct COLNAME) From TABLE) + 1.

Это будет повторяться над каждым столбцом в каждой таблице в БД. Включает сотни таблиц, некоторые из которых имеют более 1 М строк. Поскольку это нужно делать для каждого столбца, добавление индексов для каждого столбца не является хорошим вариантом.

Это будет сделано как часть сайта ASP.net, поэтому интеграция с логикой кода также будет нормально (т.е. это не должно быть выполнено как часть одного запроса, хотя, если это можно сделать с хорошей производительностью, то еще лучше).

Каков наиболее эффективный способ сделать это?

Обновление после тестирования

Я тестировал различные методы из ответов, приведенных на хорошей представительской таблице. Таблица имеет 3,2 миллиона записей, десятки столбцов (несколько с индексами, большинство из них). Один столбец имеет 3,2 миллиона уникальных значений. Другие столбцы варьируются от всех Null (одно значение) до максимального значения 40K уникальных значений. Для каждого метода я выполнил четыре теста (с несколькими попытками в каждом, усредняя результаты): одновременно 20 столбцов, 5 столбцов за один раз, 1 столбец со многими значениями (3.2M) и 1 столбец с небольшим количеством значений ( 167). Вот результаты, в порядке наиболее быстрого и медленного

Результаты тестирования (в секундах):

   Method          20_Columns   5_Columns   1_Column (Large)   1_Column (Small)
1) Count/GroupBy      10.8          4.8            2.8               0.14       
2) CountDistinct      12.4          4.8            3                 0.7         
3) dense_rank        226           30              6                 4.33 
4) Count+Max          98.5         44             16                12.5

Примечания:

Интересно, что два метода, которые были самыми быстрыми (кстати, с небольшой разницей между ними) были оба метода, которые представляли отдельные запросы для каждого столбца (а в случае результата №2 запрос включал подзапрос, поэтому на столбе было действительно два запроса). Возможно, потому, что выгоды, которые будут достигнуты за счет ограничения количества сканирований таблиц, малы по сравнению с производительностью, полученной с точки зрения требований к памяти (просто догадка).
Хотя метод dense_rank определенно является самым элегантным, кажется, что он недостаточно масштабируется (см. результат для 20 столбцов, что, безусловно, является худшим из четырех методов), и даже в небольшом масштабе просто невозможно конкурировать с производительностью Count.

Спасибо за помощь и предложения!

Ответы

Ответ 1

SELECT COUNT(*)
FROM (SELECT ColumnName
      FROM TableName
      GROUP BY ColumnName) AS s;

GROUP BY выбирает различные значения, включая NULL. COUNT(*) будет включать в себя NULL, в отличие от COUNT(ColumnName), который игнорирует NULL.

Ответ 2

Я думаю, вам стоит попытаться сохранить число таблиц сканирования и подсчитать все столбцы в одной таблице за один раз. Что-то вроде этого можно было бы попробовать.

;with C as
(
  select dense_rank() over(order by Col1) as dnCol1,
         dense_rank() over(order by Col2) as dnCol2
  from YourTable
)
select max(dnCol1) as CountCol1,
       max(dnCol2) as CountCol2
from C

Проверьте запрос SE-Data

Ответ 3

Запустите один запрос, который подсчитывает количество отличительных значений и добавляет 1, если в столбце есть какие-то NULL (с использованием подзапроса)

Select Count(Distinct COLUMNNAME) +
       Case When Exists 
                 (Select * from TABLENAME Where COLUMNNAME is Null) 
            Then 1 Else 0 End
From TABLENAME

Ответ 4

Разработка собственного решения OP:

SELECT
  COUNT(DISTINCT acolumn) + MAX(CASE WHEN acolumn IS NULL THEN 1 ELSE 0 END)
FROM atable

Ответ 5

Вы можете попробовать:

count(
distinct coalesce(
    your_table.column_1, your_table.column_2
    -- cast them if you want replace value from column are not same type
    )
) as COUNT_TEST

Функция coalesce поможет вам объединить два столбца с заменой не нулевыми значениями.

Я использовал это в моем случае и успех с правильным результатом.

Ответ 6

Не уверен, что это будет самый быстрый, но может стоить тестирования. Используйте случай, чтобы дать значение null. Ясно, что вам нужно будет выбрать значение null, которое не было бы в реальных данных. Согласно плану запроса это было бы мертвым теплом с решением count (*) (group by), предложенным Cheran S.

    SELECT 
        COUNT( distinct
                 (case when [testNull] is null then 'dbNullValue' else [testNull] end)
             )
    FROM [test].[dbo].[testNullVal]

При таком подходе можно также подсчитать более одного столбца

    SELECT 
        COUNT( distinct
                 (case when [testNull1] is null then 'dbNullValue' else [testNull1] end)
             ),
        COUNT( distinct
                 (case when [testNull2] is null then 'dbNullValue' else [testNull2] end)
             )
    FROM [test].[dbo].[testNullVal]