PostgreSQL：按小时查看调整日期

发布于01月02日

我有一个视图，它将销售数据查询到一个压缩表中，该表汇总每个位置的net_sales. 我的问题是午夜后有销售. 我的代码是按日期分组的，所以午夜之后的任何销售都列在错误的一天.

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(Orderhr)中还有另一列，它是从0到23的大整数.系统在凌晨4点关闭，所以我需要做的是收集从orderHour[4]到orderHour[3]的销售额，并将orderHour[0-3]的销售额应用于前一个日期.这是可以做到的吗？

推荐答案

我try 修改您的SQL查询，以将午夜到凌晨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;