问题描述
作为 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 中的临时序列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!