Witam,
mam widok, w którym mam ID usera, datę założenia konta i nazwę oprogramowania.
Zapytaniem wyciągam liczbę nowych użytkowników każdego miesiąca, ale jak mogę dorzucić kolumnę, w której będzie mi sumowało wszystkie poprzednie miesiące?
SELECT CONVERT(VARCHAR(7), Date, 111) Date, COUNT(DISTINCT UserId) NewUsers
from view_myView
where Software = 'Testowa apka'
GROUP BY CONVERT(VARCHAR(7), Date, 111)
ORDER BY CONVERT(VARCHAR(7), Date, 111)
Ale co dokładnie ma sumować ? Wypluwasz liczbę założonych userów po dacie założenia konta i w 3 kolumnie chcesz sumować .... co ?
@BlackBad: może nie do końca jasno opisałem co chciałbym osiągnąć.
Chodzi mi o to że teraz to zapytanie wypluje coś w stylu:
Date NewUsers
2020/01 25
2020/02 23
2020/03 17
Czyli w styczniu doszło 25 użytkowników, w lutym 23 a w marcu 17.
Teraz chciałbym dostać oprócz tego łączną ilość nowych użytkowników, a nie tylko z danego miesiąca, czyli:
Date NewUsers TotalUsers
2020/01 25 25
2020/02 23 48
2020/03 17 65
Czy wartość totalUsers to nie jest ilość z poprzedniego miesiąca + to, co przybyło w bieżącym miesiącu?
Jeśli tak, to masz 2 opcje - albo sobie to liczyć za każdym razem, albo zapamiętywać liczbę userów na koniec każdego miesiąca. To drugie wymaga lekkich zmian w bazie, ale za to będzie dla niej przyjaźniejsze - nie będziesz za każdym razem jej obciążał obliczaniem wartości, które się i tak nie będą zmieniać, więc można je wyliczyć raz i potem tylko odczytać.
Np tak z wykorzystanuiem Row number .. pisane na szybko wiec takie z pupy nazwy ale chodzi o zsadę:
with cte as
(SELECT 1 id, '2020-01-01' Udata, 15 as licz
UNION
SELECT 1 id, '2020-01-02' Udata, 20 as licz
UNION
SELECT 1 id, '2020-01-03' Udata, 30 as licz
)
, dane as
(SELECT id, Udata, licz, ROW_NUMBER() OVER (Partition by id Order by Udata) rn from cte)
SELECT d1.id, d1.udata, d1.licz, d1.licz + ISNULL(d2.licz,0) Total
FROM dane d1
left join dane d2
ON d1.id =d2.id
and d1.rn - 1 = d2.rn
--EDIT: 2 wersja do sprawdznie co optymalniejsze
SELECT d1.id, d1.udata, d1.licz, (SELECT SUM(licz) Total FROM dane d2 WHERE d2.Udata <= d1.Udata)
FROM dane d1
Bardziej mi zależy no obliczaniu tej wartości co zapytanie
Pytanie jeszcze, czy chcesz to mieć w taki sposób przedstawione, jak podałeś wyżej - czyli dla każdego miesiąca liczbę nowych plus całkowitą?
Jeśli tak, to chyba najprościej będzie nie robić jednego zapytania, które by to ogarnęło (co pewnie jest do zrobienia, ale na szybko nie przychodzi mi żadne rozwiązanie do głowy) co zrobienie w pętli serii zapytań - każde obliczające wartość nowych userów dla jednego okresu. Zauważ, że liczba całkowita jest po prostu zwykłą sumą poprzedniej liczby wszystkich oraz nowych w danym okresie.
Bierzesz od początku historii - pierwszy miesiąc, podliczasz ilu userów w tym okresie się przyłączyło. Potem wypluwasz na ekran wartości total oraz new, które w pierwszym okresie oczywiście będą takie same. Następnie obliczasz liczbę nowych userów w kolejnym okresie i ponownie - wypluwasz wartość nowych, a potem sumujesz wartość nowych z total z poprzedniego okresu i w ten sposób masz całkowitą liczbę. Następnie liczysz kolejny okres i znowu, poprzez zwiększenie wartości **total **z poprzedniego etapu o liczbę nowych w bieżącym okresie, masz wartość wszystkich userów.
Tylko zauważ, że to rozwiązanie jest obciążające dla bazy. Nawet jakby to zrobić tak, jak napisał @BlackBad to za każdym razem baza musi to obliczyć. Przy niewielkiej bazie to nie jest większy problem, ale jeśli historia będzie sięgać wielu miesięcy czy lat wstecz, userów będą tysiące, a samo wyświetlanie historii będzie odpalane w miarę często, to niepotrzebnie będziesz dowalać bazie niepotrzebne obliczenia.
Trzymanie raz wyliczonych wyników cząstkowych jest o wiele lepszą opcją. Zresztą ten problem jest bardzo podobny do tego wątku - Saldo użytkowników na własnej stronie baza danych MySQL. Tam także powinno się pewne dane raz wyliczyć i zachować, a nie obliczać za każdym razem od nowa.
Zdecydowanie wyliczanie tego za każdym razem jeśli danych mamy naprawdę bardzo dużo i taki raport odpalany byłby często to nie jest najlepszy pomysł. Ale o tym może tylko zdecydować OP. Generalnie takie informacje nie wydają się przydatne w dziennym użyciu więc to chyba jakiś wyjątek. Rozumiem raport z sumą wszystkich userow przed today/week/month + suma total ale tak dzień po dniu? Tak czy inaczej - w ten sposób się da :) A czy warto ... to zależy ;)
BlackBad napisał(a):
Np tak z wykorzystanuiem Row number .. pisane na szybko wiec takie z pupy nazwy ale chodzi o zsadę:
--EDIT: 2 wersja do sprawdznie co optymalniejsze SELECT d1.id, d1.udata, d1.licz, (SELECT SUM(licz) Total FROM dane d2 WHERE d2.Udata <= d1.Udata) FROM dane d1
Spróbowałem tego sposobu i fajnie śmiga, dzięki. Jedynie co to musiałem podmienić sum na count w tym zagnieżdżonym zapytaniu bo mi jakieś dziwne liczby wychodziły.
Kod działający:
SELECT CONVERT(VARCHAR(7), v1.Date, 111) Date,
COUNT(DISTINCT v1.UserId) NewUsers,
(
SELECT COUNT(DISTINCT UserId) --tutaj musiałem podmienić SUM <-> COUNT
FROM view_myView v2
WHERE v2.Software = 'Testowa aplikacja'
AND CONVERT(VARCHAR(7), v2.Date, 111) <= CONVERT(VARCHAR(7), v1.Date, 111)
) TotalUsers
from view_myView v1
where v1.Software = 'Testowa aplikacja'
GROUP BY CONVERT(VARCHAR(7), v1.Date, 111)
ORDER BY CONVERT(VARCHAR(7), v1.Date, 111)
Wynik zapytania z count:
Wynik zapytania z sum:
@BlackBad tak się teraz przyjrzałem temu rozwiązaniu i jest tak proste i banalne.. Aż mi głupio że sam na nie nie wpadłem tylko pisałem post na forum.
Mozna użyć window function i wtedy jest prościej:
select
software
,miesiac
,NewUsers
,sum(newusers) over (partition by software order by miesiac) TotalUser
from (select
software
,CONVERT(VARCHAR(7), [Date], 111) miesiac
, count(*) NewUsers
from
view_myView
group by
software
,CONVERT(VARCHAR(7), [Date], 111)
) u
order by 1,2
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a79635e7114685854645e4d42de5e155
Można i tak (kod PostgreSQL):
with x(d,uid) as
(values
('2020-01-01'::date, 123),
('2020-01-01', 122),
('2020-01-01', 124),
('2020-01-01', 125),
('2020-02-01', 13),
('2020-02-01', 12),
('2020-03-01', 1),
('2019-12-12',34),
('2019-12-12',342),
('2019-12-12',341),
('2019-11-12',3)
)
select
d::varchar(7),
count(uid) ilosc,
sum(count(uid)) over(order by d::varchar(7) rows between unbounded preceding and 1 preceding) poprzednie
from
x
group by 1
order by 1