uniqCombined64

gistfile1.txt

https://fiddle.clickhouse.com/fabe3448-a682-4866-af76-90bdf816d74a


CREATE TABLE dummy (date Date, id  String ) 
ENGINE = Null;

CREATE TABLE t (
  date Date,
  idstate13 AggregateFunction(uniqCombined64(13), String),
  idstate17 AggregateFunction(uniqCombined64(17), String),
  hll12State AggregateFunction(uniqHLL12, String),
  thetaState AggregateFunction(uniqTheta, String)
)ENGINE = AggregatingMergeTree()
ORDER BY date settings min_bytes_for_wide_part=1;

create materialized view mv1 to t
as select date, 
  uniqCombined64State(13)(id) as idstate13,
  uniqCombined64State(17)(id) as idstate17,
  uniqHLL12State(id) hll12State,
  uniqThetaState(id) thetaState 
from dummy
group by date;

INSERT INTO dummy select today() + number % 10, number from numbers(1e7);

INSERT INTO dummy select today() + number % 10, number%100 from numbers(1e8);

select uniqCombined64Merge(13)(idstate13) uc13,
       uniqCombined64Merge(17)(idstate17) uc17,
       uniqHLL12Merge(hll12State) hll12,
       uniqThetaMerge(thetaState) theta
from t format Pretty;


select date, uniqCombined64Merge(13)(idstate13) uc13,
  uniqCombined64Merge(17)(idstate17) uc17,
  uniqHLL12Merge(hll12State) hll12,
  uniqThetaMerge(thetaState) theta  
from t 
group by date
order by date 
format Pretty;


SELECT
    table,
    column,
    formatReadableSize(sum(column_data_compressed_bytes) AS size) AS compressed
FROM system.parts_columns
WHERE (active = 1) AND (database LIKE '%') AND (table LIKE 't')
GROUP BY
    table,
    column
ORDER BY size DESC
format Pretty;


   +---------+---------+---------+---------+
   |    uc13 |    uc17 |   hll12 |   theta |
   +---------+---------+---------+---------+
1. | 9994834 | 9989169 | 9915063 | 9943597 |
   +---------+---------+---------+---------+
    +------------+---------+---------+---------+---------+
    |       date |    uc13 |    uc17 |   hll12 |   theta |
    +------------+---------+---------+---------+---------+
 1. | 2025-02-18 |  972080 |  998678 | 1003071 | 1013978 |
    +------------+---------+---------+---------+---------+
 2. | 2025-02-19 | 1000229 |  999204 | 1022415 | 1028416 |
    +------------+---------+---------+---------+---------+
 3. | 2025-02-20 |  992601 |  995065 |  999748 |  991140 |
    +------------+---------+---------+---------+---------+
 4. | 2025-02-21 | 1013674 | 1000013 |  962150 |  992961 |
    +------------+---------+---------+---------+---------+
 5. | 2025-02-22 | 1019410 | 1007964 |  990321 |  996738 |
    +------------+---------+---------+---------+---------+
 6. | 2025-02-23 |  988692 |  999819 | 1033093 |  987293 |
    +------------+---------+---------+---------+---------+
 7. | 2025-02-24 | 1007871 | 1003891 | 1007449 |  994984 |
    +------------+---------+---------+---------+---------+
 8. | 2025-02-25 |  982381 |  996437 |  996377 |  994794 |
    +------------+---------+---------+---------+---------+
 9. | 2025-02-26 |  995541 |  999362 |  990535 | 1001624 |
    +------------+---------+---------+---------+---------+
10. | 2025-02-27 |  986490 |  997577 |  989040 |  988492 |
    +------------+---------+---------+---------+---------+
   +-------+------------+------------+
   | table | column     | compressed |
   +-------+------------+------------+
1. | t     | idstate17  | 959.60 KiB |
   +-------+------------+------------+
2. | t     | thetaState | 322.40 KiB |
   +-------+------------+------------+
3. | t     | idstate13  | 56.88 KiB  |
   +-------+------------+------------+
4. | t     | hll12State | 26.54 KiB  |
   +-------+------------+------------+
5. | t     | date       | 94.00 B    |
   +-------+------------+------------+