SQL HowTo: rating-per-interval

One of the most frequent requirements, "wants" of a business, is the construction of all sorts of different ratings -  "the most resourceful customers", "the most sold positions", "the most active employees" , ... - a favorite topic of various dashboards.

For example, in our Presto solution for automating restaurants and cafes , the following is very popular:

But just the "most" for the entire prehistoric period are usually uninteresting - you sold a car of felt boots 3 years ago, and now you have it in the "most" sales forever. Therefore, one usually wants to see the  "top" in some limited last interval  - for example, "for the last year" (more precisely, for the last 12 calendar months).

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

( , ) -  , ( , ) , .