我有一个包含队列项的表，这些项会定期(500 毫秒)以 10 个为一组进行处理。

select * from tbl_queue order by prio asc;


----------------------------
 client_id |object_key |prio
----------------------------
1          |4711       | 10
1          |2831       | 10
1          |1912       | 10
1          |1913       | 10
1          |1914       | 10
1          |1915       | 10
1          |1922       | 10
1          |1933       | 10
1          |1944       | 10
1          |1955       | 10
1          |1966       | 10
2          |7861       | 10
2          |1234       | 10
3          |5463       | 10
3          |5464       | 10
4          |7341       | 10
4          |7342       | 10
5          |9425       | 10
5          |9426       | 10
5          |9427       | 10

每次我从表中选择时，我都会限制我想要处理的数量:

select * from tbl_queue order by prio asc limit 10;

 client_id |object_key |prio
----------------------------
1          |4711       | 10
1          |2831       | 10
1          |1912       | 10
1          |1913       | 10
1          |1914       | 10
1          |1915       | 10
1          |1922       | 10
1          |1933       | 10
1          |1944       | 10
1          |1955       | 10

但是，我想平等对待每个客户，因此我想要的结果是:

----------------------------
 client_id |object_key |prio
----------------------------    
1          |1913       | 10 
1          |1966       | 10
2          |7861       | 10
2          |1234       | 10
3          |5463       | 10
3          |5464       | 10
4          |7341       | 10
4          |7342       | 10
5          |9425       | 10
5          |9426       | 10

请注意，我不关心选择了哪个 object_key。有两个重要的要求:

1. 如果存在来自不同客户的项目，每个项目 必须选择客户(最好是同等)。
2. 查询必须始终返回 10 行， 除非少于 10 行。

建议的解决方案必须在 Mysql 和 PostgreSQL 中工作，并且从表中插入和选择的成本不能太高。

我已经考虑过添加像 sort_idx 这样的列，并在每一行中插入每个客户的实际行数，所以我可以这样做:

--------------------------------------
 client_id |object_key |prio| sort_idx
--------------------------------------
1          |4711       | 10 | 1
1          |2831       | 10 | 2
1          |1912       | 10 | 3
1          |1913       | 10 | 4
1          |1914       | 10 | 5
1          |1915       | 10 | 6
1          |1922       | 10 | 7
1          |1933       | 10 | 8 
1          |1944       | 10 | 9
1          |1955       | 10 | 10
1          |1966       | 10 | 11
2          |7861       | 10 | 1
2          |1234       | 10 | 2
3          |5463       | 10 | 1
3          |5464       | 10 | 2
4          |7341       | 10 | 1
4          |7342       | 10 | 2
5          |9425       | 10 | 1
5          |9426       | 10 | 2
5          |9427       | 10 | 3

select * from tbl_queue order by prio, sort_index asc limit 10;

--------------------------------------
 client_id |object_key |prio| sort_idx
--------------------------------------
1          |4711       | 10 | 1
2          |7861       | 10 | 1
3          |5463       | 10 | 1
4          |7341       | 10 | 1
5          |9425       | 10 | 1
1          |2831       | 10 | 2     
2          |1234       | 10 | 2
3          |5464       | 10 | 2
4          |7342       | 10 | 2
5          |9426       | 10 | 2 

但是，我对有效计算每个插入的排序索引不太有信心。可能会出现一次插入 20.000 行(或快速插入)的情况。

我不是在寻找引入一些变量或函数的解决方案，我也不想引入数据库触发器，只是普通的 sql。

编辑 13.09.2017 17:13:如果可以在 springs 的 JdbcTemplate 上下文中使用用户定义的变量，我想这将是一个可以接受的解决方案。

但是我希望在数据库层上尽可能简单(即不使用特定于数据库的/独占的命令)

编辑 26.09.2017 11:18 因此，我针对包含大约 40.000 条记录的真实数据集测试了建议的解决方案，但我不得不发现它的性能并不理想。事实上，我在大约一分钟后取消了查询执行。

对该查询的解释给出了以下输出:

explain select c1.client_id,
  c1.object_key,
  c1.prio,
  count(*)-1 as sort_idx
from clients as c1
  left join clients as c2
    on c1.client_id = c2.client_id
      and c1.object_key >= c2.object_key
      and c1.prio >= c2.prio
group by c1.client_id,
        c1.object_key,
        c1.prio
order by
  c1.prio,
  sort_idx
 limit 10;



Limit  (cost=512.53..512.56 rows=10 width=12)
  ->  Sort  (cost=512.53..513.03 rows=200 width=12)
        Sort Key: c1.prio, ((count(*) - 1))
        ->  HashAggregate  (cost=505.71..508.21 rows=200 width=12)
              Group Key: c1.client_id, c1.object_key, c1.prio
              ->  Nested Loop Left Join  (cost=0.15..484.80 rows=2091 width=12)
                    ->  Seq Scan on clients c1  (cost=0.00..29.40 rows=1940 width=12)
                    ->  Index Only Scan using clients_idx on clients c2  (cost=0.15..0.22 rows=1 width=12)
                          Index Cond: ((client_id = c1.client_id) AND (object_key <= c1.object_key) AND (prio <= c1.prio))

我不是解释解释计划的专家，但每当成本数字很高或我看到嵌套循环连接时，我就会感觉到危险。

用密集排序函数给出的语句工作得更快，不幸的是我需要支持 mysql 和 postgres。

最佳答案

通常(MSSQL、Oracle、PostgreSQL等)这个问题可以借助DENSE_RANK函数来解决。 但是因为 MySQL 没有窗口函数，你可以这样做:

select c1.client_id,
  c1.object_key,
  c1.prio,
  count(*)-1 as sort_idx
from clients as c1
  left join clients as c2
    on c1.client_id = c2.client_id
      and c1.object_key >= c2.object_key
      and c1.prio >= c2.prio
group by c1.client_id,
        c1.object_key,
        c1.prio
order by
  c1.prio,
  sort_idx
limit 10;

我做了一个 fiddle ，欢迎大家测试

更新: 还使用 DENSE_RANK 为 PostgreSQL 制定了一个解决方案 - 以防其他人不会受到 MySQL 限制的限制:

select c.client_id,
  c.object_key,
  c.prio,
  DENSE_RANK() OVER   
    (PARTITION BY c.client_id ORDER BY c.object_key) as sort_idx
from clients as c
order by
  c.prio,
  sort_idx
limit 10;

还有 fiddle

更新 2: 为 dense_rank 计算做了一个 MySQL 特定的解决方案，在 40000 条记录上比自连接快得多。但它依赖于顺序，因此您必须以某种方式(可能使用临时表)使用此查询结果来获取按 prio ASC、dense_rank ASC 排序的结果。

SELECT
t2.client_id,
t2.object_key,
t2.prio,
t2.dense_rank
FROM
    (SELECT 
          t1.*,
          @dense:=IF(@prev_client_id=t1.client_id AND @prev_prio=t1.prio, @dense+1, 1) AS dense_rank,
          @prev_client_id:=t1.client_id,
          @prev_prio:=t1.prio
        FROM (select * from clients c1 order by prio, client_id) t1,
             (SELECT @dense:=0, @prev_client_id:=NULL, @prev_prio:=NULL) var1
    ) t2;

还有 fiddle .

关于java - 为列中的每个不同值获取 2 行，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/46198262/