PostgreSQL SELECT随机值具有唯一值 [英] Postgresql SELECT random with unique value

问题描述

我无法进行一次干净的选择

I am not able to make one clean SELECT

我有带有字段
id，Providerid，deal_name的postgresql表

I have postgresql table with fields id , providerid, deal_name

我要使用唯一的Providerid进行随机选择

I want to make random select with unique providerid

SELECT * FROM deals ORDER BY random() LIMIT 10

如何设置此值以返回10个具有唯一Providerid的结果？

How I can set this to return 10 results with unique providerid's ?

推荐答案

您的问题是否可以改写为：

Can your question be rephrased as:

对于十个随机选择的提供者中的每一个，找到该提供商提供的一项随机选择的交易？

"For each of ten randomly selected providers, find one randomly selected deal offered by that provider" ?

如果是这样，这几乎可以告诉您该怎么做。您需要两层，一层通过以随机选择十个提供商，然后一层通过以选择每个提供商一个随机交易。

If so, that pretty much tells you what to do. You need two layers, one pass to randomly select ten providers, then one pass to select one random deal per provider.

给出虚拟数据：

create table spam ( deal text, provider text );

insert into spam(deal,provider) 
SELECT prov_id||n::text, prov_id FROM (
    SELECT chr(x) AS prov_id from  generate_series(97, 92+26) x) y 
CROSS JOIN generate_series(1,10) n;

select provider FROM spam GROUP BY provider ORDER BY random() LIMIT 10;

以下是可行的方法：

SELECT
 (SELECT deal FROM spam WHERE spam.provider = sel_prov.provider ORDER BY random() LIMIT 1),
 sel_prov.provider
FROM (select provider FROM spam GROUP BY provider ORDER BY random() LIMIT 10) sel_prov;

...差不多只是通过将上述措辞实现为SQL。不知道它是否很快；

... pretty much just by implementing the phrasing above as SQL. Don't know if it's fast; you get the idea.

如果上述措辞不正确，请通过示例数据和示例，或在描述中提供更多详细信息来澄清您的问题。

If the above rephrasing isn't correct, please clarify your question with sample data and an example, or some more detail in your description.

这篇关于PostgreSQL SELECT随机值具有唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！