วิธีที่จะกระจายนประจำปีของจำนวนมากแล้วก็เพิ่มโดยเดือนในภาษา sql

Question 1

ตอนนี้ฉันกำลังทำงานกับโต๊ะนั่นดูเหมือนนี้:

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

คน amount บนโต๊ะนั่นคือทั้งหมดเดือนจำนวนมา(อดีต. ฉันมี 100 ลูกค้าที่จ่ายเงินให้$สำหรับ 15 คนประจำปีของสมาชิก,ดังนั้นของฉันทั้งหมดเดือนจำนวนมากจะเป็น$1500).

แต่สิ่งที่ฉันต้องการจะทำ(และฉันไม่มีเบาะแสว่า)เป็น--แบ่งจำนวนเงินโดบประมาณโดย 12 คนและกระจายเข้าไปในอนาคตเพื่อนมีรายได้นับเดือนต่อเดือน เป็นตัวอย่างสำหรับ 2021-09-01 ฉันจะได้ต่อไปนี้:


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

ฉันสงสัยว่าถ้าใครบางคนสามารถให้ฉันเป็นความคิดของวิธีที่จะแสดงในร้องขอแบบ sql/CTE?

Question 2

สมมติว่าทั้งหมดเดือนคุณสนใจเรื่องมีตัวตนอยู่ในโต๊ะของคุณฉันจะแนะนำอะไรอย่าง:

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

สำหรับแต่ละเดือนนั้นอยู่ในโต๊ะนี้ดับ 1/12 ของปัจจุบันจำนวนมากอีกอย่างก่อน 11 เดือน(ใช้ add_months ฟังก์ชัน). ฉันคิดว่านั่นเป็นสิ่งที่คุณต้องการ

บางบันทึกย่อ/ความคิด:

ฉันกำลังคิดว่าคุณจะได้(พื้นฐานบนคอลัมน์ชื่อว่าทั้งหมดที่วันที่ใน month คอลัมน์สุดในหน่วยที่ 1 ก็ดังนั้นเราไม่ต้องห่วงเรื่องตรงกับวันหรือมี group by กลับมาหลายแถวให้เหมือนกันเดือน
คุณอาจจะอยากรอง SUMs ผมทำตั้งแต่ในบางคดี dividing โดย 12 อาจจะให้คุณมีตัวเลขหลังจากที่ทศนิยมกว่าที่คุณต้องการสำหรับเงิน(แม้ว่าในกรณีนั้นคุณอาจจะต้องพิจารณา remainders).
ถ้าคุณต้องการต่อรองต่อเดือน(เช่นในตัวอย่าง),คุณไม่จำเป็นต้องทำ group by.
ถ้าเดือนคุณสนใจเรื่องไม่มีตัวตนอยู่ในโต๊ะของคุณแล้วนี้จะไม่ทำงานแต่คุณจะทำอย่างเดียวกันกับที่กำลังสร้างเป็นโต๊ะของเดือน e.g. ถ้าคุณมีข้อจำนวนมากบน 2020-01-01 แต่ไม่มีอะไรใน 2020-02-01 งั้นนี่จะไม่กลับมาเป็นแถวสำหรับ 2021-02-01.

Question 3

CTE=ตั้งค่าวันที่

CTE_2=โปร-วันที่อัตราการ

สุดท้ายภาษา 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

สมมติว่าทั้งหมดเดือนคุณสนใจเรื่องมีตัวตนอยู่ในโต๊ะของคุณฉันจะแนะนำอะไรอย่าง:

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

สำหรับแต่ละเดือนนั้นอยู่ในโต๊ะนี้ดับ 1/12 ของปัจจุบันจำนวนมากอีกอย่างก่อน 11 เดือน(ใช้ add_months ฟังก์ชัน). ฉันคิดว่านั่นเป็นสิ่งที่คุณต้องการ

บางบันทึกย่อ/ความคิด:

ฉันกำลังคิดว่าคุณจะได้(พื้นฐานบนคอลัมน์ชื่อว่าทั้งหมดที่วันที่ใน month คอลัมน์สุดในหน่วยที่ 1 ก็ดังนั้นเราไม่ต้องห่วงเรื่องตรงกับวันหรือมี group by กลับมาหลายแถวให้เหมือนกันเดือน
คุณอาจจะอยากรอง SUMs ผมทำตั้งแต่ในบางคดี dividing โดย 12 อาจจะให้คุณมีตัวเลขหลังจากที่ทศนิยมกว่าที่คุณต้องการสำหรับเงิน(แม้ว่าในกรณีนั้นคุณอาจจะต้องพิจารณา remainders).
ถ้าคุณต้องการต่อรองต่อเดือน(เช่นในตัวอย่าง),คุณไม่จำเป็นต้องทำ group by.
ถ้าเดือนคุณสนใจเรื่องไม่มีตัวตนอยู่ในโต๊ะของคุณแล้วนี้จะไม่ทำงานแต่คุณจะทำอย่างเดียวกันกับที่กำลังสร้างเป็นโต๊ะของเดือน e.g. ถ้าคุณมีข้อจำนวนมากบน 2020-01-01 แต่ไม่มีอะไรใน 2020-02-01 งั้นนี่จะไม่กลับมาเป็นแถวสำหรับ 2021-02-01.

มีของคุณภาษา sql หนีเหรอ?

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

CTE=ตั้งค่าวันที่

CTE_2=โปร-วันที่อัตราการ

สุดท้ายภาษา 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

วิธีที่จะกระจายนประจำปีของจำนวนมากแล้วก็เพิ่มโดยเดือนในภาษา sql

คำถาม

คำตอบที่ดีที่สุด

ในภาษาอื่นๆ

หน้านี้อยู่ในภาษาอื่นๆ

ดังอยู่ในนี้หมวดหมู่

ดังคำถามอยู่ในนี้หมวดหมู่