Advertisement
Guest User

Untitled

a guest
Aug 29th, 2015
67
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.66 KB | None | 0 0
  1. CREATE TABLE t (
  2. id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  3. sum_component FLOAT
  4. );
  5. INSERT INTO t (sum_component) VALUES (0.5), (0.6), (0.4), (0.5);
  6.  
  7. SELECT t.id,
  8. @cumulative_sum
  9. FROM t
  10. CROSS JOIN (SELECT @cumulative_sum:=0) a
  11. WHERE (@cumulative_sum:=@cumulative_sum+t.sum_component) > 1.3
  12. ORDER BY id ASC LIMIT 1;
  13.  
  14. SELECT t.id,
  15. @cumulative_sum
  16. FROM t
  17. CROSS JOIN (SELECT @cumulative_sum:=0) a
  18. WHERE (@cumulative_sum:=(@another_variable:=@cumulative_sum)+t.sum_component) > 1.3
  19. ORDER BY id ASC LIMIT 1;
  20.  
  21. 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;
  22. +----+--------------------+
  23. | id | @cumulative_sum |
  24. +----+--------------------+
  25. | 3 | 1.5000000298023224 |
  26. +----+--------------------+
  27. 1 row in set (0.00 sec)
  28.  
  29. MariaDB [a51]> SELECT @another_variable;
  30. +-------------------+
  31. | @another_variable |
  32. +-------------------+
  33. | 1.100000023841858 |
  34. +-------------------+
  35. 1 row in set (0.01 sec)
  36.  
  37. 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;
  38. Empty set (0.18 sec)
  39.  
  40. mysql> SELECT @another_variable;
  41. +-------------------+
  42. | @another_variable |
  43. +-------------------+
  44. | 0 |
  45. +-------------------+
  46. 1 row in set (0.01 sec)
  47.  
  48. 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;
  49. +----+----------------+---------------+
  50. | id | cumulative_sum | sum_component |
  51. +----+----------------+---------------+
  52. | 1 | 0.5 | 0.5 |
  53. | 2 | 0.6 | 0.6 |
  54. | 3 | 0.4 | 0.4 |
  55. | 4 | 0.5 | 0.5 |
  56. +---+----------------+----------------+
  57. 4 rows in set (0.04 sec)
  58.  
  59. mysql> ALTER TABLE t MODIFY sum_component DECIMAL(4,2);
  60. Query OK, 4 rows affected, 2 warnings (0.16 sec)
  61. Records: 4 Duplicates: 0 Warnings: 2
  62.  
  63. 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;
  64. +----+----------------+---------------+
  65. | id | cumulative_sum | sum_component |
  66. +----+----------------+---------------+
  67. | 1 | 0.50 | 0.50 |
  68. | 2 | 1.60 | 0.60 |
  69. | 3 | 2.40 | 0.40 |
  70. | 4 | 2.50 | 0.50 |
  71. +----+----------------+---------------+
  72. 4 rows in set (0.18 sec)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement