0

I have a dataset as in this example :

id | product_id  |        date       |  weight
1  |    454      |2019-06-26 16:08:45|   900
2  |    454      |2019-06-27 13:24:16|   900
3  |    454      |2019-06-28 10:53:42|   899
4  |    352      |2018-04-18 10:53:42|   124
5  |    352      |2018-04-19 15:26:51|   124
6  |    112      |2019-12-08 11:44:01|   065
7  |    375      |2020-03-15 08:23:43|   483
8  |    375      |2020-03-15 18:07:33|   496
9  |    375      |2020-03-16 14:32:24|   496

And I would like to get only the rows that have a weight different from the previous one or different from the next one. In the case of the example the expected output is :

id | product_id  |        date       |  weight
2  |    454      |2019-06-27 13:24:16|   900
3  |    454      |2019-06-28 10:53:42|   899
7  |    375      |2020-03-15 08:23:43|   483
8  |    375      |2020-03-15 18:07:33|   496

However, I have only reading permissions on this database, so the LAG() function does not work. What other options do I have?

7
  • 1
    Are you using the MySQL version that supports window functions?
    – mustaccio
    Jun 9 '20 at 15:39
  • 2
    What is the output from SELECT version(); from within MySQL Workbench?
    – Vérace
    Jun 9 '20 at 15:54
  • @mustaccio I think I can't use window functions such as lag or lead... di you have any specific functino in mind? Jun 10 '20 at 7:19
  • @Vérace the output is 5.7.24 Jun 10 '20 at 7:20
  • 1
    I would strongly advise you to upgrade to version 8 of MySQL - Oracle/MySQL have added a shedload of really really useful functionality recently (CHECK constraints, Common Table Expressions, Window functions, JSON). You really are missing out by using 5.7! MySQL 8 is now at version 20, time enough for any (ahem...) issues with the new functionality to have been (largely) ironed out! Window functions are made for this sort of problem.
    – Vérace
    Jun 10 '20 at 7:35
1
SELECT id
    ,product_id 
    ,date
    ,weight
FROM
(
SELECT t.id
    ,t.product_id 
    ,t.date
    ,t.weight
    ,(SELECT weight FROM table WHERE product_id = t.product_id  AND date < t.date ORDER BY date LIMIT 1) AS prev_weight
    ,(SELECT weight FROM table WHERE product_id = t.product_id  AND date > t.date ORDER BY date LIMIT 1) AS next_weight
FROM table AS t 
) AS t1
WHERE weight <> prev_weight OR weight <> next_weight
2
  • I ended up doing something very similar to this, but I didn't use ORDER BY, because it is too expensive to a large database. Jun 11 '20 at 11:44
  • @LucasMatosMolter you may receive incorrect results without ORDER BY. But you can use appropriate index (on product_id, date) to improve the performance. Also you can probably use ORDER BY id (if id has the same order as date and it is a primary key and you use inndodb then index on product_id will already be sorted in a way you need). Jun 11 '20 at 11:49
0
set @tmp:=-1; set @vari:= 0; set @prodtmp:=-1;
select id,
    product_id,
    date,
    weight,
    if(@prodtmp=-1,@prodtmp:= product_id,if(@prodtmp=product_id,1,@tmp:=-1)) as sameProduct,
    @prodtmp:=prod_id as prod,
    if(@tmp=-1,0,@vari:=weight-@tmp) as temporary,
    @tmp:= weight as dummy,
    @vari as variation
from creditquote
where weight is not null
order by product_id asc, date asc;

What I still cannot do is hodding the rows where the variation is 0, if someone knows how to do it please let me know.

-1

I will add another answer, leaving the comments on my previous answer for what they are... 😉

A tested answer is:

select id, product_id, date, weight, previous, next from (
  select 
    d.id,
    d.product_id,
    d.date,
    d.weight,
    (select d1.weight from dataset d1 where d1.product_id=d.product_id and d1.date<d.date and d1.weight<>d.weight order by d1.date desc limit 1) "PREVIOUS",
    (select d1.weight from dataset d1 where d1.product_id=d.product_id and d1.date>d.date and d1.weight<>d.weight order by d1.date asc limit 1) "NEXT"
  from dataset d
) x
where PREVIOUS is not null
  or NEXT is not null
;

output:

id  product_id  date                weight  previous    next
1   454         2019-06-26 16:08:45 900                899
2   454         2019-06-27 13:24:16 900                899
3   454         2019-06-28 10:53:00 899     900 
7   375         2020-03-15 08:23:00 483                496
8   375         2020-03-15 18:07:00 496     483 
9   375         2020-03-16 13:32:00 496     483 

I have two more results than the 'expected output'. But i think they are within the description of the 'desired output' (see columns previous and/or next), which states " have a weight different from the previous one or different from the next one."

This query was tested here: DBFIDDLE

-2
select 
    d.id, 
    d.product_id, 
    d.date, 
    d.weight,
    d2.weigth "PREVIOUS WEIGHT",
    d3.weigth "NEXT WEIGHT"
from dataset d
cross join (
    select weight 
    from dataset dp 
    where dp.product_id=d.product_id and dp.date<d.date
    order by dp.date desc
    limit 1
      ) d2
cross join (
    select weight 
    from dataset dp 
    where dp.product_id=d.product_id and dp.date>d.date
    order by dp.date asc
    limit 1
      ) d3
12
  • I doubt that this is valid syntax. Jun 9 '20 at 15:45
  • Thank you! It seems like a very good idea, however, I don't know why it say "Unknown column 'd.product_id' in 'where clause' ". Do you have aby guess about the mistake I am making? Jun 9 '20 at 16:02
  • 1
    This query is not a correct answer to the question. It does NOT provide previous and next weights Jun 9 '20 at 16:22
  • 1
    Does this query work in MySQL? dataset d should not be visible inside d2 without a LATERAL join?
    – Lennart
    Jun 9 '20 at 19:38
  • 1
    @ypercubeᵀᴹ you were right, at first I had the impression that it woked, but I double checked this morning and I realised that it does not provide the correct answer. Do you hvae any idea about what I should change? Jun 10 '20 at 7:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.