Как распределить годовую сумму, а затем добавить по месяцам в 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 верните несколько строк за один и тот же месяц.
Возможно, вы захотите обойти SUMя так и сделал, так как в некоторых случаях деление на 12 может дать вам больше цифр после десятичного знака, чем вы хотите за деньги (хотя в этом случае вам также может потребоваться рассмотреть остатки).
Если у вас действительно только одна транзакция в месяц (как в вашем примере), вам не нужно выполнять group by.
Если месяцев, о которых вы заботитесь, в вашей таблице нет, то это не сработает, но вы можете сделать то же самое, создав таблицу месяцев. Например Если у вас есть сумма на 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 верните несколько строк за один и тот же месяц.
Возможно, вы захотите обойти SUMя так и сделал, так как в некоторых случаях деление на 12 может дать вам больше цифр после десятичного знака, чем вы хотите за деньги (хотя в этом случае вам также может потребоваться рассмотреть остатки).
Если у вас действительно только одна транзакция в месяц (как в вашем примере), вам не нужно выполнять group by.
Если месяцев, о которых вы заботитесь, в вашей таблице нет, то это не сработает, но вы можете сделать то же самое, создав таблицу месяцев. Например Если у вас есть сумма на 2020-01-01, но ничего в 2020-02-01, то это не вернет строку за 2021-02-01.

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

Вопрос

Лучший ответ

На других языках

Эта страница на других языках

Популярное в этой категории

Популярные вопросы в этой категории