Ako šíriť ročné množstvo a potom pridajte do mesiaca, v SQL

Question 1

V súčasnosti pracujem s tabuľkou, ktorá vyzerá takto:

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

na amount na tejto tabuľke je celkový mesačný objem (ex. Som 100 zákazníci, ktorí zaplatili $15 za ročné členstvo, takže môj celkový mesačný objem by $1500).

Avšak to, čo by som chcel robiť (a nemám tušenie, ako) je rozdeliť sumu 12 a šíri sa do budúcnosti, aby mal mesačný príjem za mesiac. Ako príklad pre 2021-09-01 by som si takto:


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

Ja som sa opýtať ak niekto môže mi dať predstavu o tom, ako vykonať túto SQL dopyt/CTE?

Question 2

Za predpokladu, že všetky mesiacov starostlivosť o existovať v tabuľke, navrhoval by som niečo ako:

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

Za každý mesiac, ktorý existuje v tabuľke tejto sumy 1/12. aktuálneho množstva plus predchádzajúceho 11 mesiacov (pomocou add_months funkcia). Myslím si, že to, čo chcete.

Niekoľko poznámok/myšlienky:

Ja som za predpokladu, že (založené na názov stĺpca), že všetky dátumy v month stĺpec konci 1., tak sme sa nemusíte starať o vyrovnanie dní alebo s group by návrat viacerých riadkov na tom istom mesiaci.
Možno budete chcieť kolo SUMs I urobil, pretože v niektorých prípadoch delení 12 môže vám viac číslice po desatinnej ako chcete, za peniaze (aj keď, v takomto prípade možno budete tiež musieť zvážiť, odrezky).
Ak ste naozaj mať iba jednu transakciu mesačne (rovnako ako v príklade), nemusíte robiť group by.
Ak mesiacov, na ktorých vám záleží neexistujú v tabuľke, a potom to nebude fungovať, ale mohli by ste urobiť to isté, čo vytvára tabuľky mesiacov. napríklad Ak máte výšku na 2020-01-01 ale nič v 2020-02-01, potom to nebude vrátiť riadok pre 2021-02-01.

Question 3

CTE = nastaviť dataset

CTE_2 = pro-sadzba dataset

VÝSLEDNÝ 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

Za predpokladu, že všetky mesiacov starostlivosť o existovať v tabuľke, navrhoval by som niečo ako:

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

Za každý mesiac, ktorý existuje v tabuľke tejto sumy 1/12. aktuálneho množstva plus predchádzajúceho 11 mesiacov (pomocou add_months funkcia). Myslím si, že to, čo chcete.

Niekoľko poznámok/myšlienky:

Ja som za predpokladu, že (založené na názov stĺpca), že všetky dátumy v month stĺpec konci 1., tak sme sa nemusíte starať o vyrovnanie dní alebo s group by návrat viacerých riadkov na tom istom mesiaci.
Možno budete chcieť kolo SUMs I urobil, pretože v niektorých prípadoch delení 12 môže vám viac číslice po desatinnej ako chcete, za peniaze (aj keď, v takomto prípade možno budete tiež musieť zvážiť, odrezky).
Ak ste naozaj mať iba jednu transakciu mesačne (rovnako ako v príklade), nemusíte robiť group by.
Ak mesiacov, na ktorých vám záleží neexistujú v tabuľke, a potom to nebude fungovať, ale mohli by ste urobiť to isté, čo vytvára tabuľky mesiacov. napríklad Ak máte výšku na 2020-01-01 ale nič v 2020-02-01, potom to nebude vrátiť riadok pre 2021-02-01.

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

CTE = nastaviť dataset

CTE_2 = pro-sadzba dataset

VÝSLEDNÝ 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

Ako šíriť ročné množstvo a potom pridajte do mesiaca, v SQL

Otázka

Najlepšiu odpoveď

V iných jazykoch

Táto stránka je v iných jazykoch

Populárne v tejto kategórii

Obľúbené otázky v tejto kategórii