6

In the following example I have a table foo from which I'd like to pick out at random a row per group.

CREATE TABLE foo (
  line INT
);
INSERT INTO foo (line)
SELECT generate_series(0, 999, 1);

Let's say that I'd like to group by line % 10. I could do this with:

SELECT DISTINCT ON (bin) bin, line
FROM (
    SELECT line, line % 10 AS bin, random() x
    FROM foo
    ORDER BY x
) X

What I'd like to do is get random picks from each bin several times. I had thought I'd be able to do this with generate_series() and LATERAL

SELECT i, line, bin
FROM
(
 SELECT generate_series(1,3) i
) m,
LATERAL
(SELECT DISTINCT ON (bin) bin, line
FROM (
    SELECT line, line % 10 bin, random() x
    FROM foo
    ORDER BY x
) X
ORDER BY bin) Q
ORDER BY bin, i;

However, when I do this in PostgreSQL 9.5 I find I get the same line for a given bin for each iteration i, e.g.,

i;line;bin
1;530;0
2;530;0
3;530;0
1;611;1
2;611;1
3;611;1
...

I'm confused, as I thought the subquery containing the random() would be run differently for each line from the generate_series().

EDIT: I realised that I can achieve the same objective by generating more combinations and choosing from these with

SELECT DISTINCT ON (bin, round) round, bin, line
FROM (
    SELECT line, line % 10 as bin, round
    FROM foo, generate_series(1,3) round
    ORDER BY bin, random()
) X;

So my question is simply why didn't the first way work?

EDIT: The problem appears to be that LATERAL only acts like a for-loop if the subqueries are correlated in some way (thanks to @ypercube's comment). Hence my original approach can be fixed by adding the following small change

SELECT i, line, bin
FROM
(
 SELECT generate_series(1,3) i
) m,
LATERAL
(
SELECT DISTINCT ON (bin) bin, line
FROM (
    SELECT line, line % 10 bin, m.i, random() x -- <NOTE m.i HERE
    FROM foo
    ORDER BY x
) X
ORDER BY bin
LIMIT 3
) Q
ORDER BY bin, i;
  • I've deleted my answer because, even the result is the same, it is not correct in this case. – McNets May 15 '17 at 10:40
  • 1
    Comment here as the other answer was deleted: LATERAL and CROSS JOIN are equivalent if there is no correlation (inside the LATERAL subquery) ... – ypercubeᵀᴹ May 15 '17 at 10:41
  • ... as , LATERAL is equivalent to CROSS JOIN LATERAL (if the precedence differences between , and CROSS JOIN are irrelevant). – ypercubeᵀᴹ May 15 '17 at 10:42
  • 1
    do you want this to be statistically true or actually true? – Evan Carroll May 15 '17 at 16:16
  • 1
    @EvanCarroll I'm generating example data for an assignment for my students, so the sampling doesn't need to be very good. But I very much like your TABLESAMPLE approaches. – beldaz May 15 '17 at 20:01
4

I'd write the query like this, using LIMIT (3) instead of DISTINCT ON.

The generate_series(0, 9) is used to get all the distinct bins. You could use (SELECT DISTINCT line % 10 FROM foo) AS g (bin) instead, if the "bins" are not all the integers from 0 up to 9:

SELECT 
    g.bin, 
    ROW_NUMBER() OVER (PARTITION BY g.bin ORDER BY d.x) AS i,
    d.* 
FROM 
    generate_series(0, 9) AS g (bin), 
  LATERAL 
    ( SELECT f.*, random() x 
      FROM foo AS f 
      WHERE  f.line % 10 = g.bin 
      ORDER BY x 
      LIMIT 3
    ) AS d
ORDER BY 
    bin, x ;

Also, if you don't need the random() number in the output, you could use ORDER BY random() in the subquery and remove x from the select and order by clauses - or replace ORDER BY d.x with ORDER BY d.line.

  • 1
    Good idea about LIMIT, but I'm struggling to compare the results to the output from my original query (mostly because I've been up way too long...). Any chance you can edit it to give columns bin, i, line? Would I need something like row_number() for i? – beldaz May 15 '17 at 10:28
  • 1
    Yes, ROW_NUMBER() could be use. Editing the answer – ypercubeᵀᴹ May 15 '17 at 10:32
  • Your comment to my answer was the thing I really needed. I'll add a note in my question body. Thanks. – beldaz May 15 '17 at 10:49
3

What I'd like to do is get random picks from each bin several times.

There are lots of ways you can solve this problem. Each one introduces more randomness and takes more time.

  1. TABLESAMPLE SYSTEM and tsm_system_rows
  2. TABLESAMPLE BERNOULLI
  3. Creating ad-hoc bins and rolling the dice with statistics.
  4. Creating ad-hoc bins and ordering them randomly and picking.

In most circumstances, TABLEAMPLE SYSTEM and tsm_system_rows is plenty to get a "fair" sampling of the table. It has the added advantage of not having to visit the whole table.

In the event you need a more evenly spaced out sample, TABLESAMPLE BERNOULLI will visit the whole table and select from all of the pages inside.

In the event you want to proceed going ad-hoc, I think this will do you want too.

SELECT *
FROM (
  SELECT dense_rank() OVER (PARTITION BY bin ORDER BY random()), *
  FROM (
    SELECT line % 10 AS bin, line
    FROM foo                          
  ) AS t
) AS t                       
WHERE dense_rank <= 3
ORDER BY line;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.