postgresql - Postgres - 选择、排序和组合结果

Question

在我正在编写的应用程序中，当每个用户第一次向服务器注册时，我会自动为每个用户生成一个“呼号”。呼号的格式为 adjective adjective creature .我从中选择这些值的底层 Postgres 表如下

CREATE TABLE adjectives ("adj" character varying(16) NOT NULL,"quality" smallint NOT NULL,
CONSTRAINT "adjectives_adj_key" UNIQUE ("adj")) WITH (oids = false);

INSERT INTO adjectives (adj,quality) VALUES ('bold',1),('witty',1),('red',5),('old',3);

CREATE TABLE creatures ("creature" character varying(16) NOT NULL,"quality" smallint NOT NULL,
CONSTRAINT "creatures_creature_key" UNIQUE ("creature")) WITH (oids = false);

INSERT INTO creatures (creature,quality) VALUES ('tiger',1),('dog',1),('sparrow',5), 
('dolphin',3);

生成新呼号时，我需要执行以下操作

从各自的表中随机选择两个形容词和一个生物

按质量升序排列形容词。为了尊重英语语法规则，我需要正确的形容词顺序。 Big Red Bull ，例如是正确的。 Red Big Bull不是。

我需要检查生成的呼号在下面显示的用户表中不存在

CREATE TABLE users ("user" character varying(16) NOT NULL,"callsign CHARACTER VARYING(32) NOT NULL);

用户表还有很多其他东西 - 我已经修剪了细节。
虽然我可以使用基本的 SQL 选择并在服务器端脚本中完成其余的工作，但我怀疑有一种智能 SQL 唯一的方法可以完成上述大部分(如果不是全部)以生成唯一的、语法上有效的呼号。我对 SQL 的知识相当有限，无法胜任这项任务。如何才能做到这一点？

Answer 1

对此进行编码的最快方法是获得 adjectives 的笛卡尔积X adjectives X creatures ，删除已经使用过的，然后从列表中随机选择一个:

select concat(
         a1.adj, ' ',
         a2.adj, ' ',
         c.creature
       )
  from adjectives a1
       join adjectives a2
         on a2.quality >= a1.quality
       cross join creatures c
 where not exists (select 1 
                     from users
                    where callsign = concat(
                                       a1.adj, ' ',
                                       a2.adj, ' ',
                                       c.creature))
 order by random()
 limit 1
;
                  

如果您不想允许翻倍 adjective值，然后将不等式更改为 >而不是 >= .
如果您进入数百个 adjectives，这将无法很好地扩展。和 creatures ，但除非我们将迭代引入解决方案，否则我想不出如何避免笛卡尔积。
Working fiddle.