Geschichtete Zufallsstichprobe mit BigQuery?

Mit #standardSQL, lassen Sie uns unseren Tisch und einige Statistiken darüber definieren:

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)
)

In diesem Setup:

• subreddit wird unsere Kategorie sein
• Wir wollen nur Subreddits mit mehr als 1000000 Kommentaren

Wenn wir also 1% jeder Kategorie in unserer Stichprobe haben möchten:

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

Oder nehmen wir an, wir wollen ~ 80.000 Proben - aber proportional zu allen Kategorien ausgewählt:

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

Wenn Sie nun die gleiche Anzahl von Proben aus jeder Gruppe erhalten möchten (sagen wir 20.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()< 20000/c
)
GROUP BY 2

Wenn Sie genau 20.000 Elemente aus jeder Kategorie möchten:

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
)

Wenn Sie genau 2% von jeder Gruppe wollen:

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

Wenn Sie diesen letzten Ansatz wünschen, können Sie feststellen, dass er fehlschlägt, wenn Sie tatsächlich Daten herausholen möchten. Eine frühe, LIMITähnlich der größten Gruppengröße, stellt sicher, dass nicht mehr Daten als erforderlich sortiert werden:

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