SQL HowTo: calificación por intervalo

Uno de los requisitos más frecuentes, los "deseos" de una empresa, es la construcción de todo tipo de calificaciones diferentes:  "los clientes más ingeniosos", "los puestos más vendidos", "los empleados más activos" , ..., un tema favorito de varios paneles.

Por ejemplo, en nuestra solución para la automatización de restaurantes y cafés, Presto es muy popular así:

Pero solo lo "más" de todo el período prehistórico no suele ser interesante: vendiste un vagón de botas de fieltro hace 3 años, y ahora lo tienes en la "mayoría" de ventas para siempre. Por lo tanto, normalmente se quiere ver el  "top" en un último intervalo limitado  , por ejemplo, "para el último año" (más precisamente, para los últimos 12 meses calendario).

, : "" . " " - SQL-, "" , , , , 1- - .

, "" ,   ""   TOP-10 - .

( , ):

CREATE TABLE item_stat(
  item -- 
    integer
, sum
    numeric(32,2)
);
CREATE INDEX ON item_stat(sum DESC);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

-    . - "" ?..

" "

- , , .

- 12- "" . - . "", - :

CREATE TABLE item_stat(
  interval_id -- 0 -  , 202001 -  2020, 202002 - , ...
    integer
, item
    integer
, sum
    numeric(32,2)
, UNIQUE(interval_id, item)
);
CREATE INDEX ON item_stat(interval_id, sum DESC);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, "" - ,     " ". ( Foreign Key, item



):

INSERT INTO item_stat(
  interval_id
, item
, sum
)
VALUES
  (0, 0, 202012) --   (0, 0),  - 2020'12  
ON CONFLICT(interval_id, item)
  DO UPDATE SET
    sum = EXCLUDED.sum; --   
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

(/) , , /   - "" :

INSERT INTO item_stat(
  interval_id
, item
, sum
)
VALUES
  (202001, 1, 100) -- +     2020
, (     0, 1, 100) -- +   
ON CONFLICT(interval_id, item)
  DO UPDATE SET
    sum = item_stat.sum + EXCLUDED.sum; --    
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

  ,   "" , , :

-- ""  
WITH next AS (
  SELECT 202101
)
--   
, prev AS (
  SELECT
    sum::integer
  FROM
    item_stat
  WHERE
    (interval_id, item) = (0, 0)
)
--    ,  ,   
, diff AS (
  SELECT
    item
  , sum(sum) sum
  FROM
    item_stat
  WHERE
    interval_id BETWEEN (TABLE prev) - 100 AND (TABLE next) - 100
  GROUP BY
    1
)
UPDATE
  item_stat dst
SET
  sum = dst.sum - diff.sum
FROM
  diff
WHERE
  (dst.interval_id, dst.item) = (0, diff.item);

UPDATE
  item_stat
SET
  sum = 202101
WHERE
  (interval_id, item) = (0, 0);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, "" - :

SELECT
  *
FROM
  item_stat
WHERE
  interval_id = 0 --  "" 
ORDER BY
  sum DESC
LIMIT 10;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

( , ) -  , ( , ) , .