Início / coding / Perguntas / 79047174
Accepted
John Beasley
Asked: 2024-10-02 22:18:53 +0800 CST

Obter contagem de ID para cada dia e agrupar por data

  • 772

Atualmente, tenho uma consulta que se parece com esta:

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

Isso retorna um resultado parecido com este:

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

Quero adicionar a data e obter a contagem de cada send_id para cada dia.

Eu escrevi esta consulta:

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;

Eu uso DATE(date) porque a coluna date é uma datetime. Não preciso da hora, só da data.

Estou obtendo resultados parecidos com estes:

|     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      | 
---------------------------------------------

Os resultados que eu estava procurando deveriam ser assim:

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

Porque send_id tinha uma contagem total de IDs de 13 em 2024-09-01, mas está dividindo em 2 linhas.

O id é o campo auto_incremented.

Acho que é por causa da data e hora, mas não tenho certeza.

Como posso corrigir a consulta para que ela obtenha a contagem de IDs de cada send_id para cada dia?

sql

5 respostas

  1. Best Answer

    Apenas adicionando comentário como resposta. Confira seu GROUP BY, onde você precisa também agrupar por data(date).

    GROUP BY DATE(DATE), SEND_ID
    • 1

  2. Quando você usa GROUP BY dateo agrupamento pela datecoluna na tabela, não pelo datealias que você atribuiu DATE(date).

    Você pode evitar esse problema usando um nome diferente para o alias:

    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. você pode usar try_cast para converter os dados em 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. Eu recomendaria uma tabela derivada para fazer a conversão de data para DATE(data) com antecedência e, em seguida, GROUP BYseu resultado. (Um truque muito útil para evitar repetir expressões complexas de agrupamento.)

    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. Considerando que você tem colunas de data e hora, esta WHEREcláusula terá um desempenho muito melhor :

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

    Graças a questões culturais/internacionalização, tratar datas como strings é muito mais lento e mais propenso a erros do que gostaríamos de acreditar. É algo a evitar.

    Usar a LIKE '2024-09-01%'expressão força o banco de dados a fazer implicitamente essa conversão mais lenta de datetime para um tipo de string para cada linha na tabela , mesmo linhas que não serão usadas. E fazer essa conversão significa que os valores convertidos não correspondem mais aos valores usados ​​com qualquer índice que você possa ter tido, tornando o índice inútil para essa consulta.

    Sempre que possível, você deve escrever suas expressões condicionais para evitar a mutação dos valores armazenados na tabela.

    No caso de datas, isso geralmente significa escrever duas expressões formando um intervalo semiaberto com um limite superior exclusivo para o dia após o fechamento do intervalo.

    Coloque tudo junto assim:

    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

    Como bônus, para intervalos de meses como esse, você não precisa mais se preocupar em encontrar o último dia correto do mês. Em vez disso, você sempre usará o primeiro dia do mês seguinte.

    • 0

relate perguntas