4

Having a list of associated dates & names and I want to select: all rows with names having multiple dates when the difference between their dates is more than 1 month.

e.g.: only the entries indicated below marked with \*this*\

CREATE TABLE IF NOT EXISTS myTab (
    id          SERIAL PRIMARY KEY,     
    dateID      DATETIME DEFAULT 0,     
    name        VARCHAR(512)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO myTab 
    (dateID, name) 
VALUES
    ("20140811","Emmy"),    /*this*/
    ("20140922","Emmy"),    /*this*/
    ("20150920","Emmy"),    /*this*/
    ("20150922","Emmy"),
    ("20140722","Dave"),
    ("20140613","Stan"),
    ("20140622","Stan"),    /*this*/
    ("20151020","Stan"),    /*this*/
    ("20140305","Lora"),
    ("20140310","Lora");

In other words the criteria is:

  • Partition by name
  • Order by date
  • Compare 2 consecutive rows: IF diff > 1 MONTH THEN select both, ELSE skip

Here's a working example as well as my attempt based on another answer on SO:

Rextester working example and attempt

Additional conditions/hints/...

  • Rows having the same name are not necessarily inserted once after the other making them spaced with +1 id from eachothers. Nor they are inserted oredered by date. In the example above it's done so just for readability. In my real problem it's not the case!
  • After applying your suggestions on real data I noticed an extra condition to be added tagged /*this_EXRA*/ in the example above. The 3rd stan row is in less than 1 MONTH from the 2nd but validates it with the 4th. Thus, it should only be selected if it validates them both. So I guess this implicates looping row by row and compare with previous and next one each time.
6
  • 2
    The additional requirements are not clear. Is the Extra row to be returned or not? And why? – ypercubeᵀᴹ Dec 28 '16 at 9:44
  • 1
    With the initial requirements, adding the 5th row ('2015-11-30', 'Stan') would result in both the 5th and the 4th row ('2015-10-22', 'Stan') to be returned. What do the additional requirements say? – ypercubeᵀᴹ Dec 28 '16 at 10:28
  • @TypoCubeᵀᴹ Forget about the extra condition. I spoke with the concerned person and results as they are are already satisfying. So unless you have something to improve the performance of the solution, I'll just wait for Rick's update before validating an answer. – Enissay Dec 28 '16 at 21:52
  • Also: is this something you need to run once or often? Are new rows inserted only in the end (with dates after existing ones) or with dates mixed (between existing dates)? These details matter for efficiency considerations. – ypercubeᵀᴹ Dec 28 '16 at 22:02
  • @TypoCubeᵀᴹ Only from time to time to reduce some sets for deeper analysis. Normally this is one of the largest sizes data could be, so performance wise, it's great. Just giving Rick a chance to beat it if he feels like it :-). His solution is quite original (for me at least). – Enissay Dec 28 '16 at 22:11
5

The logic looks simple at first, but it's quite complicated to get it right.

Let's have a working solution first, and worry about performance later. Tested at rextester.com:

SELECT t.id, t.dateID, t.name 
FROM  myTab AS t
WHERE 
       ( SELECT b.dateID
         FROM myTab AS b
         WHERE t.name = b.name
           AND b.dateID < t.dateID
         ORDER BY b.dateID DESC
         LIMIT 1
       ) + INTERVAL 1 MONTH  <=  t.dateID    
    OR 
       t.dateID + INTERVAL 1 MONTH <= 
       ( SELECT b.dateID
         FROM myTab AS b
         WHERE t.name = b.name
           AND t.dateID < b.dateID
         ORDER BY b.dateID ASC
         LIMIT 1
       )
 ;

Regarding efficiency: the query will perform rather poorly. An index on (name, dateID, id) will help but the query will still need to do 2 subqueries for each row of the table.

2
  • From 1.9M data table, results are 162539 row(s) returned 48.906 sec / 5.000 sec – Enissay Dec 28 '16 at 21:54
  • Updated with an index suggestion I forgot to put yesterday. I have an idea about a cursor / variable solution but the requirements are not trivial. Not sure when I'll find time to write and test. – ypercubeᵀᴹ Dec 28 '16 at 22:00
3
SELECT a.name, a.dateID, b.dateID
    FROM myTab AS a
    JOIN myTab AS b  ON a.name = b.name
                  AND b.id = a.id + 1
    WHERE b.dateID > a.dateID + INTERVAL 1 MONTH;

INDEX(name, id, dateID)

But if the data is not ordered 'correctly', then renumber thus:

CREATE TABLE myTab2 (
    id INT UNSIGNED AUTO_INCREMENT,
    PRIMARY KEY(id)
    ) ENGINE=InnoDB
    SELECT name, dateID
        FROM myTab
        ORDER BY name, dateID;

SELECT a.name, a.dateID, b.dateID
    FROM myTab2 AS a
    JOIN myTab2 AS b  ON a.name = b.name
                  AND b.id = a.id + 1
    WHERE b.dateID > a.dateID + INTERVAL 1 MONTH; 

(Note: this assumes auto_increment_increment = 1.)

The PRIMARY KEY(id) suffices.

DROP TABLE myTab2;  -- (With 8.0.x or MariaDB 10.2, use CTE to avoid this)

Output:

    name
    dateID
    dateID

1
    Emmy
    11.08.2014 00:00:00
    22.09.2014 00:00:00

2
    Emmy
    22.09.2014 00:00:00
    20.09.2015 00:00:00

3
    Stan
    22.06.2014 00:00:00
    20.10.2015 00:00:00
7
  • Oh, for readability I have grouped names together making it that for the same name, all ids are +1 from eachothers. In my real problem it's not the case. Sorry, I should've specified that. – Enissay Dec 28 '16 at 5:32
  • Then edit the question. (Anyway, I build a workaround.) – Rick James Dec 28 '16 at 5:47
  • Note the absence of a ; between the CREATE and the SELECT. The two are combined -- The CREATE provides the new id; the SELECT provides more columns. They combine to create and populate myTab2. – Rick James Dec 28 '16 at 19:53
  • What output do you get / expect from my code with your extra 'stan' row? – Rick James Dec 28 '16 at 19:56
  • I have adapted your query mistakenly to my real problem which led to me thinking it was faulty since also, I've never seen this construction evnethough it kindof made sense at first sight. Anyway, it works now, though, I prefer getting the original rows one by one as they contain other useful columns needed but I didnt include here since not used for the comparison etc... – Enissay Dec 28 '16 at 22:00

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.