SQL - 5% случайной выборки по группам

Question

У меня есть таблица с примерно 10 миллионами строк и 4 столбцами, без первичного ключа. Данные в столбце 2 3 4 (x2 x3 и x4) сгруппированы по 50 группам, указанным в столбце 1 X1.

Чтобы получить случайную выборку 5% из таблицы, я всегда использовал

SELECT TOP 5 PERCENT *
FROM thistable
ORDER BY NEWID()

Результат возвращает около 500 000 строк. Но некоторые группы получают неравное представление в выборке (относительно их первоначального размера), если выборка выполняется таким образом.

На этот раз, чтобы получить лучшую выборку, я хотел получить 5% выборки из каждой из 50 групп, указанных в столбце X1. Итак, в конце я могу получить случайную выборку из 5% строк в каждой из 50 групп в X1 (вместо 5% всей таблицы).

Как я могу подойти к этой проблеме? Спасибо.

Answer 1

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

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

WITH randomID AS (
    -- First assign a random ID to all rows. This will give us a random order.
    SELECT *, NEWID() as random FROM sourceTable
),
countGroups AS (
    -- Now we add row numbers for each group. So each group will start at 1. We order 
    -- by the random column we generated in the previous expression, so you should get
    -- different results in each execution
    SELECT *, ROW_NUMBER() OVER (PARTITION BY groupcolumn ORDER BY random) AS rowcnt FROM randomID
)
-- Now we get the data
SELECT * 
    FROM countGroups c1
    WHERE rowcnt <= (
        SELECT MAX(rowcnt) / 20 FROM countGroups c2 WHERE c1.groupcolumn = c2.groupcolumn
    )

Два выражения CTE позволяют вам случайным образом упорядочить, а затем подсчитать каждую группу. Окончательный выбор должен быть достаточно простым: для каждой группы выясните, сколько в ней строк, и верните только 5% из них (total_row_count_in_group / 20).