PostgreSQL按集群对连续记录排序

Question

我需要用一个数值对一些记录进行连续排序。我需要使用lead()和lag()函数，但我不知道如何使用。

表*

lote        cod_guia    box     des_prod 
1,50731E+14   42012    50434    MERLUZA DEL SUR ENFRIADO REFRIGERADO
1,51E+14    42012   50435   MERLUZA DEL SUR ENFRIADO REFRIGERADO
1,51E+14    42012   50436   MERLUZA DEL SUR ENFRIADO REFRIGERADO

我的sql

Select 
    packing_acum.lote as lote_completo,
    m_pesaje.cod_guia,
    --lag(packing_acum.corr,1) over wt as anterior,
    min(packing_acum.corr),
    max(packing_acum.corr),
    --lead(packing_acum.corr,1) over wt as proximo,
    producto.des_prod ,
   case when packing.tipo = 1 THEN 'ENFRIADO REFRIGERADO' ELSE 'CONGELADO' end  as estado_fresco_o_congelado 
From m_pesaje 
 inner join ( producto 
 inner join (packing 
 inner join packing_acum 
    on packing.cod_packing = packing_acum.cod_packing) 
    on packing_acum.cod_prod = producto.cod_prod) 
    on packing_acum.cod_pesaje = m_pesaje.cod_pesaje 
where 
    packing_acum.estado = 5668 and packing_acum.cod_prod <> 0
group by lote_completo,cod_guia,des_prod,estado_fresco_o_congelado
order by 
    min(packing_acum.corr),max(packing_acum.corr)

结果：

lote    cod_guia    box_ini box_end des_prod        estado_fresco_o_congelado
1,50731E+14 42012   50434   50507   MERLUZA DEL SUR ENFRIADO REFRIGERADO
1,50731E+14 41934   50498   50502   MERLUZA DEL SUR ENFRIADO REFRIGERADO
1,50731E+14 42010   50503   50505   MERLUZA DEL SUR ENFRIADO REFRIGERADO

我需要的：

lote    cod_guia    box_ini box_end des_prod        estado_fresco_o_congelado
1,50731E+14 42012   50434   50497   MERLUZA DEL SUR ENFRIADO REFRIGERADO
1,50731E+14 41934   50498   50502   MERLUZA DEL SUR ENFRIADO REFRIGERADO
1,50731E+14 42010   50503   50505   MERLUZA DEL SUR ENFRIADO REFRIGERADO
1,50731E+14 42012   50505   50507   MERLUZA DEL SUR ENFRIADO REFRIGERADO

Answer 1

您的问题是，您必须在连续的“成对”行上分组(由packing_acum.corr连续，由m_pesaje.cod_guia配对)，或者从您提供的数据来看。使用lag()和lead()窗口函数无法做到这一点，因为您没有合适的PARTITION可以打开。相反，您应该首先为GROUP创建一些值，然后可以使用简单的聚合函数。

考虑到您没有提供表定义、示例数据或PostgreSQL版本，下面的内容有些猜测，但可能只适用于您。

SELECT sub.lote_completo, sub.cod_guia, sub.box_ini, sub.box_end, 
       producto.des_prod,
       CASE WHEN packing.tipo = 1 THEN 'ENFRIADO REFRIGERADO' ELSE 'CONGELADO'
       END AS estado_fresco_o_congelado
FROM (  

  SELECT lote AS lote_completo, cod_guia, cod_packing, cod_prod,
         min(corr) AS box_ini, max(corr) AS box_end
  FROM (

    SELECT pa.lote, pes.cod_guia, pa.corr, pa.cod_packing, pa.cod_prod,
           row_number() OVER (ORDER BY pa.corr) -
           row_number() OVER (PARTITION BY pes.cod_guia ORDER BY pa.corr) AS cls
    FROM packing_acum pa
    JOIN m_pesaje pes USING (cod_pesaje)
    WHERE pa.estado = 5668
      AND pa.cod_prod <> 0) subsub

  GROUP BY lote, cod_guia, cod_packing, cod_prod, cls) sub

JOIN producto USING (cod_prod)
JOIN packing USING (cod_packing)
ORDER BY sub.box_ini, sub.box_end;

在subsub子查询中，伪类编号cls是通过在整个表上(在对WHERE子句中的两个条件进行过滤之后)减去cod_guia值上的分区中的行号来生成的。这给出了从0开始的数字，但是除了相邻行中的“pa.corr”( cod_guia )值相同的cls值之外，没有其他特殊的值或属性。

在sub子查询中，您对该cls值进行聚合，生成最小和最大框号，在主查询中添加其他所需列。