在SQL中显示固定值的结果

Question

我在解决以下问题时遇到了困难：

我有一个表，其中包含商店I，日期，小时，类别和销售金额。

shopid   date     hour      category  amount
    ------------------------------------
    1    date1    7          food       10
    1    date1    8          food       15
    1    date1    10         misc.      5
    2    date1    7          food       6
    ...................................

我正在尝试按食品类别计算每小时的总销售额，并如下所示：

shopid  category     hour        amount
    ------------------------------------
    1       food      6             0
    1       food      7             5
    1       food      8             20
    2       food      9             40
    ...................................

店铺的开放时间为上午六时至晚上十时。因此，对于每个小时，可能会有任何销售或没有。我能够执行每小时一次的求和。但我无法显示每个销售类别的零以及在特定时间(例如上午6点或营业时间之间的任何其他时间)没有销售的时间。

Answer 1

对小时列表使用左连接：

select t.shopid, t.category. g.hour, sum(t.amount)
from generate_series(6,22) as g(hour)
  left join the_table t on t.hour = g.hour
group by t.shopid, t.category, g.hour
order by t.shopid, t.category, g.hour;

Answer 2

我正在尝试按食品类别计算每小时的总销售额。

这是有道理的，但是在结果中包含shopid是没有意义的。

为此，您需要生成行--它们都是小时数和食物类别。然后使用left join带来实际的结果

select c.category. g.hour, coalesce(sum(s.amount), 0)
from generate_series(6, 22) g(hour) cross join
     (select distinct category from sales) c left join
     sales s
     on s.hour = g.hour and s.category = c.category
group by c.category, g.hour
order by c.category, g.hour;

如果你想要商店/类别/小时的结果，那么你可以使用相同的想法：

select sh.shopid, c.category. g.hour,
       coalesce(sum(s.amount), 0)
from generate_series(6, 22) g(hour) cross join
     (select distinct category from sales) c cross join
     (select distinct shopid from sales) sh left join
     sales s
     on s.shopid = sh.shopid and
        s.hour = g.hour and
        s.category = c.category
group by sh.shopid, c.category, g.hour
order by sh.shopid, c.category, g.hour;