Стратифицированная случайная выборка с помощью BigQuery?

С #standardSQL давайте определим нашу таблицу и некоторую статистику по ней:

WITH table AS (
  SELECT *, subreddit category
  FROM `fh-bigquery.reddit_comments.2018_09` a
), table_stats AS (
  SELECT *, SUM(c) OVER() total 
  FROM (
    SELECT category, COUNT(*) c 
    FROM table
    GROUP BY 1 
    HAVING c>1000000)
)

В этой настройке:

• subreddit будет нашей категорией
• нам нужны только субреддиты с более чем 1000000 комментариями

Итак, если мы хотим 1% каждой категории в нашей выборке:

SELECT COUNT(*) samples, category, ROUND(100*COUNT(*)/MAX(c),2) percentage
FROM (
  SELECT id, category, c  
  FROM table a
  JOIN table_stats b
  USING(category)
  WHERE RAND()< 1/100 
)
GROUP BY 2

Или, скажем, мы хотим ~ 80 000 сэмплов, но выбираемых пропорционально по всем категориям:

SELECT COUNT(*) samples, category, ROUND(100*COUNT(*)/MAX(c),2) percentage
FROM (
  SELECT id, category, c  
  FROM table a
  JOIN table_stats b
  USING(category)
  WHERE RAND()< 80000/total
)
GROUP BY 2

Теперь, если вы хотите получить ~ одинаковое количество образцов из каждой группы (скажем, 20000):

SELECT COUNT(*) samples, category, ROUND(100*COUNT(*)/MAX(c),2) percentage
FROM (
  SELECT id, category, c  
  FROM table a
  JOIN table_stats b
  USING(category)
  WHERE RAND()< 20000/c
)
GROUP BY 2

Если вам нужно ровно 20 000 элементов из каждой категории:

SELECT ARRAY_LENGTH(cat_samples) samples, category, ROUND(100*ARRAY_LENGTH(cat_samples)/c,2) percentage
FROM (
  SELECT ARRAY_AGG(a ORDER BY RAND() LIMIT 20000) cat_samples, category, ANY_VALUE(c) c
  FROM table a
  JOIN table_stats b
  USING(category)
  GROUP BY category
)

Если вы хотите ровно 2% от каждой группы:

SELECT COUNT(*) samples, sample.category, ROUND(100*COUNT(*)/ANY_VALUE(c),2) percentage
FROM (
  SELECT ARRAY_AGG(a ORDER BY RAND()) cat_samples, category, ANY_VALUE(c) c
  FROM table a
  JOIN table_stats b
  USING(category)
  GROUP BY category
), UNNEST(cat_samples) sample WITH OFFSET off
WHERE off<0.02*c
GROUP BY 2

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

SELECT sample.*
FROM (
  SELECT ARRAY_AGG(a ORDER BY RAND() LIMIT 105000) cat_samples, category, ANY_VALUE(c) c
  FROM table a
  JOIN table_stats b
  USING(category)
  GROUP BY category
), UNNEST(cat_samples) sample WITH OFFSET off
WHERE off<0.02*c

Я думаю, что самый простой способ получить пропорциональную стратифицированную выборку - это упорядочить данные по категориям и сделать «n-ю» выборку данных. Для 10% выборки вам нужны каждые 10 строк.

Это выглядит так:

select t.*
from (select t.*,
             row_number() over (order by category order by rand()) as seqnum
      from t
     ) t
where seqnum % 10 = 1;

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

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

select t.*
from (select t.*,
             row_number() over (partition by category order by rand()) as seqnum
      from t
     ) t
where seqnum <= 100;

Примечание. Это не гарантирует, что в каждой категории существует 100 строк. Он берет все строки для более мелких категорий и случайную выборку для более крупных категорий.

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