sql - 如何连接条件不相等的表？

Question

我正在尝试使用应用版本详细信息查询所有首次成功安装应用的用户。

所以我尝试查询用户首次成功安装的情况，然后将installed_date与installed_date之前的最新应用版本进行比较。

SELECT DISTINCT user_id, installed_time
WHERE state=SUCCESSFUL
FROM installation
ORDER BY ASC

上面的查询返回所有第一次成功安装的用户。

我如何找到他们首次成功安装时安装的应用程序版本？

我的表格描述如下:

Installation:
user_id,
installed_date,

Version:
version_id,
release_date,
release_code,

Answer 1

我不相信这个查询会按照你说的做:

SELECT DISTINCT user_id, installed_time
WHERE state=SUCCESSFUL
FROM installation
ORDER BY ASC;

除了明显的语法错误外，多次安装的用户会出现多次。这可能是您想要的，但这不是第一次成功安装。

我认为查询应该是:

SELECT i.user_id, MIN(i.installed_time)
FROM installation i
WHERE i.state = 'SUCCESSFUL'
GROUP BY i.user_id;

或者，如果您想要表中的所有列，请使用 DISTINCT ON:

SELECT DISTINCT ON (i.user_id) i.*
FROM installation i
WHERE i.state = 'SUCCESSFUL'
ORDER BY i.user_id, i.installation_time

如果你想要当时的有效版本，你有几种选择。在 Postgres 中，我建议使用横向连接:

SELECT i.*, v.*
FROM (SELECT i.user_id, MIN(i.installed_time) as installed_time
      FROM installation i
      WHERE i.state = 'SUCCESSFUL'
      GROUP BY i.user_id
     ) i LEFT JOIN LATERAL
     (SELECT v.*
      FROM versions v
      WHERE v.release_date <= i.installed_time
      ORDER BY v.release_date DESC
      FETCH FIRST 1 ROW ONLY
     ) v
     ON 1=1;