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. – bbaird Sep 9 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 at 14:28
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 )
| improve this answer | |
  • 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 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 at 10:23

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.