从多个递增序列中选择最大值和

Question

我想从增量值的序列中计算出最大值的和。

对于此数据集：

time_stamp count
1467820429  6 *
1467820428  5
1467820427  4
1467820426  3
1467820416  2
1467820415  1
1467820413  0
1467820412  3 *
1467820411  2
1467820409  1
1467820408  0
1467820405  1 *
1467820404  0
1467820400  5 *

答案=6+3+1+5= 15

如何编写与MySQL兼容的SQL语句以实现

Answer 1

SELECT SUM(a.cnt)
  FROM 
     ( SELECT x.*
            , MIN(y.time_stamp) next
         FROM my_table x
         LEFT
         JOIN my_table y
           ON y.time_stamp > x.time_stamp
        GROUP 
           BY x.time_stamp
     ) a
  LEFT
  JOIN my_table b
    ON b.time_stamp = a.next
   AND b.cnt > a.cnt
 WHERE b.cnt IS NULL;

Answer 2

您可以通过以下方法获得它

mysql> select time_stamp,count,if (count=0,@curRank :=0,@curRank := @curRank + 1) as rank from ff,(SELECT @curRank := 0) r;
+------------+-------+------+
| time_stamp | count | rank |
+------------+-------+------+
| 1467820429 |     6 |    1 |
| 1467820428 |     5 |    2 |
| 1467820427 |     4 |    3 |
| 1467820426 |     3 |    4 |
| 1467820415 |     2 |    5 |
| 1467820415 |     1 |    6 |
| 1467820413 |     0 |    0 |
| 1467820412 |     3 |    1 |
| 1467820411 |     2 |    2 |
| 1467820409 |     1 |    3 |
| 1467820408 |     0 |    0 |
| 1467820405 |     1 |    1 |
| 1467820404 |     0 |    0 |
| 1467820408 |     5 |    1 |
+------------+-------+------+
14 rows in set (0.00 sec)

mysql> SELECT * FROM (select time_stamp,count,if (count=0,@curRank :=0,@curRank := @curRank + 1) as rank from ff,(SELECT @curRank := 0) r) t WHERE rank=1;
+------------+-------+------+
| time_stamp | count | rank |
+------------+-------+------+
| 1467820408 |     5 |    1 |
| 1467820412 |     3 |    1 |
| 1467820429 |     6 |    1 |
| 1467820405 |     1 |    1 |
+------------+-------+------+
4 rows in set (0.00 sec)

    mysql> SELECT sum(count) as total FROM 
(select time_stamp,count,if (count=0,@curRank :=0,@curRank := @curRank + 1) as rank from ff,
(SELECT @curRank := 0) r) t WHERE rank=1;
    +-------+
    | total |
    +-------+
    |    15 |
    +-------+
    1 row in set (0.00 sec)

您可以通过简单的内部查询获得它。

Answer 3

正如我在评论中提到的，在Mysql中没有有效的方法来做到这一点，至少据我所知

尝尝这个

SELECT Sum(CASE 
             WHEN `count` >= prev_cnt THEN `count` 
             ELSE 0 
           END) 
FROM   (SELECT *, 
               IFnull((SELECT `count` 
                       FROM   yourtable b 
                       WHERE  a.`time_stamp` < b.`time_stamp` 
                       ORDER  BY `time_stamp` LIMIT 1), `count`) AS prev_cnt 
        FROM   yourtable a) c