I have a MySQL table with two fields: date and value. date is of type date, and value is decimal(10,2).

There are "pairs" of rows (non-consecutive) whose values are of equal magnitude but opposite sign, within 7 days of each other.

date        value
2020-06-20  123.45
2020-06-25  -123.45

The above is a "pair" because they are negatives of each other and their dates are within 7 days of each other. There may be any number of intervening rows. There may be any number of pairs in the table. Pairs of differing values may overlap 7 day periods. For simplicity assume there are no other equal-value rows also with 7 days (ie, no triples).

I'm trying to find a SELECT statement that gives me all the table's rows EXCEPT "pairs". Could someone please help?

  • Which version of MySQL and what engine are you using? Could you provide more detail, I'm a little confused by "Pairs of differing values may overlap 7 day periods"? Also provide the relevant key information for the table.
    – user212533
    Sep 9, 2020 at 13:02
  • @bbaird by overlapping I mean in a table sorted by date one pair may exist within another pair, or one pair may include one member of another pair. I wasn't sure if this would influence the query. Basically assume nothing about the dates or values. I'm running MySQL 5.6. Thanks for mentioning "key". I checked my table - duh - I was indexing neither column. Added indexes for both columns and now the query runs in a few seconds. Cheers.
    – Scott
    Sep 9, 2020 at 14:28

1 Answer 1

FROM table t1
                   FROM TABLE t2
                   WHERE t2.`value` = -t1.`value`
                     AND t2.`date` BETWEEN t1.`date` - INTERVAL 7 DAY 
                                       AND t1.`date` + INTERVAL 7 DAY )
  • Thanks @Akina. Is the size of this query quadratic? My table has nearly 10,000 rows and I think my server is killing the query after 30 seconds. Can any efficiencies be had?
    – Scott
    Sep 9, 2020 at 10:20
  • @Scott No, it looks like N*log(N). If the index by (value, date) (or in backward order - I cannot say what is more useful on your data) exists.
    – Akina
    Sep 9, 2020 at 10:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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