主页 / coding / 问题 / 79047174
Accepted
John Beasley
Asked: 2024-10-02 22:18:53 +0800 CST

获取每天的 ID 数量并按日期分组

  • 772

目前，我有一个如下的查询：

SELECT send_id, count(id) FROM `table1`
WHERE date BETWEEN '2024-09-01%' AND '2024-09-30%'
GROUP BY send_id;

这将返回如下结果：

|   send_id   |   count(id)   |
-------------------------------
|    00123    |       32      |  
|    00234    |       12      | 
|    00567    |      100      | 
|    00890    |       07      | 
-------------------------------

我想添加日期并获取每天每个 send_id 的计数。

我写了这个查询：

SELECT DATE(date) as 'date', send_id, count(id) FROM `table1`
WHERE date BETWEEN '2024-09-01%' AND '2024-09-30%'
GROUP BY date, send_id;

我使用 DATE(date)，因为日期列是日期时间。我不需要时间，只需要日期。

我得到的结果如下：

|     date    |   send_id   |   count(id)   |
---------------------------------------------
| 2024-09-01  |    00123    |       03      |  
| 2024-09-01  |    00123    |       10      | 
| 2024-09-01  |    00567    |       20      | 
| 2024-09-01  |    00567    |       05      | 
---------------------------------------------

我想要的结果应该是这样的：

|     date    |   send_id   |   count(id)   |
---------------------------------------------
| 2024-09-01  |    00123    |       13      |  
| 2024-09-01  |    00567    |       25      | 
---------------------------------------------

因为 send_id 在 2024-09-01 的总 id 数为 13，但它将其分成 2 行。

id 是 auto_incremented 字段。

我认为这是因为日期时间，但我不确定。

我如何修复查询以便它获取每天每个 send_id 的 id 计数？

sql

5 个回答

  1. Best Answer

    只需添加评论作为答案。查看您的GROUP BY，您还需要按日期(日期)分组。

    GROUP BY DATE(DATE), SEND_ID
    • 1

  2. 当您使用它时GROUP BY date，它是按date表中的列进行分组，而不是按date您指定的别名进行分组DATE(date)。

    您可以通过对别名使用不同的名称来避免此问题：

    SELECT DATE(date) AS txn_date, , send_id, count(id) 
FROM `table1`
WHERE date BETWEEN '2024-09-01%' AND '2024-09-30%'
GROUP BY txn_date, send_id;
    • 1

  3. 您可以使用 try_cast 将数据转换为 date_type

    SELECT 
    TRY_CAST(date AS DATE) AS date,
    send_id,
    COUNT(id) AS count_id
FROM table1
WHERE date BETWEEN '2024-09-01' AND '2024-09-30'
GROUP BY TRY_CAST(date AS DATE), send_id
    • 0

  4. 我建议使用派生表，提前将日期转换为 DATE(date)，然后GROUP BY获取其结果。 (非常方便的技巧，可以避免重复复杂的 group by 表达式。)

    SELECT date, send_id, count(id)
FROM
(
    SELECT DATE(date) as 'date', send_id, id  FROM `table1`
    WHERE date BETWEEN '2024-09-01%' AND '2024-09-30%'
) dt
GROUP BY date, send_id
    • 0

  5. 假设您有日期时间列，此WHERE子句的性能将好得多：

    WHERE date >= '2024-09-01' AND date < '2024-10-01'

    由于文化/国际化问题，将日期视为字符串比我们想象的要慢得多，而且更容易出错。这是应该避免的。

    使用LIKE '2024-09-01%'表达式会强制数据库对表中的每一行（即使不会使用的行）隐式执行从日期时间到字符串类型的较慢转换。进行此转换意味着转换后的值不再与您可能拥有的任何索引使用的值匹配，从而使索引对此查询毫无价值。

    只要有可能，您就应该编写条件表达式以避免改变表中存储的值。

    对于日期而言，这通常意味着写两个表达式形成一个半开范围，并且该范围结束后的第二天具有一个独有的上限。

    把它们放在一起像这样：

    SELECT DATE(date) as 'date', send_id, count(id)
FROM `table1`
WHERE date >= '2024-09-01' AND date < '2024-10-01'
GROUP BY DATE(DATE), SEND_ID

    作为奖励，对于这样的月份范围，您不再需要担心找到正确的月份最后一天。相反，您将始终使用下个月的第一天。

    • 0

相关问题