CODEWITHSUNDEEP
mysqlsql
IT Edemy
IT Edemy

Reputation: 81

how to merge next row into previous row in mysql?

I want to calculate value between start_time and end_time but I don't know how to right sql statement to do that.

This is my table.

enter image description here

Upvotes: 0

Views: 635

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You can do this using window functions. However, you need to include an order by as part of the lead() if you want the results to really work. That is, there are no guarantees unless you have the order by there.

select start_time, end_time
from (select start_time, lead(end_time) over (order by usage_created) as end_time
      from times
     ) t
where start_time is not null and end_time is not null;

Note that this assumes that the rows are interleaved, as in your sample data. You have a harder problem if this is not true.

Upvotes: 0

Schwern
Schwern

Reputation: 165218

If you're using MySQL 8 you can use the extremely useful window functions in particular lead to get a value from the next row.

select start_time, lead(end_time, 1) over() as end_time
from times
order by usage_created;

+---------------------+--------------------------+
| start_time          | end_time                 |
+---------------------+--------------------------+
| 2019-07-15 04:07:55 | 2019-07-15 04:08:23      |
| NULL                | NULL                     |
| 2019-07-15 04:08:31 | 2019-07-15 04:12:07      |
| NULL                | NULL                     |
| 2019-07-15 04:12:20 | 2019-07-15 04:12:28      |
| NULL                | NULL                     |
| 2019-07-15 04:11:14 | NULL                     |
+---------------------+--------------------------+

Then put that into a subquery to filter out the null rows.

select start_time, end_time
from (
    select start_time, lead(end_time, 1) over() as end_time
    from times
    order by usage_created
) t
where start_time is not null
  and end_time is not null;

+---------------------+---------------------+
| start_time          | end_time            |
+---------------------+---------------------+
| 2019-07-15 04:07:55 | 2019-07-15 04:08:23 |
| 2019-07-15 04:08:31 | 2019-07-15 04:12:07 |
| 2019-07-15 04:12:20 | 2019-07-15 04:12:28 |
+---------------------+---------------------+
3 rows in set (0.00 sec)

Upvotes: 2

DarkRob
DarkRob

Reputation: 3833

Try this...

    ; with cte as (
    select row_number() over (order by usage_created) AS sno, usage_created from tab )
    , ctstart as (
    select row_number() over (order by sno) as Slno, usage_created as Start_date from cte where slno%2=1)
    , ctend as (
    select row_number() over (order by sno) as Slno, usage_created as end_date from cte where slno%2=0)
    select ctstart.Slno,
    Datediff(second,  ctend.endate , ctstart.startdate) as diff     ---- here use your parameter in Datediff as per your need
    from ctstart inner join ctend on ctstart.Slno=ctend.Slno

Upvotes: 0

Related Questions