执行联接时,将表列值用作LIMIT [英] Use table column value for LIMIT when performing join
问题描述
在一种情况下,我正在两个表之间执行联接,并且我需要一个表中的值用作联接中子查询的LIMIT因子.假设我有以下[极其简化的]表-
I have a situation where I'm performing a join between two tables, and I need a value from one table to be used as a LIMIT factor for a subquery in the join. Assume I have the following [extremely simplified] tables -
data:
experiment_id | value
--------------|--------
1 | 2.5
1 | 2.6
1 | 4.5
1 | 2.3
1 | 3.5
1 | 2.8
2 | 2.3
2 | 1.2
2 | 1.1
2 | 3.6
2 | 3.8
2 | 4.1
2 | 7.9
2 | 4.2
2 | 1.0
data_clip:
experiment_id | clip_index
--------------|------------
1 | 3
2 | 5
我需要将每个实验的已排序值相加到一个特定的clip_index,该值在实验之间会有所不同.因此,理想情况下,我的结果表将如下所示:
I need to sum each experiment's sorted values up to a certain clip_index, that varies between experiments. So, my result table will ideally look like this:
results:
experiment_id | sum
--------------|-------
1 | 7.6 # => 2.3 + 2.5 + 2.8
2 | 13.0 # => 1.0 + 1.1 + 1.2 + 2.3 + 3.6 + 3.8
通常,我会使用一些客户端脚本(红宝石,python等)进行此计算,但是我想尝试在数据库级别进行此计算.一些虚构的SQL可能看起来像这样(我知道此查询存在种种错误,但希望您能理解这个主意):
Typically, I'd do this calculation with some client side scripting (ruby, python, etc), but I wanted to try doing this on the db level. Some imaginary SQL might look like this (there's all sorts of wrong with this query, I know, but hopefully you get the idea):
SELECT
T0.experiment_id as `id`,
(SELECT SUM(x.value) from
(SELECT value
FROM data
WHERE experiment_id = t0.experiment_id
ORDER BY value
LIMIT t0.clip_index ) as x) AS `sum`
FROM data_clip AS t0
几个问题:
- 必须使用常量(1000、10等)而不是列来定义LIMIT.
- 子查询中的
WHERE
条件由于无法识别子查询外部的t0
表而失败.
- LIMIT must be defined with a constant (1000, 10, etc.), not a column.
- the
WHERE
condition in the subquery fails for not recognizingt0
table, which is external to the sub-query.
我的问题基本上是如何使用SQL来完成两个表之间的变量限制和求和.我曾考虑过使用group_concat
和substring_index
来将每一行的值隔离为clip_index
,但是这是一个问题,即对带数字的字符串("1.2,2.3,3.2"
)进行求和以及服务器对
My question is basically how to accomplish the variable limit and sum between the two tables using mostly SQL. I thought about using group_concat
and substring_index
to isolate the values up to clip_index
for each row, but then there's the issue of summing up the numbered strings ("1.2,2.3,3.2"
) and the server limitation on the size of group_concat
buffer (configurable, but values can be around ~100k per experiment). Any thoughts? Thanks.
推荐答案
我想您只需要在选择的每个值中包含一个行号,然后将结果限制为行数 像这样的东西: (未经测试)
I guess you just need to include a row number with each value selected and limit the results by the number of rows something like this: (not not tested)
SELECT T0.experiment_id as `id`,
(SELECT SUM(x.value) from
(SELECT value,@rownum := @rownum + 1 AS rownum
FROM data
JOIN (SELECT @rownum := 0) r
WHERE experiment_id = t0.experiment_id
ORDER BY value
) AS x
WHERE x,rownum < t0.clip_index
) AS `sum`
FROM data_clip AS t0
请参阅: MySQL-在选择时获取行号
这篇关于执行联接时,将表列值用作LIMIT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!