Блог

Главная - Вопросы по программированию - Превратить результаты count distinct во что-то, что можно агрегировать

Превратить результаты count distinct во что-то, что можно агрегировать

#sql #amazon-redshift #analytics #business-intelligence #distinct-values

#sql #amazon-redshift #аналитика #бизнес-аналитика #distinct-значения

Вопрос:

У меня есть такая таблица:

  ---------- -------------- ------------- 
| category | sub_category | customer_id |
 ---------- -------------- ------------- 
|    A     |     AB2      |    A876     |
|    A     |     AB2      |    A876     |
|    A     |     AA1      |    A876     |
|    A     |     AA1      |    A876     |
|    A     |     AC3      |    A756     |
|    B     |     AB2      |    A876     |
|    B     |     AA1      |    A756     |
|    B     |     AB7      |    A908     |
|    C     |     AA1      |    A756     |
|    C     |     AB7      |    A908     |
|    C     |     AC3      |    A908     |
 ---------- -------------- -------------

И я хочу подсчитать отдельных клиентов, чтобы я мог легко сделать что-то вроде:

 SELECT category, sub_category, COUNT(DISTINCT customer_id) as count_of_customers
  FROM tbl
  GROUP BY category, sub_category

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

Например, customer_id = ‘A876′ будет учитываться дважды в category =’A’ (один раз в sub_category = ‘AB2’ и один раз в sub_category = ‘AA1’), если я просто суммирую count_of_customers из результата моего запроса.

Итак, вот вопрос, я хотел бы сделать эти результаты запроса «агрегируемыми». Глядя на проблему, похоже, что это просто невозможно, но мне интересно, есть ли какой-нибудь умный способ распределения этих результатов по категориям? так что в моем уровне отчетов (например, в сводной таблице Excel) я могу получить результат, который подсчитывает ‘A876′ один раз в category =’A’, но подсчитывает его дважды, когда я также включаю sub_category в поля. В основном преобразование результатов во что-то суммируемое.

Я должен упомянуть, что это слишком упрощенный пример. Решение должно быть обобщено по n различным категориям и подкатегориям.

Я ищу результат, который легко позволил бы мне получить любой из следующих результатов в чем-то похожем на сводную таблицу (например, инструменты отчетности, похожие на таблицы):

  ---------- -------------------- 
| category | distinct_customers |
 ---------- -------------------- 
|    A     |         2          |
|    B     |         3          |
|    C     |         2          |
 ---------- -------------------- 


 -------------- -------------------- 
| sub_category | distinct_customers |
 -------------- -------------------- 
|     AA1      |         2          |
|     AB2      |         1          |
|     AB7      |         1          |
|     AC3      |         2          |
 -------------- --------------------

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

Комментарии:

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

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

3. Какой результат вы ищете?

4. было бы лучше, если бы вы опубликовали ожидаемые результаты

5. Добавлены ожидаемые выходные данные.

Ответ №1:

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

 select category, sub_category,
       count(distinct customer_id),
       sum(1.0 / num_cs) as weighted_customers
from (select t.*,
             count(*) over (partition by customer_id) as num_cs
      from t
     ) t
group by category, sub_category;

Это взвешивает оба category и sub_category . Очевидно, что вы можете настроить partition by вес только одним или другим.