Wybierz wiersz z najnowszą datą na użytkownika


125

Mam tabelę („lms_attendance”) z czasami zameldowania i wymeldowania użytkowników, która wygląda następująco:

id  user    time    io (enum)
1   9   1370931202  out
2   9   1370931664  out
3   6   1370932128  out
4   12  1370932128  out
5   12  1370933037  in

Próbuję utworzyć widok tej tabeli, który wyprowadziłby tylko najnowszy rekord na identyfikator użytkownika, jednocześnie dając mi wartość „in” lub „out”, więc coś takiego:

id  user    time    io
2   9   1370931664  out
3   6   1370932128  out
5   12  1370933037  in

Jestem dość blisko, ale zdałem sobie sprawę, że widoki nie akceptują podzapytań, co znacznie utrudnia sprawę. Najbliższe zapytanie, które otrzymałem, brzmiało:

select 
    `lms_attendance`.`id` AS `id`,
    `lms_attendance`.`user` AS `user`,
    max(`lms_attendance`.`time`) AS `time`,
    `lms_attendance`.`io` AS `io` 
from `lms_attendance` 
group by 
    `lms_attendance`.`user`, 
    `lms_attendance`.`io`

Ale otrzymuję:

id  user    time    io
3   6   1370932128  out
1   9   1370931664  out
5   12  1370933037  in
4   12  1370932128  out

Co jest bliskie, ale nie idealne. Wiem, że ta ostatnia grupa według nie powinna tam być, ale bez niej zwraca ostatni czas, ale nie ze swoją względną wartością we / wy.

Jakieś pomysły? Dzięki!



Wróć do instrukcji. Zobaczysz, że oferuje rozwiązania tego problemu zarówno z podzapytaniami (skorelowanymi i nieskorelowanymi), jak i bez nich.
Truskawka

@Barmar, technicznie, jak wskazałem w mojej odpowiedzi, jest to duplikat wszystkich 700 pytań z największą liczbą n na grupę tagiem .
TMS

@Prodikl, co to jest „io (enum)”?
Monica Heddneck

Miałem kolumnę o nazwie „IO”, która oznacza „in lub out”, był to typ wyliczenia z możliwymi wartościami „in” lub „out”. Służyło to do śledzenia, kiedy ludzie meldowali się i wychodzili z zajęć.
Keith

Odpowiedzi:


199

Pytanie:

SQLFIDDLEExample

SELECT t1.*
FROM lms_attendance t1
WHERE t1.time = (SELECT MAX(t2.time)
                 FROM lms_attendance t2
                 WHERE t2.user = t1.user)

Wynik:

| ID | USER |       TIME |  IO |
--------------------------------
|  2 |    9 | 1370931664 | out |
|  3 |    6 | 1370932128 | out |
|  5 |   12 | 1370933037 |  in |

Rozwiązanie, które będzie działać za każdym razem:

SQLFIDDLEExample

SELECT t1.*
FROM lms_attendance t1
WHERE t1.id = (SELECT t2.id
                 FROM lms_attendance t2
                 WHERE t2.user = t1.user            
                 ORDER BY t2.id DESC
                 LIMIT 1)

2
łał! nie tylko to zadziałało, ale pozwolono mi stworzyć widok z tym zapytaniem, mimo że zawiera podzapytania. wcześniej, kiedy próbowałem utworzyć widok zawierający podzapytania, nie pozwalało mi to. czy istnieją zasady, dlaczego jest to dozwolone, a inna nie?
Keith

bardzo dziwne. wielkie dzięki! być może dlatego, że moje podzapytanie było pseudo tabelą, którą wybierałem z FROM, gdzie w tym przykładzie jest używane w klauzuli WHERE.
Keith

4
Nie ma potrzeby wykonywania podzapytań! Co więcej, to rozwiązanie nie działa, jeśli istnieją dwa rekordy o dokładnie tym samym czasie . Nie ma potrzeby, aby za każdym razem próbować odkrywać koło na nowo, ponieważ jest to częsty problem - zamiast tego sięgnij po już przetestowane i zoptymalizowane rozwiązania - @Prodikl zobacz moją odpowiedź.
TMS

ach, dzięki za wgląd! spróbuję nowego kodu, kiedy jutro będę w biurze.
Keith

3
@TMS To rozwiązanie działa, jeśli rekordy mają dokładnie ten sam czas, ponieważ kwerenda lokalizuje rekord o największym identyfikatorze. Oznacza to, że czas w tabeli to czas wstawienia, co może nie być dobrym założeniem. Zamiast tego Twoje rozwiązanie porównuje sygnatury czasowe, a gdy dwa znaczniki czasu są identyczne, zwracasz również wiersz z największym identyfikatorem. Dlatego rozwiązanie zakłada również, że sygnatura czasowa w tej tabeli jest związana z kolejnością wstawiania, która jest największą wadą obu zapytań.
WebWanderer

73

Nie ma potrzeby odkrywania na nowo koła, ponieważ jest to powszechny problem z największą liczbą n na grupę . Przedstawiono bardzo ładne rozwiązanie .

Wolę najbardziej uproszczone rozwiązanie ( zobacz SQLFiddle, zaktualizowane Justin's ) bez podzapytań (dzięki czemu jest łatwe w użyciu w widokach):

SELECT t1.*
FROM lms_attendance AS t1
LEFT OUTER JOIN lms_attendance AS t2
  ON t1.user = t2.user 
        AND (t1.time < t2.time 
         OR (t1.time = t2.time AND t1.Id < t2.Id))
WHERE t2.user IS NULL

Działa to również w przypadku, gdy istnieją dwa różne rekordy o tej samej największej wartości w tej samej grupie - dzięki sztuczce z (t1.time = t2.time AND t1.Id < t2.Id). Jedyne, co tutaj robię, to zapewnienie, że w przypadku, gdy dwa rekordy tego samego użytkownika mają ten sam czas, wybierany jest tylko jeden. Właściwie nie ma znaczenia, czy kryteria są, Idczy coś innego - w zasadzie każde kryterium, które z pewnością są wyjątkowe, sprawdzi się tutaj.


1
Maksymalne zastosowania t1.time < t2.timei minimalne byłyby t1.time > t2.timeprzeciwieństwem mojej początkowej intuicji.
Brak

1
@ J.Money, ponieważ ukryta jest niejawna negacja: wybierasz wszystkie rekordy z t1, które nie mają odpowiedniego rekordu z t2, gdzie t1.time < t2.timewarunek ma zastosowanie :-)
TMS

4
WHERE t2.user IS NULLjest trochę dziwne. Jaką rolę odgrywa ta linia?
tumultous_rooster

1
Przyjęta odpowiedź, wysłana przez Justina, może być bardziej optymalna. Zaakceptowana odpowiedź wykorzystuje skanowanie indeksu wstecz na kluczu podstawowym tabeli, po którym następuje ograniczenie, po którym następuje skanowanie sekwencji tabeli. Dlatego zaakceptowaną odpowiedź można znacznie zoptymalizować dzięki dodatkowemu indeksowi. To zapytanie może być również zoptymalizowane przez indeks, ponieważ wykonuje ono dwa skanowanie sekwencji, ale zawiera również hash i „hash-anti-join” wyników skanowania sekwencji i hash drugiego skanowania sekwencji. Byłbym zainteresowany wyjaśnieniem, które podejście jest naprawdę bardziej optymalne.
WebWanderer

@TMS czy mógłbyś wyjaśnić OR (t1.time = t2.time AND t1.Id < t2.Id))sekcję?
Oleg Kuts

6

Opierając się na odpowiedzi @TMS, podoba mi się to, ponieważ nie ma potrzeby wykonywania podzapytań, ale myślę, że pominięcie 'OR'części będzie wystarczające i znacznie prostsze do zrozumienia i przeczytania.

SELECT t1.*
FROM lms_attendance AS t1
LEFT JOIN lms_attendance AS t2
  ON t1.user = t2.user 
        AND t1.time < t2.time
WHERE t2.user IS NULL

jeśli nie interesują Cię wiersze z zerowymi czasami, możesz je przefiltrować w WHEREklauzuli:

SELECT t1.*
FROM lms_attendance AS t1
LEFT JOIN lms_attendance AS t2
  ON t1.user = t2.user 
        AND t1.time < t2.time
WHERE t2.user IS NULL and t1.time IS NOT NULL

Pominięcie ORczęści jest naprawdę złym pomysłem, jeśli dwa rekordy mogą mieć to samo time.
TMS

Unikałbym tego rozwiązania ze względu na wydajność. Jak wspomniał @OlegKuts, robi się to bardzo wolno w średnich i dużych zestawach danych.
Peter Meadley

4

Już rozwiązane, ale tak dla przypomnienia innym podejściem byłoby stworzenie dwóch widoków ...

