SQL HowTo: classificação por intervalo

Um dos requisitos mais frequentes, "desejos" de uma empresa, é a construção de todos os tipos de classificações diferentes -  "os clientes mais engenhosos", "as posições mais vendidas", "os funcionários mais ativos" , ... - um tópico favorito de vários painéis.

Por exemplo, em nossa solução Presto para automatizar restaurantes e cafés , o seguinte é muito popular:

Mas apenas o "máximo" de todo o período pré-histórico costuma ser desinteressante - você vendeu uma carroça de botas de feltro há 3 anos e agora está na "maior" venda para sempre. Portanto, geralmente queremos ver o  "topo" em algum último intervalo limitado  - por exemplo, "para o último ano" (mais precisamente, para os últimos 12 meses do calendário).

, : "" . " " - 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;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

( , ) -  , ( , ) , .