Distinct count: SQL COUNT() with DISTINCT — w3resource

Функция DISTINCTCOUNT (DAX) — DAX

• 
  01/06/2021
• Чтение занимает 2 мин

В этой статье

Подсчитывает количество уникальных значений в столбце.Counts the number of distinct values in a column.

СинтаксисSyntax

DISTINCTCOUNT(<column>)  

ПараметрыParameters

Возвращаемое значениеReturn value

Количество уникальных значений в column.The number of distinct values in column.

RemarksRemarks

• Единственным аргументом, допустимым для этой функции, является столбец.The only argument allowed to this function is a column. Можно использовать столбцы, содержащие данные любого типа.You can use columns containing any type of data. Если функция не находит строки для подсчета, она возвращает значение BLANK, в противном случае возвращается число уникальных значений.When the function finds no rows to count, it returns a BLANK, otherwise it returns the count of distinct values.

• Функция DISTINCTCOUNT включает значение BLANK.DISTINCTCOUNT function includes the BLANK value. Чтобы пропустить значение BLANK, используйте функцию DISTINCTCOUNTNOBLANK.To skip the BLANK value, use the DISTINCTCOUNTNOBLANK function.

• Эта функция не поддерживается для использования в режиме DirectQuery при использовании в вычисляемых столбцах или правилах безопасности на уровне строк (RLS).This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

ПримерExample

В приведенном ниже примере показано, как подсчитать количество уникальных заказов на продажу в столбце ResellerSales_USD[SalesOrderNumber].The following example shows how to count the number of distinct sales orders in the column ResellerSales_USD[SalesOrderNumber].

= DISTINCTCOUNT(ResellerSales_USD[SalesOrderNumber])  

Использование приведенной выше меры в таблице с календарным годом в боковой и верхней категориях продукции возвращает следующие результаты.Using the above measure in a table with calendar year in the side and product category on top returns the following results:

В приведенном выше примере видно несоответствие чисел общего итога строк. Это происходит потому, что один порядок может содержать линейные элементы в том же порядке из разных категорий продуктов. In the above example, note that the rows Grand Total numbers do not add up, this happens because the same order might contain line items, in the same order, from different product categories.

См. такжеSee also

Функция COUNTCOUNT function
Функция COUNTACOUNTA function
COUNTAX, функцияCOUNTAX function
COUNTX, функцияCOUNTX function
Статистические функцииStatistical functions

count | Документация ClickHouse

1. Справка по SQL
2. Агрегатные функции
3. Справочник

Вычисляет количество строк или не NULL значений.

ClickHouse поддерживает следующие виды синтаксиса для count:

• count(expr) или COUNT(DISTINCT expr).
• count() или COUNT(*). Синтаксис count() специфичен для ClickHouse.

Аргументы

Функция может принимать:

Возвращаемое значение

• Если функция вызывается без параметров, она вычисляет количество строк.
• Если передаётся выражение, то функция подсчитывает количество раз, когда выражение не равно NULL. Если выражение имеет тип Nullable, то результат count не становится Nullable. Функция возвращает 0, если выражение равно NULL для всех строк.

В обоих случаях тип возвращаемого значения UInt64.

Подробности

ClickHouse поддерживает синтаксис COUNT(DISTINCT ...). Поведение этой конструкции зависит от настройки count_distinct_implementation. Она определяет, какая из функций uniq* используется для выполнения операции. По умолчанию — функция uniqExact.

Запрос SELECT count() FROM table не оптимизирован, поскольку количество записей в таблице не хранится отдельно. Он выбирает небольшой столбец из таблицы и подсчитывает количество значений в нём.

Примеры

Пример 1:

┌─count()─┐
│       5 │
└─────────┘

Пример 2:

SELECT name, value FROM system. settings WHERE name = 'count_distinct_implementation'

┌─name──────────────────────────┬─value─────┐
│ count_distinct_implementation │ uniqExact │
└───────────────────────────────┴───────────┘

SELECT count(DISTINCT num) FROM t

┌─uniqExact(num)─┐
│              3 │
└────────────────┘

Этот пример показывает, что count(DISTINCT num) выполняется с помощью функции uniqExact в соответствии со значением настройки count_distinct_implementation.

SQL count (*) и distinct

Почему мы не можем использовать count(distinct *) в SQL? Как считать все отдельные строки?

sql

Поделиться

Источник

Nitish Upreti    

01 декабря 2009 в 13:13

9 ответов

• DISTINCT COUNT возвращает ошибку SQL

  Почему следующий запрос SQL возвращает ошибку? SELECT COUNT (DISTINCT rm.id) FROM ts_room rm LEFT JOIN ts_roomfacilities rf ON rm.id = rf.room_id LEFT JOIN ts_facilities f ON f.id = rf.facilities_id LEFT JOIN ts_building b ON rm.building_id=b.id; На первый взгляд это кажется логичным! Вот мой…

• Использование Distinct и Count

  Помогите мне, пожалуйста, как использовать ключевые слова Distinct и Count вместе. Использовать Distinct на columnA и Count на columnB .

35

select count(*) from (select distinct * from MyTable) as T

Хотя я настоятельно рекомендую вам переосмыслить любые запросы, использующие DISTINCT . В большом проценте случаев GROUP BY является более подходящим (и более быстрым).

EDIT: прочитав комментарии к вопросу, я должен отметить, что вы никогда не должны просить DBMS сделать больше работы, чем на самом деле нужно сделать, чтобы получить результат. Если вы заранее знаете, что в таблице не будет дублированных строк, то не используйте DISTINCT .

Поделиться

Christian Hayter    

01 декабря 2009 в 13:18

11

Вы можете выбрать все столбцы в таблице и сгруппировать их…

SELECT column1, column2, column3, count(*)
FROM someTable
GROUP BY column1, column2, column3

Поделиться

Jason Punyon    

01 декабря 2009 в 13:16

4

а почему бы и нет?

select 
  count(distinct name)
from 
  people

Поделиться

silent    

01 декабря 2009 в 13:16

• SQL синтаксическая ошибка (COUNT (DISTINCT .. ))

  Я пытаюсь использовать COUNT (DISTINCT .. )) в моем синтаксисе SQL, это мой синтаксис SQL: SELECT COUNT (DISTINCT ID) FROM teaches WHERE semester = ‘Spring’ AND year = 2010; Но этот синтаксис не работает, в чем проблема? Это сообщение об ошибке: ERROR 1064 (42000): You have an error in your SQL…

• count(distinct (column))

  Я могу успешно запустить этот sql: select count(distinct(BusinessEntityID)) from Person.Person Но что, если я хочу знать это, дайте мне ошибку: select count(distinct(*)) from Person.Person Как я могу запустить equliviant sql ?

1

Вы действительно можете.

Однако, если у вас есть идентификатор, у вас не будет совершенно разных строк. Но вы могли бы сделать например:

SELECT COUNT(DISTINCT SenderID) FROM Messages

Поделиться

David Hedlund    

01 декабря 2009 в 13:16

0

Вы можете попробовать CTE в Sql Server 2005

;WITH cte AS (
        SELECT  DISTINCT Val1,Val2, Val3
        FROM    @Table
)
SELECT  COUNT(1)
FROM    cte

Чтобы ответить на этот вопрос, из документации

Указывает, что все строки должны быть
подсчитаны, чтобы вернуть общее количество
строк в таблице. COUNT( ) не принимает никаких
параметров и не может быть использован с
DISTINCT. COUNT( ) не требует
параметра выражения, поскольку по
определению он не использует
информацию о каком-либо конкретном
столбце. COUNT (*) возвращает количество
строк в указанной таблице, не
избавляясь от дубликатов. Это считается
каждый ряд отдельно. Сюда входят
строки, содержащие значения null.

Поделиться

Adriaan Stander    

01 декабря 2009 в 13:20

0

COUNT (*) — это количество строк, соответствующих запросу.

Строка содержит уникальную информацию, такую как rowid. Все строки по определению различны.

Вместо этого вы должны подсчитать отдельные экземпляры значений в некотором поле.

Поделиться

Will    

01 декабря 2009 в 13:21

0

