Jak uzyskać aktualną i następną większą wartość w jednym wyborze?

Mam tabelę InnoDB „idtimes” (MySQL 5.0.22-log) z kolumnami

`id` int(11) NOT NULL,
`time` int(20) NOT NULL, [...]

ze złożonym unikalnym kluczem

UNIQUE KEY `id_time` (`id`,`time`)

więc może istnieć wiele znaczników czasu na identyfikator i wiele identyfikatorów na znacznik czasu.

Próbuję skonfigurować zapytanie, w którym otrzymuję wszystkie wpisy plus następny większy czas dla każdego wpisu, jeśli taki istnieje, więc powinien zwrócić np .:

+-----+------------+------------+
| id  | time       | nexttime   |
+-----+------------+------------+
| 155 | 1300000000 | 1311111111 |
| 155 | 1311111111 | 1322222222 |
| 155 | 1322222222 |       NULL |
| 156 | 1312345678 | 1318765432 |
| 156 | 1318765432 |       NULL |
+-----+------------+------------+

W tej chwili jestem do tej pory:

SELECT l.id, l.time, r.time FROM 
    idtimes AS l LEFT JOIN idtimes AS r ON l.id = r.id
    WHERE l.time < r.time ORDER BY l.id ASC, l.time ASC;

ale oczywiście zwraca wszystkie wiersze z r.time> l.time i nie tylko pierwszy ...

Chyba będę potrzebować podselekcji jak

SELECT outer.id, outer.time, 
    (SELECT time FROM idtimes WHERE id = outer.id AND time > outer.time 
        ORDER BY time ASC LIMIT 1)
    FROM idtimes AS outer ORDER BY outer.id ASC, outer.time ASC;

ale nie wiem, jak odwoływać się do bieżącej godziny (wiem, że powyższe nie jest poprawnym SQL).

Jak to zrobić za pomocą pojedynczego zapytania (i wolałbym nie używać @ zmiennych, które zależą od przejścia przez tabelę jeden wiersz na raz i zapamiętania ostatniej wartości)?

Wykonanie JOIN to jedna rzecz, której możesz potrzebować.

SELECT l.id, l.time, r.time FROM 
    idtimes AS l LEFT JOIN idtimes AS r ON l.id = r.id

Przypuszczam, że połączenie zewnętrzne jest celowe, a ty chcesz mieć wartości zerowe. Więcej o tym później.

WHERE l.time < r.time ORDER BY l.id ASC, l.time ASC;

Chcesz tylko r. wiersz, który ma najniższy (MIN) czas, który jest dłuższy niż czas l. To jest miejsce, w którym potrzebujesz podkwerendowania.

WHERE r.time = (SELECT MIN(time) FROM idtimes r2 where r2.id = l.id AND r2.time > l.time)

Teraz do zera. Jeśli „nie będzie następnego wyższego czasu”, wówczas SELECT MIN () będzie mieć wartość zerową (lub gorszą), a to samo nigdy nie będzie się równać z niczym, więc twoja klauzula WHERE nigdy nie będzie spełniona, a „najwyższy czas” dla każdego identyfikatora nigdy nie może pojawić się w zestawie wyników.

Rozwiązujesz go, eliminując DOŁĄCZ i przenosząc podkwerendę skalarną na listę WYBIERZ:

SELECT id, time, 
    (SELECT MIN(time) FROM idtimes sub 
        WHERE sub.id = main.id AND sub.time > main.time) as nxttime
  FROM idtimes AS main 

Zawsze unikam używania podkwerend albo w SELECTbloku, albo w FROMbloku, ponieważ powoduje to, że kod staje się „brudniejszy”, a czasem mniej wydajny.

Myślę, że bardziej eleganckim sposobem na to jest:

1. Znajdź czasy większe niż czas rzędu

Możesz to zrobić z tabeląJOIN między przedziałami czasowymi z samym sobą, ograniczając łączenie do tego samego identyfikatora i czasów większych niż czas bieżącego wiersza.

Powinieneś użyć, LEFT JOINaby uniknąć wykluczania wierszy, w których nie ma czasów większych niż ten z bieżącego wiersza.

SELECT
    i1.id,
    i1.time AS time,
    i2.time AS greater_time
FROM
    idtimes AS i1
    LEFT JOIN idtimes AS i2 ON i1.id = i2.id AND i2.time > i1.time

Problem, jak wspomniałeś, polega na tym, że masz wiele wierszy, w których next_time jest większy niż czas .

