Степенное распределение в T-SQL
Мне в основном нужен ответ на этот SO вопрос, который обеспечивает степенное распределение, переведенное для меня на T-SQL.
Я хочу получить фамилию, одну за другой, из таблицы имен, предоставленной переписью. Я хочу получить примерно такое же распределение, как в популяции. Таблица насчитывает 88 799 имен, ранжированных по частоте. "Смит" - это ранг 1 с частотой 1,006%, "Алдеринк" - это ранг 88,799 с частотой 1,7 х 10^-6. "Сандерс" занимает 75-е место с частотой 0,100%.
Кривая не должна точно соответствовать. Просто дайте мне около 1% "Смит" и около 1 на миллион "Alderink"
Вот что у меня так далеко.
SELECT [LastName]
FROM [LastNames] as LN
WHERE LN.[Rank] = ROUND(88799 * RAND(), 0)
Но это, конечно, дает равномерное распределение.
Я обещаю, что я все еще буду пытаться выяснить это сам, когда умный человек ответит.
4 ответа
Зачем соглашаться на степенное распределение, когда вы можете извлечь из фактического распределения?
Я предлагаю вам изменить таблицу LastNames, включив в нее числовой столбец, который будет содержать числовое значение, представляющее фактическое число индивидуумов с более распространенным именем. Вы, вероятно, захотите число в меньшей, но пропорциональной шкале, скажем, возможно, 10000 для каждого процента представительства.
Список будет выглядеть примерно так:
(кроме трех имен, упомянутых в вопросе, я предполагаю, что Уайт, Джонсон и др.)
Smith 0
White 10,060
Johnson 19,123
Williams 28,456
...
Sanders 200,987
..
Alderink 999,997
И выбор имени будет
SELECT TOP 1 [LastName]
FROM [LastNames] as LN
WHERE LN.[number_described_above] < ROUND(100000 * RAND(), 0)
ORDER BY [number_described_above] DESC
Это выбирает имя, число которого не превышает случайное число [равномерное распределение]. Обратите внимание, как в запросе используется меньше и упорядочение в порядке убывания; это гарантирует, что будет выбрана самая первая запись (Смит). Альтернативой было бы начать серию с Смитом с 10 060, а не с нуля, и отбросить случайные ничьи, меньшие, чем это значение.
Помимо упомянутого выше вопроса управления границами (начиная с нуля, а не 10 060), это решение наряду с двумя другими ответами до сих пор совпадает с предложенным в ответе dmckee на вопрос, упомянутый в этом вопросе. По сути, идея состоит в том, чтобы использовать CDF (функция накопительного распределения).
Редактировать:
Если вы настаиваете на использовании математической функции, а не фактического распределения, следующее должно обеспечить степенную функцию, которая каким-то образом передаст форму "длинного хвоста" реального распределения. Возможно, вы захотите изменить значение @PwrCoef (что BTW не обязательно должно быть целым числом), по существу, чем больше коэффициент, тем больше перекошено начало списка функций.
DECLARE @PwrCoef INT
SET @PwrCoef = 2
SELECT 88799 - ROUND(POWER(POWER(88799.0, @PwrCoef) * RAND(), 1.0/@PwrCoef), 0)
Заметки:
- дополнительные ".0" в функции выше важны для того, чтобы заставить SQL выполнять операции с плавающей запятой, а не целочисленные операции.
- причина, по которой мы вычитаем вычисление мощности из 88799, заключается в том, что распределение вычислений таково, что чем ближе число ближе к концу нашей шкалы, тем больше вероятность того, что оно будет нарисовано. Список фамилий, отсортированных в обратном порядке (скорее всего, имена в первую очередь), нам нужно это вычитание.
При условии, скажем, 3, запрос будет выглядеть примерно так:
SELECT [LastName]
FROM [LastNames] as LN
WHERE LN.[Rank]
= 88799 - ROUND(POWER(POWER(88799.0, 3) * RAND(), 1.0/3), 0)
Какой запрос из вопроса, кроме последней строки.
Повторное редактирование:
При рассмотрении фактического распределения, как видно из данных переписи, кривая является чрезвычайно крутой и потребует очень большого коэффициента мощности, что, в свою очередь, приведет к переполнению и / или крайним ошибкам округления в наивной формуле, показанной выше.
Более разумный подход может состоять в том, чтобы работать на нескольких уровнях, т.е. выполнять равное количество ничьих в каждой из, скажем, трех третей (или четырех четвертей или...) совокупного распределения; в каждом из этих списков частей мы будем рисовать с использованием степенной функции, возможно, с тем же коэффициентом, но с разными диапазонами.
Например
Предполагая трети, список делится следующим образом:
- Первая треть = 425 имен, от Смита до Альварадо
- Второй третий = 6277 имен, от Гейнера
- Последняя треть = 82,097 имен, от Фрисби до конца
Если бы нам потребовалось, скажем, 1000 имен, мы бы вытянули 334 из верхней трети списка, 333 из второй трети и 333 из последней трети.
Для каждой из третей мы использовали бы подобную формулу, возможно, с большим коэффициентом мощности для первой трети (где были действительно заинтересованы в том, чтобы отдать предпочтение более ранним именам в списке, а также там, где относительные частоты являются более статистически значимыми). Три запроса выбора могут выглядеть следующим образом:
-- Random Drawing of a single Name in top third
-- Power Coef = 12
SELECT [LastName]
FROM [LastNames] as LN
WHERE LN.[Rank]
= 425 - ROUND(POWER(POWER(425.0, 12) * RAND(), 1.0/12), 0)
-- Second third; Power Coef = 7
...
WHERE LN.[Rank]
= (425 + 6277) - ROUND(POWER(POWER(6277.0, 7) * RAND(), 1.0/7), 0)
-- Bottom third; Power Coef = 4
...
WHERE LN.[Rank]
= (425 + 6277 + 82097) - ROUND(POWER(POWER(82097.0, 4) * RAND(), 1.0/4), 0)
Вместо того, чтобы сохранять pdf в качестве ранга, сохраните CDF (сумма всех частот до этого имени, начиная с Aldekirk).
Затем измените ваш выбор, чтобы получить первый LN с рангом больше, чем результат вашей формулы.
Я читаю вопрос как "мне нужно получить поток имен, который будет отражать частоту фамилий из переписи населения США 1990 года"
Возможно, я прочитал вопрос несколько иначе, чем другие предложения, и, хотя ответ был принят, и очень точный ответ, я поделюсь своим опытом с фамилиями переписи.
Я скачал те же данные из переписи 1990 года. Моя цель состояла в том, чтобы подготовить большое количество имен, которые будут отправлены на поисковое тестирование во время тестирования производительности приложения медицинской карты. Я вставил фамилии и процент частоты в таблицу. Я добавил столбец и заполнил его целым числом, которое было произведением "общее количество имен * частота". Данные о частоте переписи не составляли ровно 100%, поэтому мое общее количество имен также было немного меньше, чем требовалось. Я смог исправить число, выбрав случайные имена из списка и увеличивая их количество до тех пор, пока у меня не будет точно необходимого числа, случайное добавленное число никогда не превышало более 0,05% от общего количества в 10 миллионов.
Я сгенерировал 10 миллионов случайных чисел в диапазоне от 1 до 88799. Для каждого случайного числа я выбрал бы это имя из списка и уменьшил счетчик для этого имени. Мой подход состоял в том, чтобы смоделировать раздачу колоды карт, за исключением того, что в моей колоде было гораздо больше разных карт и разное количество каждой карты.
Вы храните фактические частоты с рангами?
Преобразование алгебры из этого принятого ответа в MySQL не является проблемой, если вы знаете, какие значения использовать для n, y будет то, что у вас есть ROUND(88799 * RAND(), 0) а также x0,x1 = 1,88799 Я думаю, хотя я мог бы неправильно понять это. Единственный нестандартный математический оператор, задействованный с точки зрения T-SQL, ^ который просто POWER(x,y) == x^y,