[T.....y]

#sql #bazydanych #programowanie

Mam tabele w takiej postaci (nazwijmy ją "Tabela"):
A 1 0
A 2 1
A 3 1
A 4 0
A 5 0
B 1 0
B 2 0
B 3 0
B 4 1

Nazwę pierwszą kolumnę "Typem", drugą "Wartością", a trzecią "Flagą".
Moim zadaniem jest przetransformować tabele tak, że dla danego wiersza muszę odjąć od wartości tyle ile jest wierszy o tym samym typie, mniejszej wartości i fladze=1. Czyli wynikiem byłoby:

A 1
A 2
A 2 (-1, bo jeden wiersz "powyżej miał flage=1)
A 2 (-2, bo dwa wiersze "powyżej" miały flage=1)
A 3 (-2, bo dwa wiersze "powyżej" miały flage=1)
B 1
B 2
B 3
B 4

Użyłem słowa "powyżej", bo w tym przykładzie faktycznie są one powyżej dla łatwiejszego zobrazowania problemu.
Chciałbym to zrobić jak najbardziej możliwie optymalnie pod względem czasu. Aktualnie od drugiej kolumny odejmuje coś takiego i jest zdecydowanie za wolne:
Coalesce(SUM(CASE WHEN Flaga = 1 THEN 1 ELSE 0 END) OVER
(PARTITION BY Typ ORDER BY Wartość
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)

Próbowałem też odjąć:
SELECT COUNT(*) FROM temp where temp.Typ = Tabela.Typ and temp.Wartość < Tabela.Wartość and Tabela.Flaga = 1
ale to było jeszcze wolniejsze xD

Może wy będziecie mieli jakieś lepsze pomysły.

[michal985]

@TenAnonToKlopoty: nie wiem jak będzie z wydajnością tego, ale zmontowałem coś takiego:

with
x as (
select type, value
from test
where flag=1
)
,y as (
select t.*
from test t
cross join x
where
t.type=x.type
and t.value>x.value
)
,z as (
select
count(*) AS subtract
,type
,value
from
y
group BY
type
,value
)
SELECT
t.*
,z.subtract
FROM
test t
LEFT JOIN
z ON z.type=t.type AND t.value=z.value

edit: wykop

[T.....y]

@michal985: ojoj jeszcze dłużej się robi, ale dzięki za chęci

[michal985]

spoko, tak myslalem ze moze nie byc za dobrze z tym cross joinem. daj znac jak cos wykombinujesz, ciekawy jestem jakie jest optymalne rozwiazanie

[DarkAlchemy]

@TenAnonToKlopoty:
SELECT
*,
Wartosc - SUM(Flaga) OVER (PARTITION BY Typ ORDER BY Wartosc ROWS UNBOUNDED PRECEDING) as Wynik
FROM
Tabela

Chyba najszybsze, jak Flaga jest typu Bit/Boolean to możliwe że musisz ją w tej sumie konwertować na int

A jak chcesz jeszcze bardziej przyspieszyć to index stworzony w ramach reguły POC (Partition, Ordering, Covering):
CREATE NONCLUSTERED INDEX IX_void ON Tabela(Typ, Wartosc) INCLUDE (Flaga)

Składnia pod MS SQL

[T.....y]

@DarkAlchemy: Dzisiaj jeszcze próbowałem oba, indeks też i niestety nie polepszało i zdecydowałem się dodać trigger w tabeli wejściowej aby od razu zmieniał dane na takie jakich potrzebuję na wyjściu

[DarkAlchemy]

@TenAnonToKlopoty: na moim serwerze testowym, dla miliona wierszy, z indeksem, zapytanie wykonuje się w ~15 sekund, z czego ~12 sekund to samo rzucanie wyników do SSMS, silnik swoje zadanie kończy po niecałych 3 sekundach

[DarkAlchemy]

@TenAnonToKlopoty: kurde, to co Ty tam trzymasz albo jakiej wydajności się spodziewasz? :D
W ogóle, jaka baza?

[T.....y]

@DarkAlchemy: problemem jest to, że to jest częścią większej procedury i chciałem najmniejszym kosztem skrócić ten jeden fragment ale okazało się, że jednak trzeba zmienić trochę więcej

[T.....y]

@DarkAlchemy: u mnie to było 200tys wierszy ale odpytywanie o te dane teoretycznie może się wykonywać do paredziesięciu razy na sekunde (praktycznie do kilku razy)

[T.....y]

@DarkAlchemy: a no i dane do tej tabeli są bardzo dynamiczne, jest pewne okno czasowe którego dotyczą ale praktycznie co sekunde moze pojawić się tam nowy wiersz

[DarkAlchemy]

@TenAnonToKlopoty: dobra to rozumiem teraz po co tak. Jeszcze jest dziwna sztuczka którą możesz użyć jak robisz na SQL Server w wersji 2016 lub wyżej.
Tworzysz tabelę tymczasową z jedną kolumną, wrzucasz tam jedną wartość (np. kolumna Dupa i wartość INT = 1) i... Zakładasz na niej CLUSTERED COLUMNSTORE INDEX
Potem robisz CROSS APPLY do swojej tabeli w zapytaniu i jest opcja, że wtedy wymusisz na silniku użycie Batch Mode zamiast