Autres fonctions

SELECT fqdn()

┌─FQDN()────────────────────────┐
│ clickhouse.us-east-2.internal │
└───────────────────────────────┘

SELECT MACNumToString(149809441867716) AS mac_address;

┌─mac_address───────┐
│ 88:40:3A:91:07:C4 │
└───────────────────┘

SELECT MACStringToNum('01:02:03:04:05:06') AS mac_numeric;

1108152157446

SELECT MACStringToOUI('00:50:56:12:34:56') AS oui;

20566

CREATE USER u1;
EXECUTE AS u1 SELECT currentUser(), authenticatedUser();
DROP USER u1;

┌─currentUser()─┬─authenticatedUser()─┐
│ u1            │ default             │
└───────────────┴─────────────────────┘

CREATE TABLE hits (EventTime DateTime('UTC')) ENGINE = Memory;

-- One row per page view, spread over the hours of a day.
INSERT INTO hits
SELECT toDateTime('2026-01-01 00:00:00', 'UTC') + toIntervalHour(hour)
FROM (
    SELECT
        number AS hour,
        [293, 181, 115, 85, 69, 78, 113, 171, 278, 391, 458, 494, 510, 523, 540, 528, 539, 524, 506, 521, 522, 542, 494, 400][number + 1] AS count
    FROM numbers(24)
)
ARRAY JOIN range(count);

SELECT
toHour(EventTime) AS h,
count() AS c,
bar(c, 0, 600, 20) AS bar
FROM hits
GROUP BY h
ORDER BY h ASC

┌──h─┬───c─┬─bar────────────────┐
│  0 │ 293 │ █████████▊         │
│  1 │ 181 │ ██████             │
│  2 │ 115 │ ███▊               │
│  3 │  85 │ ██▊                │
│  4 │  69 │ ██▎                │
│  5 │  78 │ ██▌                │
│  6 │ 113 │ ███▊               │
│  7 │ 171 │ █████▋             │
│  8 │ 278 │ █████████▎         │
│  9 │ 391 │ █████████████      │
│ 10 │ 458 │ ███████████████▎   │
│ 11 │ 494 │ ████████████████▍  │
│ 12 │ 510 │ █████████████████  │
│ 13 │ 523 │ █████████████████▍ │
│ 14 │ 540 │ ██████████████████ │
│ 15 │ 528 │ █████████████████▌ │
│ 16 │ 539 │ █████████████████▉ │
│ 17 │ 524 │ █████████████████▍ │
│ 18 │ 506 │ ████████████████▊  │
│ 19 │ 521 │ █████████████████▎ │
│ 20 │ 522 │ █████████████████▍ │
│ 21 │ 542 │ ██████████████████ │
│ 22 │ 494 │ ████████████████▍  │
│ 23 │ 400 │ █████████████▎     │
└────┴─────┴────────────────────┘

SELECT blockNumber()
FROM
(
    SELECT *
    FROM system.numbers
    LIMIT 10
) SETTINGS max_block_size = 2

┌─blockNumber()─┐
│             6 │
│             6 │
│             7 │
│             7 │
│             8 │
│             8 │
│             9 │
│             9 │
│            10 │
│            10 │
└───────────────┘

SELECT blockSerializedSize(maxState(1)) AS x;

┌─x─┐
│ 2 │
└───┘

SELECT blockSize()
FROM system.numbers LIMIT 5

┌─blockSize()─┐
│           5 │
│           5 │
│           5 │
│           5 │
│           5 │
└─────────────┘

SELECT buildId()

┌─buildId()────────────────────────────────┐
│ B49BA4BC500E5E850F832BEC918885516B22FC0E │
└──────────────────────────────────────────┘

SELECT byteSize('string')

┌─byteSize('string')─┐
│                 14 │
└────────────────────┘

SELECT byteSize(NULL, 1, 0.3, '')

┌─byteSize(NULL, 1, 0.3, '')─┐
│                         18 │
└────────────────────────────┘

SELECT colorOKLABToSRGB((0.4466, 0.0991, 0.44)) AS rgb;

┌─rgb──────────────────────┐
│ (198.07056923258935,0,0) │
└──────────────────────────┘

WITH colorOKLABToSRGB((0.7, 0.1, 0.54)) AS t
SELECT tuple(toUInt8(t.1), toUInt8(t.2), toUInt8(t.3)) AS RGB;

