TopN 的函數及其應用

列出函數

\df topn*
                               List of functions
 Schema |        Name         | Result data type  | Argument data types | Type 
--------+---------------------+-------------------+---------------------+------
 public | topn                | SETOF topn_record | jsonb, integer      | func
 public | topn_add            | jsonb             | jsonb, text         | func
 public | topn_add_agg        | jsonb             | text                | agg
 public | topn_add_trans      | internal          | internal, text      | func
 public | topn_deserialize    | internal          | bytea, internal     | func
 public | topn_pack           | jsonb             | internal            | func
 public | topn_serialize      | bytea             | internal            | func
 public | topn_union          | jsonb             | jsonb, jsonb        | func
 public | topn_union_agg      | jsonb             | jsonb               | agg
 public | topn_union_internal | internal          | internal, internal  | func
 public | topn_union_trans    | internal          | internal, jsonb     | func
(11 rows)

topn_add(jsonb, text) -> jsonb

select topn_add('{}', 'a');
  
 topn_add 
----------
 {"a": 1}

select topn_add(topn_add('{}', 'a'), 'a');

 topn_add 
----------
 {"a": 2}

topn_add_agg(text) -> jsonb

select topn_add_agg(n::text)
     , topn_add_agg('a')
  from generate_series(1,6) as g(n);

                   topn_add_agg                   | topn_add_agg 
--------------------------------------------------+--------------
 {"1": 1, "2": 1, "3": 1, "4": 1, "5": 1, "6": 1} | {"a": 6}
(1 row)

create extension tablefunc with schema public;

commit;

select topn_add_agg(floor(abs(i))::text)
  from normal_rand(1000, 5, 0.7) i;

                      topn_add_agg                      
--------------------------------------------------------
 {"2": 3, "3": 69, "4": 424, "5": 436, "6": 63, "7": 5}

應用 topn_add_agg() 聚合後 再用 topn() 取出前N

set topn.number_of_counters to 1000;
commit;

--十大 customer 以及資料次數(出現頻率 frequency)

select (topn(topn_add_agg(customer_id), 10)).*
  from customer_reviews;
  
      item      | frequency 
----------------+-----------
 A3UN6WX5RRO2AG |     15157
 ATVPDKIKX0DER  |      8649
 A9Q28YTLYREO7  |      2761
 A14OJS0VWMOSWO |      1977
 A1K1JW1C5CUSUZ |      1302
 AFVQZQ8PW0L    |      1144
 A2QRB6L1MCJ53G |      1011
 A3QVAKVRAH657N |       986
 A2EENLV6OQ3DYM |       802
 A2CSNTGQ0A3IRN |       726
(10 rows)

set topn.number_of_counters to 100;

select (topn(topn_add_agg(customer_id), 10)).*
  from customer_reviews;
  
      item      | frequency 
----------------+-----------
 A3UN6WX5RRO2AG |     15078
 ATVPDKIKX0DER  |      8649
 A9Q28YTLYREO7  |      2757
 A14OJS0VWMOSWO |      1942
 A3QVAKVRAH657N |       907
 A3EOHYIHQM3KK  |       522
 A1K1JW1C5CUSUZ |       407
 A1EKTLUL24HDG8 |       392
 AFVQZQ8PW0L    |       375
 A1AZ64Z84RY52T |       347
(10 rows)

set topn.number_of_counters to 1000;

--十大 產品 以及資料次數(出現頻率 frequency)

select (topn(topn_add_agg(product_id), 10)).*
  from customer_reviews;

    item    | frequency 
------------+-----------
 0807282596 |      3430
 0786229276 |      3428
 0439139597 |      3428
 0939173379 |      3427
 0807282588 |      3427
 0439139600 |      3427
 B00004NRPZ |      1527
 B00004NRU3 |      1527
 0807281751 |      1234
 0807286001 |      1234
(10 rows)

--每種 product_category, 前10名客戶
select product_category
     , (topn(topn_add_agg(customer_id), 10)).*
  from customer_reviews
 group by product_category
 order by 3 desc
 limit 10;

     product_category      |      item      | frequency 
---------------------------+----------------+-----------
 Children's Books          | A3UN6WX5RRO2AG |      6773
 Teens                     | A3UN6WX5RRO2AG |      1102
 Books on Tape             | A3UN6WX5RRO2AG |      1065
 Literature & Fiction      | ATVPDKIKX0DER  |      1032
 Children's Books          | ATVPDKIKX0DER  |       971
 Science Fiction & Fantasy | A3UN6WX5RRO2AG |       745
 Literature & Fiction      | A3UN6WX5RRO2AG |       682
 R&B                       | A9Q28YTLYREO7  |       597
 Science Fiction & Fantasy | ATVPDKIKX0DER  |       557
 Mystery & Thrillers       | AFVQZQ8PW0L    |       522
(10 rows)

-- 會有 850 筆,但是使用 limit