Утилиты

Утилиты — это функции для работы с данными различных типов, которые сложно отнести к конкретной категории. Их описания собраны в этом разделе.

coalesce()

Описание

Возвращает первое значение, отличное от NULL, из списка значений аргументов.
Использование

coalesce(argument1[, argument2, ...])

Если в единственном аргументе значение NULL, то возвращается NULL.

Посмотреть пример 
SELECT
    coalesce(NULL, 'Tengri', NULL) AS result_1,
    coalesce(NULL, '', NULL) AS result_2,
    coalesce('Tengri') AS result_3,
    coalesce(NULL) AS result_4;
+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 |
+----------+----------+----------+----------+
| Tengri   |          | Tengri   | null     |
+----------+----------+----------+----------+

generate_series()

Описание

Создает список значений в диапазоне между start и stop.
Использование

generate_series([start,] stop[, step])

Параметры start и stop обрабатываются как "включительные".
Значение по умолчанию для start — 0, для step — 1.

Посмотреть пример 
SELECT
    generate_series(10) AS stop,
    generate_series(5, 10) AS start_stop,
    generate_series(5, 10, 2) AS start_stop_step;
+--------------------------+----------------+-----------------+
| stop                     | start_stop     | start_stop_step |
+--------------------------+----------------+-----------------+
| {0,1,2,3,4,5,6,7,8,9,10} | {5,6,7,8,9,10} | {5,7,9}         |
+--------------------------+----------------+-----------------+

hash()

Описание

Возвращает хеш данных из argument в виде числа.
Использование

hash(argument)
Посмотреть пример 
SELECT
    hash('Tengri') AS hash;
+----------------------+
|         hash         |
+----------------------+
| 15418814193266442000 |
+----------------------+

read_csv()

Описание

Читает файл .csv и записывает прочитанные данные в таблицу.
Использование 

read_csv(filepath[,
    skip = 5,
    header = FALSE,
    all_varchar = TRUE]
    )

Читает файл .csv по указанному пути filepath и записывает прочитанные данные в таблицу. Пропуск начальных строк, приведение типов и настройки заголовков колонок могут регулироваться опциональными параметрами.

Для загрузки файлов .csv с локального компьютера воспользуйтесь Мастером загрузки.

Параметры

  • skip — опциональный параметр, указывающий количество начальных строк файла, которые следует пропустить при чтении (включая первую строку!)

  • header — опциональный параметр, указывающий на то, нужно ли интерпретировать первую строку таблицы как строку заголовков колонок (по умолчанию — TRUE)

  • all_varchar — опциональный параметр; если TRUE, то приведение типов будет отключено и тип всех колонок будет текстовым (по умолчанию — FALSE)

Посмотреть примеры

В примерах используется файл strava_activities.csv из сценария Анализ геоданных спортивных трекеров.

Выведем первые пять строк из файла без использования опциональных параметров функции:

