sql - 计算按某些行分组的出现次数

我已经查询了没有库存的产品数量（我知道通过查看制造商返回的带有状态代码的订单），按产品、日期和存储，如下所示：

SELECT count(*) as out_of_stock,
prod.id as product_id,
ped.data_envio::date as date,
opl.id as storage_id
from sub_produtos_pedidos spp
left join cad_produtos prod ON spp.ean_produto = prod.cod_ean
left join sub_pedidos sp ON spp.id_pedido = sp.id
left join pedidos ped ON sp.id_pedido = ped.id
left join op_logisticos opl ON sp.id_op_logistico = opl.id
where spp.motivo = '201' -- this is the code that means 'not in inventory'
group by storage_id,product_id,date

这就产生了这样一个答案：

 out_of_stock | product_id | date        | storage_id
--------------|------------|-------------|-------------
 1            | 5          | 2012-10-16  | 1
 5            | 4          | 2012-10-16  | 2

现在，我需要按产品和存储来获取缺货2天或更长时间、5天或更长时间等产品的发生次数。
所以我想我需要对第一个查询进行一个新的计数，在一些定义的日期间隔内聚合结果行。
我试着查看Postgres（http://www.postgresql.org/docs/7.3/static/functions-datetime.html）中的datetime函数，但找不到所需的内容。

最佳答案

由于您似乎希望结果中的每一行都是单独的，因此无法聚合。使用window function来获取每天的计数。众所周知的聚合函数count()也可以用作窗口聚合函数：

SELECT current_date - ped.data_envio::date AS days_out_of_stock
      ,count(*) OVER (PARTITION BY ped.data_envio::date)
                                        AS count_per_days_out_of_stock
      ,ped.data_envio::date AS date
      ,p.id                 AS product_id
      ,opl.id               AS storage_id
FROM   sub_produtos_pedidos spp
LEFT   JOIN cad_produtos    p   ON p.cod_ean = spp.ean_produto
LEFT   JOIN sub_pedidos     sp  ON sp.id     = spp.id_pedido
LEFT   JOIN op_logisticos   opl ON opl.id    = sp.id_op_logistico
LEFT   JOIN pedidos         ped ON ped.id    = sp.id_pedido
WHERE  spp.motivo = '201'                   -- code for 'not in inventory'
ORDER  BY ped.data_envio::date, p.id, opl.id

排序顺序：首先是缺货时间最长的产品。
注意，你只需减去dates就可以在Postgres中得到integer。
如果您想要一个“n行在此天数或更长时间内缺货”的运行计数，请使用：

count(*) OVER (ORDER BY ped.data_envio::date) -- ascending order!
                                        AS running_count_per_days_out_of_stock

你在同一天得到相同的计数，同龄人被归在一起。

关于sql - 计算按某些行分组的出现次数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/19053225/