Geek asks and answers

PostgeSQL: a random choice?

I am working on PostgeSQL with PHP.

Is it possible to select a certain number of random values ​​in the FROM table column WHERE clause

instead

select column FROM table WHERE condition

then convert them to an array and use array_rand()?

(I do not want to use this path, because I will have millions of rows and first select all the values, and it array_rand()will probably take a lot of time.)

Let's say I have a table like this:

   name    |   items
-----------+------------
    Ben    | {dd,ab,aa}  
-----------+------------
   David   |  {dd,aa}  
-----------+------------
   Bryan   | {aa,ab,cd}
-----------+------------
    Glen   |    {cd}
-----------+------------
   Edward  |   {aa,cd}
-----------+------------
   Aaron   |  {dd,aa}
-----------+------------
  ..... (many many more)

Update:

And I need to select 10 random values ​​in a column (or basically 10 random rows) that match the condition (in this case it will @> ARRAY[aa]) without sequentially scanning the table or something that is time consuming.

order by random() , , .

+3
2

, ​​. .

@> ARRAY[aa], ( ).

CREATE TABLE tbl_pick (rn serial, id int, PRIMARY KEY (rn, id);

INSERT INTO tbl_pick (id)
SELECT id FROM tbl
WHERE  items @> ARRAY[aa];

, :

SELECT *
FROM  (
    SELECT 1 + floor(random() * <insert_count_plus_a_bit_here>)::integer AS rn
    FROM   generate_series(1, 20) g
    GROUP  BY 1                     -- trim duplicates
    ) r
JOIN   tbl_pick USING (rn)
JOIN   tbl USING (id)
LIMIT  10;                          -- trim surplus

, , () ~ 10 .

, tbl_pick () INSERT/DELETE/UPDATE tbl. / ( ) tbl_pick, - . TRUNCATE INSERT. .

UPDATE DELETE tbl_pick ON UPDATE CASCADE ON DELETE CASCADE. AFTER INSERT . , .

tbl_pick , .

, "", , tbl_pick ( fk ) , ( ). . "" , UPDATE. TRUE () tbl, FALSE .

+1

PostgreSQL order by random(), , :

select name
from table
where items @>ARRAY['aa']
order by random()
limit 10;
+5

More articles:


All Articles