Sumowanie poprzednich wartości w kolumnie

0

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)
1

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 ?

0

@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
0

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ć.

2

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
1

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 - https://4programmers.net/Foru[...]nej_stronie_baza_danych_mysql. Tam także powinno się pewne dane raz wyliczyć i zachować, a nie obliczać za każdym razem od nowa.

0

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 ;)

1
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:
screenshot-20201229110903.png

Wynik zapytania z sum:
screenshot-20201229110935.png

@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.

3

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=sq[...]635e7114685854645e4d42de5e155

1

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

1 użytkowników online, w tym zalogowanych: 0, gości: 1, botów: 0