我进行了查询,以使我了解没有库存的产品数量(通过查看制造商以某些状态代码返回的订单),按产品,日期和库存,如下所示:
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函数,但找不到所需的东西。
由于您似乎希望单独显示结果中的每一行,因此无法进行汇总。而是使用窗口函数来获取每天的计数。众所周知的聚合函数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
以integer
在Postgres中获得一个。
如果要以“ n行已超过此天数或更多的行数”的意义进行计数,请使用:
count(*) OVER (ORDER BY ped.data_envio::date) -- ascending order!
AS running_count_per_days_out_of_stock
您在同一天获得相同的计数,将同伴聚集在一起。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句