title_wip

Additionally, when there are at least two more "state" rows than "cancel"
rows, or at least two more "cancel" rows than "state" rows, the merge continues.
ClickHouse, however, treats this situation as a logical error and records it in the server log.
This error can occur if the same data is inserted more than once.
Thus, collapsing should not change the results of calculating statistics.
Changes are gradually collapsed so that in the end only the last state of almost every object is left.

The `Sign` column is required because the merging algorithm does not guarantee
that all the rows with the same sorting key will be in the same resulting data part and even on the same physical server.
ClickHouse processes `SELECT` queries with multiple threads, and it cannot predict the order of rows in the result.

Aggregation is required if there is a need to get completely "collapsed" data from the `CollapsingMergeTree` table.
To finalize collapsing, write a query with the `GROUP BY` clause and aggregate functions that account for the sign.
For example, to calculate quantity, use `sum(Sign)` instead of `count()`.
To calculate the sum of something, use `sum(Sign * x)` together `HAVING sum(Sign) > 0` instead of `sum(x)`
as in the [example](#example-of-use) below.

The aggregates `count`, `sum` and `avg` could be calculated this way.
The aggregate `uniq` could be calculated if an object has at least one non-collapsed state.
The aggregates `min` and `max` could not be calculated
because `CollapsingMergeTree` does not save the history of the collapsed states.

Note

If you need to extract data without aggregation
(for example, to check whether rows whose newest values match certain conditions are present),
you can use the [`FINAL`](/docs/sql-reference/statements/select/from#final-modifier) modifier for the `FROM` clause. It will merge the data before returning the result.
For CollapsingMergeTree, only the latest state row for each key is returned.

## Examples[​](#examples "Direct link to Examples")

### Example of use[​](#example-of-use "Direct link to Example of use")

Given the following example data:

```
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
```

Let's create a table `UAct` using the `CollapsingMergeTree`:

```
CREATE TABLE UAct
(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID
```

Next we will insert some data:

```
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1)
```

```
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1),(4324182021466249494, 6, 185, 1)
```

We use two `INSERT` queries to create two different data parts.

Note

If we insert the data with a single query, ClickHouse creates only one data part and will not perform any merge ever.

We can select the data using:

```
SELECT * FROM UAct
```

```
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
```

Let's take a look at the returned data above and see if collapsing occurred...
With two `INSERT` queries, we created two data parts.
The `SELECT` query was performed in two threads, and we got a random order of rows.
However, collapsing **did not occur** because there was no merge of the data parts yet
and ClickHouse merges data parts in the background at an unknown moment which we cannot predict.

We therefore need an aggregation
which we perform with the [`sum`](/docs/sql-reference/aggregate-functions/reference/sum)
aggregate function and the [`HAVING`](/docs/sql-reference/statements/select/having) clause:

```
SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM UAct
GROUP BY UserID
HAVING sum(Sign) > 0
```

```
┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │         6 │      185 │
└─────────────────────┴───────────┴──────────┘
```

If we do not need aggregation and want to force collapsing, we can also use the `FINAL` modifier for `FROM` clause.

```
SELECT * FROM UAct FINAL
```

```
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
```

Note

This way of selecting the data is less efficient and is not recommended for use with large amounts of scanned data (millions of rows).

### Example of another approach[​](#example-of-another-approach "Direct link to Example of another approach")

The idea with this approach is that merges take into account only key fields.
In the "cancel" row, we can therefore specify negative values
that equalize the previous version of the row when summing without using the `Sign` column.

For this example, we will make use of the sample data below:

```
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
│ 4324182021466249494 │        -5 │     -146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
```

For this approach, it is necessary to change the data types of `PageViews` and `Duration` to store negative values.
We therefore change the values of these columns from `UInt8` to `Int16` when we create our table `UAct` using the
`collapsingMergeTree`:

```
CREATE TABLE UAct
(
    UserID UInt64,
    PageViews Int16,
    Duration Int16,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID
```

Let's test the approach by inserting data into our table.

For examples or small tables, it is, however, acceptable:

```
INSERT INTO UAct VALUES(4324182021466249494,  5,  146,  1);
INSERT INTO UAct VALUES(4324182021466249494, -5, -146, -1);
INSERT INTO UAct VALUES(4324182021466249494,  6,  185,  1);

SELECT * FROM UAct FINAL;
```

```
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
```

```
SELECT
    UserID,
    sum(PageViews) AS PageViews,
    sum(Duration) AS Duration
FROM UAct
GROUP BY UserID
```

```
┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │         6 │      185 │
└─────────────────────┴───────────┴──────────┘
```

```
SELECT COUNT() FROM UAct
```

```
┌─count()─┐
│       3 │
└─────────┘
```

```
OPTIMIZE TABLE UAct FINAL;

SELECT * FROM UAct
```

```
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
```

- [Description](#description)- [Parameters](#parameters)- [Creating a table](#creating-a-table)- [Collapsing](#table_engine-collapsingmergetree-collapsing)
        - [Data](#data)- [Algorithm](#table_engine-collapsingmergetree-collapsing-algorithm)- [Examples](#examples)
          - [Example of use](#example-of-use)- [Example of another approach](#example-of-another-approach)

Was this page helpful?