Question 72336 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL select and compare to previous (next) value

I have the following SQL statement. It selects the visit count (vc) for a particular date (vd)

SELECT count(vid) vc, date(visitwhen) vd
FROM book_visit 
where date(visitwhen) >=date_sub(now(), interval 14 day)
group by vd
order by vd desc

This selects a range of data and the output would look like this:

{
    "data": [
        {
            "vc": 2,
            "vd": "2023-04-25"
        },
        {
            "vc": 1,
            "vd": "2023-04-24"
        },
        {
            "vc": 84,
            "vd": "2023-04-23"
        },
        {
            "vc": 17,
            "vd": "2023-04-21"
        },
        {
            "vc": 26,
            "vd": "2023-04-20"
        },
        {
            "vc": 1,
            "vd": "2023-04-19"
        },
        {
            "vc": 3,
            "vd": "2023-04-17"
        },
        {
            "vc": 18,
            "vd": "2023-04-16"
        },
        ....

What I would like to see is:

  1. If the vc in one vd is larger/smaller than the previous or next vc/vd, I would also like to return a + (or -) or whatever that is indicative of a vc increase / decrease.

I can do this probably at the client side, but I would like to see if it is possible to do so in MySQL.

Tags:

2

Answers


  1. 0

    Try something like this, if your MySQL supports:

    SELECT * FROM (
    SELECT dt, num, IF(@oldnum IS NOT NULL, IF(@oldnum > num, '-', '+'), NULL) AS 'change', @oldnum := num
    FROM test_data
    ORDER BY dt
) t
ORDER BY dt DESC

    This will result in something like this for some test data:

    dt             num  change  @oldnum := num  
----------  ------  ------  ----------------
2023-04-10      15  -                     15
2023-04-09      32  +                     32
2023-04-08      14  +                     14
2023-04-07      13  -                     13
2023-04-06      83  +                     83
2023-04-05      35  -                     35
2023-04-04      63  -                     63
2023-04-03      93  +                     93
2023-04-02      34  -                     34
2023-04-01      59  (NULL)                59

    First order by date – Keep @oldnum = num at the end, so that for the first record, it will be NULL. From 2nd record onward, it will have the value of the previous record. IF should be done before @oldnum = num. IF will decide what is the change in the current record in num as compared to previous record’s num available as oldnum.

    Yes, or, as @user1191247 has suggested, for MySQL 8+: https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

    SELECT dt, num, LAG(num) OVER w AS 'prev_num',
    IF(LAG(num) OVER w IS NOT NULL, IF(num > LAG(num) OVER w, '+', IF(num < LAG(num) OVER w, '-', '=')), NULL) AS 'change'
FROM test_data
WINDOW w AS (ORDER BY dt)
ORDER BY dt DESC;

dt             num  prev_num  change
----------  ------  --------  --------
2023-04-10      15        32  -
2023-04-09      32        14  +
2023-04-08      14        13  +
2023-04-07      13        83  -
2023-04-06      83        35  +
2023-04-05      35        63  -
2023-04-04      63        93  -
2023-04-03      93        34  +
2023-04-02      34        59  -
2023-04-01      59    (NULL)  (NULL)
    Login or Signup to reply.
  2. 0

    Your current date criterion is non-sargable, as the server has to apply the DATE() function to the column value before it can be compared:

    WHERE DATE(visitwhen) >= DATE_SUB(NOW(), INTERVAL 14 DAY)

    should be changed to:

    WHERE visitwhen >= CURRENT_DATE - INTERVAL 13 DAY

    and make sure you have an index on visitwhen.

    You can use the LAG() window function to get the previous row’s value for a given expression:

    SELECT COUNT(vid) vc, DATE(visitwhen) vd,
    CASE
        WHEN COUNT(vid) > LAG(COUNT(vid)) OVER w THEN '+'
        WHEN COUNT(vid) < LAG(COUNT(vid)) OVER w THEN '-'
        WHEN COUNT(vid) = LAG(COUNT(vid)) OVER w THEN 'nc'
    END AS `change`
FROM book_visit 
WHERE visitwhen >= CURRENT_DATE - INTERVAL 13 DAY
GROUP BY vd
WINDOW w AS (ORDER BY DATE(visitwhen))
ORDER BY vd DESC;

    Output:

    vc vd change
    28 2023-04-26 +
    22 2023-04-25 –
    24 2023-04-24 –
    29 2023-04-23 +
    21 2023-04-22 –
    25 2023-04-21 –
    29 2023-04-20 +
    24 2023-04-19 +
    22 2023-04-18 –
    26 2023-04-17 nc
    26 2023-04-16 +
    21 2023-04-15 –
    23 2023-04-14 nc
    23 2023-04-13 NULL
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search