Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE t (
- id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
- sum_component FLOAT
- );
- INSERT INTO t (sum_component) VALUES (0.5), (0.6), (0.4), (0.5);
- SELECT t.id,
- @cumulative_sum
- FROM t
- CROSS JOIN (SELECT @cumulative_sum:=0) a
- WHERE (@cumulative_sum:=@cumulative_sum+t.sum_component) > 1.3
- ORDER BY id ASC LIMIT 1;
- SELECT t.id,
- @cumulative_sum
- FROM t
- CROSS JOIN (SELECT @cumulative_sum:=0) a
- WHERE (@cumulative_sum:=(@another_variable:=@cumulative_sum)+t.sum_component) > 1.3
- ORDER BY id ASC LIMIT 1;
- MariaDB [a51]> SELECT t.id, @cumulative_sum FROM t CROSS JOIN (SELECT @cumulative_sum:=0) a WHERE (@cumulative_sum:=(@another_variable:=@cumulative_sum)+t.sum_component) > 1.3 ORDER BY id ASC LIMIT 1;
- +----+--------------------+
- | id | @cumulative_sum |
- +----+--------------------+
- | 3 | 1.5000000298023224 |
- +----+--------------------+
- 1 row in set (0.00 sec)
- MariaDB [a51]> SELECT @another_variable;
- +-------------------+
- | @another_variable |
- +-------------------+
- | 1.100000023841858 |
- +-------------------+
- 1 row in set (0.01 sec)
- mysql> SELECT t.id, @cumulative_sum FROM t CROSS JOIN (SELECT @cumulative_sum:=0) a WHERE (@cumulative_sum:=(@another_variable:=@cumulative_sum)+t.sum_component) > 1.3 ORDER BY id ASC LIMIT 1;
- Empty set (0.18 sec)
- mysql> SELECT @another_variable;
- +-------------------+
- | @another_variable |
- +-------------------+
- | 0 |
- +-------------------+
- 1 row in set (0.01 sec)
- mysql> SELECT t.id, (@cumulative_sum:=(@another_variable:=@cumulative_sum)+t.sum_component) AS cumulative_sum, sum_component FROM t CROSS JOIN (SELECT @cumulative_sum:=0) a ORDER BY id ASC;
- +----+----------------+---------------+
- | id | cumulative_sum | sum_component |
- +----+----------------+---------------+
- | 1 | 0.5 | 0.5 |
- | 2 | 0.6 | 0.6 |
- | 3 | 0.4 | 0.4 |
- | 4 | 0.5 | 0.5 |
- +---+----------------+----------------+
- 4 rows in set (0.04 sec)
- mysql> ALTER TABLE t MODIFY sum_component DECIMAL(4,2);
- Query OK, 4 rows affected, 2 warnings (0.16 sec)
- Records: 4 Duplicates: 0 Warnings: 2
- mysql> SELECT t.id, (@cumulative_sum:=(@another_variable:=@cumulative_sum)+t.sum_component) AS cumulative_sum, sum_component FROM t CROSS JOIN (SELECT @cumulative_sum:=0) a ORDER BY id ASC;
- +----+----------------+---------------+
- | id | cumulative_sum | sum_component |
- +----+----------------+---------------+
- | 1 | 0.50 | 0.50 |
- | 2 | 1.60 | 0.60 |
- | 3 | 2.40 | 0.40 |
- | 4 | 2.50 | 0.50 |
- +----+----------------+---------------+
- 4 rows in set (0.18 sec)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement