← Все курсы · Курс SQL

Одна ячейка, много значений: когда столбец содержит список

Форма столбца, которая заставляет DISTINCT лгать · Часть Analyst Prep Kit

К Майкл Носито, аналитик данных · Обновлено 19 августа 2026 г.

Вы запрашиваете у таблицы список категорий, и ответ — 2923. Вы ожидали около тридцати. Ничто не допустило ошибок, вас ничего не предупредило, и номер не является ошибкой. Это правильный ответ на вопрос, который вы не хотели задавать. Это происходит, когда в столбце хранится список внутри единственной клетки, и она тихонько ломается DISTINCT, COUNT, и GROUP BY все сразу. Вот как определить это менее чем за минуту, почему число вернулось неправильно, а также три способа работы со столбцом, самый простой из них.

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

Признак: отчетливое число, которое слишком велико.

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

SELECT COUNT(DISTINCT Genres) AS genre_count
FROM games_raw;

Ответ вернулся 2,923. На цифровой витрине нет 2923 жанров. В нем есть меню из нескольких десятков, из которых выбирает разработчик.

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

Посмотрите на значения, прежде чем считать их

Прежде чем теоретизировать, посмотрите. Счет говорит вам, сколько, а не что:

SELECT DISTINCT Genres
FROM games_raw
LIMIT 20;

Вот оно. Некоторые ячейки содержат один жанр, а некоторые — три, разделенные запятыми. В столбце не хранится жанр, в нем хранится список жанров в виде фрагмента текста.

Оцените, насколько это распространено

Двадцать рядов — это впечатление. Превратите это в доказательство, потому что от ответа зависит, сколько работы вам предстоит. Если несколько строк содержат списки, это небольшая очистка. Если почти все из них так делают, то проблема заключается в форме колонны:

SELECT COUNT(DISTINCT Genres) AS values_with_a_comma
FROM games_raw
WHERE Genres LIKE '%,%';

2,898 из 2923 различных значений содержат запятую. Это 99 процентов. Это не крайний случай в данных, это сами данные.

(LIKE '%,%' читается как «что угодно, потом запятая, потом что угодно». Если этот синтаксис новый, НРАВИТСЯ и подстановочные знаки прикрывает это.)

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

DISTINCT сделал именно то, что обещает. Он возвращал каждое отдельное значение в столбце один раз. Проблема в том, что «значение» здесь представляет собой целую строку, разделенную запятыми, и он понятия не имеет, что запятые что-то означают:

Три строки в результате и слово Indie есть во всех трёх. Так что 2923 – это не количество жанров. Это количество уникальных комбинации жанров, которые использует хотя бы одна игра. Это реальный факт, просто это не тот факт, о котором кто-то просил.

То же самое искажение присутствует в каждом агрегате, который вы строите на столбце. GROUP BY Genres составляет 2923 группы, в большинстве из которых есть несколько игр. Гистограмму невозможно прочитать. Вычисленная на основе этого процентная доля неверна, поскольку игра, помеченная тремя жанрами, учитывается один раз в отдельной группе, а не один раз для каждого жанра.

Ценности, которые никогда не появляются в одиночку

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

SELECT DISTINCT Genres
FROM games_raw
WHERE Genres NOT LIKE '%,%'
ORDER BY Genres;

Это возвращает 24 ряды, и они выглядят правильно. Экшены, Приключения, Казуальные игры, Инди, Ролевые игры, Гонки, Симуляторы, Спортивные игры, Стратегии и так далее. Соблазнительно назвать это списком жанров и двигаться дальше.

Оно неполное, и ничто в результате об этом не говорит. Значение, которое применяется только когда-либо рядом еще один будет находиться в 2898 списках и ни разу не появится сам по себе. Зонд для одного:

SELECT DISTINCT Genres
FROM games_raw
WHERE Genres LIKE '%Gore%';

132 комбинации возвращаются, и ни одна из них не Gore само собой. Gore реален, он используется, и в списке из 24 строк он полностью отсутствует. Правильное разделение столбца оказывается 33 различные ценности, поэтому девять из них никогда не остаются одинокими.

Три способа работы с ним: первый самый простой

SELECT Name, Price
FROM games_raw
WHERE Genres LIKE '%RPG%';

SELECT Name, Price
FROM games_raw
WHERE ',' || Genres || ',' LIKE '%,RPG,%';

SELECT g.genre, COUNT(*) AS games
FROM genre_list AS g
JOIN games_raw AS r
  ON ',' || r.Genres || ',' LIKE '%,' || g.genre || ',%'
GROUP BY g.genre
ORDER BY games DESC;

WITH RECURSIVE split(genre, rest) AS (
  SELECT '', Genres || ','
  FROM games_raw
  WHERE Genres IS NOT NULL AND Genres <> ''
  UNION ALL
  SELECT substr(rest, 1, instr(rest, ',') - 1),
         substr(rest, instr(rest, ',') + 1)
  FROM split
  WHERE rest <> ''
)
SELECT trim(genre) AS genre,
       COUNT(*) AS games
FROM split
WHERE trim(genre) <> ''
GROUP BY lower(trim(genre))
ORDER BY games DESC;

Какой из них выбрать

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

Запишите это как ограничение

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

• Что на самом деле содержит столбец. «Поле «Жанры» хранит список, разделенный запятыми, поэтому игра может содержать несколько жанров».
• Как ты с этим справился. «Перед подсчетом жанры были разделены на одну строку для каждой игры».
• Что это делает с итоговыми показателями. «Игры нескольких жанров учитываются один раз в каждой, поэтому сумма жанров превышает 125 855 игр».

В этом конкретном наборе данных есть еще одна вещь, на которую стоит обратить внимание, и это та деталь, которая отличает настоящий анализ от учебного пособия. Эти 33 ценности относятся не ко всем жанрам. Gore, Violent, Nudity и Sexual Content являются предупреждениями о содержании. Accounting, Photo Editing и Video Production это категории программного обеспечения, а вовсе не игры. В этом поле в одном столбце смешаны несколько типов ярлыков, поэтому заголовок «Основной жанр» является вводящим в заблуждение, если вы не решите, какой из 33 учитывается, и не скажете, какой вы удалили. Документирование ограничений данных охватывает написание этого раздела, не подрывая при этом вашу собственную работу.

Почему данные выглядят так

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

С этой таблицей подсчет по жанрам является обычным делом. GROUP BY и ничего из этого руководства не нужно. Так почему же плоская версия продолжает появляться? Потому что аналитики получают данные, а не проектируют их. Экспорт стабилизируется. Таблицы печатаются вручную. API возвращают массив, и тот, кто написал CSV, соединил его запятыми. Инструменты для проведения опросов, естественно, записывают ответы «выберите все подходящие» в одно поле.

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