Mam problemy z zapytaniem niektórych danych. Tabela, którą próbuję pociągnąć dane z tabeli dziennika, gdzie chciałbym zobaczyć zmiany w wartościach obok siebie (przykład poniżej)

Stół:


+-----------+----+-------------+----------+------------+
| UNIQUE_ID | ID |    NAME     |   CITY   |    DATE    |
+-----------+----+-------------+----------+------------+
| xa220     |  1 | John Smith  | Berlin   | 2020.05.01 |
| xa195     |  1 | John Smith  | Berlin   | 2020.03.01 |
| xa111     |  1 | John Smith  | München  | 2020.01.01 |
| xa106     |  2 | James Brown | Atlanta  | 2018.04.04 |
| xa100     |  2 | James Brown | Boston   | 2017.12.10 |
| xa76      |  3 | Emily Wolf  | Shanghai | 2016.11.03 |
| xa20      |  3 | Emily Wolf  | Shanghai | 2016.07.03 |
| xa15      |  3 | Emily Wolf  | Tokyo    | 2014.02.22 |
| xa12      |  3 | Emily Wolf  | null     | 2014.02.22 |
+-----------+----+-------------+----------+------------+

Pożądany wynik:

+----+-------------+----------+---------------+
| ID |    NAME     |   CITY   | PREVIOUS_CITY |
+----+-------------+----------+---------------+
|  1 | John Smith  | Berlin   | München       |
|  2 | James Brown | Atlanta  | Boston        |
|  3 | Emily Wolf  | Shanghai | Tokyo         |
|  3 | Emily Wolf  | Tokyo    | null          |
+----+-------------+----------+---------------+

Próbowałem jednak użyć pierwszych i ostatnich wartości, jednak nie może uzyskać pożądanego wyniku.

select distinct id,
name, 
city, 
first_value(city) over (partition by id order by city) as previous_city
from test

Każda pomoc jest doceniana! Dziękuję Ci!

2
llorcs 20 październik 2020, 11:51

1 odpowiedź

Najlepsza odpowiedź

Użyj funkcji LAG, aby uzyskać miasto na poprzednią datę i wyświetlić tylko wiersze, w których aktualne miasto i wynik opóźnienia są różne:

WITH cte AS (
    SELECT t.*, LAG(CITY, 1, CITY) OVER (PARTITION BY ID ORDER BY "DATE") LAG_CITY
    FROM yourTable t
)

SELECT ID, NAME, CITY, LAG_CITY AS PREVIOUS_CITY
FROM cte
WHERE
    CITY <> LAG_CITY OR
    CITY IS NULL AND LAG_CITY IS NOT NULL OR
    CITY IS NOT NULL AND LAG_CITY IS NULL
ORDER BY
    ID, "DATE" DESC;

screen capture from demo link below

Próbny

Niektóre komentarze na temat sposobu stosowania LAG oraz sprawdzone wartości są uzasadnione. Używamy tutaj trzech wersji parametrów LAG. Drugi parametr oznacza liczbę rekordów, aby wyglądać wstecz, które w tym przypadku jest 1 (domyślnie). Trzeci parametr oznacza wartość domyślną do użycia, gdyby dany rekord za ID Partycja będzie najpierw . W takim przypadku korzystamy z domyślnych jako tej samej wartości CITY. Oznacza to, że pierwszy zapis nigdy nie pojawiłby się w zestawie wyników.

Dla klauzuli WHERE powyżej pasującego rekord jest jednym, dla którego miasto miasto i lag są różne, lub , gdzie jeden z dwóch będzie {X1}} i drugi nie { {X2}}. Jest to logika potrzebna do traktowania miasta NULL i niektórych niektórych NULL wartość miasta jako inna.

3
Tim Biegeleisen 20 październik 2020, 09:27