Yıllık tutarı yaymak ve sonra sql'de aya göre ekleme

Question 1

Şu anda şuna benzeyen bir tabloyla çalışıyorum:

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

bu amount bu tabloda toplam aylık tutar (ör. Yıllık üyelik için 15 dolar ödeyen 100 müşterim var, bu yüzden toplam aylık tutarım 1500 dolar olacaktı).

Bununla birlikte, yapmak istediğim şey (ve nasıl olduğuna dair hiçbir fikrim yok) miktarı 12'ye bölmek ve aylık bir gelire sahip olmak için geleceğe yaymaktır. 2021-09-01 için bir örnek olarak aşağıdakileri elde ederim:


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

Birisi bana bunu bir SQL sorgusu / Cte'de nasıl gerçekleştireceğime dair bir fikir verebilir mi diye merak ediyordum?

Question 2

Önemsediğiniz tüm ayların masanızda var olduğunu varsayarsak, şöyle bir şey öneririm:

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

Tabloda var olan her ay için bu, geçerli tutarın 1/12'si ile önceki 11 ayı (add_months işlevini kullanarak) toplamlar. Bence istediğin bu.

Birkaç not / düşünce:

Varsayıyorum (sütun adına göre) tüm tarihler month sütun 1'de sona eriyor, bu yüzden eşleşen günler veya sahip olma konusunda endişelenmemize gerek yok group by aynı ay için birden çok satır döndürür.
Yuvarlamak isteyebilirsin. SUMyaptım, çünkü bazı durumlarda 12'ye bölmek, ondalık basamaktan sonra para için istediğinizden daha fazla basamak verebilir (bu durumda, kalanları da göz önünde bulundurmanız gerekebilir).
Gerçekten ayda yalnızca bir işleminiz varsa (örneğinizde olduğu gibi), yapmanız gerekmez group by.
Önemsediğiniz aylar tablonuzda yoksa, bu işe yaramaz, ancak aynı şeyi bir ay tablosu oluşturarak da yapabilirsiniz. örneğin, 2020-01-01'de bir miktarınız varsa, ancak 2020-02-01'de hiçbir şey yoksa, bu 2021-02-01 için bir satır döndürmez.

Question 3

CTE = veri kümesini ayarlama

CTE_2 = pro-rate veri kümesi

SON 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

Önemsediğiniz tüm ayların masanızda var olduğunu varsayarsak, şöyle bir şey öneririm:

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

Tabloda var olan her ay için bu, geçerli tutarın 1/12'si ile önceki 11 ayı (add_months işlevini kullanarak) toplamlar. Bence istediğin bu.

Birkaç not / düşünce:

Varsayıyorum (sütun adına göre) tüm tarihler month sütun 1'de sona eriyor, bu yüzden eşleşen günler veya sahip olma konusunda endişelenmemize gerek yok group by aynı ay için birden çok satır döndürür.
Yuvarlamak isteyebilirsin. SUMyaptım, çünkü bazı durumlarda 12'ye bölmek, ondalık basamaktan sonra para için istediğinizden daha fazla basamak verebilir (bu durumda, kalanları da göz önünde bulundurmanız gerekebilir).
Gerçekten ayda yalnızca bir işleminiz varsa (örneğinizde olduğu gibi), yapmanız gerekmez group by.
Önemsediğiniz aylar tablonuzda yoksa, bu işe yaramaz, ancak aynı şeyi bir ay tablosu oluşturarak da yapabilirsiniz. örneğin, 2020-01-01'de bir miktarınız varsa, ancak 2020-02-01'de hiçbir şey yoksa, bu 2021-02-01 için bir satır döndürmez.

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

CTE = veri kümesini ayarlama

CTE_2 = pro-rate veri kümesi

SON 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

Yıllık tutarı yaymak ve sonra sql'de aya göre ekleme

Soru

En iyi cevabı

Diğer dillerde

Bu sayfa diğer dillerde

Bu kategoride popüler

Popüler soruları bu kategoride