Obtain the last [n,n+t] days per group

Question

First of all, please excuse the language, I have been having trouble parsing my problem into actual English so if anyone could edit to make it clearer that would help.

I have been struggling for some time with this one. I am in need of a query that, per each group, from the last N days, skips the most recent one and retrieves the next T days. This is a version of the classic 'LIMIT with GROUP' problem, and in fact, one of the queries that I tried that didn't work, used that form.

MRE as follows:

CREATE TABLE `trying` (id INTEGER PRIMARY KEY AUTO_INCREMENT, types1 TEXT, stuffs INTEGER, dates DATE);
INSERT INTO `trying`(types1, stuffs, dates) VALUES("one",123,'2015-09-06');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("one",67,'2015-09-05');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("one",45,'2015-09-04');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("one",98,'2015-09-03');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("one",89,'2015-09-02');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("two",56,'2015-09-02');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("two",34,'2015-09-01');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("two",98,'2015-08-31');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("two",34,'2015-08-30');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("two",12,'2015-08-29');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("three",3,'2015-09-06');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("three",8,'2015-09-04');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("three",80,'2015-09-02');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("three",9,'2015-09-01');
INSERT INTO `trying`(types1, stuffs, dates) VALUES("three",6,'2015-08-31');

In table trying there are three types1 'types': 'one', 'two' and 'three', and there are 5 observations per group. Note that the dates are not similar between the groups and there might even be gaps between them (so no dates BETWEEN, like in this question).

In this example I would like to get a table with the three middle values per group. So skip the first and last value, the expected output would look like this:

types1  stuffs  dates
one     67      2015-09-05
one     45      2015-09-04
one     98      2015-09-03
two     34      2015-09-01
two     98      2015-08-31
two     34      2015-08-30
three   8       2015-09-04
three   80      2015-09-02
three   9       2015-09-01

A couple of queries that did not work:

SELECT types1, stuffs, dates FROM trying  GROUP BY types1 LIMIT 2,4;
/*this returned the following */
types1  stuffs  dates
two     56      2015-09-02


SELECT trying.* FROM (SELECT types1, stuffs, dates FROM trying)  GROUP BY trying.types1 OFFSET 2,4;
/*threw out an error: Every derived table must have its own alias */

I discuss a solution in my groupwise max blog: (mysql.rjweb.org/doc.php/groupwise_max). — Rick James, Sep 06 '15 at 19:48

jpw · Answer 1 · 2015-09-06T19:40:55.270

One way that might work is to use user variables to number the rows in each group and then limit the result to the rows with row number in the desired interval:

SELECT id, types1, stuffs, dates
FROM (
  SELECT 
    id, types1, stuffs, dates,
    (
      CASE types1
      WHEN @type 
      THEN @row := @row + 1 
      ELSE @row := 1 AND @type := types1 END
    ) + 1 AS row
  FROM trying p,
  (SELECT @row := 0, @type := '') r
  ORDER BY types1, dates asc  
) src
WHERE row BETWEEN 2 AND 4
ORDER BY id;

Sample SQL Fiddle for query 1

Or, if you always want to remove the first and last rows in each group then you could use a left join to a derived table that returns the max and min date for each group:

select t.* from trying t
left join (
    select types1, min(dates) min_dates, max(dates) max_dates 
    from trying group by types1
    ) minmax 
    on t.types1 = minmax.types1 
    and t.dates in (minmax.max_dates, minmax.min_dates)
where minmax.types1 is null;

Sample SQL Fiddle for query 2

With your sample data both queries return the same result:

| id | types1 | stuffs |                       dates |
|----|--------|--------|-----------------------------|
|  2 |    one |     67 | September, 05 2015 00:00:00 |
|  3 |    one |     45 | September, 04 2015 00:00:00 |
|  4 |    one |     98 | September, 03 2015 00:00:00 |
|  7 |    two |     34 | September, 01 2015 00:00:00 |
|  8 |    two |     98 |    August, 31 2015 00:00:00 |
|  9 |    two |     34 |    August, 30 2015 00:00:00 |
| 12 |  three |      8 | September, 04 2015 00:00:00 |
| 13 |  three |     80 | September, 02 2015 00:00:00 |
| 14 |  three |      9 | September, 01 2015 00:00:00 |

score 1 · Accepted Answer · answered Sep 06 '15 at 19:48

1

select types1,stuffs,dates from (
select @rank:=if(@prev_cat=types1,@rank+1,1) as rank,
  types1,stuffs,dates,@prev_cat:=types1

from trying,(select @rank:=0, @prev_cat:="")t
order by types1, dates desc
  ) temp

  where rank between 2 and  4

answered Sep 06 '15 at 19:48

seahawk

1,872
12
18

Obtain the last [n,n+t] days per group

2 Answers2