SELECT 中的临时序列 [英] Temporary sequence within a SELECT

问题描述

作为 sql 查询的一部分，我需要连接两个表，而设置数据库的 login 表的人没有为每次登录包含具有唯一 ID 的列.我需要将 login 加入一个表，其中每次登录都将与另一个表的一行或多行对齐，并且我希望能够唯一标识 login 无需依赖用户 ID 和唯一的登录时间戳.

I need to join two tables as part of a sql query, and whoever set up the database's login table did not include a column with a unique id for each login. I need to join login with a table where each login will line up with one or more rows of the other table, and I want to be able to uniquely identify each row from login without having to rely on the user id and the login timestamp being unique.

我想做这样的事情:

SELECT l.*, pp.personpositionid
FROM (SELECT login.*, next_unique_bigint() AS loginid FROM login) l, personposition pp, personpositionstatus pps
WHERE l.personid = pp.personid
AND [...]

我可以使用 random()，但我宁愿让临时唯一的id"是连续的(甚至只是可预测的)而不是随机的.我已经搜索了我想要做的事情，但没有找到我要找的东西.

I could use random(), but I'd rather have the temporary unique "id's" be sequential (or even just predictable) rather than random. I've done a search for what I'm trying to do, but haven't found what I'm looking for.

有没有办法在 SQL 中做到这一点，尤其是 PostgreSQL?我猜有，只是不知道要搜索什么关键字.

Is there a way to do this in SQL, especially PostgreSQL? I'm guessing there is and I just don't know what keywords to search for.

如果您有不同的方式来保持登录记录的限制，而不是为每一行提供一个临时 ID，则可以获得奖励积分.

Bonus points if you have a different way to keep the login records strait than giving each row a temporary id.

这样做的总体目的是获取给定日期范围内的登录计数.每个用户都有一组他们从一个时间点到另一个时间点持有的职位(角色?工作?)，我需要根据用户登录时担任的职位来计算登录次数.每个用户都可以在任何给定时间拥有 1 个或多个职位.如果他们的用户帐户被停用，他们只能拥有0个职位，因此在他们拥有0个职位的时间点自然不会记录登录.

The overall purpose of this is to get a count of logins for a given date range. Each user has a set of Positions (roles? jobs?) that they hold from one point in time to another, and I need to count logins according to what position(s) the user held at the time they logged in. Each user can have 1 or more positions at any given time. They can only have 0 positions if their user account is deactivated, so naturally no logins will be recorded at a point in time when they have 0 positions.

推荐答案

你为什么不添加一个 serial 表的主键列?

Why don't you add a serial primary key column to the table?

ALTER TABLE login ADD column login_id serial;
ALTER TABLE login ADD CONSTRAINT login_pkey PRIMARY KEY(login_id);

第一个操作将重写表并锁定一段时间.然后我会跑

The first operation will rewrite the table and take a lock for some time. I would then run

VACCUM FULL ANALYZE login;

劣质替代方案:row_number() 如由@Joachim 指出.为了获得最佳性能，您可以将 OVER 子句留空:

Inferior alternatives: row_number() as pointed out by @Joachim. For maximum performance you can leave the OVER clause empty:

row_number() OVER () AS rn

旁白:对列别名使用 AS 关键字(而它们只是表别名的噪音).

Aside: use the AS keyword for column aliases (while they are just noise for table aliases).

或者你可以使用ctid 作为主键的替代品.那会更快:

Or you can use the ctid as poor man's surrogate for a primary key. That would be even faster:

详情:
有序序列生成

dba.SE 上的示例:
为多个表连续编号行

Example on dba.SE:
numbering rows consecutively for a number of tables

这篇关于SELECT 中的临时序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！