#mysql #grouping
#mysql #группировка
Вопрос:
У меня есть таблица, в которой заполняется поле, если запись является дубликатом. Код уже запущен, правильно проверяет наличие дубликатов и работает.
Таблица выглядит следующим образом:
id | dupe_ids | id_subscription
1 NULL 5343
2 3, 4 5343
3 2, 4 5343
4 2, 3 5343
5 NULL 5343
6 7 5343
7 6 5343
Запрос должен возвращать количество записей, но необходимо сгруппировать дублированные идентификаторы. Мне нужен запрос для группировки записей, содержащих записи, в один счетчик, но каким-то образом на основе дубликатов. В приведенном выше примере количество для подписки 5343, количество будет равно 4. Запись 2 будет считаться единицей, при этом 3 и 4 будут пропущены или сгруппированы, а запись 6 будет считаться единицей, при этом запись 7 будет сгруппирована или пропущена.
Теперь запрос выглядит следующим образом:
SELECT app.id_subscription, app.id_site, app.id_customer, COUNT(*) AS app_count, site.url
FROM web_manager.app, web_manager.site
WHERE app.id_customer = :wm_id
AND (app.received_at BETWEEN :sdate AND :edate)
AND app.id_site = site.id
AND app.dupe_ids IS NULL
GROUP BY app.id_subscription
ORDER BY app_count DESC
Комментарии:
1. В чем ваш вопрос?
2. Я думаю, что вопрос в том, «Какой оператор SQL я могу и должен выполнить для этой таблицы в стиле Monty Python, чтобы получить количество возвращенных 3″…
And the LORD spake, saying, "First shalt thou take out the Holy Pin, then shalt thou count to three, no more, no less. Three shall be the number thou shalt count, and the number of the counting shall be three. Four shalt thou not count, neither count thou two, excepting that thou then proceed to three. Five is right out.
3. Просто измените
AND app.dupe_ids IS NULL
наAND app.dupe_ids IS NOT NULL
. Тогда вы получите правильное количество.4. Извините, что не был более точным в моем вопросе. Я собираюсь обновить свой вопрос и просмотреть ответы. Спасибо!
5. Этот вопрос служит хорошим примером того, почему мы настоятельно рекомендуем избегать «списков значений, разделенных запятыми». Это было бы намного проще, если бы мы не имели дело с этим … если бы у нас был какой-то другой способ идентификации дубликатов, например, хранения ссылок на внешние ключи во второй таблице или некоторых условий / критериев, чтобы мы могли использовать SQL для оценки набора. Синтаксический анализ и обработка строк на самом деле не являются сильной стороной SQL.
Ответ №1:
Если значения в dupe_ids
— это список числовых id
значений, и список всегда «в порядке», причем наименьшее значение является первым в списке, как грязное решение…
Запрос в моем первоначальном ответе (ниже) изменен, чтобы заменить константу 0
выражением, подобным этому: LEAST(a.id,SUBSTRING_INDEX(a.dupe_ids,',',1) 0)
.
Это выражение гласит: возьмите первое значение из dupe_ids
списка, оцените его в числовом контексте, сравните числовое значение со id
значением из строки и верните меньшее из двух.
SELECT COUNT(DISTINCT IF(a.dupe_ids IS NULL,a.id,LEAST(a.id,SUBSTRING_INDEX(a.dupe_ids,',',1) 0))) AS my_funky_cnt
, a.id_subscription
FROM web_manager.app a
JOIN web_manager.site s
ON s.id = a.id_site
WHERE ...
GROUP BY a.id_subscription
ПОРЯДОК ПО my_funky_cnt DESC
Опять же, удаление GROUP BY
и агрегат, чтобы увидеть, что на самом деле возвращается выражением…
SELECT a.id
, a.dupe_ids
, a.id_subscription
, IF(a.dupe_ids IS NULL,a.id,LEAST(a.id,SUBSTRING_INDEX(a.dupe_ids,',',1) 0)) AS expr
FROM web_manager.app a
JOIN web_manager.site s
ON s.id = a.id_site
WHERE ...
ORDER BY a.id_subscription, a.dupe_ids IS NULL, a.id
мы ожидаем, что это вернет:
id | dupe_ids | id_subscription | expr
2 3, 4 5343 2 -- id=2 is less than fv=3
3 2, 4 5343 2 -- fv=2 is less than id=3
4 2, 3 5343 2 -- fv=2 is less than id=4
6 7 5343 6 -- id=6 is less than fv=7
7 6 5343 6 -- fv=6 is less than id=7
1 NULL 5343 1
5 NULL 5343 5
Таким образом, a GROUP BY id_subscription
и COUNT(DISTINCT expr)
вернет количество 4.
(это не проверялось)
Этот подход зависит от dupes_id
наличия наименьшего значения идентификатора, указанного первым (первое значение в списке), вычисления этого первого значения в числовом контексте и сравнения его со id
значением из строки.
Если dupe_ids
это пустая строка, или начинается с запятой, или первые непустые символы не могут быть интерпретированы как числовое значение, то expr
будет возвращено a 0
.
Редактировать
Первоначальный ответ (ниже) был основан на свертывании всех строк с ненулевыми значениями для заданного id_subscription… возвращает количество, равное 3. Вопрос был обновлен, добавлено больше примеров строк с ненулевыми значениями, которые не должны быть свернуты вместе. Желаемый возврат для «count» теперь равен 4. Запрос в исходном ответе вернет значение 3.
Получить количество строк с нулевым значением dupe_ids
просто.
Липкая калитка — это причудливое содержимое dupe_ids
столбца, разделенный запятыми список значений идентификаторов…
id dupe_ids
---- --------
2 '3,4'
3 '2,4'
4 '2,3'
6 '7'
7 '6'
Это было бы проще, если бы мы не имели дело со «списком значений, разделенных запятыми». Если бы вместо этого у нас были ссылки на внешние ключи к строкам в отдельной таблице. Или, если бы у нас были какие-то критерии, отличные от dupe_ids
столбцов, для идентификации строк, которые являются «дубликатами».
Но это не был заданный вопрос. В вопросе не спрашивалось, было бы лучше избегать хранения списка, разделенного запятыми; был ли лучший подход.
Вопрос заставляет нас иметь дело со списком, разделенным запятыми. (Это служит примером того, почему мы настоятельно рекомендуем избегать списков, разделенных запятыми, в первую очередь).
Если бы у нас было выражение, которое содержит значения dupe_ids
вместе со id
значением, вместе, так что у нас были одинаковые значения в строках…
id dupe_ids expr
---- -------- ------
2 '3,4' '2,3,4'
3 '2,4' '2,3,4'
4 '2,3' '2,3,4'
6 '7' '6,7'
7 '6' '6,7'
Тогда мы могли бы использовать a, COUNT(DISTINCT expr)
чтобы получить желаемый результат. Уродливая часть получает это значение expr
. Было бы легко добавить или добавить id
dupe_ids
, но результирующие строковые значения не будут идентичными. Списки будут в другом порядке.
В MySQL нет простой встроенной функции для возврата значений, показанных для expr
, на основе содержимого id
и dupe_ids
.
ОРИГИНАЛЬНЫЙ ОТВЕТ
Подход, который я бы выбрал, заключается в использовании выражения и подсчете его различных значений.
Если dupe_ids
значение равно null, возвращается уникальное значение. Если id
является уникальным в таблице, я бы просто использовал значение этого столбца. Если dupe_ids
не равно нулю, то подставьте константу, которая не является допустимым id
значением. Предполагая id
, что значения являются целыми положительными числами, я бы использовал 0 или отрицательное значение.
В качестве примера:
SELECT COUNT(DISTINCT IF(a.dupe_ids IS NULL,a.id,0)) AS my_funky_cnt
, a.id_subscription
FROM web_manager.app a
JOIN web_manager.site s
ON s.id = a.id_site
WHERE ...
GROUP BY a.id_subscription
ORDER BY my_funky_cnt DESC
Я бы проверил, что выражение «работает», сначала выполнив запрос без GROUP BY и aggregate …
SELECT a.id
, a.dupe_ids
, a.id_subscription
, IF(a.dupe_ids IS NULL,a.id,0) AS derived_col
FROM web_manager.app a
JOIN web_manager.site s
ON s.id = a.id_site
WHERE ...
ORDER BY a.id_subscription, a.dupe_ids IS NULL, a.id
Мы ожидаем, что это вернет:
id | dupe_ids | id_subscription | derived_col
1 NULL 5343 1
2 3, 4 5343 0
3 2, 4 5343 0
4 2, 3 5343 0
5 NULL 5343 5
Таким образом, все строки с ненулевым dupe_ids
значением имеют одинаковое значение, а строки с нулевым dupe_ids
значением имеют уникальное значение.
И a COUNT(DISTINCT
этого выражения вернет 3.
Комментарии:
1. Вы поняли его вопрос?
2. @Barmar: Сначала нет. Единственный способ, которым я мог получить количество 3, — это «свернуть» все строки с ненулевыми значениями
dupe_id
в одну строку. (Мне нужно проверить книгу Билла Карвина, чтобы узнать, есть ли глава об этом анти-шаблоне.)3. Разве это не просто взято из
AND dupe_ids IS NOT NULL
его запроса?4. Я обновил вопрос. Прочитав комментарии и просмотрев пример, я понял, что пример был недостаточно подробным, потому что будут дубликаты, которые необходимо сгруппировать на основе других дубликатов, и не ВСЕ дубликаты должны быть сгруппированы в один.
5. @spencer7593. После изучения файлов я обнаружил, что дубликаты помечены на основе адреса электронной почты и телефона. Телефон может быть НУЛЕВЫМ, но я могу использовать электронную почту, используя ваш ‘derived_col’ выше.