PostgreSQL：基于订单时段调整视图中的日期归属

核心是根据orderhour字段调整订单归属的日期：当orderhour为0-3时，将订单日期向前推一天，其余时段保持原日期。调整后的完整SQL如下：

SELECT 
    r.name AS store,
    r.id,
    -- 核心调整：根据orderhour修正归属日期
    CASE 
        WHEN se.orderhour BETWEEN 0 AND 3 THEN date(se.date) - INTERVAL '1 day'
        ELSE date(se.date)
    END AS date,
    c.dow,
    c.week,
    c.period,
    c.year,
    sum(se.netsales) AS net_sales,
    sum(se.numberofguests) AS guest_count
FROM sales_employee se
JOIN restaurants r ON r.locationid::text = se.location
-- 关联calendar时使用修正后的日期
JOIN calendar c ON c.date = CASE 
        WHEN se.orderhour BETWEEN 0 AND 3 THEN date(se.date) - INTERVAL '1 day'
        ELSE date(se.date)
    END
GROUP BY 
    r.name, 
    r.id, 
    -- 分组字段同步使用修正后的日期
    CASE 
        WHEN se.orderhour BETWEEN 0 AND 3 THEN date(se.date) - INTERVAL '1 day'
        ELSE date(se.date)
    END, 
    c.dow, 
    c.week, 
    c.period, 
    c.year
ORDER BY 
    CASE 
        WHEN se.orderhour BETWEEN 0 AND 3 THEN date(se.date) - INTERVAL '1 day'
        ELSE date(se.date)
    END DESC, 
    r.name;

关键说明 #

• 所有涉及日期的环节（查询字段、关联条件、分组、排序）必须统一使用修正后的日期逻辑，避免出现数据统计不一致的问题。
• 通过CASE语句判断orderhour范围，0-3时段的订单日期通过- INTERVAL '1 day'前移一天，确保归属到前一天的统计周期内。
• 该写法兼容性较强，适用于大多数主流SQL数据库，若你的数据库支持更简洁的日期函数，也可以替换为对应语法简化逻辑。

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