选择此选项可根据客户将首次销售考虑在内的方式来计算客户的平均销售收入 [英] Select to Calculate Sales Average by Customer taking First Sales into Account MYSQL

问题描述

我在Mysql中有按客户列出的此销售表

I have this Sales Table by Customer in Mysql

+-----------+------------+-------+-----------------+
| Customer  |    Date    | Sales | Date_First_Sale |
+-----------+------------+-------+-----------------+
| Jane      | 2016-04-30 |   903 | 2015-02-03      |
| Jane      | 2016-02-03 |    51 | 2015-02-03      |
| Jane      | 2016-03-09 |   192 | 2015-02-03      |
| John      | 2016-05-10 |    64 | 2015-10-03      |
| John      | 2016-04-16 |   880 | 2015-10-03      |
| John      | 2016-08-17 |   386 | 2015-10-03      |
| John      | 2016-03-01 |   503 | 2015-10-03      |
| Juan      | 2016-07-06 |   765 | 2015-09-01      |
| Juan      | 2016-01-20 |    36 | 2015-09-01      |
| Juan      | 2016-03-03 |   928 | 2015-09-01      |
| Momo      | 2016-06-29 |   573 | 2015-09-01      |
| Momo      | 2016-04-25 |   375 | 2015-09-01      |
| Momo      | 2016-06-10 |   999 | 2015-09-01      |
| Nour      | 2016-02-28 |   956 | 2015-05-01      |
| Nour      | 2016-01-03 |   582 | 2015-05-01      |
| Nour      | 2016-08-17 |   366 | 2015-05-01      |
| Philip    | 2016-03-22 |   296 | 2015-09-01      |
| Philip    | 2016-04-14 |   459 | 2015-09-01      |
| Sylvie    | 2016-03-29 |   551 | 2015-09-03      |
| Sylvie    | 2016-02-14 |   896 | 2015-09-03      |
+-----------+------------+-------+-----------------+

从今天开始，我需要计算过去12个月(取决于日历是52周还是53周，以每周为基础)按客户计算的平均销售额.

I need to calculate the Average Sales by Customer calculated on a WEEKLY basis in the last 12 months (52 or 53 weeks depending on the calendar?), starting from Today.

现在的问题是，我不想为首次购买的时间少于12个月的客户计算客户的平均每周销售额，例如，如果当前日期为2016-09-01，并且客户他在2016年7月24日首次购买商品时，不应以12个月为基础计算平均值，而应仅基于2016年7月24日至2016年9月1日之间产生的每周销售额.

Now the problem is that I do not want to calculate the Average Weekly sales by customer for customers that have made their first purchase in a range below 12 months, for instance If current date is 2016-09-01, and Customers made his first purchase on 2016-07-24, the average should not be calculated on a 12 months basis but on the weekly sales generated between the 2016-07-24 and the 2016-09-01 only.

对于在12个月范围内进行首次购买的客户，则平均值应仅在12个月内计算.

For customers who have made their First purchase before the 12 months range, then the average should be calculated on 12 months only.

我一直在尝试查找此SELECT，但是由于我对较复杂的查询的了解有限，因此我什么都没找到！

I have been trying to find this SELECT but have not reached anything due to my limited Mysql knowledge for more complex queries!

预先感谢您的帮助

推荐答案

这应该对您有帮助

SELECT Customer, (total_sales/weeks) AS avg_sales FROM
(
SELECT Customer, total_sales, Date_First_Sale, IF(weeks>52,52,weeks) as weeks
FROM (
    SELECT Customer, SUM(Sales) AS total_sales, Date_First_Sale, TIMESTAMPDIFF(WEEK, Date_First_Sale, CURDATE()) AS weeks
    FROM (
        SELECT Customer, sales , Date_First_Sale
        FROM test.SO_customer
        WHERE Date > DATE_SUB(curdate(), INTERVAL 1 YEAR)
         ) as subTable
    GROUP BY Customer
    ) as subTable2
) as subTable3

这篇关于选择此选项可根据客户将首次销售考虑在内的方式来计算客户的平均销售收入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！