Jak podzielenie rocznej kwoty, a następnie dodać według miesięcy w SQL

Question 1

Obecnie pracuję z tabelą, która wygląda w następujący sposób:

Month     | Transaction          | amount
2021-07-01| Annual Membership Fee| 45
2021-08-01| Annual Membership Fee| 145
2021-09-01| Annual Membership Fee| 2940
2021-10-01| Annual Membership Fee| 1545

to amount w tej tabeli jest podana łączna miesięczna kwota (np. U mnie 100 klientów, którzy zapłacili 15 dolarów za roczne członkostwo, więc moja ogólna miesięczna kwota wynosi 1500 dolarów).

Jednak to, co chciałbym zrobić (i nie mam pojęcia, jak to zrobić), to podzielić kwotę na 12 i rozprowadzić ją w przyszłości, aby mieć miesięczny dochód na miesiąc. Jako przykład dla 2021-09-01 ja dostałem następujący:


$2490/12 = $207.5 (dollars per month for the next 12 months)

in 2021-09-01 I would only get $207.5 for that specific month.

On 2021-10-01 I would get $1545/12 = $128.75 plus $207.5 from the previous month (total = $336.25 for 2021-10-01)

And the same operation would repeat onwards. The last period that I would collect my $207.5 from 2021-09-01 would be in 2022-08-01.

Zastanawiałem się, czy może ktoś mi dać wyobrażenie o tym, jak wykonać to w zapytaniu SQL/CTE?

Question 2

Zakładając, że wszystkie miesiące, o które dbają, istnieją w biurku, ja bym proponował coś takiego:

SELECT 
 month, 
 (SELECT SUM(m2.amount/12) FROM mytable m2 WHERE m2.month BETWEEN ADD_MONTHS(m1.month, -11) AND m1.month) as monthlyamount
FROM mytable m1
GROUP BY month
ORDER BY month

Dla każdego miesiąca podanego w tabeli, to wynosi 1/12 od aktualnej kwoty plus poprzednie 11 miesięcy (za pomocą funkcji add_months). Myślę, że to jest to, czego chcesz.

Kilka uwag/pomysłów:

Zakładam (opierając się na nazwie kolumny), że wszystkie daty w month kolumna kończy się 1-go dnia, więc nie musimy martwić się o pasujących dniach lub magazynie group by oddajcie kilka wierszy za ten sam miesiąc.
Może chcesz obejść SUMzrobiłem tak, ponieważ w niektórych przypadkach podział na 12 może dać ci więcej cyfr po po przecinku, co chcesz za pieniądze (choć w tym przypadku konieczne jest także wziąć pod uwagę pozostałości).
Jeśli masz naprawdę tylko jedna transakcja w miesiącu (jak w twoim przykładzie), nie trzeba wykonywać group by.
Jeżeli miesięcy, o które dbają, w twojej tabeli nie, to nie zadziała, ale można zrobić to samo, tworząc tabelę miesięcy. Na przykład Jeśli masz suma w 2020-01-01, ale nic w 2020-02-01, to nie zwróci wiersz za 2021-02-01.

Question 3

CTE = konfiguracja zestawu danych

CTE_2 = zestaw danych z dużą prędkością

OSTATECZNY SQL = select future_cal_month,sum(pro_rated_amount) from cte_2 group by 1

with cte as (
select '2021-07-01' cal_month,'Annual Membership Fee' transaction ,45 amount
union all select '2021-08-01' cal_month,'Annual Membership Fee' transaction ,145 amount
union all select '2021-09-01' cal_month,'Annual Membership Fee' transaction ,2940 amount
union all select '2021-10-01' cal_month,'Annual Membership Fee' transaction ,1545 amount) 
, cte_2 as (    
select 
    dateadd('month', row_number() over (partition by cal_month order by 1), cal_month) future_cal_month
    ,amount/12 pro_rated_amount
from 
     cte 
    ,table(generator(rowcount => 12)) v)
select 
  future_cal_month
, sum(pro_rated_amount) 
from 
  cte_2 
group by 
  future_cal_month

EdmCoff · Answer 1 · 2021-11-23T16:06:20

Zakładając, że wszystkie miesiące, o które dbają, istnieją w biurku, ja bym proponował coś takiego:

SELECT 
 month, 
 (SELECT SUM(m2.amount/12) FROM mytable m2 WHERE m2.month BETWEEN ADD_MONTHS(m1.month, -11) AND m1.month) as monthlyamount
FROM mytable m1
GROUP BY month
ORDER BY month

Dla każdego miesiąca podanego w tabeli, to wynosi 1/12 od aktualnej kwoty plus poprzednie 11 miesięcy (za pomocą funkcji add_months). Myślę, że to jest to, czego chcesz.

Kilka uwag/pomysłów:

Zakładam (opierając się na nazwie kolumny), że wszystkie daty w month kolumna kończy się 1-go dnia, więc nie musimy martwić się o pasujących dniach lub magazynie group by oddajcie kilka wierszy za ten sam miesiąc.
Może chcesz obejść SUMzrobiłem tak, ponieważ w niektórych przypadkach podział na 12 może dać ci więcej cyfr po po przecinku, co chcesz za pieniądze (choć w tym przypadku konieczne jest także wziąć pod uwagę pozostałości).
Jeśli masz naprawdę tylko jedna transakcja w miesiącu (jak w twoim przykładzie), nie trzeba wykonywać group by.
Jeżeli miesięcy, o które dbają, w twojej tabeli nie, to nie zadziała, ale można zrobić to samo, tworząc tabelę miesięcy. Na przykład Jeśli masz suma w 2020-01-01, ale nic w 2020-02-01, to nie zwróci wiersz za 2021-02-01.

Adrian White · Answer 2 · 2021-11-23T21:00:56

CTE = konfiguracja zestawu danych

CTE_2 = zestaw danych z dużą prędkością

OSTATECZNY SQL = select future_cal_month,sum(pro_rated_amount) from cte_2 group by 1

with cte as (
select '2021-07-01' cal_month,'Annual Membership Fee' transaction ,45 amount
union all select '2021-08-01' cal_month,'Annual Membership Fee' transaction ,145 amount
union all select '2021-09-01' cal_month,'Annual Membership Fee' transaction ,2940 amount
union all select '2021-10-01' cal_month,'Annual Membership Fee' transaction ,1545 amount) 
, cte_2 as (    
select 
    dateadd('month', row_number() over (partition by cal_month order by 1), cal_month) future_cal_month
    ,amount/12 pro_rated_amount
from 
     cte 
    ,table(generator(rowcount => 12)) v)
select 
  future_cal_month
, sum(pro_rated_amount) 
from 
  cte_2 
group by 
  future_cal_month

Jak podzielenie rocznej kwoty, a następnie dodać według miesięcy w SQL

Pytanie

Najlepsza odpowiedź

W innych językach

Ta strona jest w innych językach

Popularne w tej kategorii

Najczęściej zadawane pytania w tej kategorii