Witam,
poszukuję rozwiązania do poniższego zadania.
Chcę stworzyć licznik dla danego Id oparty o WARTOŚĆ.
Licznik ma się zerować co 4 lub jeśli wartość jest inna niż 10, jak w załączonym przykładzie.
W tabeli jest kilkaset ID i dla każdego ID licznik musi działać osobno.
Czy ktoś ma jakiś pomysł?
trigger, ale i tak będzie problem jak będzie dużo insertów w krótkim czasie. Aby to było w 100% pewne to trzeba by mieć dodatkową tabelkę, a w niej id i aktualny_licznik. Pobierać z blokowaniem stan licznika dla danego id, wyliczać nową wartość, wstawiać rekord i updejtować ten w tabelce z licznikiem
Panczo napisał(a):
Która wersja SQL Servera?
Co determinuje kolejność?
2014, kolejność determinuje data
Potrzebujesz tego w czasie rzeczywistym? Jeżeli nie, to co jakiś czas możesz robić update na kolumnie i problem staje się całkiem prosty do rozwiązania.
To jest tabela, która jest zasilana danymi codziennie.
Codziennie wpada kilkadziesiąt wierszy danych. Liczba wierszy cały czas rośnie.
W pewnym momencie osiagnie kilka tysięcy wierszy więc update'owanie tej tabeli przy kilkuset ID nie tym o czym marze :)
wolałbym ustawić tutaj automat.
Możesz stworzyć joba, który będzie codziennie robił update na tabeli. Nie korzystam z MS serwera, ale ktoś na pewno Ci podpowie jak to zrobić.
Not skoro to jest zrzut z jakiegoś systemu, to jaki masz wpływ na wykonanie tego importu.
Mnie się wydaje, że najrozsądniej jest paczkę danych dać do jakiejś tempowej tabeli, wyliczyć te id i wrzucić do tabeli docelowej.
Chyba, że na docelowej tabeli działają inni użytkownicy i dodają usuwają dane. Z ciekawości, do czego służyć ma takie numerowanie?
p.s. jak słyszę że ktoś mi mówi, że coś jest niemożliwe to na pewno się przydarzy ;)
Numerowanie jest potrzebne do weryfikowania poprawnosci podpisanego kontraktu pomiedzy moja firma a klientami. Jezeli klient podczas czterech wizyt przedstawiciela z rzędu spelni warunki kontraktu to jest mu wyplacany bonus. Bonus jest wyplacany ci 4 poprawne wizyty. U danego klienta jest jedna wizyta w tygodniu. Jesli na ktorejs z wizyt nie spelnia warunkow kontraktu to licznik wraca do 1.
No to skoro nie potrzebujesz numerka, to wystarczy sprawdzić ile rekordów jest pomiędzy zmianami i podzielić przez 4 bez reszty, ale pokolei
Dla twoich danych
SQL CREATE TABLE T
([id] int, [wartosc] int, [data] int)
;
INSERT INTO T
([id], [wartosc], [data])
VALUES
(354, 10,1),
(354, 10,2),
(354, 10, 3),
(354, 10, 4),
(354, 10, 5),
(354, 10, 6),
(354, 8, 7),
(354, 10, 8),
(354, 10, 9),
(354, 10, 10),
(354, 10, 11)
Date zrobiłem int, ale to bez znaczenia bo ta kolumna służy tylko do sprawdzenia kolejności.
Pierwsze co to musimy zidentyfikować początek i koniec przedziałów czwórek, w naszym przypadku to są dwa przedziały:
data: 1-6 i 7-11
Do tego posłuży zapytanie
select
* ,
CASE
WHEN (LAG(WARTOSC, 1,-1) OVER (PARTITION BY ID ORDER BY DATA) <> WARTOSC AND WARTOSC <> 10)
OR LAG(WARTOSC, 1,-1) OVER (PARTITION BY ID ORDER BY DATA) = -1 THEN
'START'
WHEN LEAD(WARTOSC, 1,0) OVER (PARTITION BY ID ORDER BY DATA) <> 10 THEN
'STOP'
ELSE ''
END AS STARTSTOP
,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATA) R
from
t
Które da w wyniku:
| id | wartosc | data | STARTSTOP | R |
|---|---|---|---|---|
| 354 | 10 | 1 | START | 1 |
| 354 | 10 | 2 | 2 | |
| 354 | 10 | 3 | 3 | |
| 354 | 10 | 4 | 4 | |
| 354 | 10 | 5 | 5 | |
| 354 | 10 | 6 | STOP | 6 |
| 354 | 8 | 7 | START | 7 |
| 354 | 10 | 8 | 8 | |
| 354 | 10 | 9 | 9 | |
| 354 | 10 | 10 | 10 | |
| 354 | 10 | 11 | STOP | 11 |
Teraz potrzebujemy tylko wierszy z wpisami w kolumnie startstop:
| id | wartosc | data | STARTSTOP | R |
|---|---|---|---|---|
| 354 | 10 | 1 | START | 1 |
| 354 | 10 | 6 | STOP | 6 |
| 354 | 8 | 7 | START | 7 |
| 354 | 10 | 11 | STOP | 11 |
Pozostaje matematyka: czyli dla każdego wpisu stop, odejmujemy od r poprzednie r dodajmy 1 i dzielimy bez reszty przez 4:
select
*
,case when startstop = 'stop' then
cast(r+1-LAG(r, 1,0) OVER (PARTITION BY ID ORDER BY DATA) as integer)/4
else
0
end iloscbonusow
from f
Wynik
| id | wartosc | data | STARTSTOP | R | iloscbonusow |
|---|---|---|---|---|---|
| 354 | 10 | 1 | START | 1 | 0 |
| 354 | 10 | 6 | STOP | 6 | 1 |
| 354 | 8 | 7 | START | 7 | 0 |
| 354 | 10 | 11 | STOP | 11 | 1 |
Reszta to podsumowanie kolumny iloscbonusow
Całe rozwiązanie:
WITH cteT as (
select
* ,
CASE
WHEN (LAG(WARTOSC, 1,-1) OVER (PARTITION BY ID ORDER BY DATA) <> WARTOSC AND WARTOSC <> 10)
OR LAG(WARTOSC, 1,-1) OVER (PARTITION BY ID ORDER BY DATA) = -1 THEN
'START'
WHEN LEAD(WARTOSC, 1,0) OVER (PARTITION BY ID ORDER BY DATA) <> 10 THEN
'STOP'
ELSE ''
END AS STARTSTOP
,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATA) R
from
t)
, f as (
select
*
from
cteT
where
startstop in ('start','stop')
)
SELECT
ID
,SUM(ILOSCBONUSOW) B
FROM (
select
id
,case when startstop = 'stop' then
cast(r+1-LAG(r, 1,0) OVER (PARTITION BY ID ORDER BY DATA) as integer)/4
else
0
end iloscbonusow
from
f) DT
GROUP BY
ID
Wynik:
| ID | B |
|---|---|
| 354 | 2 |
Dostałem za zadanie rozwiązać to za pomocą kursora.
Stworzyłem kursor ale licznik nie zeruje mi się kiedy zmienia się ID klienta. Czy ktoś może mi powiedzieć gdzie popełniłem błąd?
===============================================================================================================
DECLARE @row int
DECLARE @kli int
DECLARE @poprzedni_kli int
DECLARE kursor SCROLL cursor for
SELECT rowid, rep_id FROM crm_target_kampanie WHERE campaign = 'akcja_lato_licznik' and target=10 ORDER BY rowid
DECLARE @counter int=1
Open kursor
FETCH NEXT FROM kursor
INTO @row, @kli
WHILE @@FETCH_STATUS=0
BEGIN
SET @poprzedni_kli=@kli
IF @poprzedni_kli=@kli
BEGIN
update crm_target_kampanie set attrib1=@counter WHERE campaign = 'akcja_lato_licznik' and rep_id=@kli and rowid=@row
SET @counter=@counter+1
END
ELSE
BEGIN
SET @counter=1
update crm_target_kampanie set attrib1=@counter WHERE campaign = 'akcja_lato_licznik' and rep_id=@kli and rowid=@row
SET @counter=@counter+1
END
FETCH NEXT FROM kursor
INTO @row, @kli
END
CLOSE kursor
DEALLOCATE kursor
=================================================================================================================
Panczo napisał(a):
A jak ci ma się zerować, skoro
@poprzedni_kli=@klizawsze będzie prawdziwe, bo PRZED porównaniem przypisujesz bieżącą wartość do poprzedniej?
Domyśliłem się, że to nie działa ale właśnie nie wiem w którym miejscu w takim razie umieścić "SET @poprzedni_kli=@kli"
Najprościej wtedy kiedy się zmienia...
DECLARE @row int
DECLARE @kli int
DECLARE @poprzedni_kli int=-1
DECLARE kursor SCROLL cursor for
SELECT rowid, rep_id FROM crm_target_kampanie WHERE campaign = 'akcja_lato_licznik' and target=10 ORDER BY rowid
DECLARE @counter int=1
Open kursor
FETCH NEXT FROM kursor INTO @row, @kli
WHILE @@FETCH_STATUS=0
BEGIN
IF @poprzedni_kli=@kli
BEGIN
update crm_target_kampanie set attrib1=@counter WHERE campaign = 'akcja_lato_licznik' and rep_id=@kli and rowid=@row
SET @counter=@counter+1
END
ELSE
BEGIN
SET @poprzedni_kli=@kli
SET @counter=1
update crm_target_kampanie set attrib1=@counter WHERE campaign = 'akcja_lato_licznik' and rep_id=@kli and rowid=@row
SET @counter=@counter+1
END
FETCH NEXT FROM kursor INTO @row, @kli
END
CLOSE kursor
DEALLOCATE kursor
Dużo czytelniej jest jednak uprościć samą pętle:
WHILE @@FETCH_STATUS=0
BEGIN
IF @poprzedni_kli<>@kli
BEGIN
SET @poprzedni_kli=@kli
SET @counter=1
END
update crm_target_kampanie set attrib1=@counter WHERE campaign = 'akcja_lato_licznik' and rep_id=@kli and rowid=@row
SET @counter=@counter+1
FETCH NEXT FROM kursor INTO @row, @kli
END