SELECT *
    FROM read_csv('abogdanov/734b6e098011_strava_activities.csv')
    LIMIT 5
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+--------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+---------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------+
| Activity ID | Activity Date             | Activity Name         | Activity Type | Activity Description | Elapsed Time | Distance | Max Heart Rate | Relative Effort | Commute | Activity Private Note | Activity Gear        | Filename                 | Athlete Weight | Bike Weight | Elapsed Time_1 | Moving Time | Distance_1 | Max Speed | Average Speed | Elevation Gain | Elevation Loss | Elevation Low | Elevation High | Max Grade | Average Grade | Average Positive Grade | Average Negative Grade | Max Cadence | Average Cadence | Max Heart Rate_1 | Average Heart Rate | Max Watts | Average Watts | Calories | Max Temperature | Average Temperature | Relative Effort_1 | Total Work | Number of Runs | Uphill Time | Downhill Time | Other Time | Perceived Exertion | Type | Start Time | Weighted Average Power | Power Count | Prefer Perceived Exertion | Perceived Relative Effort | Commute_1 | Total Weight Lifted | From Upload | Grade Adjusted Distance | Weather Observation Time | Weather Condition | Weather Temperature | Apparent Temperature | Dewpoint | Humidity | Weather Pressure | Wind Speed | Wind Gust | Wind Bearing | Precipitation Intensity | Sunrise Time | Sunset Time | Moon Phase | Bike    | Gear | Precipitation Probability | Precipitation Type | Cloud Cover | Weather Visibility | UV Index | Weather Ozone | Jump Count | Total Grit | Average Flow | Flagged | Average Elapsed Speed | Dirt Distance | Newly Explored Distance | Newly Explored Dirt Distance | Activity Count | Total Steps | Carbon Saved | Pool Length | Training Load | Intensity | Average Grade Adjusted Pace | Timer Time | Total Cycles | Recovery | With Pet | Competition | Long Run | For a Cause | Media |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+--------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+---------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------+
| 187782986   | Aug 31, 2014, 10:54:43 AM | Из дома к родителям   | Ride          | null                 | 2462         | 19.41    | null           | null            | false   | null                  | Merida S-Presso 900d | activities/187782986.gpx | 83             | 11          | 2462           | 2286        | 19415.4    | 13.5      | null          | 158.2          | null           | 118.2         | 206            | 12        | 0.3           | null                   | null                   | null        | null            | null             | null               | null      | 192           | null     | null            | null                | null              | null       | null           | null        | null          | null       | null               | null | null       | null                   | null        | null                      | null                      | 0         | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | 1512511 | null | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null  |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+--------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+---------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------+
| 187840092   | Aug 31, 2014, 12:48:49 PM | Послеобеденный заезд  | Ride          | null                 | 2396         | 15.9     | null           | null            | false   | null                  | null                 | activities/187840092.gpx | 83             | null        | 2396           | 1920        | 15906.6    | 13.4      | null          | 93.9           | null           | 122.5         | 214.6          | 8.8       | -0.5          | null                   | null                   | null        | null            | null             | null               | null      | 178           | null     | null            | null                | null              | null       | null           | null        | null          | null       | null               | null | null       | null                   | null        | null                      | null                      | 0         | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | null    | null | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null  |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+--------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+---------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------+
| 187844596   | Aug 31, 2014, 1:29:23 PM  | Послеобеденный заезд  | Ride          | null                 | 103          | 0.7      | null           | null            | false   | null                  | null                 | activities/187844596.gpx | 83             | null        | 103            | 103         | 708.4      | 8.4       | null          | 0              | null           | 127.4         | 136            | 3.7       | 1.2           | null                   | null                   | null        | null            | null             | null               | null      | 172           | null     | null            | null                | null              | null       | null           | null        | null          | null       | null               | null | null       | null                   | null        | null                      | null                      | 0         | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | null    | null | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null  |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+--------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+---------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------+
| 187850580   | Aug 31, 2014, 1:37:59 PM  | Крылатское малый круг | Ride          | null                 | 561          | 4.41     | null           | null            | false   | null                  | null                 | activities/187850580.gpx | 83             | null        | 561            | 561         | 4414.1     | 13.6      | null          | 46.7           | null           | 137.6         | 179.4          | 10.4      | -0.1          | null                   | null                   | null        | null            | null             | null               | null      | 192           | 120      | null            | null                | null              | null       | null           | null        | null          | null       | null               | null | null       | null                   | null        | null                      | null                      | 0         | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | null    | null | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null  |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+--------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+---------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------+
| 187867604   | Aug 31, 2014, 1:59:58 PM  | Послеобеденный заезд  | Ride          | null                 | 886          | 5.03     | null           | null            | false   | null                  | null                 | activities/187867604.gpx | 83             | null        | 886            | 820         | 5036.3     | 10.6      | null          | 34.6           | null           | 123           | 156.7          | 7.3       | 0.3           | null                   | null                   | null        | null            | null             | null               | null      | 127           | null     | null            | null                | null              | null       | null           | null        | null          | null       | null               | null | null       | null                   | null        | null                      | null                      | 0         | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | null    | null | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null  |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+--------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+---------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------+

