MySQL 按比例计算上一时期的不同值

发布于 2024-12-21 13:54:14 字数 1333 浏览 0 评论 0原文

这个问题相当棘手，我一整天都在挠头。

我有一个包含计费周期的表

ID     | DATEBEGIN | DATEEND  | CUSTOMER_ID
=======+===========+==========+=================
1      | 1/1/2011  | 30/1/2011 | 1

我有一个包含“子客户”的表

ID     | NAME    
=======+===========
1      | JOHN
2      | JACK
3      | Elwood

我有一个包含通过订阅购买的商品的表（批发帐户

ID     | DATE      | SUBCUSTOMER_ID | CUSTOMER ID
=======+===========+================+==============
1      | 15/1/2011 | 1              | 1
2      | 18/1/2011 | 1              | 1
3      | 25/1/2011 | 2              | 1
4      | 28/1/2011 | 3              | 1

所以我想计算“积分”以从他们的帐户中扣除。所以订阅是按“子客户”计算的因此，

在计费周期结束时（2011 年 1 月 30 日），我需要计算不同的子客户（有 3 个）。计费。

Days Having Subscription  |  SUBCUSTOMER_ID   |  Pro Rata Rate   | CUSTOMER_ID
==========================+===================+==================+==============
3                         |  3                | 3/30             | 1
6                         |  2                | 6/30             | 1
16                        |  1                | 16/30            | 1

因此，输出应该是

CUSTOMER_ID | BILLING CREDITS
============+========================
1           | 25/30

我必须按比例计算，以前即使他们在计费日期前 1 天购买商品，对整个期间进行计费也是不公平的

原文

This one is quite tricky i've been scratching my head all day.

I have a table containing billing periods

ID     | DATEBEGIN | DATEEND  | CUSTOMER_ID
=======+===========+==========+=================
1      | 1/1/2011  | 30/1/2011 | 1

I have a table containing 'sub customers'

ID     | NAME    
=======+===========
1      | JOHN
2      | JACK
3      | Elwood

I have a table containing items purchased on a subscription (wholesale account

ID     | DATE      | SUBCUSTOMER_ID | CUSTOMER ID
=======+===========+================+==============
1      | 15/1/2011 | 1              | 1
2      | 18/1/2011 | 1              | 1
3      | 25/1/2011 | 2              | 1
4      | 28/1/2011 | 3              | 1

So I want to count 'credits' to deduct from their account. So the subscription is per 'sub customer'.

So at the end of the billing period (30/1/2011 from first table). I need to count the distinct sub customers (there are 3). They are charged pro-rata from the first purchase they make during the billing period.

Days Having Subscription  |  SUBCUSTOMER_ID   |  Pro Rata Rate   | CUSTOMER_ID
==========================+===================+==================+==============
3                         |  3                | 3/30             | 1
6                         |  2                | 6/30             | 1
16                        |  1                | 16/30            | 1

The output should therefore be

CUSTOMER_ID | BILLING CREDITS
============+========================
1           | 25/30

I have to count it pro rata, previously it would be unfair to bill a full period even if they purchase an item 1 day prior to the billing date

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我做我的改变 2024-12-28 13:54:14

SELECT  customerId, SUM(DATEDIFF(dateend, fp) + 1) / (DATEDIFF(dateend, datestart) + 1)
FROM    (
        SELECT  b.*, MIN(date) AS fp
        FROM    billing b
        JOIN    purchase p
        ON      p.customerId = b.customerId
                AND p.date >= b.datebegin
                AND p.date < b.dateend + INTERVAL 1 DAY
        GROUP BY
                b.id, p.subcustomerId
        ) q
GROUP BY
        customerId

SELECT  customerId, SUM(DATEDIFF(dateend, fp) + 1) / (DATEDIFF(dateend, datestart) + 1)
FROM    (
        SELECT  b.*, MIN(date) AS fp
        FROM    billing b
        JOIN    purchase p
        ON      p.customerId = b.customerId
                AND p.date >= b.datebegin
                AND p.date < b.dateend + INTERVAL 1 DAY
        GROUP BY
                b.id, p.subcustomerId
        ) q
GROUP BY
        customerId

回复收藏 0 原文

云胡 2024-12-28 13:54:14

SELECT customer_id, sum(pro_rated_date) / max(days_in_billing_cycle)
  FROM (SELECT min(date),
               subcustomer_id,
               datebegin,
               dateend,
               items.customer_id,
               datediff(dateend, datebegin) + 1 AS days_in_billing_cycle,
               datediff(dateend, min(date)) AS pro_rated_date
          FROM    items
               JOIN
                  billing_period
               ON items.date BETWEEN billing_period.datebegin
                                 AND billing_period.dateend
                  AND items.customer_id = billing_period.customer_id
        GROUP BY subcustomer_id) AS billing
GROUP BY customer_id

SELECT customer_id, sum(pro_rated_date) / max(days_in_billing_cycle)
  FROM (SELECT min(date),
               subcustomer_id,
               datebegin,
               dateend,
               items.customer_id,
               datediff(dateend, datebegin) + 1 AS days_in_billing_cycle,
               datediff(dateend, min(date)) AS pro_rated_date
          FROM    items
               JOIN
                  billing_period
               ON items.date BETWEEN billing_period.datebegin
                                 AND billing_period.dateend
                  AND items.customer_id = billing_period.customer_id
        GROUP BY subcustomer_id) AS billing
GROUP BY customer_id

回复收藏 0 原文

~没有更多了~