Table engines

The reason is that you must write cancel rows when you delete states. Cancel rows must contain the data that has the same primary keys as the primary keys of original state rows, except the Sign column.

1. Create a detail table named visits.

   CREATE TABLE visits
   (
       UserID UInt64,
       CounterID UInt8,
       StartDate Date,
       Sign Int8
   )
   ENGINE = CollapsingMergeTree(Sign)
   ORDER BY UserID;

2. Create a materialized view named visits_agg_view for the visits table, and run the sumState and uniqState functions to pre-aggregate the visits table.

   CREATE MATERIALIZED VIEW visits_agg_view
   ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate)
   AS SELECT
       CounterID,
       StartDate,
       sumState(Sign)    AS Visits,
       uniqState(UserID) AS Users
   FROM visits
   GROUP BY CounterID, StartDate;

3. Write data to the visits table.

   INSERT INTO visits VALUES(0, 0, '2019-11-11', 1);
   INSERT INTO visits VALUES(1, 1, '2019-11-12', 1);

4. Run the sumMerge and uniqMerge aggregate function to aggregate the materialized view. Then, query the aggregated data.

   SELECT
       StartDate,
       sumMerge(Visits) AS Visits,
       uniqMerge(Users) AS Users
   FROM visits_agg_view
   GROUP BY StartDate
   ORDER BY StartDate

   Note The sum and uniq functions can no longer be used. If you use the preceding functions, the following error message is returned when you execute the SQL statement: Illegal type AggregateFunction(sum, Int8) of argument for aggregate function sum...
   The following query result is returned:

   StartDate │Visits│Users
   ----------│------│----
   2019-11-11│ 1    │ 1
   2019-11-12│ 1    │ 1

1. Create a detail table named detail_table.

   CREATE TABLE detail_table
   (   CounterID UInt8,
       StartDate Date,
       UserID UInt64
   ) ENGINE = MergeTree() 
   PARTITION BY toYYYYMM(StartDate) 
   ORDER BY (CounterID, StartDate);

2. Write data to detail_table.

   INSERT INTO detail_table VALUES(0, '2019-11-11', 1);
   INSERT INTO detail_table VALUES(1, '2019-11-12', 1);

3. Create an aggregate table named agg_table in which the data type of the UserID column is AggregateFunction.

   CREATE TABLE agg_table
   (   CounterID UInt8,
       StartDate Date,
       UserID AggregateFunction(uniq, UInt64)
   ) ENGINE = AggregatingMergeTree() 
   PARTITION BY toYYYYMM(StartDate) 
   ORDER BY (CounterID, StartDate);

4. Run the uniqState aggregate function to insert data of the detail table into the aggregate table.

   INSERT INTO agg_table
   select CounterID, StartDate, uniqState(UserID)
   from detail_table
   group by CounterID, StartDate;

   Note You cannot execute the INSERT INTO agg_table VALUES(1, '2019-11-12', 1); statement to insert data into the aggregate table. If you execute the preceding statement, the following error message is returned: Cannot convert UInt64 to AggregateFunction(uniq, UInt64)...
5. Run the uniqMerge aggregate function to aggregate data in the aggregate table. Then, query the aggregated data.

   SELECT uniqMerge(UserID) AS state 
   FROM agg_table 
   GROUP BY CounterID, StartDate;

   The following query result is returned:

   state 
   ------
    1
    1