некоторые языки могут быть не в состоянии обрабатывать «distinct *», поэтому, если вы хотите, чтобы различие проводилось через множество столбцов , вы можете использовать «distinct ColumnA || ColumnB», комбинируя значения, прежде чем судить, отличаются ли они. Будьте внимательны, являются ли ваши переменные числовыми, и ваш обработчик базы данных может автоматически типизировать символьные строки.

Поделиться

Jose Antonio Padros    

01 декабря 2009 в 13:29

-2

UberKludge, и может быть постгреспецифичным, но

select count( distinct table::text ) from table

Поделиться

richo    

01 декабря 2009 в 13:19

-2

select count (Tag_no) from tab_raw_tag_value where tag_no in (select distinct tag_no from tab_raw_tag_value)

Поделиться

May    

20 августа 2012 в 07:37

Похожие вопросы:

От LINQ до SQL с использованием GROUP BY и COUNT(DISTINCT)

Я должен выполнить следующий запрос SQL: select answer_nbr, count(distinct user_nbr) from tpoll_answer where poll_nbr = 16 group by answer_nbr Запрос от LINQ до SQL from a in tpoll_answer where. ..

Как написать distinct и count в запросе linq?

У меня есть SQL, который я пытаюсь преобразовать в Linq выражения в c#. Не могли бы вы помочь? if (String.IsNullOrEmpty(alpha)) sql = select distinct(Keyword) as word, count(*) as Counter from…

Django ORM версия SQL COUNT (DISTINCT <column> )

Мне нужно заполнить шаблон с краткой информацией об активности пользователей в простой системе обмена сообщениями. Для каждого отправителя сообщения мне нужно количество отправленных сообщений и…

DISTINCT COUNT возвращает ошибку SQL

Почему следующий запрос SQL возвращает ошибку? SELECT COUNT (DISTINCT rm.id) FROM ts_room rm LEFT JOIN ts_roomfacilities rf ON rm.id = rf.room_id LEFT JOIN ts_facilities f ON f.id = rf.facilities_id…

Использование Distinct и Count

Помогите мне, пожалуйста, как использовать ключевые слова Distinct и Count вместе. Использовать Distinct на columnA и Count на columnB .

SQL синтаксическая ошибка (COUNT (DISTINCT .. ))

Я пытаюсь использовать COUNT (DISTINCT .. )) в моем синтаксисе SQL, это мой синтаксис SQL: SELECT COUNT (DISTINCT ID) FROM teaches WHERE semester = ‘Spring’ AND year = 2010; Но этот синтаксис не…

count(distinct (column))

Я могу успешно запустить этот sql: select count(distinct(BusinessEntityID)) from Person.Person Но что, если я хочу знать это, дайте мне ошибку: select count(distinct(*)) from Person.Person Как я…

Использование COUNT(Distinct ) в SQL

