ウインドウ関数(ntile)

ntile関数 概要

データを1〜Nに分割する
ntile(N) over (...)

使用例

(セットアップ)

-- 初期化
drop table if exists orders;
create table orders (
    user_id bigint,
    amount bigint
);

-- ランダムデータを登録
insert into orders (user_id, amount)
select
    ((random() * 14) + 1)::integer
    , (random() * 100000)::integer
from generate_series(1, 10000);

ntile関数

select
    user_id as user_id,
    sum(amount) as total,
    avg(amount) as avarage,
    count(*) as count,
    ntile(10) over(order by sum(amount) desc) as decile
from orders
group by user_id;

(結果)

 user_id |  total   |      avarage       | count | decile
---------+----------+--------------------+-------+--------
       5 | 36971500 | 51207.063711911357 |   722 |      1
       9 | 36941712 | 48479.937007874016 |   762 |      1
       4 | 36925173 | 51356.290681502086 |   719 |      2
      12 | 36713323 | 50920.004160887656 |   721 |      2
       2 | 36594138 | 51180.612587412587 |   715 |      3
       7 | 36120228 | 47464.162943495401 |   761 |      3
       6 | 35432197 | 50116.261669024045 |   707 |      4
      14 | 35334110 | 48535.865384615385 |   728 |      4
       3 | 35136999 | 51748.157584683358 |   679 |      5
      11 | 34629867 | 49330.294871794872 |   702 |      5
      10 | 34360367 | 48946.391737891738 |   702 |      6
       8 | 34194119 | 49991.402046783626 |   684 |      7
      13 | 33541738 | 49913.300595238095 |   672 |      8
      15 | 17982500 | 50371.148459383754 |   357 |      9
       1 | 17594380 | 47681.246612466125 |   369 |     10
(15 rows)