+-----+------------+--------------+
| id  | time       | greater_time |
+-----+------------+--------------+
| 155 | 1300000000 | 1311111111   |
| 155 | 1300000000 | 1322222222   |
| 155 | 1311111111 | 1322222222   |
| 155 | 1322222222 |       NULL   |
| 156 | 1312345678 | 1318765432   |
| 156 | 1318765432 |       NULL   |
+-----+------------+--------------+

2. Znajdź wiersze, w których większa_czas jest nie tylko większa, ale i następna_czas

Najlepszym sposobem na odfiltrowanie wszystkich tych bezużytecznych wierszy jest sprawdzenie, czy istnieją czasy między czasem (większym niż) a czasem dłuższym (mniejszym) dla tego identyfikatora .

SELECT
    i1.id,
    i1.time AS time,
    i2.time AS next_time,
    i3.time AS intrudor_time
FROM
    idtimes AS i1
    LEFT JOIN idtimes AS i2 ON i1.id = i2.id AND i2.time > i1.time
    LEFT JOIN idtimes AS i3 ON i2.id = i3.id AND i3.time > i1.time AND i3.time < i2.time

ops, nadal mamy fałszywy następny czas !

+-----+------------+--------------+---------------+
| id  | time       | next_time    | intrudor_time |
+-----+------------+--------------+---------------+
| 155 | 1300000000 | 1311111111   |         NULL  |
| 155 | 1300000000 | 1322222222   |    1311111111 |
| 155 | 1311111111 | 1322222222   |         NULL  |
| 155 | 1322222222 |       NULL   |         NULL  |
| 156 | 1312345678 | 1318765432   |         NULL  |
| 156 | 1318765432 |       NULL   |         NULL  |
+-----+------------+--------------+---------------+

Wystarczy przefiltrować wiersze, w których zdarzenie to się dzieje, dodając WHEREograniczenie poniżej

WHERE
    i3.time IS NULL

Voilà, mamy to, czego potrzebujemy!

+-----+------------+--------------+---------------+
| id  | time       | next_time    | intrudor_time |
+-----+------------+--------------+---------------+
| 155 | 1300000000 | 1311111111   |         NULL  |
| 155 | 1311111111 | 1322222222   |         NULL  |
| 155 | 1322222222 |       NULL   |         NULL  |
| 156 | 1312345678 | 1318765432   |         NULL  |
| 156 | 1318765432 |       NULL   |         NULL  |
+-----+------------+--------------+---------------+

Mam nadzieję, że nadal potrzebujesz odpowiedzi po 4 latach!

Przed przedstawieniem rozwiązania należy zauważyć, że nie jest ładne. Byłoby znacznie łatwiej, gdybyś miał AUTO_INCREMENTkolumnę na stole (prawda?)

SELECT 
  l.id, l.time, 
  SUBSTRING_INDEX(GROUP_CONCAT(r.time ORDER BY r.time), ',', 1)
FROM 
  idtimes AS l 
  LEFT JOIN idtimes AS r ON (l.id = r.id)
WHERE 
  l.time < r.time
GROUP BY
  l.id, l.time

Wyjaśnienie:

• Tak samo jak twój: połącz dwie tabele, właściwa dostanie tylko więcej razy
• Pogrupuj według obu kolumn z lewej tabeli: zapewnia to otrzymanie wszystkich (id, time)kombinacji (które są również znane jako unikalne).
• Dla każdego (l.id, l.time)otrzymaj pierwszy, r.time który jest większy niż l.time. Dzieje się tak przy pierwszym zamówieniu r.times poprzez GROUP_CONCAT(r.time ORDER BY r.time), poprzez przecięcie pierwszego tokena przez SUBSTRING_INDEX.

Powodzenia i nie oczekuj dobrej wydajności, jeśli ta tabela jest duża.

Możesz również uzyskać to, czego chcesz od min()a GROUP BYbez wewnętrznego wyboru:

SELECT l.id, l.time, min(r.time) 
FROM idtimes l 
LEFT JOIN idtimes r on (r.id = l.id and r.time > l.time)
GROUP BY l.id, l.time;

Chciałbym prawie postawić dużą sumę pieniędzy, że optymalizator zamienia to w samej rzeczy jako odpowiedź Erwina Smout w każdym razie, i to jest wątpliwe, czy to jakieś jaśniejsze, ale nie jest to dla zasady ...