时间戳

首页 > 解决方案 > Postgres - 选择、排序和组合结果

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

问题描述

在我正在编写的应用程序中，当每个用户首​​次向服务器注册时，我会自动为他们生成一个“呼号”。呼号采用格式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);

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

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

users 表还有很多其他内容 - 我已经修剪了细节。

虽然我可以使用基本的 SQL 选择并在服务器端脚本中完成其余的工作，但我怀疑有一种智能 SQL 唯一的方法来执行上述大部分（如果不是全部）以生成唯一的、语法上有效的呼号。我相当有限的 SQL 知识并不能胜任这项任务。如何才能做到这一点？

标签： postgresql

解决方案

编码此代码的最快方法是获取adjectivesX adjectivesX的笛卡尔积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值，则将不等式更改为>而不是>=。

如果您使用数百个adjectivesand creatures，这将无法很好地扩展，但是除非我们将迭代引入解决方案，否则我无法想到如何避免笛卡尔积。

工作小提琴。

推荐阅读