在 PostgreSQL 中，可以根据小时调整视图中的日期：

Question

我有一个视图，它查询销售数据并将其汇总到一个简化的表中，该表按每个地点对净销售额（net_sales）进行求和。然而，我遇到的问题是当存在凌晨后的销售记录时。我的代码是按照日期来进行分组的，因此任何在午夜之后发生的销售都被错误地归到了第二天。

以下是当前使用的SQL查询：

SELECT r.name AS store,
       r.id,
       date(se.date) 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
JOIN calendar c ON c.date = date(se.date)
GROUP BY r.name, r.id, (date(se.date)), c.dow, c.week, c.period, c.year
ORDER BY (date(se.date)) DESC, r.name;

在sales_employee表中还有一列名为orderhour的大整数类型列，其取值范围从0至23，表示订单时间。由于系统在凌晨4点关闭，我现在需要做的是收集从orderhour为4开始直到3的所有时段的销售，并将orderhour为0至3时段的销售数据归入前一天。请问这个需求是否可以实现？

abatishchev · Answer

在使用日期的地方，请替换为：

(datecolumn - interval '4 hours')::date

通过减去四个小时，可以将00:00至04:00之间的交易移动到前一天。例如：

JOIN calendar c ON c.date = (se.date - interval '4 hours')::date

ecatmur · Answer

我尝试修改了您的SQL查询，以将凌晨0点至4点间的销售分配给前一天。通过使用CASE语句根据orderhour列调整日期。请尝试以下修改后的查询：

SELECT
    r.name AS store,
    r.id,
    CASE 
        WHEN se.orderhour >= 4 THEN date(se.date)
        ELSE date(se.date) - interval '1 day'
    END AS adjusted_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
JOIN
    calendar c ON c.date = CASE 
                             WHEN se.orderhour >= 4 THEN date(se.date)
                             ELSE date(se.date) - interval '1 day'
                           END
GROUP BY
    r.name, r.id, adjusted_date, c.dow, c.week, c.period, c.year
ORDER BY
    adjusted_date DESC, r.name;