MySql второй наименьший элемент в каждой группе

У меня есть таблица, похожая на следующую:

    date    |   expiry
-------------------------    
2010-01-01  | 2010-02-01
2010-01-01  | 2010-03-02
2010-01-01  | 2010-04-04
2010-02-01  | 2010-03-01
2010-02-01  | 2010-04-02

В таблице каждая дата может иметь несколько значений "expiry". Мне нужен запрос, который возвращает n-е наименьшее количество истечений в каждую дату. Например, для n = 2 я ожидал бы:

     date    |   expiry
-------------------------       
2010-01-01  | 2010-03-02
2010-02-01  | 2010-04-02

Моя проблема в том, что AFAIK не существует агрегированной функции, которая возвращает n-й наибольший/наименьший элемент, поэтому я не могу использовать "GROUP BY". Более конкретно, если бы у меня был магический MIN() агрегат, который принимает второй параметр "смещение", я бы написал:

SELECT MIN(expiry, 1) FROM table WHERE date IN ('2010-01-01', '2010-02-01') GROUP BY date

Любые предложения?

Ответ 1

Один взломать - использовать group_concat. Группируйте по дате и укажите дату истечения срока в порядке возрастания и используйте функцию substring_index для получения n-го значения.

mysql> select * from expiry;
+------------+------------+
| date       | expiry     |
+------------+------------+
| 2010-01-01 | 2010-02-01 |
| 2010-01-01 | 2010-03-02 |
| 2010-01-01 | 2010-04-04 |
| 2010-02-01 | 2010-03-01 |
| 2010-02-01 | 2010-04-02 |
+------------+------------+
5 rows in set (0.00 sec)

mysql> SELECT mdate,
       Substring_index(Substring_index(edate, ',', 2), ',', -1) AS exp_date
FROM   (SELECT `date`               AS mdate,
               GROUP_CONCAT(expiry order by expiry asc separator ",") AS edate
        FROM   expiry
        GROUP  BY mdate) e1;  
+------------+------------+
| mdate      | exp_date   |
+------------+------------+
| 2010-01-01 | 2010-03-02 |
| 2010-02-01 | 2010-04-02 |
+------------+------------+
2 rows in set (0.00 sec)

В этом примере подзапрос дает следующий результат:

+------------+----------------------------------+
| mdate      | edate                            |
+------------+----------------------------------+
| 2010-01-01 | 2010-02-01,2010-03-02,2010-04-04 |
| 2010-02-01 | 2010-03-01,2010-04-02            |
+------------+----------------------------------+

substring_index (edate, ',', 2) отправляет 2 элемента вперед (для n-го элемента замените 2 на n).

+------------+------------------------------+
| mdate      | substring_index(edate,',',2) |
+------------+------------------------------+
| 2010-01-01 | 2010-02-01,2010-03-02        |
| 2010-02-01 | 2010-03-01,2010-04-02        |
+------------+------------------------------+

мы запускаем другой substring_index на вышеприведенном выходе, чтобы получить только второй элемент (последний элемент промежуточного результата), используя substring_index (substring_index (edate, ',', 2), ',', - 1)

+------------+------------------------------------------------------+
| mdate      | substring_index(substring_index(edate,',',2),',',-1) |
+------------+------------------------------------------------------+
| 2010-01-01 | 2010-03-02                                           |
| 2010-02-01 | 2010-04-02                                           |
+------------+------------------------------------------------------+

Если в concat слишком много значений, вы можете потерять значение group_concat_max_len (по умолчанию 1024, но может быть установлено выше).

UPDATE: приведенный выше SQL даст n-й элемент, даже если для группы th будет меньше n элементов. Чтобы избежать того, что sql может быть изменен как:

SELECT mdate,
       IF(cnt >= 2,Substring_index(Substring_index(edate, ',', 2), ',', -1),NULL) AS exp_date
FROM   (SELECT `date`               AS mdate,
               count(expiry) as cnt,
               GROUP_CONCAT(expiry order by expiry asc separator ",") AS edate
        FROM   expiry
        GROUP  BY mdate) e1;

Ответ 2

Я предлагаю вам взять ваше значение n и использовать его для управления вашим размером возврата. Например, скажем, вы хотели получить третье минимальное значение... То, что вы на самом деле после этого, является наибольшим значением из нижних 3 значений

Итак, это было бы ТОП 1 ОТ (TOP n ORDER BY col ASC)

EDIT: как отмечено в комментариях @Chad Birch, этот подход может быть проблематичным, если вы не можете использовать LIMIT внутри подзапросов.

EDIT2: Вот интересное обходное решение, использующее JOIN с LIMIT http://lists.mysql.com/mysql/211239