intervalLengthSum

Calculates the total length of union of all ranges (segments on numeric axis).

Syntax

intervalLengthSum(start, end)

Arguments

• start — The starting value of the interval. Int32, Int64, UInt32, UInt64, Float32, Float64, DateTime or Date.
• end — The ending value of the interval. Int32, Int64, UInt32, UInt64, Float32, Float64, DateTime or Date.

Note

Arguments must be of the same data type. Otherwise, an exception will be thrown.

Returned value

• Total length of union of all ranges (segments on numeric axis). Depending on the type of the argument, the return value may be UInt64 or Float64 type.

Examples

1. Input table:

┌─id─┬─start─┬─end─┐
│ a  │   1.1 │ 2.9 │
│ a  │   2.5 │ 3.2 │
│ a  │     4 │   5 │
└────┴───────┴─────┘

In this example, the arguments of the Float32 type are used. The function returns a value of the Float64 type.

Result is the sum of lengths of intervals [1.1, 3.2] (union of [1.1, 2.9] and [2.5, 3.2]) and [4, 5]

Query:

SELECT id, intervalLengthSum(start, end), toTypeName(intervalLengthSum(start, end)) FROM fl_interval GROUP BY id ORDER BY id;

Result:

┌─id─┬─intervalLengthSum(start, end)─┬─toTypeName(intervalLengthSum(start, end))─┐
│ a  │                           3.1 │ Float64                                   │
└────┴───────────────────────────────┴───────────────────────────────────────────┘

2. Input table:

┌─id─┬───────────────start─┬─────────────────end─┐
│ a  │ 2020-01-01 01:12:30 │ 2020-01-01 02:10:10 │
│ a  │ 2020-01-01 02:05:30 │ 2020-01-01 02:50:31 │
│ a  │ 2020-01-01 03:11:22 │ 2020-01-01 03:23:31 │
└────┴─────────────────────┴─────────────────────┘

In this example, the arguments of the DateTime type are used. The function returns a value in seconds.

Query:

SELECT id, intervalLengthSum(start, end), toTypeName(intervalLengthSum(start, end)) FROM dt_interval GROUP BY id ORDER BY id;

Result:

┌─id─┬─intervalLengthSum(start, end)─┬─toTypeName(intervalLengthSum(start, end))─┐
│ a  │                          6610 │ UInt64                                    │
└────┴───────────────────────────────┴───────────────────────────────────────────┘

3. Input table:

┌─id─┬──────start─┬────────end─┐
│ a  │ 2020-01-01 │ 2020-01-04 │
│ a  │ 2020-01-12 │ 2020-01-18 │
└────┴────────────┴────────────┘

In this example, the arguments of the Date type are used. The function returns a value in days.

Query:

SELECT id, intervalLengthSum(start, end), toTypeName(intervalLengthSum(start, end)) FROM date_interval GROUP BY id ORDER BY id;

Result:

┌─id─┬─intervalLengthSum(start, end)─┬─toTypeName(intervalLengthSum(start, end))─┐
│ a  │                             9 │ UInt64                                    │
└────┴───────────────────────────────┴───────────────────────────────────────────┘

intervalLengthSum

Introduced in: v21.7

Takes multiple numeric ranges and calculates the total length when all overlapping parts are combined into a single unified range.

Note

Arguments must be of the same data type. Otherwise, an exception will be thrown.

Syntax

intervalLengthSum(start, end)

Arguments

• start — The starting value of the interval. (U)Int32/64 or Float* or DateTime or Date
• end — The ending value of the interval. (U)Int32/64 or Float* or DateTime or Date

Returned value

Returns the total length of union of all ranges (segments on numeric axis). Depending on the type of the argument, the return value may be UInt64 or Float64 type. UInt64 or Float64

Examples

Float32 example

CREATE TABLE fl_interval (id String, start Float32, end Float32) ENGINE = Memory;
INSERT INTO fl_interval VALUES ('a', 1.1, 2.9), ('a', 2.5, 3.2), ('a', 4, 5);

SELECT id, intervalLengthSum(start, end), toTypeName(intervalLengthSum(start, end)) FROM fl_interval GROUP BY id ORDER BY id;

┌─id─┬─intervalLengthSum(start, end)─┬─toTypeName(intervalLengthSum(start, end))─┐
│ a  │                           3.1 │ Float64                                   │
└────┴───────────────────────────────┴───────────────────────────────────────────┘

DateTime example

CREATE TABLE dt_interval (id String, start DateTime, end DateTime) ENGINE = Memory;
INSERT INTO dt_interval VALUES ('a', '2020-01-01 01:12:30', '2020-01-01 02:10:10'), ('a', '2020-01-01 02:05:30', '2020-01-01 02:50:31'), ('a', '2020-01-01 03:11:22', '2020-01-01 03:23:31');

SELECT id, intervalLengthSum(start, end), toTypeName(intervalLengthSum(start, end)) FROM dt_interval GROUP BY id ORDER BY id;

┌─id─┬─intervalLengthSum(start, end)─┬─toTypeName(intervalLengthSum(start, end))─┐
│ a  │                          6610 │ UInt64                                    │
└────┴───────────────────────────────┴───────────────────────────────────────────┘

Date example

CREATE TABLE date_interval (id String, start Date, end Date) ENGINE = Memory;
INSERT INTO date_interval VALUES ('a', '2020-01-01', '2020-01-04'), ('a', '2020-01-12', '2020-01-18');

SELECT id, intervalLengthSum(start, end), toTypeName(intervalLengthSum(start, end)) FROM date_interval GROUP BY id ORDER BY id;

┌─id─┬─intervalLengthSum(start, end)─┬─toTypeName(intervalLengthSum(start, end))─┐
│ a  │                             9 │ UInt64                                    │
└────┴───────────────────────────────┴───────────────────────────────────────────┘