┌─RGB───────┐
│ (255,0,0) │
└───────────┘

SELECT colorOKLCHToSRGB((0.6, 0.12, 40)) AS rgb;

┌─rgb───────────────────────────────────────────────────────┐
│ (186.02058688365264,100.68677189684993,71.67819977081575) │
└───────────────────────────────────────────────────────────┘

WITH colorOKLCHToSRGB((0.6, 0.12, 40)) AS t
SELECT tuple(toUInt8(t.1), toUInt8(t.2), toUInt8(t.3)) AS RGB;

┌─RGB──────────┐
│ (186,100,71) │
└──────────────┘

SELECT colorSRGBToOKLAB((128, 64, 32), 2.2) AS lab;

┌─lab──────────────────────────────────────────────────────────┐
│ (0.4436238384931984,0.07266246769242975,0.07500108778529994) │
└──────────────────────────────────────────────────────────────┘

SELECT colorSRGBToOKLCH((128, 64, 32), 2.2) AS lch;

┌─lch───────────────────────────────────────────────────────┐
│ (0.4436238384931984,0.1044269954567863,45.90734548193018) │
└───────────────────────────────────────────────────────────┘

SELECT connectionId();

┌─connectionId()─┐
│              0 │
└────────────────┘

SELECT countDigits(toDecimal32(1, 9)), countDigits(toDecimal32(-1, 9)),
       countDigits(toDecimal64(1, 18)), countDigits(toDecimal64(-1, 18)),
       countDigits(toDecimal128(1, 38)), countDigits(toDecimal128(-1, 38));

┌─countDigits(toDecimal32(1, 9))─┬─countDigits(toDecimal32(-1, 9))─┬─countDigits(toDecimal64(1, 18))─┬─countDigits(toDecimal64(-1, 18))─┬─countDigits(toDecimal128(1, 38))─┬─countDigits(toDecimal128(-1, 38))─┐
│                             10 │                              10 │                              19 │                               19 │                               39 │                                39 │
└────────────────────────────────┴─────────────────────────────────┴─────────────────────────────────┴──────────────────────────────────┴──────────────────────────────────┴───────────────────────────────────┘

SELECT currentDatabase()

┌─currentDatabase()─┐
│ default           │
└───────────────────┘

SELECT CURRENT_DATABASE

┌─CURRENT_DATABASE─┐
│ default          │
└──────────────────┘

SELECT currentHandler()

SELECT currentProfiles();

┌─currentProfiles()─┐
│ ['default']       │
└───────────────────┘

SELECT currentQueryID();

┌─currentQueryID()─────────────────────┐
│ 1280d0e8-1a08-4524-be6e-77975bb68e7d │
└──────────────────────────────────────┘

SELECT currentRequestURL()

SELECT currentRoles();

┌─currentRoles()─┐
│ []             │
└────────────────┘

SELECT currentSchemas(true)

┌─currentSchemas(true)─┐
│ ['default']          │
└──────────────────────┘

SELECT currentUser()

┌─currentUser()─┐
│ default       │
└───────────────┘

SELECT CURRENT_USER

┌─CURRENT_USER─┐
│ default      │
└──────────────┘

SELECT defaultProfiles();

┌─defaultProfiles()─┐
│ ['default']       │
└───────────────────┘

SELECT defaultRoles();

┌─defaultRoles()─┐
│ []             │
└────────────────┘

SELECT defaultValueOfArgumentType(CAST(1 AS Int8));

┌─defaultValueOfArgumentType(CAST(1, 'Int8'))─┐
│                                           0 │
└─────────────────────────────────────────────┘

SELECT defaultValueOfArgumentType(CAST(1 AS Nullable(Int8)));

┌─defaultValueOfArgumentType(CAST(1, 'Nullable(Int8)'))─┐
│                                                  ᴺᵁᴸᴸ │
└───────────────────────────────────────────────────────┘

SELECT defaultValueOfTypeName('Int8');

┌─defaultValueOfTypeName('Int8')─┐
│                              0 │
└────────────────────────────────┘

SELECT defaultValueOfTypeName('Nullable(Int8)');

┌─defaultValueOfTypeName('Nullable(Int8)')─┐
│                                     ᴺᵁᴸᴸ │
└──────────────────────────────────────────┘

