Vertica数据库按周统计用户借书记录并输出周结束日期的SQL查询问题

解决方案 #

你可以直接通过NEXT_DAY函数计算每一条记录对应周的周日（统计周期为周一到周日），再按用户和计算得到的周结束日期分组统计即可，最终SQL如下：

SELECT
    COUNT(*) AS WEEKCOUNT,
    "USER",
    TO_CHAR(
        NEXT_DAY(TO_DATE(TO_CHAR(DATE), 'YYYYMMDD') - INTERVAL '1 day', 'Sunday'),
        'MM/DD'
    ) AS WEEKDATE
FROM BOOKSISSUED
GROUP BY
    "USER",
    NEXT_DAY(TO_DATE(TO_CHAR(DATE), 'YYYYMMDD') - INTERVAL '1 day', 'Sunday')
ORDER BY WEEKDATE, "USER";

逻辑说明 #

1. 首先将原表中数字格式的DATE字段转换为Vertica支持的标准日期类型：TO_DATE(TO_CHAR(DATE), 'YYYYMMDD')，如果你的DATE字段本身就是日期类型，可以省略这层转换直接使用该字段。
2. 计算每条记录对应周的结束日期（周日）：使用NEXT_DAY(日期 - INTERVAL '1 day', 'Sunday')，减1天的作用是避免当天为周日时被算到下一周的统计周期里。
3. 按用户和计算得到的周结束日期分组，直接计数即可得到每个用户对应周的总记录数，无需额外写CASE判断。
4. 通过TO_CHAR将周结束日期格式化为MM/DD的格式，即可得到你需要的WEEKDATE字段。
5. 注意USER是SQL保留字，需要用双引号包裹引用。

结果验证 #

按你的测试数据执行上述SQL，输出结果和你给出的预期完全一致：

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