6

The following returns a 10% sample of the A and X columns stratified by the values of X. 

  select A, X from(
  select A, 
      count(*) over (partition by X) as cnt, 
      rank() over (partition by X order by rand()) as rnk
      from my_table) table 
  where rnk <= cnt*0.1

In other words, if X takes the values [X0, X1] it returns the union of:

  • 10% of the the rows where X = X0
  • 10% of the the rows where X = X1

How can I stratify my query by values of tuples for several columns (e.g. X, Y)?

For example, if X takes values [X0, X1] and Y takes values [Y0, Y1], I would like to get a sample that is the union of:

  • 10% of the the rows where X = X0 and Y=Y0
  • 10% of the the rows where X = X0 and Y=Y1
  • 10% of the the rows where X = X1 and Y=Y0
  • 10% of the the rows where X = X1 and Y=Y1
Amelio Vazquez-Reina
  • 91,494
  • 132
  • 359
  • 564

1 Answers1

2

I'd use your method above, but use a hash of the columns you'd like to consider.

Jim Murphy
  • 333
  • 1
  • 2
  • 9