SELECT digits(1234567890, 7)

7890

SELECT digits(1234567890, 7, 2)

78

SELECT digits(1234567890, -3)

890

SELECT digits(1234567890, 3, -2)

345678

SELECT digits(1234567890, 11)

0

SELECT displayName();

┌─displayName()─┐
│ production    │
└───────────────┘

SELECT dumpColumnStructure(CAST('2018-01-01 01:02:03', 'DateTime'));

┌─dumpColumnStructure(CAST('2018-01-01 01:02:03', 'DateTime'))─┐
│ DateTime, Const(size = 1, UInt32(size = 1))                  │
└──────────────────────────────────────────────────────────────┘

SELECT enabledProfiles();

┌─enabledProfiles()─┐
│ ['default']       │
└───────────────────┘

SELECT enabledRoles();

┌─enabledRoles()─┐
│ []             │
└────────────────┘

SELECT errorCodeToName(252);

┌─errorCodeToName(252)─┐
│ TOO_MANY_PARTS       │
└──────────────────────┘

INSERT INTO FUNCTION file('a.txt', 'RawBLOB') SELECT 'Hello' SETTINGS engine_file_truncate_on_insert = 1;
INSERT INTO FUNCTION file('b.txt', 'RawBLOB') SELECT 'World!' SETTINGS engine_file_truncate_on_insert = 1;

CREATE TABLE data (a String, b String) ENGINE = Memory;
INSERT INTO data SELECT file('a.txt'), file('b.txt');

SELECT * FROM data;

┌─a─────┬─b──────┐
│ Hello │ World! │
└───────┴────────┘

SELECT formatReadableSize(filesystemAvailable()) AS "Available space";

┌─Available space─┐
│ 30.75 GiB       │
└─────────────────┘

SELECT formatReadableSize(filesystemCapacity()) AS "Capacity";

┌─Capacity──┐
│ 39.32 GiB │
└───────────┘

SELECT formatReadableSize(filesystemUnreserved()) AS "Free space";

┌─Free space─┐
│ 32.39 GiB  │
└────────────┘

SELECT finalizeAggregation(arrayReduce('maxState', [1, 2, 3]));

┌─finalizeAggregation(arrayReduce('maxState', [1, 2, 3]))─┐
│                                                       3 │
└─────────────────────────────────────────────────────────┘

SET allow_deprecated_error_prone_window_functions = 1;
WITH initializeAggregation('sumState', number) AS one_row_sum_state
SELECT
    number,
    finalizeAggregation(one_row_sum_state) AS one_row_sum,
    runningAccumulate(one_row_sum_state) AS cumulative_sum
FROM numbers(5);

┌─number─┬─one_row_sum─┬─cumulative_sum─┐
│      0 │           0 │              0 │
│      1 │           1 │              1 │
│      2 │           2 │              3 │
│      3 │           3 │              6 │
│      4 │           4 │             10 │
└────────┴─────────────┴────────────────┘

SELECT flipCoordinates((1.0, 2.0));

(2,1)

SELECT flipCoordinates([(1.0, 2.0), (3.0, 4.0)]);

[(2,1),(4,3)]

SELECT flipCoordinates([[(1.0, 2.0), (3.0, 4.0)], [(5.0, 6.0), (7.0, 8.0)]]);

[[(2,1),(4,3)],[(6,5),(8,7)]]

SELECT flipCoordinates(readWkt('POINT(10 20)'));

(20,10)

SELECT flipCoordinates(readWkt('POLYGON((0 0, 5 0, 5 5, 0 5, 0 0))'));

[[(0,0),(0,5),(5,5),(5,0),(0,0)]]

SELECT formatQuery('select a,    b FRom tab WHERE a > 3 and  b < 3');

SELECT\n    a,\n    b\nFROM tab\nWHERE (a > 3) AND (b < 3)

SELECT formatQueryFromJSON(parseQueryToJSON('SELECT a, b FROM t WHERE x > 1'));

┌─formatQueryFromJSON(parseQueryToJSON('SELECT a, b FROM t WHERE x > 1'))─┐
│ SELECT a, b FROM t WHERE x > 1                                          │
└─────────────────────────────────────────────────────────────────────────┘

SELECT formatQueryFromJSON(parseQueryToJSON('SELECT a FROM t'), 'SELECT /* comment */ a FROM t');

