如何在MySQL中实现访问量与前后日期的增减标识返回

我原本用以下SQL查询book_visit表中近14天的每日访问量：

SELECT count(vid) vc, date(visitwhen) vd
FROM book_visit 
WHERE date(visitwhen) >= date_sub(now(), interval 14 day)
GROUP BY vd
ORDER BY vd desc

查询结果示例：

{
    "data": [
        {
            "vc": 2,
            "vd": "2023-04-25"
        },
        {
            "vc": 1,
            "vd": "2023-04-24"
        },
        ...
    ]
}

现在希望在MySQL查询结果中直接返回当日访问量相较于前/后一天的增减标识（如+/-/=），无需在客户端处理逻辑。

解决方案：利用窗口函数（MySQL 8.0+） #

MySQL 8.0及以上版本支持窗口函数，用LAG()或LEAD()可以轻松获取相邻日期的访问量，再通过CASE语句生成标识。

1. 对比前一天的访问量

SELECT 
    vc,
    vd,
    CASE
        WHEN prev_vc IS NULL THEN '无对比'
        WHEN vc > prev_vc THEN '+'
        WHEN vc < prev_vc THEN '-'
        ELSE '='
    END AS change_flag
FROM (
    SELECT 
        count(vid) vc,
        date(visitwhen) vd,
        -- 获取按日期升序排列的前一行（前一天）的访问量
        LAG(count(vid)) OVER (ORDER BY date(visitwhen)) AS prev_vc
    FROM book_visit 
    WHERE date(visitwhen) >= date_sub(now(), interval 14 day)
    GROUP BY vd
) AS daily_stats
ORDER BY vd desc;

2. 对比后一天的访问量

如果需要和后一天的访问量对比，把LAG()换成LEAD()即可：

SELECT 
    vc,
    vd,
    CASE
        WHEN next_vc IS NULL THEN '无对比'
        WHEN vc > next_vc THEN '+'
        WHEN vc < next_vc THEN '-'
        ELSE '='
    END AS change_flag
FROM (
    SELECT 
        count(vid) vc,
        date(visitwhen) vd,
        -- 获取按日期升序排列的后一行（后一天）的访问量
        LEAD(count(vid)) OVER (ORDER BY date(visitwhen)) AS next_vc
    FROM book_visit 
    WHERE date(visitwhen) >= date_sub(now(), interval 14 day)
    GROUP BY vd
) AS daily_stats
ORDER BY vd desc;

兼容低版本MySQL（<8.0） #

如果你的MySQL版本不支持窗口函数，可以用自连接的方式实现：

SELECT 
    current.vc,
    current.vd,
    CASE
        WHEN prev.vc IS NULL THEN '无对比'
        WHEN current.vc > prev.vc THEN '+'
        WHEN current.vc < prev.vc THEN '-'
        ELSE '='
    END AS change_flag
FROM (
    SELECT count(vid) vc, date(visitwhen) vd
    FROM book_visit 
    WHERE date(visitwhen) >= date_sub(now(), interval 14 day)
    GROUP BY vd
) AS current
LEFT JOIN (
    SELECT count(vid) vc, date(visitwhen) vd
    FROM book_visit 
    WHERE date(visitwhen) >= date_sub(now(), interval 14 day)
    GROUP BY vd
) AS prev ON current.vd = date_add(prev.vd, interval 1 day)
ORDER BY current.vd desc;

这个方法通过自连接将当前日期和前一天的记录关联，同样能计算出增减标识。

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