Выведем первые пять строк из файла .xlsx, игнорируя строку заголовков:

SELECT *
    FROM read_csv('abogdanov/734b6e098011_strava_activities.csv',
        header = FALSE
        )
    LIMIT 5
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+----------+-----------------------+----------------------+--------------------------+----------------+-------------+--------------+-------------+----------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+----------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-----------------+------------+----------------+-------------+---------------+------------+--------------------+----------+------------+------------------------+-------------+---------------------------+---------------------------+----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+-----------+----------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+----------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+----------+
| column00    | column01                  | column02              | column03      | column04             | column05     | column06 | column07       | column08        | column09 | column10              | column11             | column12                 | column13       | column14    | column15     | column16    | column17 | column18  | column19      | column20       | column21       | column22      | column23       | column24  | column25      | column26               | column27               | column28    | column29        | column30       | column31           | column32  | column33      | column34 | column35        | column36            | column37        | column38   | column39       | column40    | column41      | column42   | column43           | column44 | column45   | column46               | column47    | column48                  | column49                  | column50 | column51            | column52    | column53                | column54                 | column55          | column56            | column57             | column58 | column59 | column60         | column61   | column62  | column63     | column64                | column65     | column66    | column67   | column68  | column69 | column70                  | column71           | column72    | column73           | column74 | column75      | column76   | column77   | column78     | column79 | column80              | column81      | column82                | column83                     | column84       | column85    | column86     | column87    | column88      | column89  | column90                    | column91   | column92     | column93 | column94 | column95    | column96 | column97    | column98 |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+----------+-----------------------+----------------------+--------------------------+----------------+-------------+--------------+-------------+----------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+----------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-----------------+------------+----------------+-------------+---------------+------------+--------------------+----------+------------+------------------------+-------------+---------------------------+---------------------------+----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+-----------+----------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+----------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+----------+
| Activity ID | Activity Date             | Activity Name         | Activity Type | Activity Description | Elapsed Time | Distance | Max Heart Rate | Relative Effort | Commute  | Activity Private Note | Activity Gear        | Filename                 | Athlete Weight | Bike Weight | Elapsed Time | Moving Time | Distance | Max Speed | Average Speed | Elevation Gain | Elevation Loss | Elevation Low | Elevation High | Max Grade | Average Grade | Average Positive Grade | Average Negative Grade | Max Cadence | Average Cadence | Max Heart Rate | Average Heart Rate | Max Watts | Average Watts | Calories | Max Temperature | Average Temperature | Relative Effort | Total Work | Number of Runs | Uphill Time | Downhill Time | Other Time | Perceived Exertion | Type     | Start Time | Weighted Average Power | Power Count | Prefer Perceived Exertion | Perceived Relative Effort | Commute  | Total Weight Lifted | From Upload | Grade Adjusted Distance | Weather Observation Time | Weather Condition | Weather Temperature | Apparent Temperature | Dewpoint | Humidity | Weather Pressure | Wind Speed | Wind Gust | Wind Bearing | Precipitation Intensity | Sunrise Time | Sunset Time | Moon Phase | Bike      | Gear     | Precipitation Probability | Precipitation Type | Cloud Cover | Weather Visibility | UV Index | Weather Ozone | Jump Count | Total Grit | Average Flow | Flagged  | Average Elapsed Speed | Dirt Distance | Newly Explored Distance | Newly Explored Dirt Distance | Activity Count | Total Steps | Carbon Saved | Pool Length | Training Load | Intensity | Average Grade Adjusted Pace | Timer Time | Total Cycles | Recovery | With Pet | Competition | Long Run | For a Cause | Media    |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+----------+-----------------------+----------------------+--------------------------+----------------+-------------+--------------+-------------+----------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+----------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-----------------+------------+----------------+-------------+---------------+------------+--------------------+----------+------------+------------------------+-------------+---------------------------+---------------------------+----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+-----------+----------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+----------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+----------+
| 187782986   | Aug 31, 2014, 10:54:43 AM | Из дома к родителям   | Ride          | null                 | 2462         | 19.41    | null           | null            | false    | null                  | Merida S-Presso 900d | activities/187782986.gpx | 83.0           | 11.0        | 2462.0       | 2286.0      | 19415.4  | 13.5      | null          | 158.2          | null           | 118.2         | 206.0          | 12.0      | 0.3           | null                   | null                   | null        | null            | null           | null               | null      | 192.0         | null     | null            | null                | null            | null       | null           | null        | null          | null       | null               | null     | null       | null                   | null        | null                      | null                      | 0.0      | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | 1512511.0 | null     | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null     | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null     |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+----------+-----------------------+----------------------+--------------------------+----------------+-------------+--------------+-------------+----------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+----------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-----------------+------------+----------------+-------------+---------------+------------+--------------------+----------+------------+------------------------+-------------+---------------------------+---------------------------+----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+-----------+----------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+----------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+----------+
| 187840092   | Aug 31, 2014, 12:48:49 PM | Послеобеденный заезд  | Ride          | null                 | 2396         | 15.90    | null           | null            | false    | null                  | null                 | activities/187840092.gpx | 83.0           | null        | 2396.0       | 1920.0      | 15906.6  | 13.4      | null          | 93.9           | null           | 122.5         | 214.6          | 8.8       | -0.5          | null                   | null                   | null        | null            | null           | null               | null      | 178.0         | null     | null            | null                | null            | null       | null           | null        | null          | null       | null               | null     | null       | null                   | null        | null                      | null                      | 0.0      | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | null      | null     | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null     | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null     |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+----------+-----------------------+----------------------+--------------------------+----------------+-------------+--------------+-------------+----------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+----------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-----------------+------------+----------------+-------------+---------------+------------+--------------------+----------+------------+------------------------+-------------+---------------------------+---------------------------+----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+-----------+----------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+----------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+----------+
| 187844596   | Aug 31, 2014, 1:29:23 PM  | Послеобеденный заезд  | Ride          | null                 | 103          | 0.70     | null           | null            | false    | null                  | null                 | activities/187844596.gpx | 83.0           | null        | 103.0        | 103.0       | 708.4    | 8.4       | null          | 0.0            | null           | 127.4         | 136.0          | 3.7       | 1.2           | null                   | null                   | null        | null            | null           | null               | null      | 172.0         | null     | null            | null                | null            | null       | null           | null        | null          | null       | null               | null     | null       | null                   | null        | null                      | null                      | 0.0      | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | null      | null     | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null     | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null     |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+----------+-----------------------+----------------------+--------------------------+----------------+-------------+--------------+-------------+----------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+----------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-----------------+------------+----------------+-------------+---------------+------------+--------------------+----------+------------+------------------------+-------------+---------------------------+---------------------------+----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+-----------+----------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+----------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+----------+
| 187850580   | Aug 31, 2014, 1:37:59 PM  | Крылатское малый круг | Ride          | null                 | 561          | 4.41     | null           | null            | false    | null                  | null                 | activities/187850580.gpx | 83.0           | null        | 561.0        | 561.0       | 4414.1   | 13.6      | null          | 46.7           | null           | 137.6         | 179.4          | 10.4      | -0.1          | null                   | null                   | null        | null            | null           | null               | null      | 192.0         | 120.0    | null            | null                | null            | null       | null           | null        | null          | null       | null               | null     | null       | null                   | null        | null                      | null                      | 0.0      | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | null      | null     | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null     | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null     |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+----------+-----------------------+----------------------+--------------------------+----------------+-------------+--------------+-------------+----------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+----------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-----------------+------------+----------------+-------------+---------------+------------+--------------------+----------+------------+------------------------+-------------+---------------------------+---------------------------+----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+-----------+----------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+----------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+----------+

read_parquet()

Описание

Читает файл .parquet и записывает прочитанные данные в таблицу.
Использование

read_parquet(filepath)

Читает файл .parquet по указанному пути filepath и записывает прочитанные данные в таблицу.

Для загрузки файлов .parquet с локального компьютера воспользуйтесь Мастером загрузки.

Посмотреть пример

Загрузим через Мастер загрузки один из файлов с данными отсюда и прочитаем его:

SELECT *
    FROM read_parquet('abogdanov/05e4fddda22d_green_tripdata_2025-01.parquet')
    LIMIT 5
+----------+----------------------+-----------------------+--------------------+------------+--------------+--------------+-----------------+---------------+-------------+-------+---------+------------+--------------+-----------+-----------------------+--------------+--------------+-----------+----------------------+--------------------+
| VendorID | lpep_pickup_datetime | lpep_dropoff_datetime | store_and_fwd_flag | RatecodeID | PULocationID | DOLocationID | passenger_count | trip_distance | fare_amount | extra | mta_tax | tip_amount | tolls_amount | ehail_fee | improvement_surcharge | total_amount | payment_type | trip_type | congestion_surcharge | cbd_congestion_fee |
+----------+----------------------+-----------------------+--------------------+------------+--------------+--------------+-----------------+---------------+-------------+-------+---------+------------+--------------+-----------+-----------------------+--------------+--------------+-----------+----------------------+--------------------+
| 2        | 2025-01-01 00:03:01  | 2025-01-01 00:17:12   | N                  | 1          | 75           | 235          | 1               | 5.93          | 24.7        | 1     | 0.5     | 6.8        | 0            | null      | 1                     | 34           | 1            | 1         | 0                    | 0                  |
+----------+----------------------+-----------------------+--------------------+------------+--------------+--------------+-----------------+---------------+-------------+-------+---------+------------+--------------+-----------+-----------------------+--------------+--------------+-----------+----------------------+--------------------+
| 2        | 2025-01-01 00:19:59  | 2025-01-01 00:25:52   | N                  | 1          | 166          | 75           | 1               | 1.32          | 8.6         | 1     | 0.5     | 0          | 0            | null      | 1                     | 11.1         | 2            | 1         | 0                    | 0                  |
+----------+----------------------+-----------------------+--------------------+------------+--------------+--------------+-----------------+---------------+-------------+-------+---------+------------+--------------+-----------+-----------------------+--------------+--------------+-----------+----------------------+--------------------+
| 2        | 2025-01-01 00:05:29  | 2025-01-01 00:07:21   | N                  | 5          | 171          | 73           | 1               | 0.41          | 25.55       | 0     | 0       | 0          | 0            | null      | 1                     | 26.55        | 2            | 2         | 0                    | 0                  |
+----------+----------------------+-----------------------+--------------------+------------+--------------+--------------+-----------------+---------------+-------------+-------+---------+------------+--------------+-----------+-----------------------+--------------+--------------+-----------+----------------------+--------------------+
| 2        | 2025-01-01 00:52:24  | 2025-01-01 01:07:52   | N                  | 1          | 74           | 223          | 1               | 4.12          | 21.2        | 1     | 0.5     | 6.13       | 6.94         | null      | 1                     | 36.77        | 1            | 1         | 0                    | 0                  |
+----------+----------------------+-----------------------+--------------------+------------+--------------+--------------+-----------------+---------------+-------------+-------+---------+------------+--------------+-----------+-----------------------+--------------+--------------+-----------+----------------------+--------------------+
| 2        | 2025-01-01 00:25:05  | 2025-01-01 01:01:10   | N                  | 1          | 66           | 158          | 1               | 4.71          | 33.8        | 1     | 0.5     | 7.81       | 0            | null      | 1                     | 46.86        | 1            | 1         | 2.75                 | 0                  |
+----------+----------------------+-----------------------+--------------------+------------+--------------+--------------+-----------------+---------------+-------------+-------+---------+------------+--------------+-----------+-----------------------+--------------+--------------+-----------+----------------------+--------------------+

read_xlsx()

Описание

Читает файл .xlsx и записывает прочитанные данные в таблицу.
Использование 

read_xlsx(filepath[,
    sheet = 'Лист1',
    range = 'A5:E100',
    stop_at_empty = TRUE,
    header = TRUE,
    all_varchar = TRUE]
    )

Читает файл .xlsx по указанному пути filepath и записывает прочитанные данные в таблицу. Если указан диапазон для чтения или лист файла, то выбирает только указанные данные. Приведение типов и настройки заголовков колонок могут регулироваться опциональными параметрами.

Для загрузки файлов .xlsx с локального компьютера воспользуйтесь Мастером загрузки.

Параметры

  • sheet — опциональный параметр, указывающий имя листа файла .xlsx для чтения. По умолчанию берется первый лист.

  • range — опциональный параметр, указывающий диапазон клеток таблицы файла .xlsx для чтения. Диапазон указывается от левой верхней клетки до правой нижней, например: range = 'A5:E100'.
    Возможно нестрогое указание диапазона, например:

    • range = 'A:E100' — выберет клетки от первой в колонке A до E100

    • range = 'B:E' — выберет клетки от первой в колонке B до последней в колонке E (в этом случае рекомендуется использовать ограничение stop_at_empty = TRUE, см. пример ниже)

  • stop_at_empty — опциональный параметр, указывающий на то, нужно ли прервать чтение на первой пустой строке таблицы

  • header — опциональный параметр, указывающий на то, нужно ли интерпретировать первую строку таблицы как строку заголовков колонок (по умолчанию — TRUE, если в первой строке нет пустых клеток)

  • all_varchar — опциональный параметр; если TRUE, то приведение типов будет отключено и тип всех колонок будет текстовым (по умолчанию — FALSE)

Посмотреть примеры

В примерах используется файл instagram_example_result_1.xlsx из сценария Анализ данных из постов Instagram*.

Выведем первые пять строк из файла без использования опциональных параметров функции:

SELECT *
    FROM read_xlsx('abogdanov/b95f3e020363_instagram_example_result_1.xlsx')
    LIMIT 5
+-------------------+-------+----------+----------+--------------+------------+--------------+
| artist_name       | songs | ig_posts | comments | tagged_users | likescount | repliescount |
+-------------------+-------+----------+----------+--------------+------------+--------------+
| "telfarglobal"    | 9     | 136      | 985      | 120          | 634        | 156          |
+-------------------+-------+----------+----------+--------------+------------+--------------+
| "everlane"        | 1     | 95       | 532      | 6            | 513        | 65           |
+-------------------+-------+----------+----------+--------------+------------+--------------+
| "asos"            | 3     | 93       | 503      | 60           | 583        | 85           |
+-------------------+-------+----------+----------+--------------+------------+--------------+
| "victoriassecret" | 1     | 83       | 686      | 49           | 185        | 16           |
+-------------------+-------+----------+----------+--------------+------------+--------------+
| "summerfridays"   | 2     | 65       | 574      | 48           | 173        | 18           |
+-------------------+-------+----------+----------+--------------+------------+--------------+

Выведем первые пять строк первых четырех колонок из файла .xlsx:

SELECT *
    FROM read_xlsx('abogdanov/b95f3e020363_instagram_example_result_1.xlsx',
        range = 'A1:D6'
        )
+-------------------+-------+----------+----------+
| artist_name       | songs | ig_posts | comments |
+-------------------+-------+----------+----------+
| "telfarglobal"    | 9     | 136      | 985      |
+-------------------+-------+----------+----------+
| "everlane"        | 1     | 95       | 532      |
+-------------------+-------+----------+----------+
| "asos"            | 3     | 93       | 503      |
+-------------------+-------+----------+----------+
| "victoriassecret" | 1     | 83       | 686      |
+-------------------+-------+----------+----------+
| "summerfridays"   | 2     | 65       | 574      |
+-------------------+-------+----------+----------+

Выведем колонки с A по C из файла .xlsx, для этого используем диапазон по именам колонок и параметр stop_at_empty (иначе у диапазона не будет явной правой границы, так как не указана конечная клетка диапазона):

SELECT *
    FROM read_xlsx('abogdanov/b95f3e020363_instagram_example_result_1.xlsx',
        range = 'A:C',
        stop_at_empty = TRUE
        )
+--------------------------------------------------------------------+-------+----------+
| artist_name                                                        | songs | ig_posts |
+--------------------------------------------------------------------+-------+----------+
| "telfarglobal"                                                     | 9     | 136      |
+--------------------------------------------------------------------+-------+----------+
| "everlane"                                                         | 1     | 95       |
+--------------------------------------------------------------------+-------+----------+
| "asos"                                                             | 3     | 93       |
+--------------------------------------------------------------------+-------+----------+
| "victoriassecret"                                                  | 1     | 83       |
+--------------------------------------------------------------------+-------+----------+
| "summerfridays"                                                    | 2     | 65       |
+--------------------------------------------------------------------+-------+----------+
| ...                                                                | ...   | ...      |
+--------------------------------------------------------------------+-------+----------+
226 rows

cast()

Описание

Приводит тип данных к указанному.
Использование

cast(argument AS DATA_TYPE)

Приводит тип данных аргумента к указанному. В случае невозможности приведения выдает ошибку.

См. также try_cast(), Оператор ::.

Посмотреть примеры 

  • SELECT
    cast('1' AS BIGINT)                        AS result_1,
    cast('00:00:00.0' AS TIME)                 AS result_2,
    cast('2025-12-03T00:00:00.0' AS TIMESTAMP) AS result_3;
    +----------+----------+---------------------+
| result_1 | result_2 | result_3            |
+----------+----------+---------------------+
| 1        | 00:00:00 | 2025-12-03 00:00:00 |
+----------+----------+---------------------+

  • SELECT
    cast('Tengri' AS BIGINT); -- error expected
    ERROR: ConversionException: Conversion Error: Could not convert string 'Tengri' to INT64

try_cast()

Описание

Приводит тип данных к указанному.
Использование

try_cast(argument AS DATA_TYPE)

Приводит тип данных аргумента к указанному. В случае невозможности приведения возвращает NULL.

См. также cast(), Оператор ::.

Посмотреть примеры 
SELECT
    try_cast('1' AS BIGINT)                        AS result_1,
    try_cast('00:00:00.0' AS TIME)                 AS result_2,
    try_cast('2025-01-01T00:00:00.0' AS TIMESTAMP) AS result_3,
    try_cast('Tengri' AS BIGINT)                   AS result_4, -- null expected
    try_cast('00:00:00.0' AS TIMESTAMP)            AS result_5; -- null expected
+----------+----------+---------------------+----------+----------+
| result_1 | result_2 | result_3            | result_4 | result_5 |
+----------+----------+---------------------+----------+----------+
| 1        | 00:00:00 | 2025-01-01 00:00:00 | null     | null     |
+----------+----------+---------------------+----------+----------+

unnest()

Описание

Разворачивает списки или структуры из argument в множество отдельных значений.
Использование

unnest(argument) [, recursive := true] [, max_depth := <num>]

Применение функции к списку дает одну строку на каждый элемент списка. Обычные скалярные выражения в том же выражении SELECT повторяются для каждой выводимой строки.

Когда несколько списков разворачиваются в одном выражении SELECT, они разворачиваются каждый в отдельный столбец. Если один список длиннее другого, более короткий список заполняется значениями NULL.

Функция изменяет кардинальность данных.

Параметры

  • recursive := true
    Включает рекурсивный режим. Если этот режим включен (значение true), то функция полностью разворачивает списки, а затем полностью разворачивает вложенные структуры. Это может быть полезно для полного "уплощения" столбцов, которые содержат списки внутри списков или структуры внутри списков. Обратите внимание, что списки внутри структур не разворачиваются.

    Подробнее о параметре на примерах

    Покажем работу этого параметра на двух примерах с одними и теми же данными с включенным параметром и без него:

    SELECT
    unnest([[1, 2, 3], [4, 5]], recursive := true) AS result;
    +--------+
| result |
+--------+
| 1      |
+--------+
| 2      |
+--------+
| 3      |
+--------+
| 4      |
+--------+
| 5      |
+--------+
    SELECT
    unnest([[1, 2, 3], [4, 5]]) AS result;
    +---------+
|  result |
+---------+
| {1,2,3} |
+---------+
| {4,5}   |
+---------+

  • max_depth := <num>
    Параметр max_depth позволяет ограничить максимальную глубину рекурсивного развертывания. Рекурсивный режим автоматически включен, если указана максимальная глубина.

    Подробнее о параметре на примерах

    Покажем работу этого параметра на трех примерах с одними и теми же данными: с глубиной развертывания по умолчанию (1), с глубиной развертывания 2 и с глубиной развертывания 3:

    SELECT
    unnest([[['T', 'e'], ['n', 'g']], [['r', 'i'], []], [['!', '!', '!']]])
AS result;
    +-------------------------+
|          result         |
+-------------------------+
| {['T', 'e'],['n', 'g']} |
+-------------------------+
| {['r', 'i'],[]}         |
+-------------------------+
| {['!', '!', '!']}       |
+-------------------------+
    SELECT
    unnest([[['T', 'e'], ['n', 'g']], [['r', 'i'], []], [['!', '!', '!']]],
    max_depth := 2)
AS result;
    +---------+
|  result |
+---------+
| {T,e}   |
+---------+
| {n,g}   |
+---------+
| {r,i}   |
+---------+
| {}      |
+---------+
| {!,!,!} |
+---------+
    SELECT
    unnest([[['T', 'e'], ['n', 'g']], [['r', 'i'], []], [['!', '!', '!']]],
    max_depth := 3)
AS result;
    +--------+
| result |
+--------+
| T      |
+--------+
| e      |
+--------+
| n      |
+--------+
| g      |
+--------+
| r      |
+--------+
| i      |
+--------+
| !      |
+--------+
| !      |
+--------+
| !      |
+--------+
Посмотреть еще примеры 
SELECT
    unnest([1,2,3])       AS numbers,
    unnest(['a','b','c']) AS letters;
+---------+---------+
| numbers | letters |
+---------+---------+
| 1       | a       |
+---------+---------+
| 2       | b       |
+---------+---------+
| 3       | c       |
+---------+---------+
SELECT
    unnest([1,2,3])   AS numbers,
    unnest(['a','b']) AS letters;
+---------+---------+
| numbers | letters |
+---------+---------+
| 1       | a       |
+---------+---------+
| 2       | b       |
+---------+---------+
| 3       | null    |
+---------+---------+
SELECT
    unnest([{'column_a': 1, 'column_b': 84},
            {'column_a': 100, 'column_b': NULL, 'column_c':22}],
            recursive := true);
+----------+----------+----------+
| column_a | column_b | column_c |
+----------+----------+----------+
| 1        | 84       | null     |
+----------+----------+----------+
| 100      | null     | 22       |
+----------+----------+----------+
SELECT
    unnest([{'column_a': 1, 'column_b': 84},
            {'column_a': 100, 'column_b': NULL, 'column_c':22}])
AS result;
+-----------------------------------------------------+
|                        result                       |
+-----------------------------------------------------+
| {"column_a": 1, "column_b": 84, "column_c": null}   |
+-----------------------------------------------------+
| {"column_a": 100, "column_b": null, "column_c": 22} |
+-----------------------------------------------------+

Оператор ::

Описание

Приводит тип данных к указанному.
Использование

argument::DATA_TYPE

Приводит тип данных аргумента к указанному. В случае невозможности приведения выдает ошибку.

См. также cast(), try_cast().

Посмотреть примеры 

  • SELECT
    '1'::BIGINT                         AS result_1,
    '00:00:00.0'::TIME                  AS result_2,
    '2025-01-01T00:00:00.0'::TIMESTAMP  AS result_3;
    +----------+----------+---------------------+
| result_1 | result_2 | result_3            |
+----------+----------+---------------------+
| 1        | 00:00:00 | 2025-01-01 00:00:00 |
+----------+----------+---------------------+

  • SELECT
    'Tengri'::BIGINT; -- error expected
    ERROR: ConversionException: Conversion Error: Could not convert string 'Tengri' to INT64