Иногда, когда статистики собирают данные, им нравится возвращать каждое из них, прежде чем брать следующую. Это называется случайной выборкой с заменой, в отличие от случайной выборки без замены, которая является более распространенным явлением.
Пример того, когда это делается в дикой природе, - во время обучения случайного леса, и это стало спусковым крючком для меня, написавшего этот пост.
Все встроенные в Snowflake функции сэмплирования вращаются вокруг сэмплирования без замены. Это означает, что вам нужно принимать решение по каждой строке только один раз - она либо добавляется, либо нет в зависимости от случая.
Сложность самонастройки в том, что это немного похоже на то, чтобы заниматься велоспортом как хобби; делать это правильно - довольно дорого.
Наивный, но абсолютно точный способ загрузки 10 000 строк:
Это дает всем 150 000 клиентов в исходной таблице равные шансы быть выбранными каждый раз, независимо от того, сколько раз они уже были выбраны.
Должен быть способ получше! Ответ, конечно, таков, что нам нужна «достаточно хорошая» альтернатива. В конце концов, мы делаем выборку, поэтому уровень точности обычно гибкий, если это означает повышение производительности.
Итак, давайте посмотрим, сможем ли мы придумать другой подход.
Цель будет заключаться в загрузке 60% из 150 000 записей в таблице «SNOWFLAKE_SAMPLE_DATA». »TPCH_SF1.« CUSTOMER ».
Мы хотели бы сделать одну итерацию таблицы (в алгоритмическом смысле) и, следовательно, по возможности принимать решение по каждой строке изолированно только один раз. Нам нужно будет найти способ выбрать его несколько раз, но не выходя за рамки параметров образца.
Простая модификация, если бы мы произвольно решили, что десяти разверток по таблице было достаточно, тогда мы могли бы просто каждый раз выбирать 6%, например:
Это совсем не очень хорошо. Во-первых, мы обрабатывали каждую строку по десять раз и вообще не оценили погрешность, а просто составили число.
Итак, давайте посмотрим, что происходит с каждой строкой каждый раз, когда мы выполняем итерацию, и рассмотрим две крайности: всегда выбирают и никогда не выбирают.
После определенного количества итераций неизбежно наступит момент, когда (как мы бы сказали в Австралии, каждая запись прошла «честный ход». Итак, нам нужен способ провести эту линию. Для целей этого упражнения давайте позволим пользователю нужно указать две вещи:
- Желаемый размер выборки в процентах от исходной таблицы
- Процентное значение, при котором мы прекращаем итерацию, когда вероятность никогда не быть выбранной опускается ниже. Представьте, что это горизонтальная линия, которую вы можете разместить где-нибудь на диаграмме выше.
Время уравнения!
Напоминаем, что целью является выборка 60%.
Если на секунду забыть о части «с заменой», вероятность того, что строка будет выбираться каждый раз:
Или, если не выбран, просто переверните его:
Значение справа - это то, что мы хотим, чтобы пользователь указал. Как видите, оно всегда уменьшается, и мы будем добавлять итерации, пока не достигнем его.
Итак, мы можем выразить это в общем, а затем решить, чтобы выяснить, сколько итераций нам нужно сделать:
Отлично, теперь у нас есть формула, которую можно использовать.
А теперь вернемся к Снежинке. Помните, что на самом деле мы не хотим делать несколько итераций. Но сложность в том, что невозможно заставить строку появляться в наборе результатов более одного раза…. или есть?!?!?!
Введите UDTF
Мне очень нравятся UDTF, они позволяют использовать элегантные решения вместо ужасных шаблонов.
Проверьте это:
Мы можем использовать это, чтобы убить двух зайцев. Прежде всего, он применит эту формулу для выработки хорошего количества итераций (то есть количества шансов быть выбранным) на основе этого порога, который мы изобрели.
Затем будет достаточно любезно разделить фракцию выборки на это число и записать это количество строк.
Чтобы продемонстрировать, вывод UDTF сам по себе выглядит так:
select * from table(SAMPLE_PROBABILITY(0.6::double,0.00001::double))
Формула говорит, что мы должны сделать 13 итераций, а 0,6 / 13 = 0,04615384615
Другими словами, мы дадим каждой строке 13 шансов с вероятностью 0,04615384615 за попытку.
Все, что нам нужно сделать, это присоединиться к этому UDTF следующим образом:
Необработанный результат:
SELECT customer.* FROM "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."CUSTOMER" customer,table(SAMPLE_PROBABILITY(0.6::double,0.00001::double))
на самом деле 150 000 * 13 = 1 950 000 строк, но затем мы добавляем предложение WHERE:
where uniform(0::float, 1::float, random()) < SAMPLE_PROBABILITY
Это генерирует случайное число от 0,0 до 1,0 и удаляет строки ниже порогового значения.
100% правильная реализация должна дать нам 90 000 строк (150 000 * 0,6). Этот (с выбранными мною входными данными), кажется, дает каждый раз где-то от 89 000 до 91 000.
И конечно, как мы и хотели, есть вероятность дублирования. Вы даже можете увидеть его на первой странице результатов:
А для нашей таблицы из 150 000 строк, чтобы вернуть ~ 90 тыс. Записей, это чуть больше секунды на x-small складах.
