sql - 限制每个组的行数从连接(不是 1 行)

Question

给定这些表:

TABLE Stores (
 store_id INT,
 store_name VARCHAR,
 etc
);

TABLE Employees (
 employee_id INT,
 store_id INT,
 employee_name VARCHAR,
 currently_employed BOOLEAN,
 etc
);

我想列出每家商店工作时间最长的 15 名员工(假设 employee_id 最低的 15 名员工)，或者如果有 15 名 current_employed= 't'。我想用连接子句来完成。

我发现很多人这样做的例子只 1 行，通常是最小值或最大值(单一雇佣时间最长的员工)，但我基本上想结合一个ORDER BY 和连接内的 LIMIT。可以在此处找到其中一些示例:

• Limit results from joined table to one row
• MySQL returning 1 image for each product

我还找到了逐个商店执行此操作的不错示例(我没有，我有大约 5000 家商店):

• Get top n records for each group of grouped results

我还看到您可以使用 TOP 代替 ORDER BY 和 LIMIT，但不能用于 PostgreSQL。

我认为两个表之间的连接子句不是唯一的(甚至不一定是最好的方法)，如果可以通过员工表中不同的 store_id 工作的话，所以我愿意接受其他方法。之后随时可以加入。

因为我是 SQL 的新手，所以我想要任何可以帮助我理解工作原理的理论背景或额外解释。

Answer 1

row_number()

获取每组前 n 行的一般解决方案是使用窗口函数 row_number():

SELECT *
FROM  (
   SELECT *, row_number() OVER (PARTITION BY store_id ORDER BY employee_id) AS rn
   FROM   employees
   WHERE  currently_employed
   ) e
JOIN   stores s USING (store_id)
WHERE  rn <= 15
ORDER  BY store_id, e.rn;

• PARTITION BY 应该使用 store_id，它保证是唯一的(相对于 store_name)。

• 首先识别 employees 中的行，然后加入stores，这样更便宜。

• 要获得 15 行，请使用 row_number() 而不是 rank()(对于此目的而言是错误的工具)。 (虽然 employee_id 是唯一的，但没有显示出差异。)

LATERAL

自 Postgres 9.3 以来的一种替代方法，通常与匹配索引结合使用时表现(好得多)，尤其是从大表中检索少量选择时。见:

• What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

SELECT s.store_name, e.*
FROM   stores s
CROSS  JOIN LATERAL (
   SELECT *  -- better just the needed columns!
   FROM   employees e
   WHERE  e.store_id = s.store_id
   AND    e.currently_employed
   ORDER  BY e.employee_id
   LIMIT  15
   ) e
-- WHERE ... work with selected stores?
ORDER  BY s.store_name, e.store_id, e.employee_id;

完美的索引应该是这样的部分多列索引:

CREATE INDEX ON employees (store_id, employee_id) WHERE  currently_employed;

相关范例:

• Create unique constraint with null columns

这两个版本都排除了没有现有员工的商店。如果需要，可以通过 LEFT JOIN LATERAL 解决此问题 ...