千万级数据量实时统计UV_实时数仓 Hologres(Hologres)-阿里云帮助中心

Hologres针对小规模数据量（千万级）的UV计算场景，提供即席UV查询方案，支持您使用COUNT DISTINCT对明细表进行UV计算，尤其当您需要从原始数据中获取最准确、最即时的结果时。本方案避免了使用预聚合表或其他复杂的数据处理技术，以简化查询过程。

方案介绍

对于小规模数据量（千万级）的UV计算场景，在Hologres中可以使用COUNT DISTINCT直接查询明细表，并通过筛选时间周期来实现任意长周期的UV计算。同时Hologres对COUNT DISTINCT进行了多种优化，可以高效地支持单个或多个字段的COUNT DISTINCT，满足大部分的UV计算场景。在明细表JOIN维表的场景，可以设置合理的索引来进一步提升性能。该方案的优缺点及适用场景如下：

优缺点
- 优点：实时性好，可以满足用户的实时UV计算需求，同时计算灵活，可以按需选择时间周期进行UV计算，无需预计算和调度等配置即可实现即席的UV、PV查询。结合Hologres内置的COUNT DISTINCT自动优化，计算效率相比其他产品有显著提升。
- 缺点：数据量变大时，计算时效可能会降低，支持的QPS也会降低。
适用场景
适用于小规模数据量（千万级）UV查询场景。

方案流程

明细宽表计算UV

若只有一张明细宽表，可以直接对UID字段使用COUNT DISTINCT实现UV计算，示例如下：

准备一张明细宽表ods_app_detail。

--明细宽表
BEGIN;
CREATE TABLE IF NOT EXISTS ods_app_detail (
     uid int,
     country text,
     prov text,
     city text,
     channel text,
     operator text,
     brand text,
     ip text,
     click_time text,
     year text,
     month text,
     day text,
     ymd text NOT NULL
);
CALL set_table_property('ods_app_detail', 'orientation', 'column');
CALL set_table_property('ods_app_detail', 'bitmap_columns', 'country,prov,city,channel,operator,brand,ip,click_time, year, month, day, ymd');
--distribution_key根据需求设置，根据该表的实时查询需求，从什么维度做分片能够取得较好效果即可
CALL set_table_property('ods_app_detail', 'distribution_key', 'uid');
CALL set_table_property('ods_app_detail', 'clustering_key', 'ymd');
CALL set_table_property('ods_app_detail', 'event_time_column', 'ymd');
COMMIT;

使用COUNT DISTINCT计算UV。

--查询一个月的UV、PV
SELECT  
 COUNT (DISTINCT uid) AS uv,
  country,
  prov,
  city,
 COUNT(1) AS pv
FROM public.ods_app_detail
WHERE ymd >= '20240301' AND ymd <= '20240331'
GROUP BY country,prov,city;

明细表和维表JOIN计算UV

部分业务场景可能需要使用维表和明细表进行JOIN才能实现UV计算，常见的示例如下：

准备基础表。

--明细表，记录用户的操作明细
BEGIN;
CREATE TABLE IF NOT EXISTS ods_app_detail (
     uid int,
     channel text,
     operator text,
     brand text,
     ip text,
     click_time text,
     year text,
     month text,
     day text,
     ymd text NOT NULL
);
CALL set_table_property('ods_app_detail', 'orientation', 'column');
CALL set_table_property('ods_app_detail', 'bitmap_columns', 'channel,operator,brand,ip,click_time, year, month, day, ymd');
--distribution_key根据需求设置，根据该表的实时查询需求，从什么维度做分片能够取得较好效果即可
CALL set_table_property('ods_app_detail', 'distribution_key', 'uid');
--用于做where过滤条件，包含完整年月日时间字段推荐设为clustering_key和event_time_column
CALL set_table_property('ods_app_detail', 'clustering_key', 'ymd');
CALL set_table_property('ods_app_detail', 'event_time_column', 'ymd');
COMMIT;

--维表，记录用户的属性信息
BEGIN;
CREATE TABLE dim_uid_info (
    uid int NOT NULL,
    name text NOT NULL,
    gender text NOT NULL,
    country text,
    prov text,
    city text
);
CALL set_table_property('dim_uid_info', 'orientation', 'column');
CALL set_table_property('dim_uid_info', 'bitmap_columns', 'country,prov,city');
CALL set_table_property('dim_uid_info', 'distribution_key', 'uid');
COMMIT;

通过明细表JOIN维表计算任意周期的UV。

--查询一个月内性别为男的用户的UV、PV
SELECT  
 COUNT (DISTINCT B.uid) AS uv,
  country,
  prov,
  city,
  COUNT(1)  AS pv
FROM 
( SELECT uid,country,prov,city FROM dim_uid_info WHERE gender = 'man'
) AS A
LEFT JOIN ods_app_detail  AS B ON A.uid = B.uid
WHERE B.ymd >= '20240301' AND B.ymd <= '20240331'
GROUP BY country,prov,city;