- Что нужно знать программисту, чтобы работать с Query
- Синтаксис запросов
- Какие еще действия можно совершить с Query
- Заключение
Функция Query — это лучшая опция, по мнению опытных программистов, в Гугл-таблицах, так как одной ею можно заменить десятки других. Ее используют для получения аналитических данных в бизнесе.
Что нужно знать программисту, чтобы работать с Query
Для работы с функцией Query в Гугл-таблицах у разработчика должны быть опыт и знания написания кода в SQL. Многие новички-программисты сразу пугаются, но бояться не нужно, потому что для работы с ней необходимы простые знания, а не углубленные.
Давайте посмотрим на запросы, которые делают программисты, и как они это делают.
Синтаксис запросов
SQL-запросы — это блоки. Их еще называют кляузами. Следующий список дает представление о синтаксисе запросов:
- select — перечисление строк и столбцов, которые программа выводит на экран после написания запроса;
- where — синтаксис фильтрации. Обычно в него заключают условия, по которым машина должна отфильтровать информацию;
- group by — включает режим группировки. Иными словами, прописывая этот код, вы задаете машине запрос о разделении на группы нужной вам информации;
- pivot — этот запрос помогает создать перекрестные таблицы, где значения таблицы будут использованы в названиях столбцов финальной таблицы;
- order by — заставляет машину сортировать информацию по заданным значениям.
Давайте попробуем написать код, пользуясь частью запросов из синтаксиса SQL. Например, вам нужно из таблицы с названием Pets вытащить имена щенков и расположить их по году рождения.
Пример таблицы с названием Pets:
| ID | Name (имя щенка) | Age (возраст) |
|---|---|---|
| 3 | Лэсси | 3 дня |
| 2 | Лорд | 1 мес. |
| 1 | Рекс | 2 мес. |
| 5 | Банана | 5 мес. |
| 4 | Майя | 12 мес. |
Чтобы расположить их по порядку, вам нужно прописать следующий код:
«SELECT * FROM pets WHERE id>0 ORDER BY age»
Вы получаете следующую таблицу:
| ID | Name (имя щенка) | Age (возраст) |
|---|---|---|
| 1 | Рекс | 2 мес. |
| 2 | Лорд | 1 мес. |
| 3 | Лэсси | 3 дня |
| 4 | Майя | 12 мес. |
| 5 | Банана | 5 мес. |
Внимание! Вы должны выучить простые правила, как прописывать код и стараться не ошибиться в значениях и в синтаксисе, чтобы машина вас поняла. Иначе дальше вы будете путаться.
Давайте изучать дальше синтаксис:
- limit — задавая его, ограничивают количество строк, которое выдаст машина;
- offset — посредством него вы сможете сделать выборку начальных строк, которые машина не должна обрабатывать запросом;
- label — здесь вы запрашиваете названия полей, выводимые таблицей;
- format — задавая его, вы подготавливаете формат выводимых данных: Эксель, Ворд или другие;
- options — запрос позволяет задавать дополнительные характеристики для выборки.
Теперь подробнее разберем, каких операторов содержат запросы типа «where» или «select». Запросы используют логические операторы «or», «and». Но есть и другие. Так, блок «where» содержит:
- contains — проверяет содержание определенных символов в строке. Так, если его использовать в нашей вышеописанной таблице, записав следующим образом: «WHERE «Name» contains ‘Лорд’», то будут возвращены в фильтр все значения из столбца A, в которых встречается Лорд, например Puppie Lord, Lord 2;
- starts with — отфильтровывает таблицу по префиксу. С помощью него тестируют символы в начале строки. Для примера: введете starts with ‘Л’, получите Лорд и Лэсси;
- ends with — после ввода вы получаете конец строки. Для примера: введете ‘Банана’, получите «ends with ‘нана’» или «ends with ‘а’»;
- like — этим запросом вы будете пользоваться часто. С помощью него программист тестирует соответствия строки той информации, которую он задает с использованием символов подстановки. Так, в операторе like вы найдете: «%» означает любое число любых символов в строке; «_» — один любой символ. Например, «where name like ‘Л%’» будет соответствовать строкам ‘Лор’, ‘Лэсс’.
Помимо вышеописанных операторов есть еще скалярные функции. Давайте погрузимся в их изучение.
Какие еще действия можно совершить с Query
Операторы действий
Какое действие производит
year()
Вы получаете год из «даты» или «даты и времени». Пример: year(date ‘1984-03-08’). Вы получите 1984. Возвращает только одно запрашиваемое число: год — год, день — день.
month()
Показывает номер запрашиваемого месяца из «date» или «date-time». Если март является началом отсчета, то будет возвращать 1, потому что функция начинает считать месяцы с нуля. Пример: month(date ‘1984-03-07’) вернет 1. Чтобы функция показала число, соответствующее месяцу, в обычном виде к ее результату добавьте 2, month(date «1984-03-07»)+2 — будет третий месяц.
day()
Вы получаете номер дня в месяце из «даты» или «даты и времени». Например: day(date ‘2020-01-01’). После запроса получите 1.
hour()
Вы получаете номер часа в дне из «даты и времени» или «времени». Например: hour(timeofday ‘22:22:22′). Вы получаете после запроса 22.
minute()
Вы получаете минуты в часе из «даты и времени» или «времени». Например: minute(timeofday ‘20:12:20′). После запроса получите 12. Вы запрашиваете один идентификатор с типом время или дата и время. И получаете число.
second()
Вы получаете секунду в минуте из «даты и времени» или «времени». Например: second(timeofday ‘20:20:23′). После запроса получите 23. Вы запрашиваете один идентификатор с типом время или дата и время. А получаете число.
millisecond()
Показывает номер миллисекунды в секунде из «даты и времени» или «времени». Например: millisecond(timeofday ‘20:20:20.101′). Вы получите после запроса 101. Так как запрашивается один идентификатор с типом время или дата и время, а возвращается число.
quarter()
Начальным значением или точкой отсчета считают 1, поэтому для первого квартала вы получите 1, для второго 2. Пример: quarter(date ‘2020-01-01’). После запроса получаете 1. Так как запрашивается один идентификатор с типом дата или дата/время, а возвращается число.
dayOfWeek()
Вы получаете после запроса номер дня недели в неделе из «даты» или «даты и времени». Началом семидневки считают по европейскому типу: воскресенье. Воскресенье машиной берется за 1, а понедельник за 2, и так до субботы. Например: dayOfWeek(date ‘2020-03-07’). Вы получаете после запроса 7, потому что 3 июля 2020 года — суббота.
now()
Вы получаете текущую дату и время в часовом поясе GTM. Эта функция, что интересно, не требует ввода каких-либо значений. Вы получаете дату и время в формате вашего часового пояса.
dateDiff()
Показывает разницу в сутках между двумя датами. Например: dateDiff(date ‘1980-01-01’ , date ‘1980-01-01). Вы получите после запроса 31.
toDate()
Превращает в дату значение из «даты», или «даты и времени», или «числа». Например: toDate(dateTime‘2020-05-15 20:20:20’). Вы получите после запроса ‘2020-05-15’.
upper()
Трансформирует текст в строке в заглавные буквы. Например: upper(‘foo’) — получится ‘FOO’.
lower()
Трансформирует текст в строке в буквы нижнего регистра. Для примера: upper(‘Car’) — получится ‘car’.
Теперь давайте попробуем запросить данные, объединив информацию из нескольких таблиц с одинаковой конструкцией. Сделать это нам снова поможет функция QUERY. С помощью нее вы создаете запрос на базе нескольких массивов данных.
Важно! Опытные программисты говорят, что при использовании функции в этом деле необходимо придерживаться одинаковой структуры таблиц. Иначе вся работа пойдет насмарку, а в итоге вы получите Error.
Соединяет входящую информацию функция с помощью первого аргумента, который принимает ссылку на диапазон или описание массива.
Если вы хоть раз сталкивались с программированием, то, наверное, знаете, что массив — это виртуальная таблица, которая содержит строки и столбцы.
Программисты записывают таблицу внутрь фигурных скобок — это обязательная процедура, иначе система вашего написания просто не поймет. Опытные разработчики советуют соблюдать такую пунктуацию, как в списке ниже, чтобы не допустить ошибок в коде:
- косая черта в правую сторону, обратный слэш «» — посредством него делят столбцы. Для примера: {2B}, где «2» располагается в левой ячейке таблицы, значение «В» располагается в ячейке справа. Здесь вы видите, как программисты описывают диапазон, куда вносится два столбца и одна строка;
- точка с запятой «;». Ее программисты рекомендуют использовать для написания новой строки кода. Для примера: {2;С}. В этой таблице находится один столбик и две строки, в первой строке вы найдете число «2», во второй строке располагается «A».
Теперь вы знаете, как описать два и более диапазона в одной таблице.
Опытные программисты говорят, что запрос может создаваться по пяти, десяти диапазонам информации, которые могут находиться на различных листах. Программист соединяет их посредством «;» в таблицу так, что пятый массив — продолжение первого, а первый массив — продолжением десятого.
Теперь вы знаете, как вытаскивать информацию из Гугл-таблиц, как пользоваться формулами в Гугл-таблицах.
Программисты посредством функции Query вытягивают информацию из разных вкладок, из иных файлов и проводят с ними дальнейшие манипуляции. В кляузе Query программисты чаще всего используют динамические параметры. Они создают 2 ячейки, посредством коих фильтруют информацию по датам. Также могут фильтровать данные по кампаниям. А еще программисты используют оператор IF в запросе.
Не были подробно рассмотрены кляузы offset и limit. Но, как говорят опытные программисты, эти штуки начинающим не пригодятся.
Важно! Соблюдайте порядок записи в запросе при использовании данной функции. Если в неправильном порядке написать запрос, то вы получите вместо правильного ответа — fatal Error.
Заключение
Теперь вы знаете, как примерно использовать функцию Query, понимаете, что собой представляют операторы и запросы, научились делать выборку из таблиц. При дальнейшем изучении вы поймете, что работа с этой функцией не так сложна и не нужно знать большое количество операторов и других значений, чтобы пользоваться ею.
Чтобы подробней изучить эту функцию и стать грамотным программистом, пройдите курсы программирования от DevEducation.