┌─formatQueryFromJSON(parseQueryToJSON('SELECT a FROM t'), 'SELECT /* comment */ a FROM t')─┐
│ SELECT /* comment */ a FROM t                                                             │
└───────────────────────────────────────────────────────────────────────────────────────────┘

SELECT formatQuery('select a,    b FRom tab WHERE a > 3 and  b < 3');

SELECT\n    a,\n    b\nFROM tab\nWHERE (a > 3) AND (b < 3)

SELECT formatQuerySingleLine('select a,    b FRom tab WHERE a > 3 and  b < 3');

SELECT a, b FROM tab WHERE (a > 3) AND (b < 3)

SELECT formatQuerySingleLine('select a,    b FRom tab WHERE a > 3 and  b < 3');

SELECT a, b FROM tab WHERE (a > 3) AND (b < 3)

SELECT
    arrayJoin([1, 1024, 1024*1024, 192851925]) AS filesize_bytes,
    formatReadableDecimalSize(filesize_bytes) AS filesize

┌─filesize_bytes─┬─filesize──┐
│              1 │ 1.00 B    │
│           1024 │ 1.02 KB   │
│        1048576 │ 1.05 MB   │
│      192851925 │ 192.85 MB │
└────────────────┴───────────┘

SELECT
    formatReadableDecimalSize(192851925, 0) AS no_decimals,
    formatReadableDecimalSize(192851925, 4) AS four_decimals

┌─no_decimals─┬─four_decimals─┐
│ 193 MB      │ 192.8519 MB   │
└─────────────┴───────────────┘

SELECT
    arrayJoin([1024, 1234 * 1000, (4567 * 1000) * 1000, 98765432101234]) AS number,
    formatReadableQuantity(number) AS number_for_humans

┌─────────number─┬─number_for_humans─┐
│           1024 │ 1.02 thousand     │
│        1234000 │ 1.23 million      │
│     4567000000 │ 4.57 billion      │
│ 98765432101234 │ 98.77 trillion    │
└────────────────┴───────────────────┘

SELECT
    formatReadableQuantity(98765432101234, 0) AS no_decimals,
    formatReadableQuantity(98765432101234, 4) AS four_decimals

┌─no_decimals─┬─four_decimals────┐
│ 99 trillion │ 98.7654 trillion │
└─────────────┴──────────────────┘

SELECT
    arrayJoin([1, 1024, 1024*1024, 192851925]) AS filesize_bytes,
    formatReadableSize(filesize_bytes) AS filesize

┌─filesize_bytes─┬─filesize───┐
│              1 │ 1.00 B     │
│           1024 │ 1.00 KiB   │
│        1048576 │ 1.00 MiB   │
│      192851925 │ 183.92 MiB │
└────────────────┴────────────┘

SELECT
    formatReadableSize(192851925, 0) AS no_decimals,
    formatReadableSize(192851925, 4) AS four_decimals

┌─no_decimals─┬─four_decimals─┐
│ 184 MiB     │ 183.9179 MiB  │
└─────────────┴───────────────┘

SELECT
    arrayJoin([100, 12345, 432546534]) AS elapsed,
    formatReadableTimeDelta(elapsed) AS time_delta

┌───elapsed─┬─time_delta──────────────────────────────────────────────────────┐
│       100 │ 1 minute and 40 seconds                                         │
│     12345 │ 3 hours, 25 minutes and 45 seconds                              │
│ 432546534 │ 13 years, 8 months, 17 days, 7 hours, 48 minutes and 54 seconds │
└───────────┴─────────────────────────────────────────────────────────────────┘

SELECT
    arrayJoin([100, 12345, 432546534]) AS elapsed,
    formatReadableTimeDelta(elapsed, 'minutes') AS time_delta

┌───elapsed─┬─time_delta─────────────────────┐
│       100 │ 1 minute and 40 seconds        │
│     12345 │ 205 minutes and 45 seconds     │
│ 432546534 │ 7209108 minutes and 54 seconds │
└───────────┴────────────────────────────────┘

SELECT formatReadableTimeDelta(INTERVAL 12345 SECOND) AS time_delta

┌─time_delta─────────────────────────┐
│ 3 hours, 25 minutes and 45 seconds │
└────────────────────────────────────┘