时间戳

首页 > 解决方案 > Determine customers that have spent money at the company for the first time, each month (mySQL)

mysql - Determine customers that have spent money at the company for the first time, each month (mySQL)

问题描述

I have a table below and I am trying to determine the number of users that have spent money at the company for the first time each month.

What I would like is to have a resulting table with new users, month and year as columns.

Before people downvote this post, I have looked through various posts already and can't seem to find a similar way to solve this issue. The code that I have included below is based on what I've managed to piece together from relevant posts.

This is the original table:

+---------------------+-------------+-----------------+
| datetime            | customer_id | amount          |
+---------------------+-------------+-----------------+
| 2018-03-01 03:00:00 | 3786        | 14              |
+---------------------+-------------+-----------------+
| 2018-03-02 17:00:00 | 5678        | 25              |
+---------------------+-------------+-----------------+
| 2018-08-17 19:00:00 | 5267        | 45              |
+---------------------+-------------+-----------------+
| 2018-08-25 08:00:00 | 3456        | 78              |
+---------------------+-------------+-----------------+
| 2018-08-25 17:00:00 | 3456        | 25              |
+---------------------+-------------+-----------------+
| 2019-05-25 14:00:00 | 3456        | 15              |
+---------------------+-------------+-----------------+
| 2019-07-02 14:00:00 | 88889       | 45              |
+---------------------+-------------+-----------------+
| 2019-08-25 08:00:00 | 1234        | 88              |
+---------------------+-------------+-----------------+
| 2019-08-30 09:31:00 | 1234        | 30              |
+---------------------+-------------+-----------------+
| 2019-08-30 12:00:00 | 9876        | 55              |
+---------------------+-------------+-----------------+
| 2019-09-01 13:00:00 | 88889       | 23              |
+---------------------+-------------+-----------------+

This is the CREATE statement:

CREATE TABLE IF NOT EXISTS `spend` ( `datetime` datetime NOT NULL, `customer_id` int(11) NOT NULL, `amount` int(11) NOT NULL, PRIMARY KEY (`datetime`)) DEFAULT CHARSET=utf8mb4;
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2018-03-01 03:00:00', 3786, 14);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2018-03-02 17:00:00', 5678, 25);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2018-08-17 19:00:00', 5267, 45);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2018-08-25 08:00:00', 3456, 78);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2018-08-25 17:00:00', 3456, 25);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-05-25 14:00:00', 3456, 15);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-07-02 14:00:00', 88889, 45);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-08-25 08:00:00', 1234, 88);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-08-30 09:31:00', 1234, 30);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-08-30 12:00:00', 9876, 55);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-09-01 13:00:00', 88889, 23);

This is the code that I have come up with:

SELECT S.datetime, S.customer_id, S.amount 
FROM spend S
INNER JOIN
    (SELECT customer_id, MIN(datetime) AS first_occurence
    FROM spend
    GROUP BY customer_id) X
ON S.customer_id = X.customer_id AND S.datetime = X.first_occurence

This is the resulting table:

+------------------+-------------+-------+
| datetime         | customer_id |amount |
+------------------+-------------+-------+
| 01/03/2018 03:00 | 3786        | 14    |
+------------------+-------------+-------+
| 02/03/2018 17:00 | 5678        | 25    |
+------------------+-------------+-------+
| 17/08/2018 19:00 | 5267        | 45    |
+------------------+-------------+-------+
| 25/08/2018 08:00 | 3456        | 78    |
+------------------+-------------+-------+
| 02/07/2019 14:00 | 88889       | 45    |
+------------------+-------------+-------+
| 25/08/2019 08:00 | 1234        | 88    |
+------------------+-------------+-------+
| 30/08/2019 12:00 | 9876        | 55    |
+------------------+-------------+-------+

This is what an example of what the table should look like:

+-----------+-------+------+
| new_users | month | year |
+-----------+-------+------+
| 2         | 3     | 2018 |
+-----------+-------+------+
| 3         | 8     | 2018 |
+-----------+-------+------+
| 1         | 5     | 2019 |
+-----------+-------+------+
| 1         | 7     | 2019 |
+-----------+-------+------+
| 3         | 8     | 2019 |
+-----------+-------+------+
| 1         | 9     | 2019 |
+-----------+-------+------+

标签： mysqlsqlmysql-8.0

解决方案

您不需要两级深度子查询。您可以简单地找到客户第一次花钱使用的时间MIN()，然后简单地提取YEAR()并MONTH()取出该最小日期时间值，以计算用户数量：

SELECT 
  YEAR(min_dt) y,
  MONTH(min_dt) m,
  COUNT(*) AS new_customers 
FROM 
(
  SELECT customer_id, MIN(datetime) AS min_dt 
  FROM spend 
  GROUP BY customer_id 
) t
GROUP BY y, m

结果

| y    | m   | new_customers |
| ---- | --- | ------------- |
| 2018 | 3   | 2             |
| 2018 | 8   | 2             |
| 2019 | 7   | 1             |
| 2019 | 8   | 2             |

在 DB Fiddle 上查看

推荐阅读