MySQL:根据付款计算订阅的剩余天数 [英] MySQL: Calculating remaining days of subscriptions based on payments

问题描述

我正在开发一个应用程序，其中我们有三种不同的订阅计划(我们称它们为小型、标准、高级)，每一种都有三种不同的长度(30、60 和 90 天).每个用户可以订阅多项服务.

I'm working on an application where we have three different subscription plans (let's call them small, standard, premium) each with three different lengths (30, 60 and 90 days). Each user can subscribe to multiple services.

我有以下表格(简化版):

I have the following tables (simplified):

**services**
id, name

**user_services**
user_id, service_id

**service_plans**
id, service_id, days, price

**payments**
id, user_id, service_id, service_plan_id, amount, created_at

我想使用付款历史来查找每个服务订阅的剩余天数，但是当我意识到用户可能在当前订阅之前购买两个相同的服务计划或服务时遇到了问题一个过期.

I want to use the payment history to find the remaining days on each service subscription, but I'm running into a problem when I realize that it's possible for a user to for example buy two identical service plans or services before the current one expires.

假设用户在以下日期购买了属于同一服务的两个服务计划:

Let's say that the user bought two service plans belonging to the same service at the following dates:

付款

id | user_id | service_id | service_plan_id | amount | created_at
1  | 1       | 1          | 1               | 40.00  | 2014-06-10 12:23:56
2  | 1       | 1          | 2               | 65.00  | 2014-06-15 12:27:11

服务计划如下(简化):

And the service plans look like this (simplified):

service_plans

id | service_id | days | price
1  | 1          | 30   | 40.00
2  | 1          | 60   | 65.00

现在假设今天的日期是:2014-07-09

Now let's say that today's date is: 2014-07-09

那么今天，用户距离第一次付款还有 1 天的时间.而且由于另一笔付款是针对同一个服务的(虽然是5天后)，完全没有生效，所以总剩余天数应该是61天.

Then today, the user would have 1 day remaining from the first payment. And since the other payment was for the same service (although 5 days later), it has not yet taken effect at all, so the total number of remaining days should be 61.

我一直在摸索这个问题，但找不到任何类似的问题.任何人都可以阐明并提出 SQL 解决方案吗?也许我在这里使用了错误的模型，存储了错误的东西?

I've been scratching my head around this and not been able to find any similar questions. Can anyone shed some light and come up with a SQL solution? Perhaps I'm using the wrong model here, storing the wrong things?

//卡尔-大卫

推荐答案

我建议您在表中存储的数据还不够多.

I'd suggest that you haven't quite stored enough data in your table.

就像现在一样，您将始终需要查看之前的每笔付款，以确定当前付款是处于活动状态还是等待激活、性能下降较大以及查询更为复杂.

As it is now, you will always need to look at every previous payment to ascertain whether the current one is active or awaiting activation, a large performance hit and more complex query.

为payments 计算的expires_at 列，该列在添加新付款时计算为MAX(payments.expires_at) + INTERVAL service_plans.daysDAYS 将允许您通过仅查看一行来计算剩余天数......以及用户是否在计划中.

A calculated expires_at column for payments, which is worked out on the addition of a new payment as MAX(payments.expires_at) + INTERVAL service_plans.days DAYS will allow for you to work out the number of remaining days by looking at one row only.. and whether or not a user is on a plan.

这篇关于MySQL:根据付款计算订阅的剩余天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！