Вот схема базы данных: customers (custID, firstname, familyname, town, state) orders (orderID, custlD, date) lineitems (orderID, itemlD, quantity, despatched) items (itemID, description, unitcost,…

count и distinct count без groupby с использованием PySpark

У меня есть dataframe ( testdf ), и я хотел бы получить count и distinct count для столбца ( memid ), где другой столбец ( booking / rental ) не является null или не пустым (т. е.) testdf : memid…

Как получить COUNT DISTINCT в translated SQL с помощью EF Core

Я хочу, чтобы EF core перевел .Select(x=>x.property).Distinct().Count() во что-то вроде SELECT COUNT(DISTINCT property) Давайте возьмем пример. Допустим, у меня есть таблица DB с PersonID(long),…

Highload.today — медиа для разработчиков

Highload —

6 часов назад
Editorial

Лучшие автономные CMS с открытым исходным кодом

Технический писатель Шаника Викрамасингхе написала в своем блоге на dzone.com о лучших Headless CMS с открытым исходным кодом. При составлении списка автор сосредоточилась на функциях, предлагаемых каждым вариантом и различиях между ними.

Highload —

9 часов назад
Editorial

Как избежать применения ORM для Go, используя чистый SQL

Если вы — инженер-программист, который опробовал множество различных языков и фреймворков, то, скорее всего, вы сталкивались с мучительной необходимостью изучать новый синтаксис ORM для каждого отдельного языка. Это большая помеха, которая либо замедлит скорость вашей работы, либо вообще лишит желания продолжать ее.

Софт —

1 день назад
Editorial

5 эффективных инструментов отладки кода на Python

Преподаватель компьютерных наук Сара Метволли поделилась пятью, по ее мнению, наиболее эффективными инструментами отладки кода на Python. Она предлагает способы отладки кода без использования команды print — метода, который, как она говорит, занимает много времени и актуален, только если код состоит максимум из нескольких сотен строк.

Фронтенд —

2 дня назад
Editorial

Практическое применение рекурсии в JavaScript

Игорь Быков, Frontend Developer в Opticks Security, написал практический гайд по применению рекурсии в JavaScript, без большого O, без чисел Фибоначчи и других скучных академических примеров. Приводим отрывок из его статьи, который демонстрирует как использовать рекурсию при работе с многоуровневыми массивами.

Истории —

3 дня назад
Editorial

Как open source и футуризм помогли Microsoft перестать быть «империей зла». Часть первая

Корпорация Microsoft с момента основания в 1975 году прошла долгий путь от студенческого стартапа с горящими глазами до ненавидимого всеми монополиста. На пике своего могущества в 1990-е и 2000-е Microsoft была известна как сторонница проприетарного программного обеспечения (ПО) и закрытого кода, из-за чего за ней прочно закрепился ярлык «империи зла» или «корпорации зла».

Базы данных —

4 дня назад

Redis: большое потребление RAM, и при чем тут TTL?

Мы в Jooble активно используем Redis как кеш и быструю базу данных. У нас два master-slave-кластера, которые выполняют в среднем 12 000 операций в секунду. А дальше — история о том, как наш кластер стал падать по «out of memory», и о сложном поиске причины.

Софт —

1 неделя назад

Как автоматизировать копирование файлов

На многих проектах есть необходимость копирования огромных файлов — размером 0,5 Гб и более. Например, это может понадобиться для получения последней версии сборки разрабатываемого продукта. В процессе копирования могут возникать сбои или обрывы коннекта, и тогда копирование приходится начинать снова. Хочу поделиться с вами простым скриптом для Windows, который поможет автоматизировать этот процесс, сделать его проще, быстрее и надежнее.

Тестирование —

1 неделя назад
Editorial

Тестирование фронтенда на примере React-приложения

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

Фронтенд —

2 недели назад
Editorial

NPM CLI v7 — пять самых важных фич

В феврале 2021 года стал широко доступен апдейт NPM CLI до версии 7. Это одно из важнейших обновлений, в котором был представлен новый функционал и другие важные настройки, упрощающие процесс управления пакетами. Команда разработчиков смогла решить некоторые из распространенных проблем, с которыми сталкивались пользователи. Подробным обзором пяти новых возможностей NPM CLI v.7 поделился разработчик Сахан Амарша (Sahan Amarsha) в материале для Bits and Pieces. Ну а мы в свою очередь спешим поделиться его выводами с вами.

Фронтенд —

2 недели назад
Editorial

Tailwind CSS: за и против

По данным опроса The State of CSS 2020, больше всего разработчиков в мире, использующих CSS-фреймворки, сейчас заинтересованы в изучении и применении Tailwind CSS. Он опережает конкурентов в этом рейтинге уже второй год подряд. Команда Tailwind предлагает альтернативный подход для поддержки и стилизации HTML-разметки, но у него есть и свои противники. Проштудировав статьи и комментарии на таких ресурсах, как Dev.to, Product Hunt и Codeburst, мы собрали наиболее популярные доводы за и против использования этого фреймворка.

Софт —

2 недели назад
Editorial

На каком ПО летают на Марсе?

18 февраля состоялось историческое событие, НАСА успешно посадила ровер Perseverance (в переводе на русский — “Настойчивость”) на Марс. Казалось бы, ничего необычного? НАСА проделывала это уже не раз в поиске ответа на вечный вопрос: есть ли жизнь на Марсе?

Мобильные приложения —

2 недели назад
Editorial

Как сделать из веб-сайта веб-приложение (а главное — зачем?)

В своем блоге индийский разработчик Шашват Верма (Shashwat Verma) рассказал, как преобразовать веб-сайт или веб-страницу в прогрессивное веб-приложение (PWA). В качестве примера программист создал PWA из простой классической игры Simon на основе HTML5 (вот ее гитхаб-репозиторий), которую после преобразования можно устанавливать в формате веб-приложения на устройства Android и iOS.

полезный трюк с count() и count(distinct) / Sandbox / Habr

Хочу поделиться одним интересным решением, к которому мне удалось прийти сегодня во время оптимизации запроса поиска пользователей. В выборке необходимо было возвращать количество общих групп текущего пользователя и меня — того, кто производит поиск. Что называется total shared groups. В итоге всё поместилось в один компактный запрос без подзапросов (что очень критично, позже объясню почему) с использованием одной таблицы в FROM, без GROUP BY и HAVING.

Представим задачу более наглядно. Примем следующие таблицы:
— group (group_id)
— user (user_id)
— user_group (хранит связки user_id-group_id)

Необходимо узнать количество групп, в котором состоят пользователи user1 и user2. То есть найти пересечения между юзерами по таблице user_group.

1. Следующий запрос выдаст количество всех записей из таблицы user_group, которые принадлежат юзерам user1 и user2.
SELECT

COUNT(`group_id`)

FROM

`user_group`

WHERE

`user_id` IN (:user1,:user2)


2. А вот этот запрос выдаст количество записей из таблицы user_group, которые принадлежат юзерам user1 и user2, но с уникальным group_id:
SELECT

COUNT(DISTINCT `group_id`)

FROM

`user_group`

WHERE

`user_id` IN (:user1,:user2)


В данном случае, если нашлись такие группы, которые были привязаны сразу к двум нашим пользователям, то они будут «схлопнуты» в одну строку.

3. В итоге приходим к виду:
SELECT

COUNT(`group_id`) - COUNT(DISTINCT `group_id`)

FROM

`user_group`

WHERE

`user_id` IN (:user1,:user2)


Если отнять общее число групп двух пользователей и число групп, с учётом уникальности group_id, то в рузультате получим наше искомое total shared groups — сколько общих групп у двух пользователей.

Как ещё можно было решить задачу?
SELECT

COUNT(*)

FROM

(

SELECT

`group_id`

FROM

`user_group`

WHERE

`user_id` IN (:user1,:user2)

GROUP BY

`group_id`

HAVING

COUNT(*) > 1

) as `shared_groups`


Такой подход я встречал довольно часто. Но у него есть один существенный минус — использование подзапроса. Если :user1 или :user2 не передаются в запрос, а подставляются динамически из запроса верхнего уровня (например, какой-нибудь `user1`. `user_id`), то данный вариант выдаст ошибку.

SQL COUNT () с DISTINCT — w3resource

Последнее обновление 26 февраля 2020 г. 08:07:42 (UTC / GMT +8 часов)

Функция COUNT () с отдельным предложением

Функция SQL COUNT () с предложением DISTINCT устраняет повторяющееся появление одних и тех же данных. DISTINCT может появиться только один раз в данном операторе выбора.

Синтаксис
:

 COUNT (DISTINCT expr; [expr ...]) 

Пример:

Чтобы получить уникальное количество строк из таблицы заказов при следующих условиях —

1.будет засчитан только уникальный cust_code,

2. Результат появится с заголовком «Количество сотрудников»,

можно использовать следующий оператор SQL:

 SELECT COUNT (DISTINCT cust_code) AS "Количество сотрудников"
ОТ заказов; 

Образец таблицы: заказы

Выход:

 Количество сотрудников
-------------------
                 25
 

Изображение:

SQL COUNT () со всеми

Далее мы обсудили использование предложения ALL с функцией SQL COUNT () для подсчета только ненулевого значения для указанного столбца в аргументе.Разница между «*» (звездочка) и ALL состоит в том, что «*» также считает значение NULL, но ALL учитывает только значение NON NULL.

Пример:

Получить данные о количестве действительных «оценок» из таблицы «покупатель» с условием —

.

1. у каждого покупателя должна быть действующая оценка,

можно использовать следующий оператор SQL:

  ВЫБРАТЬ СЧЕТЧИК (ВСЕ оценки)
ОТ заказчика;  

Образец таблицы: заказчик

Выход:

 СЧЕТ (ВСЕГДА)
---------------
             25
 

org/WebPageElement/Heading»> Примечание: выходные данные указанного оператора SQL, показанного здесь, получены с помощью Oracle Database 10g Express Edition.

Вот слайд-презентация всех агрегатных функций.

Назад: COUNT Function
Next: COUNT with Group by

Функция DISTINCTCOUNT (DAX) — DAX

• 06.01.2021
• 2 минуты на чтение

В этой статье

Подсчитывает количество различных значений в столбце.

Синтаксис

  DISTINCTCOUNT (<столбец>)
  

Параметры

Возвращаемое значение

Количество различных значений в столбце .

Замечания

• Единственный допустимый аргумент этой функции — столбец.Вы можете использовать столбцы, содержащие данные любого типа. Когда функция не находит строк для подсчета, она возвращает ПУСТО, в противном случае она возвращает количество различных значений.

• Функция DISTINCTCOUNT включает значение ПУСТО. Чтобы пропустить значение BLANK, используйте функцию DISTINCTCOUNTNOBLANK.

• Эта функция не поддерживается для использования в режиме DirectQuery при использовании в вычисляемых столбцах или правилах безопасности на уровне строк (RLS).

Пример

В следующем примере показано, как подсчитать количество отдельных заказов на продажу в столбце ResellerSales_USD [SalesOrderNumber].

  = DISTINCTCOUNT (ResellerSales_USD [SalesOrderNumber])
  

Использование вышеуказанного показателя в таблице с календарным годом в стороне и категорией продукта вверху дает следующие результаты:

Обратите внимание, что в приведенном выше примере цифры общего итога в строках не суммируются, это происходит потому, что один и тот же заказ может содержать позиции в одном порядке из разных категорий продуктов.

См. Также

Функция COUNT
Функция COUNTA
Функция COUNTAX
Функция COUNTX
Статистические функции

DISTINCTCOUNT — Руководство DAX

DISTINCTCOUNT — Руководство DAX

Функции

Подсчитывает количество различных значений в столбце.

Синтаксис

DISTINCTCOUNT ()

Возвращаемые значения

Количество различных значений в ColumnName.

»2 связанных статьи
» 4 связанных функции

Примеры

- DISTINCTCOUNT подсчитывает количество различных значений в столбце.
ОПРЕДЕЛЯТЬ
    MEASURE клиента [# клиентов] = COUNTROWS (клиент)
    Клиент MEASURE [# Names] =
        DISTINCTCOUNT (клиент [имя])
    Клиент MEASURE [# Стран 1] =
        DISTINCTCOUNT (клиент [CountryRegion])
    Клиент MEASURE [# Стран 2] =
        COUNTROWS (DISTINCT (клиент [CountryRegion]))
ОЦЕНИВАТЬ
ОБОБЩЕННЫЕ КОЛОННЫ (
    Клиент [Континент],
    «# Клиентов», [# клиентов],
    "# Имена", [# Имена],
    "# Стран 1", [# стран 1],
    «# Стран 2», [# стран 2]
)
 

-
- DISTINCTCOUNT рассматривает BLANK как допустимое значение, тогда как
- DISTINCTCOUNTNOBLANK не считает пустое значение. 
-
ОПРЕДЕЛЯТЬ
    ИЗМЕРЕНИЕ клиентов [# магазинов] =
        COUNTROWS (Магазин)
    MEASURE Клиент [# менеджер] =
        DISTINCTCOUNT (магазин [региональный менеджер])
    ИЗМЕРЕНИЕ клиента [# менеджер (без поля)] =
        DISTINCTCOUNTNOBLANK (магазин [региональный менеджер])
    ИЗМЕНИТЬ клиента [# магазинов без менеджера] =
        СЧИТАТЬПУСТОТЫ (Магазин [Региональный менеджер])
ОЦЕНИВАТЬ
ОБОБЩЕННЫЕ КОЛОННЫ (
    Магазин [Континент],
    «# Магазинов», [# магазинов],
    «# Менеджер», [# Менеджер],
    "# Менеджер (без пробела)", [# Менеджер (без пробела)],
    «# Магазины без менеджера», [# Магазины без менеджера]
)
СОРТИРОВАТЬ ПО
    Магазин [Континент]
 

Статьи по теме

Узнайте больше о DISTINCTCOUNT в следующих статьях:

• Родственный отдельный счетчик

  Шаблон «Связанное количество отличий» позволяет применить расчет подсчета отличий к любому столбцу в любой таблице в модели данных.Вместо того, чтобы просто подсчитывать количество различных значений счетчика во всей таблице с использованием только функции DISTINCTCOUNT, шаблон фильтрует только те значения, которые относятся к событиям, отфильтрованным в другой таблице. »Читать дальше

• Анализ производительности DISTINCTCOUNT в DAX

  В этой статье описывается, как анализировать производительность меры DAX на основе вычисления DISTINCTCOUNT и как оценивать возможные оптимизации. »Читать дальше

Связанные функции

Прочие связанные функции:

Последнее обновление: 24 марта 2021 г. »Внести вклад» Показать авторов

Авторы: Альберто Феррари, Марко Руссо

Документация Microsoft: https: // docs.microsoft.com/en-us/dax/distinctcount-function-dax

Совместимость

DirectQuery

Меры

Расчетные столбцы

• Power BI

  v13.0.1700.620 →
  v15.1.79.26

  Текущая версия

• Excel

  v11.0.9165.1186 →
  v13.0.1700.1054

• Табличные SSAS

  v11.0.2100.60 →
  v15.1.81.17

  • SSAS 2012
  • SSAS 2014
  • SSAS 2016
  • SSAS 2017
  • SSAS 2019
• Azure AS

  v14.0.1.501 →
  v15.1.81.18

  Текущий выпуск

• SSDT

  v14.0.1.432 →
  v15.1.63.21

  Текущий выпуск

»Показать проверенные сборки

Первый выпуск

До 01.01.2017

Внести вклад

Хотите улучшить содержание DISTINCTCOUNT ? Вы нашли какую-нибудь проблему?
Пожалуйста, сообщите нам! Все представленные материалы будут оцениваться на предмет возможных обновлений содержания.

Связанный отдельный счетчик — шаблоны DAX

Шаблон связанного отличного подсчета полезен всякий раз, когда у вас есть одна или несколько таблиц фактов, связанных с измерением, и вам нужно выполнить отдельный подсчет значений столбца в таблице измерения только с учетом элементов, связанных с транзакциями в таблице фактов. В демонстрационных целях мы используем отдельный счетчик названия продукта в модели с двумя таблицами фактов: Продажи и Поступления .

Поскольку название продукта не является уникальным — мы искусственно ввели дублированные имена, удалив описание цвета из названия продукта — простой отдельный счетчик ключа продукта в таблице Sales или Receipts не работает.Наконец, мы покажем, как вычислить различное количество названий продуктов, которые появляются в обеих таблицах и по крайней мере в одной из двух.

Этот шаблон также доступен в виде видео ( 12 мин. ) Посмотреть сейчас

Столбец Продукт [Название продукта] не уникален в таблице Продукт , и нам нужен отдельный счетчик названий продуктов, которые имеют связанные транзакции продаж. Модель содержит две таблицы с проводками, относящимися к продуктам: Продажи и Поступления .На рисунке 1 показана эта модель данных.

Рисунок 1 Модель данных содержит две таблицы фактов: Продажи и Поступления .

На основе этой модели мы хотим вычислить различное количество появляющихся названий продуктов:

• В Продажи .
• В Квитанциях .
• В обеих продажах и Поступления
• По крайней мере в одном из Продажи и Поступления

Отчет показан на Рисунке 2.

Рисунок 2 В отчете показаны четыре показателя, представленные в шаблоне.

Код для первых двух мер следующий:

Показатель в таблице продаж

# Товары с продаж: =
VAR ProdsFromSales =
    СУММИРОВАТЬ (продажи, "продукт" [название продукта])
VAR Результат =
    SUMX (ProdsFromSales, 1) - оптимизация для COUNTROWS (ProdsFromSales)
ВОЗВРАЩАТЬСЯ
    Результат
 

Измерение в таблице квитанций

# Подсказки из квитанций: =
VAR ProdsFromReceipts =
    СУММИРОВАТЬ (поступления, "продукт" [название продукта])
VAR Результат =
    SUMX (ProdsFromReceipts, 1) - оптимизация для COUNTROWS (ProdsFromReceipts)
ВОЗВРАЩАТЬСЯ
    Результат
 

Используя SUMMARIZE, меры # Prods from Sales и # Prods from Receipts извлекают отдельные наименования продуктов, указанные в соответствующей таблице. SUMX просто подсчитывает количество этих продуктов и используется вместо COUNTROWS или DISTINCTCOUNT по соображениям производительности — подробнее в статье Анализ производительности DISTINCTCOUNT в DAX.

Несмотря на то, что эта версия кода длиннее, чем решение, использующее DISTINCTCOUNT и двунаправленную перекрестную фильтрацию, в наиболее частом случае, когда количество продуктов значительно меньше количества транзакций, она работает быстрее.

ПРИМЕЧАНИЕ Естественный синтаксис для вычисления переменной Result в показателях # Prods from Sales и # Prods from Receipts должен использовать COUNTROWS.Версия SUMX предлагается только из соображений производительности в простых мерах. Следующие меры этого шаблона используют COUNTROWS, потому что не будет преимуществ от использования SUMX в более сложных выражениях.

Формулировку с использованием СУММАРИРОВАНИЯ и СЧЕТЧИКА можно легко расширить, чтобы учесть следующие формулы, которые производят пересечение ( # Prods from Both ) или объединение ( # Prods from Any ) названий продуктов:

Измерение в таблице квитанций

# Prods from Both: =
VAR ProdsFromSales =
    СУММИРОВАТЬ (продажи, "продукт" [название продукта])
VAR ProdsFromReceipts =
    СУММИРОВАТЬ (поступления, "продукт" [название продукта])
VAR ProdsFromBoth =
    ПЕРЕСЕЧЕНИЕ (ProdsFromSales, ProdsFromReceipts)
VAR Результат =
    COUNTROWS (ProdsFromBoth)
ВОЗВРАЩАТЬСЯ
    Результат
 

Измерение в таблице квитанций

# Prods from Any: =
VAR ProdsFromSales =
    СУММИРОВАТЬ (продажи, "продукт" [название продукта])
VAR ProdsFromReceipts =
    СУММИРОВАТЬ (поступления, "продукт" [название продукта])
VAR ProdsFromOne =
    ОТЛИЧИТЕЛЬНЫЙ (СОЮЗ (ProdsFromSales, ProdsFromReceipts))
VAR Результат =
    COUNTROWS (ProdsFromOne)
ВОЗВРАЩАТЬСЯ
    Результат
 

Мы предоставили примеры для INTERSECT и UNION. Но шаблон можно легко адаптировать для выполнения более сложных вычислений. В качестве дополнительного примера, показатель # Prods in Sales, а не в Receipts вычисляет количество наименований продуктов, которые существуют в Sales , но не в Receipts , с использованием функции набора EXCEPT вместо функций INTERSECT или UNION, используемых в предыдущие измерения:

Показатель в таблице продаж

# Товары в продажах, а не в квитанциях: =
VAR ProdsFromSales =
    СУММИРОВАТЬ (продажи, "продукт" [название продукта])
VAR ProdsFromReceipts =
    СУММИРОВАТЬ (поступления, "продукт" [название продукта])
VAR ProdsFromSalesAndNotReceipts =
    ИСКЛЮЧАЯ (ProdsFromSales, ProdsFromReceipts)
VAR Результат =
    COUNTROWS (ProdsFromSalesAndNotReceipts)
ВОЗВРАЩАТЬСЯ
    Результат
 

Результат измерения # Prods в Sales, а не в Receipts показан на рисунке 3.

Рисунок 3 Показатель # Prods в продажах, а не в поступлениях подсчитывает продукты, присутствующие в Sales , но не в Receipts .

Шаблон может быть расширен для вычисления количества различных столбцов в таблице, к которому можно получить доступ через цепочку отношений «многие к одному» из таблиц фактов. Это потому, что SUMMARIZE может группироваться по любому из этих столбцов.

Сводные таблицы Excel Количество уникальных элементов

Как подсчитать уникальные элементы (подсчитать отдельные) в сводной таблице Excel.Три метода для разных версий Excel. Получите бесплатную рабочую тетрадь, чтобы следовать по

Спасибо Роджеру Говье, создавшему это
учебник и рабочая тетрадь.

Уникальное количество

Если вы используете сводную таблицу для обобщения данных, возможно, у вас
такие вопросы как:

• Сколько уникальных (различных) клиентов совершили покупки в каждом регионе?
• Сколько уникальных товаров было продано в каждом магазине?
• Сколько уникальных продавцов продали каждый продукт в каждом регионе?

В обычной сводной таблице нет встроенной функции «Уникальный счетчик», но в этой
учебник, вы увидите, как получить уникальное количество элементов в сводной таблице.
стол.

• В Excel 2013 и более поздних версиях создайте модель данных
• В Excel 2010 и более поздних версиях используйте метод «поворота сводной таблицы».
• Или, в более старых версиях, добавьте новый столбец к исходным данным и используйте CountIf.
• Если установлена ​​надстройка Power Pivot, используйте
  чтобы показать отчетливое количество

В этом видео показаны шаги, чтобы показать четкое количество с помощью модели данных.

Введение

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

В этом примере файл образца содержит 4999 записей, которые показывают продукт
продажи, с указанием региона и имени продавца. Первые несколько записей
показаны на снимке экрана ниже.Вы можете скачать образец файла
по ссылке ниже.

Создание сводной таблицы из данных даст нам что-то вроде
следующее, если мы добавили Person в область значений Pivot
таблицу и резюмировал ее с помощью графа.

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

Получите уникальный счет

Есть обходные пути, которые вы можете использовать, чтобы получить уникальное количество:

• Метод 1. В Excel 2013 и более поздних версиях
  Добавьте исходные данные сводной таблицы в модель данных, и можно легко выполнить уникальный подсчет.
• Метод 2. В Excel 2010 и более поздних версиях
  используйте технику «поворот за поворот».
• Метод 3. В старых версиях Excel добавьте столбец
  к исходным данным, чтобы поместить 1 в ячейки в строке, где значение
  уникален, и 0 в любых других ячейках.Затем, суммируя эти дополнительные
  столбец предоставляет уникальное значение.
• Метод 4. Используйте PowerPivot для создания сводной таблицы и используйте ее функции
  создать уникальный счетчик. Видеть
  подробности ниже.

Все четыре метода описаны ниже вместе со сравнением производительности методов 2 и 3.

1. Добавить в модель данных — Excel 2013 и более поздние версии

В Excel 2013, если вы добавите исходные данные сводной таблицы в модель данных книги, легко создать уникальный счетчик.

ПРИМЕЧАНИЕ : Этот метод создает сводную таблицу на основе OLAP, которая имеет некоторые ограничения, такие как отсутствие группировки, а также вычисляемых полей или вычисляемых элементов. Если вам нужны эти ограниченные функции, попробуйте вместо этого метод «поворот за поворот».

Создать уникальный счетчик (отдельный счет)

Чтобы создать сводную таблицу, выполните следующие действия:

• Выберите ячейку в таблице исходных данных.
• В нижней части диалогового окна «Создание сводной таблицы» установите флажок «Добавить эти данные в модель данных».
• Нажмите ОК

Чтобы настроить макет сводной таблицы, выполните следующие действия:

• В сводной таблице добавьте область в область строк.
• Добавьте эти 3 поля в область значений — человек, единицы, значение
• Поле «Человек» содержит текст, поэтому по умолчанию используется «Количество человек». Счетчик показывает общее количество транзакций в каждом регионе, а не уникальное количество людей

Чтобы получить уникальное количество людей в каждом регионе, выполните следующие действия:

• Щелкните правой кнопкой мыши одно из значений в поле «Человек»
• Параметры поля значения щелчка

• Прокрутите список «Суммировать значение поля по по» вниз и нажмите «Различное количество», затем нажмите «ОК».

Поле «Человек» изменится, и вместо общего количества транзакций оно покажет отдельное количество имен продавцов.

2. Поверните ось — Excel 2010

Чтобы получить уникальное количество продавцов по регионам в Excel 2010, мы можем
создать сводный отчет, а затем «Повернуть сводную диаграмму», чтобы получить окончательный
результат.

Создание первой сводной таблицы

• Создайте сводную таблицу на основе этих данных с указанием региона и человека в
  Рядов площадь
• Добавьте единицы измерения и значение в области значений.Потому что Person — это текст
  поле, в сводной таблице оно автоматически отобразится как «Подсчет
  из «.
• Отформатируйте сводную таблицу с макетом табличного отчета
• Задайте для всех меток элементов повторение в каждой строке.
• Измените заголовки, чтобы удалить «Сумму из».
  СОВЕТ: Введите исходное имя поля с пробелом в
  конец, например «единиц»

Теперь это выглядит как обычная таблица данных, но это сводка
исходной полной таблицы данных с 4999 строками.

Создание именованного диапазона для начальной ячейки

Затем вы назовете первую ячейку в сводной таблице.

• Выберите ячейку B2, которая является заголовком в области сводной таблицы
  колонка
• Щелкните панель формул и введите имя ячейки — pvtStart
• Нажмите Enter, чтобы ввести имя

Создание именованного диапазона для конечной строки

Следующий шаг — вычислить положение последней строки в
сводная таблица.Мы будем использовать функцию ПОИСКПОЗ с очень большим числом,
чтобы найти последнее число в столбце D и получить номер его строки. Этот
формула будет работать только в столбце с числами.

Создание динамического именованного диапазона

Затем мы создадим динамический именованный диапазон, чтобы ссылаться на этот новый
стол. Этот диапазон начинается с ячейки с именем pvtstart (
Ячейка заголовка региона) и оканчивается последней строкой данных в столбце E
(именованный диапазон, PvtEnd).

• На вкладке «Формулы» ленты нажмите «Определить имя»
• В диалоговом окне «Новое имя» введите имя myData

.

• В раскрывающемся списке «Область действия» выберите «Рабочая книга»

.

• В поле «Ссылается на» введите следующую формулу:
  = pvtStart: INDEX (Method_1! $ E: $ E, PvtEnd)
• Нажмите ОК, чтобы создать именованный диапазон

Создание второй сводной таблицы

Наконец, создайте вторую сводную таблицу на основе динамического диапазона,
myData.

Поместите регион в область строк, а человека, единицы и стоимость в значения
площадь

Показывает количество уникальных продавцов по регионам, как показано ниже.
Например, в первой сводной таблице Восточный регион показал 8 уникальных
имена в столбце людей, и это количество в этой сводной таблице.

Обновление сводных таблиц

Естественно, поскольку в этом решении задействованы 2 сводные таблицы,
оба должны быть обновлены после добавления или изменения каких-либо данных
в исходной таблице.Важно, чтобы первый PT был обновлен.
сначала, затем следует обновление окончательной сводной таблицы.

Вы можете обновить сводные таблицы вручную или использовать amacro. Использовать
макрос, добавьте одну из следующих процедур на лист с вашим
итоговая сводная таблица.

A) Если обе сводные таблицы находятся на одном листе, используйте этот код:

 Private Sub Рабочий лист_Activate ()

    Me.PivotTables (1) .PivotCache.Обновить

    Me.PivotTables (2) .PivotCache.Refresh

Концевой переводник 

B) Если сводные таблицы находятся на разных листах, используйте этот код
(замените имя вашей таблицы на yoursheetname):

 Private Sub Рабочий лист_Activate ()

    Таблицы ("yoursheetname"). PivotTables (1) .PivotCache.Refresh
    Me.PivotTables (1) .PivotCache.Refresh
Концевой переводник 

(Необязательно) Назовите диапазон данных сводной таблицы

Если вы используете макросы для обновления сводных таблиц, вы также можете
используйте макрос для сброса именованного диапазона myData после каждого обновления.Добавлять
следующую процедуру в свою книгу, затем вызовите этот SetmyData
в процедурах Worksheet_Activate.

 Sub SetmyData ()
'Измените "PT_A" на любое имя
'вы отдали свою первую сводную таблицу
'который будет использоваться в качестве источника
'для окончательной сводной таблицы

'Также измените имя листа
'от "Method_1" к
'имя вашего листа, если оно другое

Dim PTName As String
Dim ShName As String

PTName = "PT_A"
ShName = "Метод_1"

ThisWorkbook. Names.Add _
    Имя: = "myData", _
    RefersTo: = Sheets (ShName) _
        .Сводные таблицы (PTName) _
        .TableRange1
Концевой переводник 

3. Используйте СЧЁТЕСЛИ — Excel 2007 и более ранние версии

В более ранних версиях Excel для сводных таблиц нет параметра
повторяющихся меток строк. Чтобы получить уникальное количество продавцов в каждом регионе,
вы можете добавить новый столбец в исходные данные и ввести формулу СЧЁТЕСЛИ

Скачать образец файла можно по ссылке
ниже.

Добавить формулу СЧЁТЕСЛИ

Чтобы добавить формулу, выполните следующие действия:

В исходных данных добавить новый заголовок столбца — «Уникальный».
— в ячейке J1

В ячейке J2 введите эту формулу и скопируйте ее в последнюю строку.
данных:

= ЕСЛИ (СЧЁТЕСЛИ ($ E $ 5: E5, E5)> 1,0,1)

Первая ссылка в диапазоне ($ E $ 5) является абсолютной, а вторая
является относительным (E5), поэтому, когда мы копируем формулу вниз по странице,
диапазон расширится с $ E $ 5: E5 до $ E $ 5: E6, затем $ E $ 5: E7 и так далее.

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

С добавлением дополнительного столбца данные будут выглядеть следующим образом. Второй
Экземпляр «Гарри» возвращает ноль в столбце Уникальный.

ПРИМЕЧАНИЕ: Если есть люди с одинаковыми именами, продающие в разных регионах:

• В Excel 2007 или более поздней версии: используйте СЧЁТЕСЛИМН, чтобы проверить несколько критериев. Например, = ЕСЛИ (СЧЁТЕСЛИ ($ D $ 5: $ D5, D5, $ E $ 5: E5, E5) = 1,1,0)
• В Excel 2003 или более ранней версии объедините область и
  Имена людей в новом столбце, а затем используйте формулу СЧЁТЕСЛИ для поиска уникальных значений в этом объединенном столбце.

Создание сводной таблицы

Чтобы увидеть уникальные числа в сводной таблице:

• Создайте сводную таблицу из этих данных с областью в строках
  площадь
• Добавьте уникальность, единицы измерения и значение в области значений.
• Измените заголовок «Уникальный» на «Количество человек».
  или «Человек»

Мы видим, что 30 человек совершили Продажи, и число
каждого, кто находится в каждом регионе, и если мы решим расширить любой регион
тогда мы увидим индивидуальные итоги для каждого человека.

Сравнение производительности

Если возможно, используйте метод «pivot a pivot», который очень удобен.
Быстрее.

Метод СЧЕТЕСЛИ

Использование формулы СЧЁТЕСЛИ в исходных данных работает и дает нам
желаемый результат, и на относительно небольшом наборе данных метод приемлем.
Но это очень дорого с точки зрения времени обработки и на очень больших
наборы данных могут быть очень медленными.

Использование Чарльза
«FastExcel» Уильямса для расчета времени, необходимого для рабочей книги.
для вычисления дает результат, показанный ниже, где время, затраченное на
Пересчитать техпаспорт составляет 224 миллисекунды.

Метод поворота вокруг оси

Метод «Pivot a Pivot» также дал правильный результат,
с уникальным счетчиком Person, если требуется. Кроме того, это не
полагаться на дополнительные столбцы в исходных данных, а также на ввод каких-либо
новые формулы.

Полученная рабочая книга меньше, и снова используется Чарльз
«FastExcel» Вильямса для расчета времени расчета
Лист данных невероятно быстрый 1.2 миллисекунды

С небольшими наборами данных время перерасчета может быть не критичным,
но при работе с большими наборами данных от 300 000 до 400 000 строк,
тогда эти различия станут очень существенными.

4. Power Pivot

Если у вас установлена ​​надстройка PowerPivot, вы можете использовать
это, чтобы показать отчетливое количество для поля. В этом видео показаны шаги по созданию сводной таблицы Power Pivot и добавлению поля с уникальным счетчиком.

Письменные инструкции см. В шагах по Excel 2013 в моем блоге со сводной таблицей. Также есть инструкции для Excel 2010.

Образец файла : Чтобы следить за видео, загрузите образец файла, который использовался для этого видео

Получить файлы примеров

• Модель данных : Загрузите образец модели данных
  книга, в которой есть пример для метода 1 (модель данных). Файл заархивирован, и
  в формате xlsx.Файл не содержит макросов.
• Модель без данных : Загрузите образец без модели данных
  книга, которая содержит примеры для метода 2 (поворот на повороте) и метода 3 (столбец в исходных данных). Файл заархивирован, и
  в формате xlsb. Файл не содержит макросов.
• Power Pivot Video : чтобы следовать вместе с видео Power Pivot в методе 4, загрузите образец файла, который использовался для видео

Связанные руководства

Диапазоны имен

штук
Уникальный в PowerPivot

Образец
Файлы — Роджер Говье

О разработчике

Роджер Говье — дипломированный специалист по Excel из Великобритании, который выполняет задания.
в Excel и VBA для клиентов по всему миру.Пока он наслаждается интеллектуалом
проблема решения проблем с функциями рабочего листа, утверждает Роджер
быть ленивым по своей природе, поэтому он всегда ищет быстрый и простой
способ предоставить надежные работоспособные решения.

Дополнительные руководства и примеры файлов Роджера можно найти здесь: Образец
Файлы — Роджер Говье

Вы можете связаться с Роджером по адресу: [email protected]

Роджер Говье

Анализ производительности DISTINCTCOUNT в DAX

Механизм VertiPaq используется DAX при запросе модели на основе данных, загруженных в память.Производительность механизма Vertipaq также является выдающейся, когда он подсчитывает уникальные значения в столбце с помощью функции DISTINCTCOUNT. Однако подсчет уникальных значений в сложных отчетах по-прежнему может создавать проблемы с производительностью. Основная причина этого в том, что DISTINCTCOUNT — это неаддитивное агрегирование, которое необходимо вычислять для каждой ячейки в отчете. Понимание поведения этой агрегации может объяснить, почему другие эквивалентные выражения, которые в теории работают медленнее, могут обеспечить лучшую производительность в определенных отчетах.

В этой статье показано, как реализовать одно и то же вычисление DISTINCTCOUNT двумя альтернативными способами, измеряя и сравнивая производительность в разных отчетах. Вы увидите, что хотя DISTINCTCOUNT можно реализовать с помощью SUMX / DISTINCT, версия DISTINCTCOUNT обычно лучше. То есть, за исключением случаев, когда плотность отчетов высока и в расчетах не применяются фильтры к показателям, которые не соответствуют степени детализации группировки визуализации — как это всегда бывает при использовании функций логики времени.Бывают случаи, когда SUMX / DISTINCT может предложить лучшую производительность, но вы должны уточнить, может ли оптимизация одного отчета замедлить работу многих других. Измерение производительности с помощью DAX Studio — единственный способ узнать, чего ожидать от вашей модели и отчетов.

Измерение производительности DISTINCTCOUNT

Рассмотрим простой отчет, в котором используются две меры для подсчета количества уникальных клиентов, покупающих продукт: # Клиенты и # Клиенты с начала года. Последний применяет к первому расчет с начала года до даты.

# Клиенты: =
DISTINCTCOUNT (Продажи [CustomerKey])

Кол-во клиентов с начала года: =
РАССЧИТАТЬ (
    [# Клиенты],
    DATESYTD ('Дата' [Дата])
)
 

Запрос DAX, созданный для этой визуализации, выполняется относительно быстро. Однако интересно увидеть большое количество запросов механизма хранения (SE Queries), выполняемых в плане запроса.

Поскольку вычисление числа отличимых друг от друга не аддитивно, результат, полученный механизмом хранения, не может использоваться механизмом формул для агрегирования результата промежуточного вычисления.Это не проблема для обычной меры DISTINCTCOUNT, поскольку существует один запрос SE для каждого уровня детализации отчета. Например, это запрос SE, отображаемый в строке 26 на предыдущем снимке экрана, соответствующий расчету показателя # Customers при квартальной детализации в 2007 году:

ВЫБРАТЬ
    'Дата' [номер квартала календарного года],
    "Дата" [Квартал календарного года],
    DCOUNT ("Продажи" [CustomerKey])
ИЗ "Продажи"
ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ "Дата"
    ON "Продажи" [Дата заказа] = "Дата" [Дата]
КУДА
    'Дата' [Число календарного года] = 2007;
 

Этот одиночный запрос SE возвращает 4 строки, соответствующие результату # Customers для каждого квартала 2017 года.Однако отправка одного запроса SE невозможна для вычисления # Customers YTD , которое имеет различный контекст фильтра для каждого квартала, созданного функцией DATESYTD. Действительно, три четверти вычисляются запросами SE в строках 14, 18 и 22 на предыдущем снимке экрана. Структура Каждый из этих запросов имеет структуру, аналогичную той, которая использовалась для расчета за октябрь 2007 г. (код xmSQL был упрощен для удобства чтения):

ВЫБРАТЬ
    DCOUNT ("Продажи" [CustomerKey])
ИЗ "Продажи"
ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ "Дата"
    ON "Продажи" [Дата заказа] = "Дата" [Дата]
КУДА
    'Дата' [Дата] IN (39302.000000, 39094.000000, 39111.000000, .. [Всего 304 значения, отображаются не все]);
 

Поскольку каждая ячейка в отчете имеет разный контекст фильтра, состоящий из разного списка дат, невозможно создать один запрос SE, который правильно описывает перекрывающиеся фильтры, необходимые для октября 2007 г., ноября 2007 г. и декабря 2007 г.

Поскольку такой подход может показаться дорогим, мы можем рассмотреть возможные альтернативы. DISTINCTCOUNT — это просто синтаксический сахар для более длинного выражения DAX с использованием COUNTROWS и DISTINCT:

- DISTINCTCOUNT внутренне использует COUNTROWS / DISTINCT
# Клиенты Basic: =
COUNTROWS (DISTINCT (Продажи [CustomerKey]))
 

План запроса, созданный мерой # Customers Basic , идентичен плану # Customers , и эти две меры семантически эквивалентны.

Измерение производительности SUMX / DISTINCT

Другая версия # Customers может быть получена путем замены COUNTROWS на SUMX, как в показателе # Customers SUMX :

Кол-во клиентов SUMX: =
СУММХ (РАЗЛИЧНЫЕ (Продажи [Клиентский ключ]); 1)

Кол-во клиентов SUMX с начала года: =
РАССЧИТАТЬ (
    [# Клиентов SUMX],
    DATESYTD ('Дата' [Дата])
)
 

Хотя результат измерения # Customers SUMX идентичен результату измерения # Customers , запрос, отправленный механизму DAX, отличается: теперь есть выражение, которое нужно вычислить для каждого уникального значения в Sales [CustomerKey] .То, что это выражение соответствует постоянному значению 1, — лишь частный случай. Теперь план запроса другой и более дорогой, по крайней мере, для предыдущего отчета.

Этот запрос выполняется за 173 мс вместо предыдущих 101 мс. Количество запросов SE было уменьшено, однако это не сильно помогло сократить общее время, затрачиваемое в механизме хранения (SE), которое по-прежнему составляет 64 мс. Причина более длительного времени выполнения заключается в том, что механизм формул (FE) потребляет 63% от общего времени выполнения.Запросы SE в строках 8, 16 и 24 имеют следующую структуру:

ВЫБРАТЬ
    "Продажи" [CustomerKey],
    "Дата" [Дата]
ИЗ "Продажи"
ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ "Дата"
     ON "Продажи" [Дата заказа] = "Дата" [Дата]
 

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

Всегда ли этот подход медленнее? По-разному.

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

Например, рассмотрим отчет, в котором каждая ячейка имеет свой контекст фильтра, который не соответствует условию группировки визуализации. Большинство мер, применяющих вычисление интеллекта времени, соответствуют этому условию. В следующем отчете используется показатель # Customers YTD в матрице, которая группирует значения по рабочим дням в столбцах.

На этот раз версия, использующая меру # Customers YTD , работает медленнее и создает очень большое количество запросов SE. Каждый запрос SE по отдельности выполняется быстро, но накладные расходы для каждого запроса имеют большое влияние на общий результат.

Отчет с использованием # Customers YTD на 30% медленнее, чем тот же отчет с использованием # Customers SUMX YTD , но что интересно, так это изучение причин, почему это происходит. Обратите внимание на то, что сейчас выполняется меньшее количество запросов SE.

Обычно мы предпочитаем план запроса, который проводит больше времени в SE, чем в FE. Это связано с тем, что запросы SE можно кэшировать, тогда как то, что выполняется FE, должно вычисляться при каждом выполнении. Однако, когда количество запросов SE, созданных одним запросом DAX, превышает предел запросов SE, хранящихся в кэше (в настоящее время 256), вы больше не получаете никаких преимуществ от кеша VertiPaq.

Выводы

Мы сознательно выбрали отчеты, в которых разница между этими реализациями была минимальной, потому что мы хотели сосредоточиться на основном поведении.Могут быть отчеты, в которых мера # Customer SUMX намного быстрее, чем классическая мера # Customer, основанная на DISTINCTCOUNT, хотя обычно верно обратное.

Это элементы, которые следует учитывать при сравнении DISTINCTCOUNT с решением на основе SUMX / DISTINCT:

• DISTINCTCOUNT больше полагается на SE.
  • Расчет результата для одной ячейки в результате обычно выполняется быстрее при использовании DISTINCTCOUNT.
  • Для меры с фильтрами, которые различаются для каждой ячейки независимо от условий группировки, могут потребоваться дополнительные запросы SE.
  • Небольшое количество запросов SE может оставаться в кэше между вычислениями.
  • Сложность отчета и количество отображаемых ячеек с вычислением DISTINCTCOUNT могут увеличить количество запросов SE.
• SUMX / DISTINCT материализует данные для выполнения вычислений в FE.
  • Требуется меньшее количество запросов SE.
  • Материализация может быть большой, требуя больше ОЗУ во время запроса по сравнению с DISTINCTCOUNT.
  • Размер материализации зависит от количества элементов отчета и от максимального количества уникальных значений, которые могут быть вычислены на основе существующих фильтров в отчете.

Вы можете видеть, что одна и та же модель может давать разные результаты в разных отчетах. Вам следует внимательно рассмотреть побочные эффекты различных реализаций DISTINCTCOUNT, чтобы избежать нежелательных побочных эффектов в других отчетах. Анализ планов запросов с помощью DAX Studio может помочь предсказать будущее поведение одного и того же показателя в разных отчетах.

Подсчет различных значений в сводной таблице Excel (простое пошаговое руководство)

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

Имея базовые знания и немного перетащив мышью, вы можете выполнить большую часть работы за несколько секунд.

Хотя в сводных таблицах можно сделать многое с помощью нескольких щелчков мышью, есть некоторые вещи, которые потребуют дополнительных действий или небольшой работы.

И одна из таких вещей — подсчет различных значений в сводной таблице.

В этом руководстве я покажу вам, как подсчитывать отдельные значения, а также уникальные значения в сводной таблице Excel.

Но прежде чем я перейду к подсчету различных значений, важно понять разницу между «уникальным счетом» и «уникальным счетом»

Отличное количество и уникальное количество

Хотя это может показаться одним и тем же, это не .

Ниже приведен пример набора данных имен, и я отдельно перечислил уникальные и уникальные имена.

Уникальные значения / имена — это те, которые встречаются только один раз. Это означает, что все повторяющиеся и повторяющиеся имена не уникальны. Уникальные имена перечислены в столбце C в вышеприведенном наборе данных

Отличные значения / имена — это те, которые встречаются в наборе данных хотя бы один раз. Таким образом, если имя встречается три раза, оно все равно считается одним отдельным именем. Этого можно достичь, удалив повторяющиеся значения / имена и сохранив все разные.Отличительные имена перечислены в столбце B приведенного выше набора данных.

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

Подсчитайте отдельные значения в сводной таблице Excel

Предположим, у вас есть данные о продажах, как показано ниже:

Щелкните здесь, чтобы загрузить файл примера и следуйте инструкциям

С помощью приведенного выше набора данных предположим, что вы хотите найдите ответ на следующие вопросы:

1. Сколько торговых представителей работает в каждом регионе (это не что иное, как отдельное количество торговых представителей в каждом регионе)?
2. Сколько торговых представителей продали принтер в 2020 году?

Хотя сводные таблицы могут мгновенно суммировать данные с помощью нескольких щелчков мышью, чтобы получить количество различных значений, вам потребуется сделать еще несколько шагов.

Если вы используете Excel 2013 или более поздние версии, чем , в сводной таблице есть встроенная функция, которая быстро дает точное количество. А если вы используете Excel 2010 или более ранние версии, чем , вам придется изменить исходные данные, добавив вспомогательный столбец.

В этом руководстве рассматриваются два следующих метода:

• Добавление вспомогательного столбца в исходный набор данных для подсчета уникальных значений (работает во всех версиях).
• Добавление данных в модель данных и использование опции Distinct Count (доступно в Excel 2013 и более поздних версиях).

Есть третий метод, который Роджер показывает в этой статье (который он называет методом Pivot the Pivot Table).

Приступим!

Добавление вспомогательного столбца в набор данных

Примечание. Если вы используете Excel 2013 и более поздние версии, пропустите этот метод и перейдите к следующему (поскольку он использует встроенную функцию сводной таблицы — Distinct Count ).

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

Хотя это простой обходной путь, у этого метода есть некоторые недостатки (которые будут рассмотрены позже в этом руководстве).

Позвольте мне сначала показать вам, как добавить вспомогательный столбец и получить точное количество.

Предположим, у меня есть набор данных, показанный ниже:

Добавьте следующую формулу в столбец F и примените ее ко всем ячейкам, которые содержат данные в соседних столбцах.

 = ЕСЛИ (СЧЁТЕСЛИМН ($ C $ 2: C2, C2, $ B $ 2: B2, B2)> 1,0,1) 

В приведенной выше формуле используется функция СЧЁТЕСЛИМН для подсчета количества раз, когда имя появляется в данный регион. Также обратите внимание, что диапазон критериев — $ C $ 2: C2 и $ B $ 2: B2. Это означает, что он продолжает расширяться по мере того, как вы спускаетесь по столбцу.

Например, в ячейке E2 диапазоны критериев: $ C $ 2: C2 и $ B $ 2: B2, а в ячейке E3 эти диапазоны расширяются до $ C $ 2: C3 и $ B $ 2: B3.

Это гарантирует, что функция СЧЁТЕСЛИМН считает первый экземпляр имени как 1, второй экземпляр имени как 2 и так далее.

Поскольку мы хотим получить только отдельные имена, используется функция ЕСЛИ, которая возвращает 1, когда имя появляется для региона в первый раз, и возвращает 0, когда оно появляется снова. Это гарантирует, что учитываются только отдельные имена, а не повторения.

Ниже показано, как будет выглядеть ваш набор данных после добавления вспомогательного столбца.

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

Ниже приведены шаги для этого:

1. Выберите любую ячейку в наборе данных.
2. Щелкните вкладку «Вставка».
3. Щелкните сводную таблицу (или воспользуйтесь сочетанием клавиш — ALT + N + V)
4. В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно (и включает вспомогательный столбец) и «Новый рабочий лист» в выбранном.
5. Нажмите ОК.

Вышеупомянутые шаги позволят вставить новый лист со сводной таблицей.

Перетащите поле «Регион» в область строк и поле «D Count» в область значений.

Вы получите сводную таблицу, как показано ниже:

Теперь вы можете изменить заголовок столбца с «Sum of D count» на «Sales Rep».

Недостатки использования вспомогательного столбца:

Хотя этот метод довольно прост, я должен выделить несколько недостатков, связанных с изменением исходных данных в сводной таблице:

• Источник данных со вспомогательным столбцом — не такой динамичный, как сводная таблица. Хотя с помощью сводной таблицы вы можете разрезать данные как угодно, когда вы используете вспомогательный столбец, вы теряете часть этой способности.Допустим, вы добавили вспомогательный столбец, чтобы получить количество отдельных торговых представителей в каждом регионе. Теперь, что, если вы также хотите получить отчетливое количество торговых представителей, продающих принтеры. Вам нужно будет вернуться к исходным данным и изменить формулу вспомогательного столбца (или добавить новый вспомогательный столбец).
• Поскольку вы добавляете больше данных в источник сводной таблицы (который также добавляется в сводный кеш), это может привести к увеличению размера файла Excel.
• Поскольку мы используем формулу Excel, это может замедлить работу вашей книги Excel, если у вас есть тысячи строк данных.

Добавить данные в модель данных и суммировать с помощью отдельного подсчета

Сводная таблица добавила новую функциональность в Excel 2013, которая позволяет получать отчетливое количество при суммировании набора данных.

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

Предположим, у вас есть набор данных, как показано ниже, и вы хотите получить количество уникальных торговых представителей в каждом регионе.

Ниже приведены шаги для получения отдельного значения счетчика в сводной таблице:

1. Выберите любую ячейку в наборе данных.
2. Щелкните вкладку «Вставка».
3. Щелкните «Сводная таблица» (или воспользуйтесь сочетанием клавиш — ALT + N + V).
4. В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон задана правильно и выбран новый рабочий лист.
5. Установите флажок «Добавить эти данные в модель данных».
6. Нажмите «ОК».

Вышеупомянутые шаги позволят вставить новый лист с новой сводной таблицей.

Перетащите область в область «Строки» и «Торговый представитель» в область «Значения». Вы получите сводную таблицу, как показано ниже:

В приведенной выше сводной таблице указано общее количество торговых представителей в каждом регионе (а не отдельное количество).

Чтобы получить точное количество в сводной таблице, выполните следующие действия:

1. Щелкните правой кнопкой мыши любую ячейку в столбце «Количество торговых представителей».
2. Щелкните «Параметры поля значений».
3. В диалоговом окне «Параметры поля значений» выберите «Различное количество» в качестве типа расчета (возможно, вам придется прокрутить список вниз, чтобы найти его).
4. Нажмите ОК.

Вы заметите, что название столбца изменится с «Количество торговых представителей» на «Отличное количество торговых представителей». Вы можете изменить его на все, что захотите.

Некоторые вещи, которые вы знаете, когда добавляете свои данные в модель данных:

• Если вы сохраните свои данные в модели данных, а затем откроете их в более старой версии Excel, появится предупреждение: «Some pivot» Табличные функции сохраняться не будут ». Вы можете не увидеть отдельного счетчика (и модели данных) при открытии в более старой версии, которая его не поддерживает.
• Когда вы добавляете данные в модель данных и составляете сводную таблицу, в ней не будут отображаться параметры для добавления вычисляемых полей и вычисляемых столбцов.

Щелкните здесь, чтобы загрузить файл примера

Что делать, если вы хотите подсчитывать уникальные значения (а не отдельные значения)?

Если вы хотите подсчитать уникальные значения, у вас нет встроенных функций в сводной таблице, и вам придется полагаться только на вспомогательные столбцы.

Помните — уникальные значения и разные значения — это не одно и то же.Щелкните здесь, чтобы узнать разницу.

Одним из примеров может быть следующий набор данных, и вы хотите узнать, сколько торговых представителей являются уникальными для каждого региона. Это означает, что они работают только в одном конкретном регионе, а не в других.

В таких случаях необходимо создать один или несколько вспомогательных столбцов.

В этом случае поможет следующая формула:

 = IF (IF (COUNTIFS ($ C $ 2: $ C $ 1001, C2, $ B $ 2: $ B $ 1001, B2) / COUNTIF ($ C $ 2: $ C $ 1001, C2) <1,0,1), IF (COUNTIF ($ C2: C $ 22, C2)> 1,0,1), 0) 

Приведенная выше формула проверяет, встречается ли имя торгового представителя в одном регионе только или более чем в одном регионе.Это делается путем подсчета количества вхождений имени в регионе и деления его на общее количество вхождений имени. Если значение меньше 1, это означает, что имя встречается в двух или более чем двух регионах.

Если имя встречается более чем в одном регионе, возвращается 0, иначе возвращается единица.

Формула также проверяет, повторяется ли имя в том же регионе или нет. Если имя повторяется, только первый экземпляр имени возвращает значение 1, а все остальные экземпляры возвращают 0.

Это может показаться немного сложным, но это опять же зависит от того, чего вы пытаетесь достичь.