使用postgres计算每月经常性收入(MRR)结果 [英] Calculate Monthly Recurring Revenue(MRR) result using postgres

问题描述

我需要按照 https://www.chargebee.com/blog/mrr-subscription-businesses-saas-metrics-101/ between selected two dates使用postgresql json，我的订阅也具有"1 DAY", "15 DAY", "2 WEEK", "X WEEK", "X MONTH", "x YEAR" where x can be any number

I need to calculate MRR as describe in https://www.chargebee.com/blog/mrr-subscription-businesses-saas-metrics-101/ between selected two dates using postgresql json, also my subscription are having intervals like "1 DAY", "15 DAY", "2 WEEK", "X WEEK", "X MONTH", "x YEAR" where x can be any number

我有以下订阅模式和值:

I have following subscription schema and values:

CREATE TABLE subscriptions
(
  id bigserial NOT NULL,
  data json,
  created_date date,
  updated_date date,
  user_id bigserial NOT NULL,
  CONSTRAINT subscription_id_pk PRIMARY KEY (id)
)

数据位于json格式的数据"列中，例如:

And data are inside column "data" in json format like:

{
  "id": "1",
  "amount": 3900,
  "interval": "1 WEEK",
  "created": 1424011935
}

和事务，是在每个定期订阅成功后创建的.我认为交易不是必需的，但是如果您可以以此发展，那也很好.

And transactions, is created when each recurring subscription is succeeded. I think transaction is not required, but if u can develop with this then good too.

CREATE TABLE transactions
(
  id bigserial NOT NULL,
  data json,
  created_date date,
  updated_date date,
  user_id bigserial NOT NULL,
  CONSTRAINT transactions_id_pk PRIMARY KEY (id)
)

数据位于json格式的数据"列中，例如:

And data are inside column "data" in json format like:

{
  "id": "1",
  "amount": 3900,
  "subscription_id": "1"
  "created": 1424011935

}

您可以使用数据json中创建的unix时间戳或表中的created_date来使用

you can use created unix timestamp from data json or created_date from table whichever is better for you

• 此处的订阅创建日期是第一次创建的订阅，并非每次都重复出现.
• 我的版本是9.3

推荐答案

generate_series可以与INTERVAL一起使用，这很简单，我们可以使用LATERAL JOIN为每个json行生成数据.

It's rather simple generate_series can be used with INTERVALs and we can use a LATERAL JOIN to generate the data for each json row.

WITH j AS(
SELECT '{
  "id": "1",
  "amount": 3900,
  "interval": "1 WEEK",
  "created": 1424011935
}'::json AS data
UNION ALL
SELECT '{
  "id": "2",
  "amount": 100,
  "interval": "23.5 DAY",
  "created": 552614400
}'::json
),
dates AS(
SELECT
'2008-03-01 00:00'::timestamp AS start
,'2015-03-08 07:00'::timestamp AS stop
)
SELECT j.data->>'id'
    ,sum((j.data->>'amount')::int)
    ,count(*) as intervals
FROM j
CROSS JOIN dates
CROSS JOIN LATERAL
    (SELECT i
    FROM generate_series(to_timestamp((j.data->>'created')::int), dates.stop + (j.data->>'interval')::interval,(j.data->>'interval')::interval) as i
    WHERE i BETWEEN dates.start AND dates.stop
    ) AS q
GROUP BY j.data->>'id'

以上内容为我们提供了结果:

The above gives us the result:

 id |  sum  | intervals
----+-------+-----------
 1  | 11700 |         3
 2  | 11000 |       110

SQLFiddle

这篇关于使用postgres计算每月经常性收入(MRR)结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！