Real-time unique visitor analytics on small dataset

Hologres optimizes COUNT DISTINCT so you can count unique visitors (UVs) directly from fact tables — no pre-aggregation or scheduled jobs required. This approach suits datasets up to tens of millions of rows and returns exact, real-time results for ad hoc queries.

As data volume grows beyond tens of millions of rows, query throughput (QPS) and computing efficiency may decrease.

How it works

COUNT DISTINCT in Hologres is automatically optimized and supports one or more fields. Two query patterns are supported:

Pattern	When to use	Performance tip
Wide fact table	All user attributes are in a single table	Apply bitmap indexes on low-cardinality filter columns; set `ymd` as the clustering key
Fact table joined with dimension table	User profile attributes (such as gender or region) are stored separately	Match the distribution key on both tables so data is co-located and the join stays in-shard

Both patterns let you specify any time range without pre-computation.

Examples

Count UVs in a wide fact table

This example counts UVs and page views (PVs) from a wide fact table, grouped by geography.

Create the fact table.

-- Create a wide fact table with column-oriented storage
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
);
-- Use column-oriented storage for analytical workloads
CALL set_table_property('ods_app_detail', 'orientation', 'column');
-- Set bitmap indexes on low-cardinality filter columns to speed up WHERE clauses
CALL set_table_property('ods_app_detail', 'bitmap_columns', 'country,prov,city,channel,operator,brand,ip,click_time,year,month,day,ymd');
-- Distribute data by uid so COUNT DISTINCT uid aggregates within each shard
CALL set_table_property('ods_app_detail', 'distribution_key', 'uid');
-- Set ymd as the clustering key and event time column for efficient date-range filtering
CALL set_table_property('ods_app_detail', 'clustering_key', 'ymd');
CALL set_table_property('ods_app_detail', 'event_time_column', 'ymd');
COMMIT;

Query UV and PV counts for a date range.

-- Count UVs and PVs by region for March 2024
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;

Count UVs by joining a fact table with a dimension table

Use this pattern when user attributes such as gender, region, or account tier are stored separately from event data.

Create the fact table and dimension table.

-- Create a fact table that stores user activity events
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');
-- Distribute by uid to co-locate rows for the upcoming join
CALL set_table_property('ods_app_detail', 'distribution_key', 'uid');
-- Set ymd as the clustering key and event time column for date-range queries
CALL set_table_property('ods_app_detail', 'clustering_key', 'ymd');
CALL set_table_property('ods_app_detail', 'event_time_column', 'ymd');
COMMIT;

-- Create a dimension table that stores user profile information
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');
-- Match the distribution key of the fact table to optimize the join
CALL set_table_property('dim_uid_info', 'distribution_key', 'uid');
COMMIT;

Join the tables and count UVs for a date range.

-- Count UVs and PVs for male users by region in March 2024
SELECT
    COUNT(DISTINCT B.uid) AS uv,
    A.country,
    A.prov,
    A.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 A.country, A.prov, A.city;