SQL HowTo: notation par intervalle

L'une des exigences les plus fréquentes, les «désirs» d'une entreprise, est la construction de toutes sortes de notations différentes -  «les clients les plus débrouillards», «les postes les plus vendus», «les employés les plus actifs» , ... - un sujet favori de divers tableaux de bord.

Par exemple, dans notre solution d'automatisation de restaurants et de cafés, Presto est très populaire comme ceci:

Mais simplement les «plus» pour toute la période préhistorique sont généralement inintéressants - vous avez vendu un chariot de bottes en feutre il y a 3 ans, et maintenant vous l'avez dans le «plus» des ventes pour toujours. Par conséquent, on veut généralement voir le  «sommet» dans un dernier intervalle limité  - par exemple, «pour la dernière année» (plus précisément, pour les 12 derniers mois civils).

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

( , ) -  , ( , ) , .