Методы выборки машинного обучения с использованием SQL

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

Случайная выборка без замены

Самый простой метод выборки — получить N случайных выборок из большого набора данных. Это легко сделать с помощью RAND() порядка. Ниже приведен пример получения 1000 случайных выборок из набора данных.

SELECT
    d.*
FROM dataset d
ORDER BY RAND()
LIMIT 1000

Если вы хотите выбрать 10% всей таблицы, вы можете поместить оператор RAND() в условие where.

SELECT
    d.*
FROM dataset d
WHERE RAND() < 0.1

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

Случайная выборка с заменой

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

Ниже приведен пример в Presto того, как вы можете сделать выборку из 1000 элементов из исходного набора данных. Объяснение сразу после блока кода.

WITH seq AS (
   SELECT rn
   FROM UNNEST(SEQUENCE(1, 1000)) t(rn)
), source_cnt AS (
   SELECT COUNT(1) AS total
   FROM source
), seq_sampling AS (
   SELECT
      CEILING(RAND() * total) AS source_rn
   FROM seq
   CROSS JOIN source_cnt
), source_with_rn AS (
   SELECT
       *,
       ROW_NUMBER() OVER () AS source_rn
   FROM source
)
SELECT
  s.*
FROM source_with_rn
JOIN seq_sampling USING (source_rn)

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

Групповая выборка

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

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

WITH activities_summary AS (
    SELECT
       user_id,
       COUNT(1) cnt
    FROM activities
), sampling AS (
    SELECT
       user_id,
       SUM(cnt) OVER (ORDER BY RAND()) cum_cnt
    FROM activities_summary
)
SELECT
   *
FROM activities
WHERE
   user_id IN (
      SELECT user_id
      FROM sampling
      WHERE cum_cnt <= 1000
   )

Детерминированная/воспроизводимая выборка

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

SELECT
    d.*
FROM dataset d
ORDER BY HASH(key)
LIMIT 1000

Если этот пост был полезен, пожалуйста, несколько раз нажмите кнопку аплодисментов 👏 ниже, чтобы выразить свою поддержку автору 👇

Методы выборки машинного обучения с использованием SQL

Вопросы по теме