使用sql[关闭]统计每天、每周和每月的总计

Question

我有一个名为-'users'的示例sql表，其中包含以下记录：

我想获得每一个用户的计数在每日，每周和每月的基础上与预期的outlike为：

Answer 1

您可以使用如下所示的查询：

SELECT user_id
    , SUM(IF( transaction_time > CURRENT_DATE()  - INTERVAL 1 DAY,   1, 0)) as daily_count
    , SUM(IF(transaction_time > CURRENT_DATE() - INTERVAL 1 WEEK,  1, 0)) as weekly_count
    , SUM(IF(transaction_time > CURRENT_DATE() - INTERVAL 1 MONTH, 1, 0)) as monthly_count
FROM yourTable
GROUP BY user_ID
ORDER by user_id;

样品

MariaDB [bernd]> select * from yourTable;
+----+---------+---------------------+-------+
| id | user_id | transaction_time    | price |
+----+---------+---------------------+-------+
|  1 |       1 | 2021-04-22 10:00:00 |    20 |
|  2 |       2 | 2021-04-03 20:00:00 |    10 |
|  3 |       2 | 2021-04-04 20:00:00 |    11 |
|  4 |       3 | 2021-04-05 20:00:00 |    12 |
|  5 |       1 | 2021-03-03 20:00:00 |    10 |
|  6 |       3 | 2021-01-06 20:00:00 |     9 |
+----+---------+---------------------+-------+
6 rows in set (0.01 sec)

MariaDB [bernd]> SELECT user_id
    -> , SUM(IF( transaction_time > CURRENT_DATE()  - INTERVAL 1 DAY,   1, 0)) as daily_count
    -> , SUM(IF(transaction_time > CURRENT_DATE() - INTERVAL 1 WEEK,  1, 0)) as weekly_count
    -> , SUM(IF(transaction_time > CURRENT_DATE() - INTERVAL 1 MONTH, 1, 0)) as monthly_count
    -> FROM yourTable
    -> GROUP BY user_ID
    -> ORDER by user_id;
+---------+-------------+--------------+---------------+
| user_id | daily_count | weekly_count | monthly_count |
+---------+-------------+--------------+---------------+
|       1 |           1 |            1 |             1 |
|       2 |           0 |            0 |             2 |
|       3 |           0 |            0 |             1 |
+---------+-------------+--------------+---------------+
3 rows in set (0.00 sec)

MariaDB [bernd]>