# PostgreSQL randomising combinations with LATERAL

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. May 15, 2017 at 10:40
• Comment here as the other answer was deleted: `LATERAL` and `CROSS JOIN` are equivalent if there is no correlation (inside the LATERAL subquery) ... May 15, 2017 at 10:41
• ... as `, LATERAL` is equivalent to `CROSS JOIN LATERAL` (if the precedence differences between `,` and `CROSS JOIN` are irrelevant). May 15, 2017 at 10:42
• do you want this to be statistically true or actually true? May 15, 2017 at 16:16
• @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. May 15, 2017 at 20:01

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`.

• 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`? May 15, 2017 at 10:28
• Yes, `ROW_NUMBER()` could be use. Editing the answer May 15, 2017 at 10:32
• Your comment to my answer was the thing I really needed. I'll add a note in my question body. Thanks. May 15, 2017 at 10:49

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;
``````