Блог

Главная - Вопросы по программированию - SQL получает количество уникальных значений

SQL получает количество уникальных значений

#sql #sqlite #count #distinct

#sql #sqlite #граф #distinct

Вопрос:

Используя sqlite3, как я могу получить разные столбцы «count» для этого примера базы данных в памяти? Использование версии 3.27.2

Пример базы данных

 CREATE TABLE events (
    id1, 
    id2, 
    id3, 
    PRIMARY KEY (id1, id2)
);

INSERT INTO events (id1, id2, id3)
VALUES 
   (1,1,99),
   (1,2,99),
   (1,3,52),
   (2,1,6),
   (2,2,7),
   (2,3,8)
;

.mode columns
.header on
SELECT * FROM events;

введите описание изображения здесь

Желаемый печатный результат

введите описание изображения здесь

Частичный успех Для первых двух новых столбцов работает следующее.

 SELECT id1, count(id3) AS total_count, count(DISTINCT id3) AS unique_count
FROM events
GROUP BY id1;

Каков наилучший способ получить последний столбец? Следующие результаты error: no such column: total_count 

 SELECT id1, count(id3) AS total_count, count(DISTINCT id3) AS unique_count, (total_count - unique_count) AS repeated_count
FROM events
GROUP BY id1;

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

1. Общее количество за вычетом уникального количества не дает вам число повторяющихся значений. Пожалуйста, посмотрите мой ответ на это. (Если вы просто хотели total_count - unique_count , вы могли бы просто использовать count(*) - count(distinct id3) . Вы не можете получить доступ к псевдонимам в том же предложении, в котором вы их определяете.)

Ответ №1:

Если вам group by id1, id3 это нравится:

 SELECT id1, id3, COUNT(*) counter
FROM events
GROUP BY id1, id3;

вы получаете количество строк для каждой комбинации id1, id3 :

id1 id3 счетчик
1 52 1
1 99 2
2 6 1
2 7 1
2 8 1

Теперь все, что вам нужно сделать, это:

  • суммируйте столбец counter для каждого id1 , чтобы получить столбец total_count
  • подсчитайте количество строк для каждого id1 , чтобы получить столбец unique_count
  • подсчитайте количество строк для каждого id1 столбца, в котором находится столбец counter > 1 , чтобы получить столбец repeated_id3

Вы можете сделать это с SUM() COUNT() помощью оконных функций и:

 SELECT DISTINCT id1, 
       SUM(COUNT(*)) OVER (PARTITION BY id1) AS total_count, 
       COUNT(*) OVER (PARTITION BY id1) AS unique_count,
       SUM(COUNT(*) > 1) OVER (PARTITION BY id1) repeated_id3
FROM events
GROUP BY id1, id3;

Посмотрите демонстрацию.
Результаты:

id1 total_count unique_count повторяемый идентификатор 3
1 3 2 1
2 3 3 0

Ответ №2:

Возможно, попробуйте CTE. Я не проверял синтаксис, но это, по-видимому, допустимый вариант при просмотре документации SQLLite.

 With X as 
(
SELECT id1, count(id3) AS total_count, count(DISTINCT id3) AS unique_count
FROM events
GROUP BY id1;
)
select id1, total_count, unique_count, (total_count - unique_count) AS repeated_count
from X

Ответ №3:

Это не так просто 🙂

В

 (1,1,99), (1,2,99), (1,3,52)

повторяется один идентификатор (99).

В

 (1,1,99), (1,2,99), (1,3,52), (1,4,99)

снова повторяется один идентификатор (по-прежнему 99).

В

 (1,1,99), (1,2,99), (1,3,52), (1,4,52)

повторяются два идентификатора (52 и 99).

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

 select
  id1,
  count(*) as total_count,
  count(distinct id3) as unique_count,
  count(case when cnt > 1 then 1 end) as repeated_count
from
(
  select id1, id3, count(*) as cnt
  from events
  group by id1, id3
) pre_aggregated
group by id1
order by id1;
Метки: