Wczoraj Ciekawe pytanie zostało poproszono, które wymagało aktualizacji tabeli MySQL za pomocą opóźnienia. Rozważ następującą tabelę wejściową (po lewej) i pożądane wyjście (po prawej):

**INPUT**                                 **OUTPUT**
ID  TestDate    PerformanceStatus (PS)    ID  TestDate    PS  PreviousPerformanceStatus
1   15/03/2016  0                         1   15/03/2016  0   0
1   01/04/2016  2                         1   01/04/2016  2   0
1   05/05/2016  1                         1   05/05/2016  1   2
1   07/06/2016  1                         1   07/06/2016  1   1
2   15/03/2016  0                         2   15/03/2016  0   1
2   01/04/2016  2                         2   01/04/2016  2   0
2   05/05/2016  1                         2   05/05/2016  1   2
2   07/06/2016  3                         2   07/06/2016  3   1
2   23/08/2016  1                         2   23/08/2016  1   3

Innymi słowy, celem jest przypisanie do PreviousPerformanceStatus wartość, która istniała w rejestrze nadchodzącym wcześniej, zgodnie z ID następnie TestDate.

Przyjęta odpowiedź, podana przez @ Spencer7593, używała skorelowanego podzapytania. Jednak, co najpierw pojawiło się do mojej głowy, było użycie zmiennej użytkownika. Oto jak odpowiedziałem:

SET @lag = 0;
UPDATE yourTable
SET PreviousPerformanceStatus = @lag,
    @lag:=PerformanceStatus
ORDER BY ID, TestDate

Powiedziano mi, że ta odpowiedź jest niestabilna, ale zastanawiałem się, czy ktoś mógłby wyjaśnić Dlaczego coś może pójść źle, co by się dzieje w takim przypadku, a wreszcie co możemy zrobić, aby użyć tutaj zmiennej użytkownika tutaj symulować opóźnienie.

To jest moje zrozumienie, że zapytanie następujące {x0}} nie miałoby wcale żadnych problemów:

SELECT PerformanceStatus,
       @lag AS PreviousPerformanceStatus,
       @lag:=PerformanceStatus
FROM yourTable
ORDER BY ID, TestDate

Jednak podczas wykonywania UPDATE istnieją inne względy do uwzględnienia.

0
Tim Biegeleisen 17 luty 2017, 13:19

2 odpowiedzi

Najlepsza odpowiedź

Nie sądzę, że możesz ustawić zmienne w instrukcji aktualizacji. Oto moje rozumowanie - biorąc pod uwagę to

drop table if exists t;

create table t (ID int, TestDate date,   PerformanceStatus int, previousperformancestatus int);
insert into t values
(1 ,  '2016-03-15' , 0, null),                         
(1 ,  '2016-04-01' , 2, null),                         
(1 ,  '2016-05-05' , 1, null),                         
(1 ,  '2016-06-07' , 1, null),                         
(2 ,  '2016-03-15' , 0, null),                         
(2 ,  '2016-04-01' , 2, null),                         
(2 ,  '2016-05-05' , 1, null),                         
(2 ,  '2016-06-07' , 3, null),                         
(2 ,  '2016-08-23' , 1, null)
;

Ten kod nie powiedzie się

MariaDB [sandbox]> SET @lag = 0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [sandbox]> UPDATE T
    -> SET previousPerformanceStatus = @lag ,
    ->     @lag:=PerformanceStatus
    -> ORDER BY ID, TestDate;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@lag:=PerformanceStatus
ORDER BY ID, TestDate' at line 3

Komentując @lag: = Wykonawca PerformanceTatus Ten kod działa

MariaDB [sandbox]> SET @lag = 0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [sandbox]> UPDATE T
    -> SET previousPerformanceStatus = @lag
    -> #,@lag:=PerformanceStatus
    -> ORDER BY ID, TestDate;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 9  Changed: 0  Warnings: 0

Ponieważ kodu przynajmniej działa bez błędu i instrukcji HTTPS: // dev. mysql.com/doc/refman/5.7/pl/update.html stwierdza "Klauzula Ustawiająca wskazuje, które kolumny do modyfikowania" Moje podjęcie tego polega na tym, że nie można ustawić zmiennych w instrukcji aktualizacji, aby symulować opóźnienie nie jest możliwe za pomocą tej metody.

1
P.Salmon 17 luty 2017, 13:29

Przyjęta odpowiedź w tym wątku jest błędna. Najlepszym sposobem, w jaki znalazłem testowany mrówki, jest użycie CTE (z klauzulą) i zaktualizowanie tabeli z zestawu wyników CTE.

Wybór proponowany przez Spencera7593 będzie działać, ale jest dość niezdrowy (w moim przypadku zajęło jedną minutę, aby zaktualizować trzy kolumny). Aktualizacja nie powiedzie się z błędem informującym, że nie możesz zaktualizować tabeli, której używasz w klauzuli z ustawionego celu.

enter image description here

Innym, o wiele bardziej skuteczny sposób, aby użyć CTE z LGD () i chwytać zaktualizowane wartości z niego. Jednak potrzebny jest unikalny klucz do użycia jako klucz dołączenia między CTE a tabelą, jeśli nie ma innego prostego lub kompozytowego unikalnego klucza.

-- Create the table as per question
drop table if exists student;

create table if not exists student (
pk int auto_increment,
id int not null,
TestDate date not null,
PerformanceStatus int not null,
PreviousPerformanceStatus int null default null,
primary key (pk)
) engine=innodb;

insert into student(id, TestDate, PerformanceStatus, PreviousPerformanceStatus)
values  (1, '2016-03-15', 0, null),
        (1, '2016-04-01', 2, null),
        (1, '2016-05-05', 1, null),
        (1, '2016-06-07', 1, null),
        (2, '2016-03-15', 0, null),
        (2, '2016-04-01', 2, null),
        (2, '2016-05-05', 1, null),
        (2, '2016-06-07', 3, null),
        (2, '2016-08-23', 1, null);


-- Update PreviousPerformanceStatus using lag()
with p as
(
    select pk, id, testdate, performancestatus, 
    LAG(performancestatus, 1, 0) OVER (
        PARTITION BY id
        ORDER BY id, testdate asc
    ) as PreviousPerformanceStatus
    from student
)
update student t
inner join p 
on p.pk = t.pk  
set     
t.PreviousPerformanceStatus = p.PreviousPerformanceStatus;

Możesz zastąpić trzeci parametr opóźnienia () z null zamiast zera. Odkryłem, że to rozwiązanie jest najbardziej wydajne wśród kilku innych rozwiązań roboczych.

0
Misa 23 maj 2019, 09:54