🕺🏽 🎯 💭 SQL HowTo：間隔ごとの評価 📣 🃏 🙋🏾

ビジネスの最も頻繁な要件の1つである「要望」は、さまざまなダッシュボードのお気に入りのトピックである「最も機知に富んだ顧客」、「最も売れたポジション」、「最もアクティブな従業員」など、あらゆる種類の異なる評価の構築です。

たとえば、レストランやカフェを自動化するためのソリューションでは、Prestoは次のように非常に人気があります。

しかし、先史時代全体の「ほとんど」は通常、面白くありません。3年前にフェルトブーツのワゴンを販売しましたが、今では永遠に「最も」販売されています。したがって、通常、限られた最後の間隔で「トップ」を表示する必要があります。たとえば、「昨年」（より正確には、過去12か月間）です。

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

( , ) - , ( , ) , .

SQL HowTo：間隔ごとの評価

" "

More articles: