Примеры SQL UDF

После ознакомления с SQL пользовательские функции, в следующих разделах приведено множество примеров корректных SQL UDF. Эти UDF подходят для использования как Inline пользовательские функции или Catalog user-defined functions после изменения имени и примеров вызова.

Примеры объединяют различные поддерживаемые операторы. Для получения дополнительной информации см. документацию по конкретным операторам:

• FUNCTION — для общего объявления UDF

• BEGIN и DECLARE — для блоков SQL UDF

• SET — для присвоения значений переменным

• RETURN — для возврата результатов

• CASE и IF — для условной логики

• LOOP, REPEAT и WHILE — для циклов

• ITERATE и LEAVE — для управления потоком выполнения

Inline и catalog UDF

Следующий раздел показывает различия в использовании inline и catalog UDF на примере простого SQL UDF. Тот же шаблон применяется ко всем последующим разделам.

Очень простой SQL UDF, который возвращает статическое значение без входных параметров:

FUNCTION answer()
RETURNS BIGINT
RETURN 42

Полный пример этого UDF как inline UDF и его использование в конкатенации строк с приведением типов:

WITH
  FUNCTION answer()
  RETURNS BIGINT
  RETURN 42
SELECT 'The answer is ' || CAST(answer() as varchar);
-- The answer is 42

Если каталог example поддерживает хранение UDF в схеме default, вы можете использовать следующее:

CREATE FUNCTION example.default.answer()
  RETURNS BIGINT
  RETURN 42;

После сохранения UDF в каталоге, вы можете вызывать его многократно без повторного определения:

SELECT example.default.answer() + 1; -- 43
SELECT 'The answer is ' || CAST(example.default.answer() as varchar); -- The answer is 42

Альтернативно, вы можете настроить SQL PATH в Config properties на каталог и схему, поддерживающие хранение UDF:

sql.default-function-catalog=example
sql.default-function-schema=default
sql.path=example.default

Теперь вы можете управлять UDF без указания полного пути:

CREATE FUNCTION answer()
  RETURNS BIGINT
  RETURN 42;

Вызов UDF также работает без полного пути:

SELECT answer() + 5; -- 47

Примеры объявлений

Результат вызова UDF answer() всегда одинаковый, поэтому вы можете объявить её как deterministic и добавить некоторую дополнительную информацию:

FUNCTION answer()
LANGUAGE SQL
DETERMINISTIC
RETURNS BIGINT
COMMENT 'Provide the answer to the question about life, the universe, and everything.'
RETURN 42

Комментарий и другая информация о UDF отображаются в выводе SHOW FUNCTIONS.

Простой UDF, который возвращает приветствие для входной строки fullname, конкатенируя две строки и входное значение:

FUNCTION hello(fullname VARCHAR)
RETURNS VARCHAR
RETURN 'Hello, ' || fullname || '!'

Ниже приведён пример вызова:

SELECT hello('Jane Doe'); -- Hello, Jane Doe!

Первый пример UDF, который использует несколько операторов в блоке BEGIN. Он вычисляет результат умножения входного целого числа на 99. Тип данных bigint используется для всех переменных и значений. Значение целого числа 99 приводится к bigint в присвоении значения по умолчанию для переменной x:

FUNCTION times_ninety_nine(a bigint)
RETURNS bigint
BEGIN
  DECLARE x bigint DEFAULT CAST(99 AS bigint);
  RETURN x * a;
END

Ниже приведён пример вызова:

SELECT times_ninety_nine(CAST(2 as bigint)); -- 198

Условные конструкции

Первый пример условного управления потоком в SQL UDF с использованием оператора CASE. Простое входное значение типа bigint сравнивается с несколькими значениями:

FUNCTION simple_case(a bigint)
RETURNS varchar
BEGIN
  CASE a
    WHEN 0 THEN RETURN 'zero';
    WHEN 1 THEN RETURN 'one';
    WHEN 10 THEN RETURN 'ten';
    WHEN 20 THEN RETURN 'twenty';
    ELSE RETURN 'other';
  END CASE;
  RETURN NULL;
END

Ниже приведены несколько примеров вызова с результатами и пояснениями:

SELECT simple_case(0); -- zero
SELECT simple_case(1); -- one
SELECT simple_case(-1); -- other (из предложения else)
SELECT simple_case(10); -- ten
SELECT simple_case(11); -- other (из предложения else)
SELECT simple_case(20); -- twenty
SELECT simple_case(100); -- other (из предложения else)
SELECT simple_case(null); -- null .. but really??

Второй пример SQL UDF с оператором CASE, на этот раз с двумя параметрами, демонстрирующий важность порядка условий:

FUNCTION search_case(a bigint, b bigint)
RETURNS varchar
BEGIN
  CASE
    WHEN a = 0 THEN RETURN 'zero';
    WHEN b = 1 THEN RETURN 'one';
    WHEN a = DECIMAL '10.0' THEN RETURN 'ten';
    WHEN b = 20.0E0 THEN RETURN 'twenty';
    ELSE RETURN 'other';
  END CASE;
  RETURN NULL;
END

Ниже приведены несколько примеров вызова с результатами и пояснениями:

SELECT search_case(0,0); -- zero
SELECT search_case(1,1); -- one
SELECT search_case(0,1); -- zero (не one, поскольку вторая проверка не достигается)
SELECT search_case(10,1); -- one (не ten, поскольку третья проверка не достигается)
SELECT search_case(10,2); -- ten
SELECT search_case(10,20); -- ten (не twenty)
SELECT search_case(0,20); -- zero (не twenty)
SELECT search_case(3,20); -- twenty
SELECT search_case(3,21); -- other
SELECT simple_case(null,null); -- null .. but really??

Пример Fibonacci

Этот SQL UDF вычисляет n-е значение в ряду Фибоначчи, где каждое число является суммой двух предыдущих. Два начальных значения устанавливаются в 1 как значения по умолчанию для a и b. UDF использует условие IF, чтобы вернуть 1 для всех входных значений 2 или меньше. Затем блок WHILE начинает вычисление каждого числа в последовательности, начиная с a=1 и b=1, и выполняется до достижения позиции n. На каждой итерации он задаёт a и b как два предыдущих значения, чтобы вычислить сумму, и в конце возвращает её. Обратите внимание, что выполнение UDF занимает всё больше времени при увеличении значения n, и результат является deterministic:

FUNCTION fib(n bigint)
RETURNS bigint
BEGIN
  DECLARE a, b bigint DEFAULT 1;
  DECLARE c bigint;
  IF n <= 2 THEN
    RETURN 1;
  END IF;
  WHILE n > 2 DO
    SET n = n - 1;
    SET c = a + b;
    SET a = b;
    SET b = c;
  END WHILE;
  RETURN c;
END

Ниже приведены несколько примеров вызова с результатами и пояснениями:

SELECT fib(-1); -- 1
SELECT fib(0); -- 1
SELECT fib(1); -- 1
SELECT fib(2); -- 1
SELECT fib(3); -- 2
SELECT fib(4); -- 3
SELECT fib(5); -- 5
SELECT fib(6); -- 8
SELECT fib(7); -- 13
SELECT fib(8); -- 21

Метки и циклы

Этот SQL UDF использует метку top для именования блока WHILE, а затем управляет потоком с помощью условных операторов, ITERATE и LEAVE. Для значений a=1 и a=2 в первых двух итерациях цикла вызов ITERATE переносит выполнение к top до того, как b увеличивается. Затем b увеличивается для значений a=3, a=4, a=5, a=6 и a=7, в результате чего получается b=5. Затем вызов LEAVE приводит к выходу из блока до того, как a увеличится до 10, и, следовательно, результат UDF равен 5:

FUNCTION labels()
RETURNS bigint
BEGIN
  DECLARE a, b int DEFAULT 0;
  top: WHILE a < 10 DO
    SET a = a + 1;
    IF a < 3 THEN
      ITERATE top;
    END IF;
    SET b = b + 1;
    IF a > 6 THEN
      LEAVE top;
    END IF;
  END WHILE;
  RETURN b;
END

Этот SQL UDF реализует вычисление n в степени p с помощью повторного умножения и отслеживания количества выполненных умножений. Обратите внимание, что этот SQL UDF не возвращает корректное значение 0 для p=0, поскольку блок top просто пропускается, и возвращается значение n. Такое же некорректное поведение наблюдается для отрицательных значений p:

FUNCTION power(n int, p int)
RETURNS int
  BEGIN
    DECLARE r int DEFAULT n;
    top: LOOP
      IF p <= 1 THEN
        LEAVE top;
      END IF;
      SET r = r * n;
      SET p = p - 1;
    END LOOP;
    RETURN r;
  END

Ниже приведены несколько примеров вызова с результатами и пояснениями:

SELECT power(2, 2); -- 4
SELECT power(2, 8); -- 256
SELECT power(3, 3); -- 256
SELECT power(3, 0); -- 3, что неверно
SELECT power(3, -2); -- 3, что неверно

Этот SQL UDF возвращает 7 как результат увеличения b в цикле от a=3 до a=10:

FUNCTION test_repeat_continue()
RETURNS bigint
BEGIN
  DECLARE a int DEFAULT 0;
  DECLARE b int DEFAULT 0;
  top: REPEAT
    SET a = a + 1;
    IF a <= 3 THEN
      ITERATE top;
    END IF;
    SET b = b + 1;
  UNTIL a >= 10
  END REPEAT;
  RETURN b;
END

Этот SQL UDF возвращает 2 и демонстрирует, что метки могут повторяться, и использование метки внутри блока относится к метке этого блока:

FUNCTION test()
RETURNS int
BEGIN
  DECLARE r int DEFAULT 0;
  abc: LOOP
    SET r = r + 1;
    LEAVE abc;
  END LOOP;
  abc: LOOP
    SET r = r + 1;
    LEAVE abc;
  END LOOP;
  RETURN r;
END

SQL UDF и встроенные функции

Эта SQL UDF показывает, что в UDF можно использовать разные типы данных и встроенные функции, такие как length() и cardinality(). Два вложенных блока BEGIN также показывают, что имена переменных локальны внутри этих блоков x, но к глобальной переменной r из блока верхнего уровня можно обращаться из вложенных блоков:

FUNCTION test()
RETURNS bigint
BEGIN
  DECLARE r bigint DEFAULT 0;
  BEGIN
    DECLARE x varchar DEFAULT 'hello';
    SET r = r + length(x);
  END;
  BEGIN
    DECLARE x array(int) DEFAULT array[1, 2, 3];
    SET r = r + cardinality(x);
  END;
  RETURN r;
END

Пример необязательного параметра

UDF могут вызывать другие UDF и другие функции. Полная сигнатура UDF состоит из имени UDF и параметров и определяет, какая именно UDF будет использоваться. Можно объявить несколько UDF с одинаковым именем, но с разным количеством аргументов или разными типами аргументов. Один из примеров использования — реализация необязательного параметра.

Следующая SQL UDF усекает строку до указанной длины, включая три точки в конце вывода:

FUNCTION dots(input varchar, length integer)
RETURNS varchar
BEGIN
  IF length(input) > length THEN
    RETURN substring(input, 1, length-3) || '...';
  END IF;
  RETURN input;
END;

Ниже приведены примеры вызовов и вывод:

SELECT dots('A long string that will be shortened',15);
-- A long strin...
SELECT	dots('A short string',15);
-- A short string

Если нужно предоставить UDF с тем же именем, но без параметра длины, можно создать другую UDF, которая вызывает предыдущую UDF:

FUNCTION dots(input varchar)
RETURNS varchar
RETURN dots(input, 15);

Теперь можно использовать обе UDF. Если параметр длины опущен, используется значение по умолчанию из второго объявления.

SELECT dots('A long string that will be shortened',15);
-- A long strin...
SELECT dots('A long string that will be shortened');
-- A long strin...
SELECT dots('A long string that will be shortened',20);
-- A long string tha...

Пример разбора строк дат

Этот пример SQL UDF разбирает строку даты типа VARCHAR в TIMESTAMP WITH TIME ZONE. Строки дат часто представлены по стандарту ISO 8601, например 2023-12-01, 2023-12-01T23. Строки дат также часто представлены в формате YYYYmmdd и YYYYmmddHH, например 20230101 и 2023010123. Таблицы Hive могут использовать этот формат для представления дневных и часовых партиций, например /day=20230101, /hour=2023010123.

Эта UDF разбирает строки дат по принципу best effort и может использоваться как замена функциям обработки строк дат, таким как date, date_parse, from_iso8601_date и from_iso8601_timestamp.

Обратите внимание, что UDF по умолчанию задает значение времени 00:00:00.000, а часовой пояс — как часовой пояс сеанса:

FUNCTION from_date_string(date_string VARCHAR)
RETURNS TIMESTAMP WITH TIME ZONE
BEGIN
  IF date_string like '%-%' THEN -- ISO 8601
    RETURN from_iso8601_timestamp(date_string);
  ELSEIF length(date_string) = 8 THEN -- YYYYmmdd
      RETURN date_parse(date_string, '%Y%m%d');
  ELSEIF length(date_string) = 10 THEN -- YYYYmmddHH
      RETURN date_parse(date_string, '%Y%m%d%H');
  END IF;
  RETURN NULL;
END

Ниже приведены несколько примеров вызова с результатом и пояснением:

SELECT from_date_string('2023-01-01'); -- 2023-01-01 00:00:00.000 UTC (использует формат ISO 8601)
SELECT from_date_string('2023-01-01T23'); -- 2023-01-01 23:00:00.000 UTC (использует формат ISO 8601)
SELECT from_date_string('2023-01-01T23:23:23'); -- 2023-01-01 23:23:23.000 UTC (использует формат ISO 8601)
SELECT from_date_string('20230101'); -- 2023-01-01 00:00:00.000 UTC (использует формат YYYYmmdd)
SELECT from_date_string('2023010123'); -- 2023-01-01 23:00:00.000 UTC (использует формат YYYYmmddHH)
SELECT from_date_string(NULL); -- NULL (обрабатывает строку NULL)
SELECT from_date_string('abc'); -- NULL (не соответствует ни одному формату)

Человекочитаемые дни

Trino включает встроенную функцию human_readable_seconds(), которая форматирует количество секунд в строку:

SELECT human_readable_seconds(134823);
-- 1 day, 13 hours, 27 minutes, 3 seconds

Пример SQL UDF hrd форматирует количество дней в человекочитаемый текст, который сообщает приблизительное количество лет и месяцев:

FUNCTION hrd(d integer)
RETURNS VARCHAR
BEGIN
    DECLARE answer varchar default 'About ';
    DECLARE years real;
    DECLARE months real;
    SET years = truncate(d/365);
    IF years > 0 then
        SET answer = answer || format('%1.0f', years) || ' year';
    END IF;
    IF years > 1 THEN
        SET answer = answer || 's';
    END IF;
    SET d = d - cast( years AS integer) * 365 ;
    SET months = truncate(d / 30);
    IF months > 0 and years > 0 THEN
        SET answer = answer || ' and ';
    END IF;
    IF months > 0 THEN
        set answer = answer || format('%1.0f', months) || ' month';
    END IF;
    IF months > 1 THEN
        SET answer = answer || 's';
    END IF;
    IF years < 1 and months < 1 THEN
        SET answer = 'Less than 1 month';
    END IF;
    RETURN answer;
END;

Следующие примеры показывают вывод для диапазона значений меньше месяца, меньше года и разных больших значений:

SELECT hrd(10); -- Less than 1 month
SELECT hrd(95); -- About 3 months
SELECT hrd(400); -- About 1 year and 1 month
SELECT hrd(369); -- About 1 year
SELECT hrd(800); -- About 2 years and 2 months
SELECT hrd(1100); -- About 3 years
SELECT hrd(5000); -- About 13 years and 8 months

Улучшения SQL UDF могут включать следующие изменения:

• Учитывать, что один месяц равен 30.4375 дня.

• Учитывать, что один год равен 365.25 дня.

• Добавить недели в вывод.

• Расширить поддержку на десятилетия, века и тысячелетия.

Усечение длинных строк

Этот пример SQL UDF strtrunc усекает строки длиннее 60 символов, оставляя первые 30 и последние 25 символов и вырезая лишние символы в середине:

FUNCTION strtrunc(input VARCHAR)
RETURNS VARCHAR
RETURN
    CASE WHEN length(input) > 60
    THEN substr(input, 1, 30) || ' ... ' || substr(input, length(input) - 25)
    ELSE input
    END;

Предыдущее объявление очень компактное и состоит только из одного сложного оператора с выражением CASE и несколькими вызовами функций. Поэтому оно может определить всю логику в предложении RETURN.

Следующий оператор показывает ту же возможность внутри самой SQL UDF. Обратите внимание на дублирующийся RETURN внутри и снаружи оператора CASE и на обязательный END CASE;. Второй оператор RETURN необходим, потому что SQL UDF должна заканчиваться оператором RETURN. В результате предложение ELSE можно опустить:

FUNCTION strtrunc(input VARCHAR)
RETURNS VARCHAR
BEGIN
    CASE WHEN length(input) > 60
    THEN
        RETURN substr(input, 1, 30) || ' ... ' || substr(input, length(input) - 25);
    ELSE
        RETURN input;
    END CASE;
    RETURN input;
END;

Следующий пример переходит от CASE к оператору IF и избегает дублирующегося RETURN:

FUNCTION strtrunc(input VARCHAR)
RETURNS VARCHAR
BEGIN
    IF length(input) > 60 THEN
        RETURN substr(input, 1, 30) || ' ... ' || substr(input, length(input) - 25);
    END IF;
    RETURN input;
END;

Все предыдущие примеры создают одинаковый вывод. Ниже приведен пример запроса, который генерирует длинные строки для усечения:

WITH
data AS (
    SELECT substring('strtrunc truncates strings longer than 60 characters,
     leaving the prefix and suffix visible', 1, s.num) AS value
    FROM table(sequence(start=>40, stop=>80, step=>5)) AS s(num)
)
SELECT
    data.value
  , strtrunc(data.value) AS truncated
FROM data
ORDER BY data.value;

Предыдущий запрос создает следующий вывод со всеми вариантами SQL UDF:

                                      value                                       |                           truncated
----------------------------------------------------------------------------------+---------------------------------------------------------------
 strtrunc truncates strings longer than 6                                         | strtrunc truncates strings longer than 6
 strtrunc truncates strings longer than 60 cha                                    | strtrunc truncates strings longer than 60 cha
 strtrunc truncates strings longer than 60 characte                               | strtrunc truncates strings longer than 60 characte
 strtrunc truncates strings longer than 60 characters, l                          | strtrunc truncates strings longer than 60 characters, l
 strtrunc truncates strings longer than 60 characters, leavin                     | strtrunc truncates strings longer than 60 characters, leavin
 strtrunc truncates strings longer than 60 characters, leaving the                | strtrunc truncates strings lon ... 60 characters, leaving the
 strtrunc truncates strings longer than 60 characters, leaving the pref           | strtrunc truncates strings lon ... aracters, leaving the pref
 strtrunc truncates strings longer than 60 characters, leaving the prefix an      | strtrunc truncates strings lon ... ers, leaving the prefix an
 strtrunc truncates strings longer than 60 characters, leaving the prefix and suf | strtrunc truncates strings lon ... leaving the prefix and suf

Возможное улучшение — добавить параметры для общей длины.

Форматирование байтов

Trino включает встроенную функцию format_number(). Однако она использует единицы, которые плохо подходят для байтов. Следующая SQL UDF format_data_size может форматировать большие значения в байтах в человекочитаемую строку:

FUNCTION format_data_size(input BIGINT)
RETURNS VARCHAR
  BEGIN
    DECLARE value DOUBLE DEFAULT CAST(input AS DOUBLE);
    DECLARE result BIGINT;
    DECLARE base INT DEFAULT 1024;
    DECLARE unit VARCHAR DEFAULT 'B';
    DECLARE format VARCHAR;
    IF abs(value) >= base THEN
      SET value = value / base;
      SET unit = 'kB';
    END IF;
    IF abs(value) >= base THEN
      SET value = value / base;
      SET unit = 'MB';
    END IF;
    IF abs(value) >= base THEN
      SET value = value / base;
      SET unit = 'GB';
    END IF;
    IF abs(value) >= base THEN
      SET value = value / base;
      SET unit = 'TB';
    END IF;
    IF abs(value) >= base THEN
      SET value = value / base;
      SET unit = 'PB';
    END IF;
    IF abs(value) >= base THEN
      SET value = value / base;
      SET unit = 'EB';
    END IF;
    IF abs(value) >= base THEN
      SET value = value / base;
      SET unit = 'ZB';
    END IF;
    IF abs(value) >= base THEN
      SET value = value / base;
      SET unit = 'YB';
    END IF;
    IF abs(value) < 10 THEN
      SET format = '%.2f';
    ELSEIF abs(value) < 100 THEN
      SET format = '%.1f';
    ELSE
      SET format = '%.0f';
    END IF;
    RETURN format(format, value) || unit;
  END;

Ниже приведен запрос, который показывает, как она форматирует широкий диапазон значений:

WITH
data AS (
    SELECT CAST(pow(10, s.p) AS BIGINT) AS num
    FROM table(sequence(start=>1, stop=>18)) AS s(p)
    UNION ALL
    SELECT -CAST(pow(10, s.p) AS BIGINT) AS num
    FROM table(sequence(start=>1, stop=>18)) AS s(p)
)
SELECT
    data.num
  , format_data_size(data.num) AS formatted
FROM data
ORDER BY data.num;

Предыдущий запрос создает следующий вывод:

         num          | formatted
----------------------+-----------
 -1000000000000000000 | -888PB
  -100000000000000000 | -88.8PB
   -10000000000000000 | -8.88PB
    -1000000000000000 | -909TB
     -100000000000000 | -90.9TB
      -10000000000000 | -9.09TB
       -1000000000000 | -931GB
        -100000000000 | -93.1GB
         -10000000000 | -9.31GB
          -1000000000 | -954MB
           -100000000 | -95.4MB
            -10000000 | -9.54MB
             -1000000 | -977kB
              -100000 | -97.7kB
               -10000 | -9.77kB
                -1000 | -1000B
                 -100 | -100B
                  -10 | -10.0B
                    0 | 0.00B
                   10 | 10.0B
                  100 | 100B
                 1000 | 1000B
                10000 | 9.77kB
               100000 | 97.7kB
              1000000 | 977kB
             10000000 | 9.54MB
            100000000 | 95.4MB
           1000000000 | 954MB
          10000000000 | 9.31GB
         100000000000 | 93.1GB
        1000000000000 | 931GB
       10000000000000 | 9.09TB
      100000000000000 | 90.9TB
     1000000000000000 | 909TB
    10000000000000000 | 8.88PB
   100000000000000000 | 88.8PB
  1000000000000000000 | 888PB

Диаграммы

В Trino уже есть встроенная функция цвета bar(), но она использует escape-коды ANSI для вывода цветов и поэтому подходит только для отображения результатов в терминале. Следующий пример показывает похожую SQL UDF, которая использует только символы ASCII:

FUNCTION ascii_bar(value DOUBLE)
RETURNS VARCHAR
BEGIN
  DECLARE max_width DOUBLE DEFAULT 40.0;
  RETURN array_join(
    repeat('█',
        greatest(0, CAST(floor(max_width * value) AS integer) - 1)), '')
        || ARRAY[' ', '▏', '▎', '▍', '▌', '▋', '▊', '▉', '█']
        [cast((value % (cast(1 as double) / max_width)) * max_width * 8 + 1 as int)];
END;

Ее можно использовать для визуализации значения:

WITH
data AS (
    SELECT
        cast(s.num as double) / 100.0 AS x,
        sin(cast(s.num as double) / 100.0) AS y
    FROM table(sequence(start=>0, stop=>314, step=>10)) AS s(num)
)
SELECT
    data.x,
    round(data.y, 4) AS y,
    ascii_bar(data.y) AS chart
FROM data
ORDER BY data.x;

Предыдущий запрос создает следующий вывод:

  x  |   y    |                  chart
-----+--------+-----------------------------------------
 0.0 |    0.0 |
 0.1 | 0.0998 | ███
 0.2 | 0.1987 | ███████
 0.3 | 0.2955 | ██████████▉
 0.4 | 0.3894 | ██████████████▋
 0.5 | 0.4794 | ██████████████████▏
 0.6 | 0.5646 | █████████████████████▋
 0.7 | 0.6442 | ████████████████████████▊
 0.8 | 0.7174 | ███████████████████████████▊
 0.9 | 0.7833 | ██████████████████████████████▍
 1.0 | 0.8415 | ████████████████████████████████▋
 1.1 | 0.8912 | ██████████████████████████████████▋
 1.2 |  0.932 | ████████████████████████████████████▎
 1.3 | 0.9636 | █████████████████████████████████████▌
 1.4 | 0.9854 | ██████████████████████████████████████▍
 1.5 | 0.9975 | ██████████████████████████████████████▉
 1.6 | 0.9996 | ███████████████████████████████████████
 1.7 | 0.9917 | ██████████████████████████████████████▋
 1.8 | 0.9738 | ██████████████████████████████████████
 1.9 | 0.9463 | ████████████████████████████████████▉
 2.0 | 0.9093 | ███████████████████████████████████▍
 2.1 | 0.8632 | █████████████████████████████████▌
 2.2 | 0.8085 | ███████████████████████████████▍
 2.3 | 0.7457 | ████████████████████████████▉
 2.4 | 0.6755 | ██████████████████████████
 2.5 | 0.5985 | ███████████████████████
 2.6 | 0.5155 | ███████████████████▋
 2.7 | 0.4274 | ████████████████▏
 2.8 |  0.335 | ████████████▍
 2.9 | 0.2392 | ████████▋
 3.0 | 0.1411 | ████▋
 3.1 | 0.0416 | ▋

Также можно рисовать более компактные диаграммы. Ниже приведена SQL UDF, которая рисует вертикальные столбцы:

FUNCTION vertical_bar(value DOUBLE)
RETURNS VARCHAR
RETURN ARRAY[' ', '▁', '▂', '▃', '▄', '▅', '▆', '▇', '█'][cast(value * 8 + 1 as int)];

Ее можно использовать для отображения распределения значений в одном столбце:

WITH
measurements(sensor_id, recorded_at, value) AS (
    VALUES
        ('A', date '2023-01-01', 5.0)
      , ('A', date '2023-01-03', 7.0)
      , ('A', date '2023-01-04', 15.0)
      , ('A', date '2023-01-05', 14.0)
      , ('A', date '2023-01-08', 10.0)
      , ('A', date '2023-01-09', 1.0)
      , ('A', date '2023-01-10', 7.0)
      , ('A', date '2023-01-11', 8.0)
      , ('B', date '2023-01-03', 2.0)
      , ('B', date '2023-01-04', 3.0)
      , ('B', date '2023-01-05', 2.5)
      , ('B', date '2023-01-07', 2.75)
      , ('B', date '2023-01-09', 4.0)
      , ('B', date '2023-01-10', 1.5)
      , ('B', date '2023-01-11', 1.0)
),
days AS (
    SELECT date_add('day', s.num, date '2023-01-01') AS day
    -- аргументы табличной функции должны быть константами, но диапазон можно вычислить
    -- с помощью: SELECT date_diff('day', max(recorded_at), min(recorded_at)) FROM measurements
    FROM table(sequence(start=>0, stop=>10)) AS s(num)
),
sensors(id) AS (VALUES ('A'), ('B')),
normalized AS (
    SELECT
        sensors.id AS sensor_id,
        days.day,
        value,
        value / max(value) OVER (PARTITION BY sensor_id) AS normalized
    FROM days
    CROSS JOIN sensors
    LEFT JOIN measurements m ON day = recorded_at AND m.sensor_id = sensors.id
)
SELECT
    sensor_id,
    min(day) AS start,
    max(day) AS stop,
    count(value) AS num_values,
    min(value) AS min_value,
    max(value) AS max_value,
    avg(value) AS avg_value,
    array_join(array_agg(coalesce(vertical_bar(normalized), ' ') ORDER BY day),
     '') AS distribution
FROM normalized
WHERE sensor_id IS NOT NULL
GROUP BY sensor_id
ORDER BY sensor_id;

Предыдущий запрос создает следующий вывод:

 sensor_id |   start    |    stop    | num_values | min_value | max_value | avg_value | distribution
-----------+------------+------------+------------+-----------+-----------+-----------+--------------
 A         | 2023-01-01 | 2023-01-11 |          8 |      1.00 |     15.00 |      8.38 | ▃ ▄█▇  ▅▁▄▄
 B         | 2023-01-01 | 2023-01-11 |          7 |      1.00 |      4.00 |      2.39 |   ▄▆▅ ▆ █▃▂

Top-N

В Trino уже есть встроенная агрегатная функция approx_most_frequent(), которая может вычислять наиболее часто встречающиеся значения. Она возвращает map, где ключи — это значения, а значения map — количество вхождений. Map не упорядочены, поэтому при отображении элементы могут меняться местами при последующих запусках того же запроса, а читателям все равно нужно сравнивать все частоты, чтобы найти одно наиболее частое значение. Ниже приведена SQL UDF, которая возвращает упорядоченные результаты как строку:

FUNCTION format_topn(input map<varchar, bigint>)
RETURNS VARCHAR
NOT DETERMINISTIC
BEGIN
  DECLARE freq_separator VARCHAR DEFAULT '=';
  DECLARE entry_separator VARCHAR DEFAULT ', ';
  RETURN array_join(transform(
    reverse(array_sort(transform(
      transform(
        map_entries(input),
          r -> cast(r AS row(key varchar, value bigint))
      ),
      r -> cast(row(r.value, r.key) AS row(value bigint, key varchar)))
    )),
    r -> r.key || freq_separator || cast(r.value as varchar)),
    entry_separator);
END;

Ниже приведен пример запроса для подсчета сгенерированных строк:

WITH
data AS (
    SELECT lpad('', 3, chr(65+(s.num / 3))) AS value
    FROM table(sequence(start=>1, stop=>10)) AS s(num)
),
aggregated AS (
    SELECT
        array_agg(data.value ORDER BY data.value) AS all_values,
        approx_most_frequent(3, data.value, 1000) AS top3
    FROM data
)
SELECT
    a.all_values,
    a.top3,
    format_topn(a.top3) AS top3_formatted
FROM aggregated a;

Предыдущий запрос создает следующий результат:

                     all_values                     |         top3          |    top3_formatted
----------------------------------------------------+-----------------------+---------------------
 [AAA, AAA, BBB, BBB, BBB, CCC, CCC, CCC, DDD, DDD] | {AAA=2, CCC=3, BBB=3} | CCC=3, BBB=3, AAA=2