SQL wybiera wszystkie wiersze na grupę po spełnieniu warunku

Question

Chciałbym zaznaczyć wszystkie wiersze dla każdej grupy po ostatnim spełnieniu warunku dla tej grupy. To powiązane pytanie zawiera skorelowaną odpowiedź podzapytania.

W moim przypadku będę miał miliony kategorii i setki milionów / miliardów wierszy. Czy można osiągnąć te same wyniki przy użyciu bardziej wydajnego zapytania?

Oto przykład. Warunkiem są wszystkie wiersze (na grupę) po ostatnim 0 w kolumnie warunkowej.

category | timestamp |  condition 
--------------------------------------
   A     |     1     |     0 
   A     |     2     |     1 
   A     |     3     |     0 
   A     |     4     |     1
   A     |     5     |     1
   B     |     1     |     0 
   B     |     2     |     1
   B     |     3     |     1

Wynik, który chciałbym osiągnąć, to

category | timestamp |  condition 
--------------------------------------
   A     |     4     |     1
   A     |     5     |     1
   B     |     2     |     1
   B     |     3     |     1

sql window-functions correlated-subquery

1

mv3 25 marzec 2020, 03:15

Answer 1

Możesz wypróbować funkcje okna:

select category, timestamp, condition
from (
    select 
        t.*,
        min(condition) over(partition by category order by timestamp desc) min_cond
    from mytable t
) t
where min_cond = 1

Okno min() z klauzulą order by oblicza minimalną wartość condition dla bieżącego i następnych wierszy tego samego category: możemy użyć go jako filtru do eliminacji wierszy dla którego istnieje nowszy wiersz z 0.

W porównaniu z podejściem skorelowanego podzapytania, zaletą korzystania z funkcji okna jest to, że zmniejsza liczbę skanów potrzebnych do wykonania tabeli. Oczywiście to obliczenie ma również koszt, więc musisz ocenić oba rozwiązania na podstawie przykładowych danych.

1

GMB 25 marzec 2020, 00:19

Answer 2

Jeśli chcesz wszystko po ostatnim 0, możesz użyć funkcji okna:

select t.*
from (select t.*,
             max(case when condition = 0 then timestamp end) over (partition by category) as max_timestamp_0
      from t
     ) t
where timestamp > max_timestamp_0 or
      max_timestamp_0 is null;

Z indeksem (category, condition, timestamp) skorelowana wersja podzapytania może również działać całkiem dobrze:

select t.*
from t
where t.timestamp > all (select t2.timestamp
                         from t t2
                         where t2.category = t.category and
                               t2.condition = 0
                        );

1

Gordon Linoff 25 marzec 2020, 00:17

SQL wybiera wszystkie wiersze na grupę po spełnieniu warunku

2 odpowiedzi

Podobne pytania

Nowe pytania

sql