CREATE TABLE lms_attendance
(id int, user int, time int, io varchar(3));

CREATE VIEW latest_all AS
SELECT la.user, max(la.time) time
FROM lms_attendance la 
GROUP BY la.user;

CREATE VIEW latest_io AS
SELECT la.* 
FROM lms_attendance la
JOIN latest_all lall 
    ON lall.user = la.user
    AND lall.time = la.time;

INSERT INTO lms_attendance 
VALUES
(1, 9, 1370931202, 'out'),
(2, 9, 1370931664, 'out'),
(3, 6, 1370932128, 'out'),
(4, 12, 1370932128, 'out'),
(5, 12, 1370933037, 'in');

SELECT * FROM latest_io;

Kliknij tutaj, aby zobaczyć, jak działa w SQL Fiddle


1
dzięki za kontynuację! tak, miałem zamiar utworzyć wiele widoków, gdyby nie było prostszego sposobu. jeszcze raz dzięki
Keith

0
select b.* from 

    (select 
        `lms_attendance`.`user` AS `user`,
        max(`lms_attendance`.`time`) AS `time`
    from `lms_attendance` 
    group by 
        `lms_attendance`.`user`) a

join

    (select * 
    from `lms_attendance` ) b

on a.user = b.user
and a.time = b.time

dzięki. Wiem, że mogę to zrobić za pomocą podzapytania, ale miałem nadzieję, że zamienię to w widok i nie pozwoli to na podzapytania w widokach AFAIK. czy musiałbym zamienić każde zapytanie podrzędne w widok itp.?
Keith

join (select * from lms_attendance ) b= join lms_attendance b
azerafati

0
 select result from (
     select vorsteuerid as result, count(*) as anzahl from kreditorenrechnung where kundeid = 7148
     group by vorsteuerid
 ) a order by anzahl desc limit 0,1

0

Jeśli korzystasz z MySQL 8.0 lub nowszego, możesz używać funkcji okna :

Pytanie:

DBFiddleExample

SELECT DISTINCT
FIRST_VALUE(ID) OVER (PARTITION BY lms_attendance.USER ORDER BY lms_attendance.TIME DESC) AS ID,
FIRST_VALUE(USER) OVER (PARTITION BY lms_attendance.USER ORDER BY lms_attendance.TIME DESC) AS USER,
FIRST_VALUE(TIME) OVER (PARTITION BY lms_attendance.USER ORDER BY lms_attendance.TIME DESC) AS TIME,
FIRST_VALUE(IO) OVER (PARTITION BY lms_attendance.USER ORDER BY lms_attendance.TIME DESC) AS IO
FROM lms_attendance;

Wynik:

| ID | USER |       TIME |  IO |
--------------------------------
|  2 |    9 | 1370931664 | out |
|  3 |    6 | 1370932128 | out |
|  5 |   12 | 1370933037 |  in |

Zaleta, którą widzę nad zastosowaniem rozwiązania zaproponowanego przez Justina jest to, że umożliwia ono wybranie wiersza z najnowszymi danymi na użytkownika (lub według identyfikatora lub cokolwiek innego) nawet z podzapytań bez potrzeby widoku pośredniego lub tabeli.

A jeśli korzystasz z HANA, jest to również ~ 7 razy szybsze: D


-1

Ok, może to być hack lub podatność na błędy, ale w jakiś sposób to również działa -

SELECT id, MAX(user) as user, MAX(time) as time, MAX(io) as io FROM lms_attendance GROUP BY id;

-2

Spróbuj tego zapytania:

  select id,user, max(time), io 
  FROM lms_attendance group by user;

Spróbuj zrobić z tego SQLFiddle. Prawdopodobnie zauważysz to idi iosą to kolumny niezagregowane, których nie można użyć w pliku group by.
Dewi Morgan

1
nie ma gwarancji, że id będzie identyfikatorem z max (czasem), może to być dowolny z identyfikatorów w grupie. to jest problem, który przyszedłem tutaj rozwiązać, wciąż szukam
robisrob

-3

Ewentualnie możesz grupować według użytkowników, a następnie porządkować według czasu. Coś jak poniżej

  SELECT * FROM lms_attendance group by user order by time desc;

-3

To zadziałało dla mnie:

SELECT user, time FROM 
(
    SELECT user, time FROM lms_attendance --where clause
) AS T 
WHERE (SELECT COUNT(0) FROM table WHERE user = T.user AND time > T.time) = 0
ORDER BY user ASC, time DESC
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.