mysql - 如何统计每个用户每天的首笔交易次数?
问题描述
我有一个名为的表transactions
,其中包含所有事务。像这样的东西:
// transactions
+--------+-------------+---------+-------------+---------------------+
| id | business_id | user_id | amount | created_at |
+--------+-------------+---------+-------------+---------------------+
| 1 | 503 | 12 | 4500 | 2021-04-15 04:22:09 |
| 2 | 33 | 12 | 1200 | 2021-04-17 12:22:10 |
| 3 | 771 | 13 | 400 | 2021-04-18 13:02:18 |
| 4 | 86 | 14 | 7500 | 2021-04-18 16:07:12 |
| 5 | 772 | 13 | 3400 | 2021-04-23 07:11:04 |
| 6 | 652 | 14 | 900 | 2021-04-24 10:35:08 |
| 7 | 567 | 15 | 1000 | 2021-04-24 14:55:11 |
+--------+-------------+---------+-------------+---------------------+
我需要计算每天进行第一笔交易的用户数量。所以这是预期的结果:
// The expected result
+------+-------+-------+--------------------------+
| year | month | day | first_transactions_num |
+------+-------+-------+--------------------------+
| 2021 | 04 | 15 | 1 |
| 2021 | 04 | 18 | 2 |
| 2021 | 04 | 24 | 1 |
+------+-------+-------+--------------------------+
知道我该怎么做吗?
这是我尝试过的:
SELECT year(created_at) year,
month(created_at) month,
day(created_at) day,
count(1) num
FROM transactions
GROUP BY year, month, day
但我的查询并不关心“每个用户的第一笔交易”。
解决方案
您应该汇总此查询的结果:
SELECT MIN(created_at) created_at
FROM transactions
GROUP BY user_id
它返回每个用户的第一笔交易。
所以这样做:
SELECT YEAR(created_at) year,
MONTH(created_at) month,
DAY(created_at) day,
COUNT(*) num
FROM (
SELECT MIN(created_at) created_at
FROM transactions
GROUP BY user_id
) t
GROUP BY year, month, day
或更好:
SELECT YEAR(created_at) year,
MONTH(created_at) month,
DAY(created_at) day,
COUNT(*) num
FROM (
SELECT DATE(MIN(created_at)) created_at
FROM transactions
GROUP BY user_id
) t
GROUP BY created_at
或者,如果您的 MySql 版本是 8.0+,请使用COUNT()
窗口函数:
SELECT DISTINCT
YEAR(MIN(created_at)) year,
MONTH(MIN(created_at)) month,
DAY(MIN(created_at)) day,
COUNT(*) OVER (PARTITION BY DATE(MIN(created_at))) num
FROM transactions
GROUP BY user_id
请参阅演示。
推荐阅读
- python - Python:如何从列表列表中获取值并将它们插入到元组列表中
- python - 使用嵌套键值的总和对嵌套字典进行排序
- python - Python - 主成分分析错误
- python - 3D CNN 在图像序列上的输入形状应该是什么?
- mongodb - MongoDB Realm SwiftUI 继续会话/自动登录
- python - Sympy 表达式生成函数将所有时间都花在 __mul__ 中;如何加快速度?
- python - 用于 Mac 和 Windows 的 python 3 中的定时输入
- vb.net - VB.net,无法访问已添加到项目的 OpenFileDialog
- elixir - 如何漂亮打印 Elixir 错误
- c - 使用 putw() 时在文件中获取乱码