pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedselecting random row values in postgres
6+ messages / 4 participants
[nested] [flat]
* selecting random row values in postgres
@ 2007-02-23 19:29 Sumeet <asumeet@gmail.com>
2007-02-23 19:42 ` Re: selecting random row values in postgres Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Sumeet @ 2007-02-23 19:29 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I'm trying to write a query to select random values from a set of 'GROUP
BY'
....see the scenario below to understand the problem here (the actual
problem cannot be discussed here so i'm taking an example scenario)
Assume there is a table
id | name | year_of_birth
query: I want to select for each year_of_birth a random name.
--> so i do a group by year_of_birth, now i have a set of names, is there
any function to select just one name from these set of names.
The current approach i'm using to solve this problem is
1) getting these names in a single string using a custom function
'group_concat'
2) Convert the single string into an array
3) use postgresql random function to generate a random number
4) us the random number to select a element from the array previously
created.
The solution is there but it's kinda hack, is there any other better way of
solving this problem.
Thanks,
Sumeet
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: selecting random row values in postgres
2007-02-23 19:29 selecting random row values in postgres Sumeet <asumeet@gmail.com>
@ 2007-02-23 19:42 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2007-02-23 19:56 ` Re: selecting random row values in postgres Sumeet <asumeet@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Rajesh Kumar Mallah @ 2007-02-23 19:42 UTC (permalink / raw)
To: Sumeet <asumeet@gmail.com>; +Cc: pgsql-sql
On 2/24/07, Sumeet <asumeet@gmail.com> wrote:
> Hi all,
>
> I'm trying to write a query to select random values from a set of 'GROUP
> BY'
> ....see the scenario below to understand the problem here (the actual
> problem cannot be discussed here so i'm taking an example scenario)
>
> Assume there is a table
>
> id | name | year_of_birth
>
> query: I want to select for each year_of_birth a random name.
Dear Sumeet
postgresql DISTINCT ON may be of help , but its not standard sql.
regds
mallah.
tradein_clients=> SELECT * from temp.test;
+----+------+-----+
| id | name | yob |
+----+------+-----+
| 1 | A | 2 |
| 2 | B | 2 |
| 3 | C | 2 |
| 4 | D | 1 |
| 5 | E | 1 |
| 6 | F | 1 |
+----+------+-----+
(6 rows)
tradein_clients=> SELECT distinct on (yob) id,name,yob from temp.test order
by yob,random();
+----+------+-----+
| id | name | yob |
+----+------+-----+
| 5 | E | 1 |
| 1 | A | 2 |
+----+------+-----+
(2 rows)
tradein_clients=> SELECT distinct on (yob) id,name,yob from temp.test order
by yob,random();
+----+------+-----+
| id | name | yob |
+----+------+-----+
| 4 | D | 1 |
| 1 | A | 2 |
+----+------+-----+
(2 rows)
>
> --> so i do a group by year_of_birth, now i have a set of names, is there
> any function to select just one name from these set of names.
> The current approach i'm using to solve this problem is
>
> 1) getting these names in a single string using a custom function
> 'group_concat'
> 2) Convert the single string into an array
> 3) use postgresql random function to generate a random number
> 4) us the random number to select a element from the array previously
> created.
>
> The solution is there but it's kinda hack, is there any other better way
of
> solving this problem.
>
>
> Thanks,
> Sumeet
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: selecting random row values in postgres
2007-02-23 19:29 selecting random row values in postgres Sumeet <asumeet@gmail.com>
2007-02-23 19:42 ` Re: selecting random row values in postgres Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
@ 2007-02-23 19:56 ` Sumeet <asumeet@gmail.com>
2007-02-23 20:07 ` Re: selecting random row values in postgres Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2007-02-23 20:45 ` Re: selecting random row values in postgres Tommy Gildseth <tommy@gildseth.com>
0 siblings, 2 replies; 6+ messages in thread
From: Sumeet @ 2007-02-23 19:56 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; +Cc: pgsql-sql
Thanks Buddy, really appreciate ur help on this
....problem solved...
Is there any way this query can be optimized...i'm running it on a huge
table with joins
- Sumeet
On 2/23/07, Rajesh Kumar Mallah <mallah.rajesh@gmail.com> wrote:
>
>
>
> On 2/24/07, Sumeet <asumeet@gmail.com> wrote:
> > Hi all,
> >
> > I'm trying to write a query to select random values from a set of
> 'GROUP
> > BY'
> > ....see the scenario below to understand the problem here (the actual
> > problem cannot be discussed here so i'm taking an example scenario)
> >
> > Assume there is a table
> >
> > id | name | year_of_birth
> >
> > query: I want to select for each year_of_birth a random name.
>
> Dear Sumeet
>
> postgresql DISTINCT ON may be of help , but its not standard sql.
>
> regds
> mallah.
>
> tradein_clients=> SELECT * from temp.test;
> +----+------+-----+
> | id | name | yob |
> +----+------+-----+
> | 1 | A | 2 |
> | 2 | B | 2 |
> | 3 | C | 2 |
> | 4 | D | 1 |
> | 5 | E | 1 |
> | 6 | F | 1 |
> +----+------+-----+
> (6 rows)
>
> tradein_clients=> SELECT distinct on (yob) id,name,yob from temp.testorder by yob,random();
> +----+------+-----+
> | id | name | yob |
> +----+------+-----+
> | 5 | E | 1 |
> | 1 | A | 2 |
> +----+------+-----+
> (2 rows)
>
> tradein_clients=> SELECT distinct on (yob) id,name,yob from temp.testorder by yob,random();
> +----+------+-----+
> | id | name | yob |
> +----+------+-----+
> | 4 | D | 1 |
> | 1 | A | 2 |
> +----+------+-----+
> (2 rows)
>
>
>
>
> >
> > --> so i do a group by year_of_birth, now i have a set of names, is
> there
> > any function to select just one name from these set of names.
> > The current approach i'm using to solve this problem is
> >
> > 1) getting these names in a single string using a custom function
> > 'group_concat'
> > 2) Convert the single string into an array
> > 3) use postgresql random function to generate a random number
> > 4) us the random number to select a element from the array previously
> > created.
> >
> > The solution is there but it's kinda hack, is there any other better way
> of
> > solving this problem.
> >
> >
> > Thanks,
> > Sumeet
>
--
Thanks,
Sumeet Ambre
Master of Information Science Candidate,
Indiana University.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: selecting random row values in postgres
2007-02-23 19:29 selecting random row values in postgres Sumeet <asumeet@gmail.com>
2007-02-23 19:42 ` Re: selecting random row values in postgres Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2007-02-23 19:56 ` Re: selecting random row values in postgres Sumeet <asumeet@gmail.com>
@ 2007-02-23 20:07 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Rajesh Kumar Mallah @ 2007-02-23 20:07 UTC (permalink / raw)
To: Sumeet <asumeet@gmail.com>; +Cc: pgsql-sql
On 2/24/07, Sumeet <asumeet@gmail.com> wrote:
>
> got it....I just figured out that i dont need the ORDER BY clause even the
> first row selected by the 'DISTINCT ON' would solve the problem.
Dear Sumeet,
if order by is not done there is no certainty about which row gets
selected. usually same row keeps getting selected. but if you want
a really random rows to come order by is required.
if you need certainty about the particular row being selected
order by a non random() column is required.
regds
mallah.
Thanks for all you help
> -Sumeet.
>
> On 2/23/07, Sumeet <asumeet@gmail.com> wrote:
> >
> > Thanks Buddy, really appreciate ur help on this
> >
> > ....problem solved...
> >
> > Is there any way this query can be optimized...i'm running it on a huge
> > table with joins
> >
> > - Sumeet
> >
> >
> > On 2/23/07, Rajesh Kumar Mallah <mallah.rajesh@gmail.com> wrote:
> > >
> > >
> > >
> > > On 2/24/07, Sumeet <asumeet@gmail.com> wrote:
> > > > Hi all,
> > > >
> > > > I'm trying to write a query to select random values from a set of
> > > 'GROUP
> > > > BY'
> > > > ....see the scenario below to understand the problem here (the
> > > actual
> > > > problem cannot be discussed here so i'm taking an example scenario)
> > > >
> > > > Assume there is a table
> > > >
> > > > id | name | year_of_birth
> > > >
> > > > query: I want to select for each year_of_birth a random name.
> > >
> > > Dear Sumeet
> > >
> > > postgresql DISTINCT ON may be of help , but its not standard sql.
> > >
> > > regds
> > > mallah.
> > >
> > > tradein_clients=> SELECT * from temp.test;
> > > +----+------+-----+
> > > | id | name | yob |
> > > +----+------+-----+
> > > | 1 | A | 2 |
> > > | 2 | B | 2 |
> > > | 3 | C | 2 |
> > > | 4 | D | 1 |
> > > | 5 | E | 1 |
> > > | 6 | F | 1 |
> > > +----+------+-----+
> > > (6 rows)
> > >
> > > tradein_clients=> SELECT distinct on (yob) id,name,yob from temp.testorder by yob,random();
> > > +----+------+-----+
> > > | id | name | yob |
> > > +----+------+-----+
> > > | 5 | E | 1 |
> > > | 1 | A | 2 |
> > > +----+------+-----+
> > > (2 rows)
> > >
> > > tradein_clients=> SELECT distinct on (yob) id,name,yob from temp.testorder by yob,random();
> > > +----+------+-----+
> > > | id | name | yob |
> > > +----+------+-----+
> > > | 4 | D | 1 |
> > > | 1 | A | 2 |
> > > +----+------+-----+
> > > (2 rows)
> > >
> > >
> > >
> > >
> > > >
> > > > --> so i do a group by year_of_birth, now i have a set of names, is
> > > there
> > > > any function to select just one name from these set of names.
> > > > The current approach i'm using to solve this problem is
> > > >
> > > > 1) getting these names in a single string using a custom function
> > > > 'group_concat'
> > > > 2) Convert the single string into an array
> > > > 3) use postgresql random function to generate a random number
> > > > 4) us the random number to select a element from the array
> > > previously
> > > > created.
> > > >
> > > > The solution is there but it's kinda hack, is there any other better
> > > way of
> > > > solving this problem.
> > > >
> > > >
> > > > Thanks,
> > > > Sumeet
> > >
> >
> >
> >
> > --
> > Thanks,
> > Sumeet Ambre
> > Master of Information Science Candidate,
> > Indiana University.
>
>
>
>
> --
> Thanks,
> Sumeet Ambre
> Master of Information Science Candidate,
> Indiana University.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: selecting random row values in postgres
2007-02-23 19:29 selecting random row values in postgres Sumeet <asumeet@gmail.com>
2007-02-23 19:42 ` Re: selecting random row values in postgres Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2007-02-23 19:56 ` Re: selecting random row values in postgres Sumeet <asumeet@gmail.com>
@ 2007-02-23 20:45 ` Tommy Gildseth <tommy@gildseth.com>
2007-02-24 03:33 ` Re: selecting random row values in postgres Geoff Tolley <geoff@polimetrix.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tommy Gildseth @ 2007-02-23 20:45 UTC (permalink / raw)
To: Sumeet <asumeet@gmail.com>; +Cc: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; pgsql-sql
Sumeet wrote:
> Thanks Buddy, really appreciate ur help on this
>
> ....problem solved...
>
> Is there any way this query can be optimized...i'm running it on a
> huge table with joins
ORDER BY rand() is rather slow on large datasets, since the db has to
actually generate a random value for each row in the table, before being
able use it to sort by. Preferable ways to do this include f.ex:
SELECT max(id) FROM table;
SELECT * FROM table WHERE id > [rand value between 0 and max(id)] ORDER
BY id LIMIT 1;
This means you need to execute 2 queries, and it can also be a good idea
to somehow cache the number of rows/largest ID of the table, for quicker
performence.
You can find an interesting discussion on this topic at
http://thedailywtf.com/Comments/Finding_Random_Rows.aspx (yeah, I know.
thedayilywtf.com isn't normally what I'd use as a reference for anything
:-) )
--
Tommy
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: selecting random row values in postgres
2007-02-23 19:29 selecting random row values in postgres Sumeet <asumeet@gmail.com>
2007-02-23 19:42 ` Re: selecting random row values in postgres Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2007-02-23 19:56 ` Re: selecting random row values in postgres Sumeet <asumeet@gmail.com>
2007-02-23 20:45 ` Re: selecting random row values in postgres Tommy Gildseth <tommy@gildseth.com>
@ 2007-02-24 03:33 ` Geoff Tolley <geoff@polimetrix.com>
0 siblings, 0 replies; 6+ messages in thread
From: Geoff Tolley @ 2007-02-24 03:33 UTC (permalink / raw)
To: Tommy Gildseth <tommy@gildseth.com>; +Cc: Sumeet <asumeet@gmail.com>; Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; pgsql-sql
Tommy Gildseth wrote:
> Sumeet wrote:
>> Thanks Buddy, really appreciate ur help on this
>>
>> ....problem solved...
>>
>> Is there any way this query can be optimized...i'm running it on a
>> huge table with joins
>
> ORDER BY rand() is rather slow on large datasets, since the db has to
> actually generate a random value for each row in the table, before being
> able use it to sort by. Preferable ways to do this include f.ex:
> SELECT max(id) FROM table;
> SELECT * FROM table WHERE id > [rand value between 0 and max(id)] ORDER
> BY id LIMIT 1;
I'd have thought that in most cases the slowness would be due more to the
sort than the cost of generating the random numbers. Your pair of queries
neatly avoid doing the sort, but it does raise the question, does it matter
if not all rows have the same chance of being picked? As an extreme example
in the above, if there are id's 1-100 and id = 100000 in the table, the
last one will almost always be picked.
I wonder if the random aggregate that Josh Berkus wrote could be adapted to
return a record type rather than a random value of a single column? Its big
advantage is that it requires no sorting and all entries are equiprobable.
Unfortunately I'm a bit short of time at this end of a Friday to do much
but give you the URL:
http://www.powerpostgresql.com/Random_Aggregate
HTH,
Geoff
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-02-24 03:33 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-23 19:29 selecting random row values in postgres Sumeet <asumeet@gmail.com>
2007-02-23 19:42 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2007-02-23 19:56 ` Sumeet <asumeet@gmail.com>
2007-02-23 20:07 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2007-02-23 20:45 ` Tommy Gildseth <tommy@gildseth.com>
2007-02-24 03:33 ` Geoff Tolley <geoff@polimetrix.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox