Stored procedure to subtract from column with same dates-CodePudding

I have the following columns:

2022-05-25T17:31:34 0000    92  7   1
2022-05-25T16:06:46 0000    50  5   9
2022-05-25T13:05:27 0000    91  10  106
2022-05-25T09:17:01 0000    48  4   4
2022-05-25T08:43:05 0000    60  4   2
2022-05-25T06:26:38 0000    24  3   6
2022-05-24T15:14:49 0000    55  12  6
2022-05-24T12:25:35 0000    43  8   2
2022-05-24T11:15:24 0000    66  7   2
2022-05-24T10:45:56 0000    37  15  2
2022-05-23T17:51:09 0000    59  7   1
2022-05-23T17:50:44 0000    47  6   3
2022-05-23T15:48:02 0000    126 7   13
2022-05-23T11:42:26 0000    64  9   9
2022-05-27T06:00:29 0000    3   0   1
2022-05-25T17:31:34 0000    96  7   1
2022-05-25T16:06:46 0000    55  5   9
2022-05-25T13:05:27 0000    99  11  116
2022-05-25T09:17:01 0000    52  4   15
2022-05-25T08:43:05 0000    61  4   2
2022-05-25T06:26:38 0000    26  3   6
2022-05-24T15:14:49 0000    57  13  7
2022-05-24T12:25:35 0000    43  8   2
2022-05-24T11:15:24 0000    66  7   2
2022-05-24T10:45:56 0000    38  15  2
2022-05-23T17:51:09 0000    59  7   1
2022-05-23T17:50:44 0000    47  6   3
2022-05-23T15:48:02 0000    127 7   13

If you look at the 16th row it has the same date as the first row but the number of PostLikes are different (92 and 96) PostComments and PostShares also change. I want to create a stored procedure where when I input the date it will return the difference. For example, if I select the third row with date 2022-05-25T13:05:27 0000 it should give the result:

DateT                           PostLikes        PostComments        PostShares
2022-05-25T13:05:27 0000            7                  1                 10

Now one method of doing this is:

select max(PostLikes) - min(PostLikes) as LikeDifference, max(PostComments) - min(PostComments) as CommentDifference, max(PostShares) - min(PostShares) as ShareDifference
from kpitb.userLikes
where DateT =  "2022-05-25T17:31:34 0000";

But this method is only good if there are two values, the database is going to have several values with the same dates. (Note DateT is VARCHAR) For e.g.

Row 1  2022-05-25T13:05:27 0000     24
Row 2  2022-05-25T13:05:27 0000     34
Row 3  2022-05-25T13:05:27 0000     67

How to find the difference.

Any help would be appreciated.

CodePudding user response：

Using window functions you can find the max id and the previous values and the main query becomes trivial.

with cte as
(select t.*,
         lag(postlikes) over(partition by datet order by id) prevlikes,
         lag(postcomments) over(partition by datet order by id) prevcomments,
         lag(postshares) over(partition by datet order by id) prevshares,
         t1.maxid
from t
join (select max(id) maxid,datet from t group by datet) t1 on t1.datet = t.datet
order by datet,id
) 
select datet,
         case when prevlikes is null then postlikes else postlikes - prevlikes end likes,
       case when prevcomments is null then postcomments else postcomments - prevcomments end comments,
       case when prevshares is null then postshares else postshares - prevshares end shares
from cte
where  id = maxid;

https://dev.mysql.com/doc/refman/8.0/en/window-functions.html