hll

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

CREATE EXTENSION hll;

-- 存储每日唯一用户计数
CREATE TABLE daily_uniques (
    date  date UNIQUE,
    users hll
);

-- 聚合每日数据
INSERT INTO daily_uniques(date, users)
    SELECT date, hll_add_agg(hll_hash_integer(user_id))
    FROM facts GROUP BY 1;

-- 每周独立用户数（合并操作无损）
SELECT hll_cardinality(hll_union_agg(users))
FROM daily_uniques
WHERE date >= '2012-01-02' AND date <= '2012-01-08';

-- 按月统计
SELECT EXTRACT(MONTH FROM date) AS month,
       #hll_union_agg(users) AS approx_uniques
FROM daily_uniques
WHERE date >= '2012-01-01' AND date < '2013-01-01'
GROUP BY 1;

-- 7 天滑动窗口
SELECT date, #hll_union_agg(users) OVER seven_days
FROM daily_uniques
WINDOW seven_days AS (ORDER BY date ASC ROWS 6 PRECEDING);