如何查询指定日期范围内每日指定服务的交易尝试次数？

很简单，只要在你的基础SQL上调整日期条件并加上分组逻辑就行，我给你一步步拆解：

基础实现（仅返回有交易的日期） #

你需要把原来的单日期筛选改成日期范围，再用GROUP BY按日期分组，这样每一天的统计结果就会单独成一行。另外建议加上ORDER BY让结果按日期排序，看起来更清晰。

这里有两种写法，优先推荐第一种（性能更优）：

写法一：利用索引的日期范围查询 #

SELECT 
    DATE(created_at) AS transaction_date, -- 提取日期作为单独列
    COUNT(id) AS daily_attempts -- 统计当日交易尝试次数
FROM transactions
WHERE 
    service_id IN ('1','2','3','4')
    -- 用>=起始日期0点，<结束日期的下一天0点，避免时间部分干扰，还能利用created_at的索引
    AND created_at >= '2018-01-20 00:00:00'
    AND created_at < '2018-01-24 00:00:00'
GROUP BY DATE(created_at) -- 按日期分组统计
ORDER BY transaction_date; -- 按日期排序

写法二：直接匹配日期部分 #

如果你的created_at字段没有索引，或者不在意性能损耗，也可以直接用DATE()函数匹配日期范围：

SELECT 
    DATE(created_at) AS transaction_date,
    COUNT(id) AS daily_attempts
FROM transactions
WHERE 
    service_id IN ('1','2','3','4')
    AND DATE(created_at) BETWEEN '2018-01-20' AND '2018-01-23'
GROUP BY DATE(created_at)
ORDER BY transaction_date;

进阶：返回日期范围内所有日期（包括0次交易的日期） #

上面的写法只会返回有交易记录的日期，如果某天没有交易，就不会出现在结果里。如果需要把指定范围内的所有日期都列出来（哪怕当天次数为0），你需要先生成一个包含所有目标日期的临时序列，再和交易表做左连接统计：

PostgreSQL版本 #

WITH date_range AS (
    -- 生成从起始到结束的日期序列
    SELECT generate_series('2018-01-20'::date, '2018-01-23'::date, '1 day'::interval) AS transaction_date
)
SELECT 
    dr.transaction_date::date,
    COUNT(t.id) AS daily_attempts -- 没有匹配到交易的话，COUNT会返回0
FROM date_range dr
LEFT JOIN transactions t 
    ON DATE(t.created_at) = dr.transaction_date::date
    AND t.service_id IN ('1','2','3','4')
GROUP BY dr.transaction_date::date
ORDER BY dr.transaction_date::date;

MySQL版本（8.0+支持递归CTE） #

WITH RECURSIVE date_range AS (
    -- 起始日期
    SELECT '2018-01-20' AS transaction_date
    UNION ALL
    -- 递归生成后续日期，直到结束日期
    SELECT DATE_ADD(transaction_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE transaction_date < '2018-01-23'
)
SELECT 
    dr.transaction_date,
    COUNT(t.id) AS daily_attempts
FROM date_range dr
LEFT JOIN transactions t 
    ON DATE(t.created_at) = dr.transaction_date
    AND t.service_id IN ('1','2','3','4')
GROUP BY dr.transaction_date
ORDER BY dr.transaction_date;

这样不管当天有没有交易，都会返回日期和对应的0次统计啦～

内容的提问来源于stack exchange，提问作者oisinmcdaid