topn

pig build pkg topn         # 构建 DEB 包

pig repo add pgdg -u          # 添加 PGDG 仓库并更新缓存

CREATE EXTENSION topn;

CREATE EXTENSION topn;

-- 按日期物化热门产品
CREATE TABLE popular_products (
  review_date date UNIQUE,
  agg_data jsonb
);

INSERT INTO popular_products
SELECT review_date, topn_add_agg(product_id)
FROM customer_reviews GROUP BY review_date;

-- 获取每天排名第一的产品
SELECT review_date, (topn(agg_data, 1)).*
FROM popular_products ORDER BY review_date;

-- 跨时间范围的 Top 10（合并每日摘要）
SELECT (topn(topn_union_agg(agg_data), 10)).*
FROM popular_products
WHERE review_date >= '2000-01-01' AND review_date < '2000-02-01'
ORDER BY 2 DESC;