Как использовать функцию Query в программировании и таблицах

Содержание:

Функция Query — это лучшая опция, по мнению опытных программистов, в Гугл-таблицах, так как одной ею можно заменить десятки других. Ее используют для получения аналитических данных в бизнесе.

Как использовать функцию Query в программировании и таблицах

Что нужно знать программисту, чтобы работать с Query

Для работы с функцией Query в Гугл-таблицах у разработчика должны быть опыт и знания написания кода в SQL. Многие новички-программисты сразу пугаются, но бояться не нужно, потому что для работы с ней необходимы простые знания, а не углубленные.

Давайте посмотрим на запросы, которые делают программисты, и как они это делают.

Синтаксис запросов

SQL-запросы — это блоки. Их еще называют кляузами. Следующий список дает представление о синтаксисе запросов:

  • select — перечисление строк и столбцов, которые программа выводит на экран после написания запроса;
  • where — синтаксис фильтрации. Обычно в него заключают условия, по которым машина должна отфильтровать информацию;
  • group by — включает режим группировки. Иными словами, прописывая этот код, вы задаете машине запрос о разделении на группы нужной вам информации;
  • pivot — этот запрос помогает создать перекрестные таблицы, где значения таблицы будут использованы в названиях столбцов финальной таблицы;
  • order by — заставляет машину сортировать информацию по заданным значениям.

Давайте попробуем написать код, пользуясь частью запросов из синтаксиса SQL. Например, вам нужно из таблицы с названием Pets вытащить имена щенков и расположить их по году рождения.

Пример таблицы с названием Pets:

ID

Name (имя щенка)

Age (возраст)

1

Рекс

2 мес.

2

Лорд

1 мес.

3

Лэсси

3 дня

4

Майя

12 мес.

5

Банана

5 мес.

Чтобы расположить их по порядку, вам нужно прописать следующий код:

«SELECT * FROM pets WHERE id>0 ORDER BY age»

Вы получаете следующую таблицу:

ID

Name (имя щенка)

Age (возраст)

3

Лэсси

3 дня

2

Лорд

1 мес.

1

Рекс

2 мес.

5

Банана

5 мес.

4

Майя

12 мес.

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

Давайте изучать дальше синтаксис:

  • 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 в программировании и таблицах

Какие еще действия можно совершить с 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 вытягивают информацию из разных вкладок, из иных файлов и проводят с ними дальнейшие манипуляции. В кляузе Query программисты чаще всего используют динамические параметры. Они создают 2 ячейки, посредством коих фильтруют информацию по датам. Также могут фильтровать данные по кампаниям. А еще программисты используют оператор IF в запросе.

Не были подробно рассмотрены кляузы offset и limit. Но, как говорят опытные программисты, эти штуки начинающим не пригодятся.

Важно! Соблюдайте порядок записи в запросе при использовании данной функции. Если в неправильном порядке написать запрос, то вы получите вместо правильного ответа — fatal Error.

Заключение

Теперь вы знаете, как примерно использовать функцию Query, понимаете, что собой представляют операторы и запросы, научились делать выборку из таблиц. При дальнейшем изучении вы поймете, что работа с этой функцией не так сложна и не нужно знать большое количество операторов и других значений, чтобы пользоваться ею.

Чтобы подробней изучить эту функцию и стать грамотным программистом, пройдите курсы программирования от DevEducation.

Присоединяйся к DevEducation — стань востребованным специалистом и построй карьеру в IT!