I have a table that is roughly:
我有一张大致的表格:
id | category | link | caption | image
My goal is to fetch a random row from each distinct category in the table, for all the categories in the table. The plan is to then assign each row to a variable for its respective category.
我的目标是从表中的每个不同类别中获取表中所有类别的随机行。计划是将每一行分配给其各自类别的变量。
Right now I'm using multiple SELECT statements resembling:
现在我正在使用类似的多个SELECT语句:
SELECT link, caption, image FROM table WHERE category='whatever' ORDER BY RANDOM() LIMIT 1
SELECT链接,标题,图像FROM表WHERE category ='whatever'ORDER BY RANDOM()LIMIT 1
But this seems inelegant and creates more trips to the DB, which is expensive.
但这看起来不那么优雅,并且创建了更多的数据库,这是昂贵的。
I'm pretty sure there's a way to do this with window functions in Postgres, but I have no experience with them and I'm not entirely sure how to use one to get what I want.
我很确定有一种方法可以在Postgres中使用窗口函数来实现这一点,但是我对它们没有经验,我不完全确定如何使用它来获得我想要的东西。
Thanks for any help!
谢谢你的帮助!
1 个解决方案
#1
13
Try something like:
尝试以下方法:
SELECT DISTINCT ON (category) *
FROM table
ORDER BY category, random();
Or with window functions:
或者使用窗口功能:
SELECT *
FROM (
SELECT *, row_number() OVER (PARTITION BY category ORDER BY random()) as rn
FROM table ) sub
WHERE rn = 1;
#1
13
Try something like:
尝试以下方法:
SELECT DISTINCT ON (category) *
FROM table
ORDER BY category, random();
Or with window functions:
或者使用窗口功能:
SELECT *
FROM (
SELECT *, row_number() OVER (PARTITION BY category ORDER BY random()) as rn
FROM table ) sub
WHERE rn = 1;