Relational Data - Aggregate Functions - Reference - Maxintersectionsposition

Aggregate function that calculates the positions of the occurrences of the maxIntersections function.

The syntax is:

maxIntersectionsPosition(start_column, end_column)

Arguments

start_column – the numeric column that represents the start of each interval. If start_column is NULL or 0 then the interval will be skipped.
end_column - the numeric column that represents the end of each interval. If end_column is NULL or 0 then the interval will be skipped.

Returned value

Returns the start positions of the maximum number of intersected intervals.

Example

CREATE TABLE my_events (
    start UInt32,
    end UInt32
)
Engine = MergeTree
ORDER BY tuple();

INSERT INTO my_events VALUES
   (1, 3),
   (1, 6),
   (2, 5),
   (3, 7);

The intervals look like the following:

1 - 3
1 - - - - 6
  2 - - 5
    3 - - - 7

Notice that three of these intervals have the value 4 in common, and that starts with the 2nd interval:

SELECT maxIntersectionsPosition(start, end) FROM my_events;

Response:

In other words, the (1,6) row is the start of the 3 intervals that intersect, and 3 is the maximum number of intervals that intersect.