Язык запросов Google — языки запросов различных браузеров
Язык запросов Google
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Это дает возможность выявить документы со ссылкой на данный URL. Например, на запрос link:www.nlr.ru будут получены документы со ссылками на домашнюю страницу РНБ. Такой запрос нельзя комбинировать с обычными ключевыми словами. Имеется возможность поиска документов на определнном языке.
Топ поисковых запросов октября пользователей Google в России и Беларуси
10 Ноября 2022 года, 16:05
Шрифт:
Grizzly Digital Company решили сравнить популярные октябрьские запросы белорусов и россиян в Google и обнаружили занимательные факты. Делимся с вами.
Белорусы активно интересовались новым для страны событием – Днем Отца. Этот праздник 21 октября отмечали впервые. Не остался без внимания День матери. У россиян лидирующую позицию в поиске занял запрос «Крымский мост».
С первым снегом в России не только появились новогодние украшения на полках в магазинах, но и сами россияне начали задумываться о празднике. Поэтому в лидерах по запросам оказался наступающий 2023 год и Дед Мороз. Также в топ попали запросы «билет будущего» (программа профессиональной ориентации для школьников), «кассы.ру Кемерово» и «билеты в Цирк Никулина». Видимо, жителям России захотелось разнообразить свой досуг.
В категории «Наука» у россиян в топ попало солнечное затмение, которое произошло 25 октября. К слову, такое явление можно наблюдать от 2 до 5 раз в год. Белорусов солнечным затмением не впечатлишь, здесь уставшие школьники и их родители пытались облегчить себе жизнь и искали решебники по физике, информацию о солнечной системе и химических элементах.
В категории «Покупки» топовые запросы россиян и белорусов существенно различаются. Жители РФ вплотную занимались подготовкой к Хеллоуину, искали открытки для учителей и калькулятор оценок.
В категории «Продукты питания» россияне и белорусы прониклись грибной темой – все пользователи интересовались осенними опятами. И если жители Беларуси вдобавок к грибам искали рецепты квашеной капусты, жители России решили отдохнуть от дел грибных и отправиться за пивом – у данного запроса +1950% к популярности по отношению к прошлому месяцу.
Что касается работы и образования, тут пользователи обеих стран сошлись – в топе запросы про каникулы и олимпиады. Вдобавок к этому белорусы искали образцы резюме и работу в Светлогорске, видимо, пользователи из РБ активно строят карьеру.
В категории «хобби и отдыха» снова в топе День отца, основное отличие в запросах лишь в том, что россияне искали дату дня женского счастья и выбирали открытки на день учителя, белорусы же все силы пустили на поздравления с Днем Матери.
Интересы пользователей Беларуси и России разные. Пока жители РБ спрашивали, сколько варить опята, пользователи из РФ задавались вопросом, сколько ног у стрекозы. Также белорусы думали, когда сажать чеснок, а россияне – когда квасить капусту. И главный вопрос у пользователей из Беларуси – зачем мне солнце монако, а из России — зачем ремесленники объединялись в цехи и зачем пьют ананасовый сок.
Вглубь поисковой системы Google мы заглянули при помощи сервиса аналитики Google trends, который доступен для каждого пользователя.
Изучите самую мощную функцию в Таблицах
Функция запроса Google Таблиц — это самая мощная и универсальная функция в Google Таблицах.
Он позволяет вам использовать команды данных для управления вашими данными в Google Sheets, и это невероятно универсальный и мощный .
Эта единственная функция выполняет работу многих других функций и может воспроизводить большую часть функций сводных таблиц.
Это видео урок 14 из 30 моего бесплатного курса Google Sheets: Advanced Formulas 30 Day Challenge
Google Sheets QUERY Синтаксис функции
=QUERY(данные, запрос, [заголовки])
Требуется 3 аргумента:
- диапазон данных, которые вы хотите проанализировать 90
- запрос, который вы хотите выполнить, заключенный в кавычки
- необязательный номер, чтобы сказать, сколько строк заголовка в ваших данных
Вот пример функции ЗАПРОС:
=ЗАПРОС(A1:D234,"ВЫБЕРИТЕ B, D",1)
Диапазон данных в этом примере — A1:D234
Оператор запроса — это строка в кавычках, выделенная зеленым цветом. В этом случае он сообщает функции выбрать столбцы B и D из данных.
Третий аргумент — это число 1, которое сообщает функции, что исходные данные имели одну строку заголовка.
Этот аргумент является необязательным и, если он опущен, будет автоматически определен Sheets.
Это одна из функций Google, которые недоступны в других инструментах для работы с электронными таблицами.
ЗАПРОС Примечания к функциям
Ключевые слова не чувствительны к регистру, поэтому вы можете написать «ВЫБРАТЬ» или «выбрать», и оба варианта будут работать.
Однако буквы столбцов должны быть в верхнем регистре: A, B, C и т. д. иначе вы получите ошибку.
Ключевые слова должны стоять в следующем порядке (конечно, вам не обязательно использовать их все):
- выберите
- где
- группа по
- заказать по
- предел
- этикетка
Вы увидите примеры всех этих ключевых слов ниже.
Есть еще несколько ключевых слов, но они менее распространены. Полный список смотрите здесь.
Google Sheets QUERY Function Template
Щелкните здесь, чтобы открыть копию только для просмотра >>
Не стесняйтесь сделать копию: Файл > Создать копию…
Если вы не можете получить доступ к шаблону, он может быть из-за настроек Google Workspace вашей организации.
Если вы щелкните ссылку правой кнопкой мыши и откроете ее в окне инкогнито, вы сможете ее увидеть.
Google Sheets QUERY Примеры функций
Если вы хотите следовать решениям, сделайте копию приведенного выше шаблона Google Sheet.
Вот как выглядят наши исходные данные:
В этом руководстве я использовал именованный диапазон для идентификации данных, что значительно упрощает и упрощает использование в функции ЗАПРОС. Не стесняйтесь также использовать именованный диапазон «страны», который уже существует в шаблоне.
Если вы не знакомы с именованными диапазонами, вот как их создавать:
Выберите диапазон данных и перейдите в меню:
Данные > Именованные диапазоны…
В правой части электронной таблицы появится новая панель. В первом поле ввода введите имя таблицы данных, чтобы к ней можно было легко обратиться.
SELECT All
Оператор SELECT * извлекает все столбцы из нашей таблицы данных.
В правой части таблицы (я использовал ячейку G1) введите следующую функцию QUERY Google Sheets, используя обозначение именованного диапазона:
=ЗАПРОС(страны,"SELECT *",1)
Примечания: если вы не хотите использовать именованные диапазоны, то это не проблема. Ваша формула ЗАПРОС будет выглядеть так:
=ЗАПРОС(A1:D234,"ВЫБЕРИТЕ *",1)
В оставшейся части этой статьи я использовал именованный диапазон «страны», но вы можете продолжить используя вместо него ссылку на обычный диапазон A1:D234.
Результатом этого запроса снова является наша полная таблица, потому что SELECT * извлекает все столбцы из таблицы стран:
Вот это да! Вы написали свой первый ЗАПРОС! Похлопайте себя по спине.
SELECT Определенные столбцы
Что делать, если мы не хотим выбирать каждый столбец, а только определенные?
Измените функцию ЗАПРОС Google Таблиц, чтобы она читалась так:
= ЗАПРОС(страны,"ВЫБЕРИТЕ B, D",1)
На этот раз мы выбрали только столбцы B и D из исходного набора данных , поэтому наш вывод будет выглядеть следующим образом:
Важное примечание
Помните, наша функция ЗАПРОС находится в ячейке G1, поэтому вывод будет в столбцах G, H, I и т.
д.
B и D внутри оператора выбора QUERY ссылаются на ссылки на столбцы в исходных данных.
Ключевое слово WHERE
Ключевое слово WHERE указывает условие, которое должно быть выполнено. Он фильтрует наши данные. Он идет после ключевого слова SELECT.
Измените функцию ЗАПРОС в Google Таблицах, чтобы выбирать только страны с населением более 100 миллионов человек:
= ЗАПРОС(страны,"ВЫБЕРИТЕ B, D, ГДЕ D > 100000000",1)
Наша выходная таблица:
Давайте посмотрим еще один пример ключевого слова WHERE, на этот раз выбрав только европейские страны. Измените формулу на:
=ЗАПРОС(страны,"ВЫБЕРИТЕ B, C, D, ГДЕ С = 'Европа'", 1)
Обратите внимание, что слово «Европа» заключено в одинарные кавычки. Сравните это с числовым примером, в котором число не заключалось в одинарные кавычки.
Теперь таблица результатов:
ORDER BY Keyword
Ключевое слово ORDER BY сортирует наши данные.
Мы можем указать столбцы и направление (по возрастанию или по убыванию). Он идет после ключевых слов SELECT и WHERE.
Давайте отсортируем наши данные по населению от меньшего к большему. Измените формулу, добавив следующее ключевое слово ORDER BY, указав восходящее направление с помощью ASC:
=ЗАПРОС(страны,"SELECT B, C, D ORDER BY D ASC",1)
Выходная таблица:
Измените формулу ЗАПРОС, чтобы отсортировать данные по странам в порядке убывания, Z – A:
= ЗАПРОС(страны,"ВЫБЕРИТЕ B, C, Упорядочить по B DESC", 1)
Таблица вывода:
Ключевое слово LIMIT
Ключевое слово LIMIT ограничивает количество возвращаемых результатов. Он идет после ключевых слов SELECT, WHERE и ORDER BY.
Давайте добавим в нашу формулу ключевое слово LIMIT и вернем только 10 результатов:
=ЗАПРОС(страны,"ВЫБЕРИТЕ B, C, D ORDER BY D ASC LIMIT 10",1)
Теперь это возвращает только 10 результатов из наших данных:
Арифметические функции
Мы можем выполнять стандартные математические операции над числовыми столбцами.
Итак, давайте подсчитаем, какой процент от всего населения мира (7,16 миллиарда) приходится на каждую страну.
Мы собираемся разделить столбец населения на общее количество (7 162 119 434) и умножить на 100, чтобы вычислить проценты. Итак, изменим нашу формулу, чтобы она читалась:
=ЗАПРОС(страны,"ВЫБЕРИТЕ B, C, (D / 7162119434) * 100",1)
Я разделил значения в столбце D на общую численность населения (в скобках), а затем умножил на 100, чтобы получить процент.
Выходная таблица на этот раз:
Примечание. Я применил форматирование к выходному столбцу в Google Таблицах, чтобы отображались только 2 десятичных знака.
LABEL Ключевое слово
Этот заголовок для арифметического столбца довольно уродлив, верно? Ну, мы можем переименовать его, используя ключевое слово LABEL, которое стоит в конце оператора QUERY. Попробуйте это:
=ЗАПРОС(страны,"ВЫБЕРИТЕ B, C, (D/7162119434) * 100 LABEL (D/7162119434) * 100 'Процент'",1)
=ЗАПРОС(страны,"ВЫБЕРИТЕ B, C , (D / 7162119434) * 100 LABEL (D / 7162119434) * 100 'Процент' ",1)
Функции агрегирования
Мы можем использовать другие функции в наших расчетах, например, мин, макс и среднее.
Чтобы рассчитать минимальную, максимальную и среднюю численность населения в наборе данных вашей страны, используйте агрегатные функции в своем запросе следующим образом:
=ЗАПРОС(страны,"ВЫБЕРИТЕ максимальное(D), минимальное(D), среднее(D)",1)
Выходные данные возвращают три значения — максимальное, минимальное и среднее заполнение набора данных, как показано ниже :
GROUP BY Ключевое слово
Хорошо, сделайте глубокий вдох. Это самая сложная концепция для понимания. Однако, если вы когда-либо использовали сводные таблицы в Google Таблицах (или Excel), то вас это должно устроить.
Ключевое слово GROUP BY используется с агрегатными функциями для суммирования данных в группы, как это делает сводная таблица.
Подведем итоги по континентам и посчитаем, сколько стран на континенте. Измените формулу запроса, включив в нее ключевое слово GROUP BY, и используйте агрегатную функцию COUNT для подсчета количества стран следующим образом:
=ЗАПРОС(countries,"SELECT C, count(B) GROUP BY C",1)
Обратите внимание: каждый столбец в операторе SELECT (т.
е. перед GROUP BY) должен быть либо агрегирован (например, counted, min, max), либо стоять после ключевого слова GROUP BY (например, столбец C в данном случае).
Результат этого запроса:
Давайте рассмотрим более сложный пример, включающий множество различных типов ключевых слов. Измените формулу, чтобы она выглядела следующим образом:
=ЗАПРОС(страны,"ВЫБЕРИТЕ C, количество(B), мин.(D), макс.(D), среднее(D) СГРУППИРОВАТЬ ПО КОРДЕРУ ПО ср.(D) DESC LIMIT 3" ,1)
Это может быть легче читать, разбитое на несколько строк:
=ЗАПРОС(страны,
"ВЫБЕРИТЕ C, количество(B), мин.(D), макс.(D), среднее(D)
ГРУППА ПО C
ПОРЯДОК ПО avg(D) DESC
LIMIT 3",1)
Это суммирует наши данные по каждому континенту, сортирует их по среднему населению от наибольшего до наименьшего и, наконец, ограничивает результаты только тремя верхними.
Результат этого запроса:
Advanced Google Sheets QUERY Function Techniques
Как добавить итоговую строку в ваши формулы запроса
Как использовать даты в качестве фильтров в ваших формулах запроса
Есть еще 4 ключевых слова, которые не были рассмотрены в этой статье: PIVOT, СМЕЩЕНИЕ, ФОРМАТ И ОПЦИИ
Кроме того, доступно больше функций для обработки данных, чем мы обсуждали выше.
Например, существует ряд скалярных функций для работы с датами.
Предположим, у вас есть столбец дат в столбце A набора данных, и вы хотите суммировать данные по годам. Вы можете свернуть его, используя скалярную функцию ГОД:
=ЗАПРОС(данные,"выбрать группу ГОД(А), СЧЕТЧИК(А) по ГОД(А)",1)
Для более продвинутых методов с Функция ЗАПРОС, посмотрите этот урок из курса Advanced 30:
Это видео является уроком 15 из 30 моего бесплатного курса Google Sheets: Advanced Formulas 30 Day Challenge .
Другие ресурсы
Функция ЗАПРОС рассматривается в дни 14 и 15 моего бесплатного курса Расширенные формулы для Google Sheets: Изучите 30 расширенных формул за 30 дней
Официальная документация Google по функции ЗАПРОС().
Официальная документация для языка запросов API визуализации Google.
Функция запроса в Google Таблицах — Полное руководство
1. Основы запросов Google Sheets
2. Написание сравнений дат в запросах
3. Отладка распространенных ошибок запросов
4. Как запрашивать несколько листов/диапазонов одновременно
5. Запрос по именам столбцов, а не по буквам
6. Создание сводных таблиц с помощью запросов
7. Использование выпадающих меню в качестве переменных
8. Разница между SQL и запросом
9. Вложение запросов вместе, как в SQL
Нужна помощь?
Функция Google Sheets Query заменяет так много других функций электронных таблиц, что это даже не смешно — ФИЛЬТРЫ, СРЕДНИЕ и СУММЫ исчезают из окна, когда они появляются на картинке.
Есть несколько нюансов в запросах, так что давайте пошагово.
Этот пост содержит восемь видеороликов, под каждым из которых есть заметки об обрыве:
Погружаемся!
Кстати… если вы хотите перейти от Таблиц к BigQuery, ознакомьтесь с нашими рецептами быстрого запуска BigQuery на странице query.recipes.
1. Основы запросов Google Таблиц
ЗАПРОС сочетает в себе все возможности арифметических функций (СУММ, СЧЕТ, СРЗНАЧ) с фильтрующими возможностями такой функции, как ФИЛЬТР.
В этом примере мы рассмотрим несколько примеров QUERY, чтобы проанализировать выборку данных Twitter.
Основная структура ЗАПРОСА состоит в том, чтобы ввести:
- Диапазон данных – «вкладка данных»!A:C’
- И строка запроса — «выберите A, где C = ‘twitter’»
Мы объединяем их следующим образом:
=query( ‘вкладка данных’!A:C, «выберите A, где C = ‘twitter’»)
Пример функции QUERY для ответа на вопросы
Q: Каково было среднее количество ретвитов из выборки?
A: =запрос(D1:G, «выбрать среднее(G)»)
В: Каково среднее количество ретвитов в твитах, содержащих @упоминание, но не являющихся ретвитами?
A: =query(D1:G, «выберите avg(G), где E содержит ‘@’, а E не содержит ‘RT'»)
Q: Сколько ретвитов получил каждый пользователь в списке ?
A: =запрос(D1:G, «выберите F, sum(G) где E != » сгруппировать по метке F sum(G) ‘всего ретвитов'»)
2.
Запись сравнения дат в запросахПочему такие сложные запросы в Таблицах — разве это не должно быть проще?
Когда я впервые начал писать сравнения дат в запросах Google (вытягивать данные до/после определенной даты), мне было сложно разобраться с правильным синтаксисом.
К сожалению, нигде в документации Google Sheets об этом не упоминается, так что мне пришлось разбираться самому.
Вы должны правильно сделать две вещи:
- Написать строку «дата» перед фактической датой, чтобы заявить о своих намерениях
- Введите дату в формате «гггг-мм-дд»
Если вы хотите динамически сравнить дату из другой ячейки, это немного другая история. Мне нравится сохранять диапазоны дат (последние 7 дней, последние 30 дней, текущий год и т. д.), а затем ссылаться на них в запросе, подобном этому:
Обратите внимание на синтаксис:
- Чтобы объединить строку с внешним значением , закройте запрос в двойных кавычках и используйте ‘&’
- Используйте функцию ТЕКСТ для преобразования даты в правильный формат «гггг-мм-дд»
Как только вы это освоите, вы больше никогда об этом не вспомните.
Но если вы когда-нибудь забудете синтаксис, вам поможет шпаргалка CIFL.
3. Отладка распространенных ошибок запросов
Об отладке распространенных ошибок запросов:
Ошибки никогда не доставляют удовольствия, но сообщения об ошибках Таблиц всегда укажут вам правильное направление.
#REF
1) Результат массива не был расширен, поскольку это привело бы к перезаписи данных в ячейке D6
Данные в ячейке D6 блокируют расширение вашего запроса — удалите его!
2) Неразрешенное имя листа…
Лист, на который вы ссылаетесь в своем запросе, не существует — вероятно, из-за опечатки или из-за его удаления :(.
#VALUE
3) PARSE_ERROR
Вызвано неверной строкой запроса — всегда будет указано, где именно искать: опечатка:
=query(tweets!A1:D,’select C, sum(D), где D >= 0 group by C limit 2′,0)
4) LABEL_COL_NOT_IN_SELECT
Вызвано несоответствием между столбцами в предложении «выбрать» и предложении «метка»:
=запрос(твиты!A1:D,‘выберите C, sum(D) где D >= 0 сгруппируйте по C limit 2 label sum© «,0)
Здесь sum(D) ошибочно помечен как sum©.
5) NO_COLUMN
Когда вы выбираете столбец, который находится за пределами вашего диапазона:
=запрос(твиты!A1:C,’выбрать C, сумма(D) где D >= 0 группа по пределу C 2′,0)
Исправлено путем расширения диапазона данных, включив в него столбец (в данном случае твиты!A1:D).
6) ADD_COL_TO_GROUP_BY_OR_AGG
Появляется, когда вы выбираете столбец (в данном случае B), которого нет в предложении «группировать по»:
=query(tweets!A1:D,’select C, B, sum(D ) где D >= 0 сгруппировать по C limit 2′,0)
Если вы не суммируете или не агрегируете столбец B, вам нужно сгруппировать по нему.
#Н/Д
7) Запрос завершен с пустым выводом.
Просто означает, что по вашему запросу нет результатов в том виде, в каком он написан в настоящее время, возможно, из-за слишком ограничительного условия:
=query(tweets!A2:D,’выбрать C, D, где D = 100 предел 2′,0)
Попробуйте упростить запрос, чтобы сделать его менее строгим (удалив D = 100 в данном случае).
4. Как запрашивать несколько таблиц/диапазонов одновременно
При запросе нескольких таблиц Google с помощью importrange + {}:
В каждой жизни наступает время, когда вы хотите объединить два данных диапазоны в запросе Google Sheets.
Давайте поговорим о том, как объединить диапазоны данных из одной электронной таблицы (или из двух разных листов) для выполнения одного запроса — работая с примерами, используя образцы данных Twitter.
Чтобы запросить комбинированный диапазон из одного листа:
- Заключите оба диапазона в { }, чтобы объединить их, и разделите знаком «;», чтобы расположить один поверх другого
- Вместо того, чтобы ссылаться на столбцы с их буквой (A, B), ссылайтесь на них как Col1, Col2 в зависимости от их порядка
Чтобы запросить комбинированный диапазон из разных листов:
- Сначала запустите функцию importrange на внешнем листе, из которого вы хотите извлечь данные.
Первый элемент — это идентификатор электронной таблицы, который содержится в URL-адресе таблицы между /d/ и /edit: (docs.google.com/spreadsheets/d/9).0308 1ekTIZ40VVT9p9kE80wNaiy6Y2xslo4K9KVmmg-Dx654 /изменить).
Первый элемент — это идентификатор электронной таблицы, который содержится в URL-адресе таблицы между /d/ и /edit: (docs.google.com/spreadsheets/d/9).0308 1ekTIZ40VVT9p9kE80wNaiy6Y2xslo4K9KVmmg-Dx654 /изменить).- Вам будет предложено разрешить доступ к листу.
- Вставьте диапазон из функции importrange в сам запрос.
Честно говоря, моя цель — никогда не объединять диапазоны внутри самого запроса (я предпочитаю выполнять такой тип агрегирования перед написанием запроса), но иногда это неизбежно.
5. Запрос по именам столбцов, а не по буквам
Это избавит вас от головной боли, если несколько коллег работают с вашими таблицами.
Запросы очень деликатны: они требуют, чтобы мы указывали точно буквы столбца ( A, sum(B) ) или числа ( Col1, sum(Col2) ), которые мы хотим вернуть.
Но, учитывая то, как мы используем Таблицы, эти столбцы могут постоянно меняться. Люди добавляют новые строки, удаляют строки и вообще меняют местами.
Объединяя два запроса вместе, мы можем гарантировать, что ваши запросы всегда будут соответствовать одним и тем же заголовкам столбцов:
- Первый (внутренний) запрос использует функцию Match для поиска номеров столбцов — match(‘header name’, tab!:A1:Z1), предполагая, что ваши заголовки находятся в первой строке. Обратите внимание, что ниже мы хотим заключить наш диапазон {tweets!A2:D} в фигурные скобки, чтобы освободить нас для выбора номеров столбцов (Col1), а не букв (A).
- Во втором (внешнем) запросе вы строите фактическую логику выбора (выберите Col1, суммируйте (Col2) группу по Col1…), потому что во внутреннем запросе вы уже выбрали нужные столбцы. Вы можете жестко запрограммировать Col1, Col2 и т. д., потому что вы всегда знаете порядок, в котором они будут отображаться, независимо от того, перемещаются ли столбцы на вашем фактическом листе.
Соедините все вместе, и это будет выглядеть так:
Первая строка (внутренний запрос) находит имена ваших столбцов – на самом деле она выполняет запрос «выберите Col3, Col4»
Вторая строка (внешний запрос) выполняет вашу фактическую математику и маркирует ваши столбцы. Помните, что метки столбцов запроса всегда будут отображаться в конце инструкции и в целом выглядят как инструкция SELECT (выберите Col1, sum(Col2) сопоставляется с меткой Col1 «дескриптор», sum(Col2) «всего ретвитов»).
Это может занять некоторое время, чтобы освоиться — не стесняйтесь копировать демонстрационный лист, использованный в видео, чтобы повторно использовать нашу формулу.
6. Построение сводных таблиц с помощью запроса
Отлично подходит для создания отчетов о временных рядах в Таблицах:
Если вы работаете с данными временных рядов в Таблицах (как это делает большинство из нас), то сводка в запросах станет вашим новым любимым ходом.
Позволяет построить полный отчет в одном запросе, группируя значения в левом столбце и перемещая диапазоны дат (или любой второй столбец) в правый.
Вы выполняете поворот с синтаксисом, очень похожим на группировку — этот запрос дает приведенный выше результат:
= запрос (твиты! A1: D, ‘выбрать C, сумма (D) где D >= 0 группировать по C повороту A’, 0)
Как видите, это так же просто, как добавить в запросе «основу А» после предложения «группировать по».
Вы можете использовать раскрывающиеся списки для добавления входных переменных в свой запрос:
Когда вы создаете отчет в Google Sheets, главная цель — не заставить людей, *использующих* отчет, думать.
Раскрывающиеся меню отлично подходят для этого, так как они позволяют вам предоставить ограниченное количество опций для отчета о пользователях.
Проверка данных в Таблицах автоматически создает выпадающие меню, когда вы их создаете — очень удобно, верно?
И смешать выбранное значение из раскрывающегося меню с запросом очень просто:
= запрос (данные! A: B, ‘выберите A, где B = » & ‘расположение вкладки раскрывающегося списка’! C2 & «‘, 0)
Требуется всего несколько нажатий клавиш:
- Заключите раскрывающееся значение в одинарные кавычки (‘), если это строка (нет, если это число)
- Закройте (а затем снова откройте) ваш запрос в двойных кавычках («)
- Используйте амперсанд (&), чтобы добавить раскрывающееся значение в строку запроса
- Ссылка на фактическую ячейку раскрывающегося списка («данные»! A2), чтобы добавить ее в строку запроса
Вот оно! Пользователи вашего отчета будут вам благодарны.
8. Разница между SQL и Query
В чем разница между Sheets Query и SQL?
Если вы привыкли писать SQL-запросы, поначалу вам может быть неприятно работать с запросами Google Sheets (и наоборот).
Если вы еще не знаете SQL, сначала изучите Query. Это сделает работу с базами данных *намного* проще, когда вы туда доберетесь.
Давайте рассмотрим, чем они похожи и чем отличаются друг от друга, и как вы можете использовать каждый из них в своей работе.
Базовые запросы
SQL: SELECT * FROM table WHERE имя_столбца = ‘xyz’ ORDER BY имя_столбца desc LIMIT 10
= ‘xyz’ ORDER BY A desc LIMIT 10′)
Базовый синтаксис запроса примерно такой же. Запросы Google Sheets используют один и тот же оператор SELECT для выбора столбцов, WHERE / AND / OR для установки логики, ORDER BY для упорядочения результатов и LIMIT для извлечения только определенного количества результатов (см. полный список языковых предложений в документах Google ). ).
Ключевое отличие состоит в том, что в запросе Sheets нет инструкции FROM — вместо ссылки на таблицу вы ссылаетесь на диапазон ячеек (в данном примере — tab!A:D).
Кроме того, вы заметите, что вместо ссылок на имена столбцов (как в SQL) в запросах Таблиц вы ссылаетесь на столбцы по их букве (A, B, C и т. д.) или по их порядку (Col1, Col2, Col3) .
Выполнение математических операций со столбцами
SQL: SELECT имя_столбца, сумма(имя_другого_столбца) ИЗ таблицы GROUP BY имя_столбца
) GROUP BY A’)
Те же функции агрегации avg(), count(), sum(), max(), min(), которые вы привыкли использовать в SQL, доступны в запросах Sheets, как и базовые + , -, *, / арифметические функции.
Маркировка столбцов
SQL: SELECT column_name AS ‘label’ FROM table
Sheets: =query( ‘tab’!A:D, ’SELECT A, B LABEL A ‘label1’, B ‘label2’ ‘)
В SQL, для метки столбец, вы просто добавляете «AS» к оператору «SELECT»: имя_столбца AS «метка».
В Sheets это делается в конце запроса с помощью оператора «метка».
Пометка столбца sum(A) как пустого приводит к удалению автоматического заголовка sum() из отображения, возвращая чистую единственную метрику.
Сравнение дат
SQL: SELECT column_name FROM table WHERE date_column > ‘22.08.2016’
Sheets: =query( ‘tab’!A:D, ’SELECT A WHERE -08-22′ ‘)
Даты в гугл-запросах сложно понять с первого раза, если вы привыкли работать в SQL.
В отличие от SQL, который может обрабатывать даты в нескольких форматах («20160826», «26.08.2016», «2016-08-26»), Таблицы могут принимать даты только в формате «гггг-мм-дд». иногда приходится использовать функцию ТЕКСТ, чтобы преобразовать дату, например 26.08.2016, в 2016-08-26.
В Таблицах вы также должны указать, что вы сравниваете дату, добавив фразу «дата» перед строкой даты.
Нечеткое совпадение фраз
SQL: SELECT column_name FROM table WHERE column_name LIKE ‘%phrase%’ ‘ ‘)
Чтобы извлечь текст, содержащий значение, вы можете просто использовать оператор «содержит» вместо оператора «подобно» в SQL.
В запросах к таблицам есть оператор «нравится», но я обнаружил, что использую «содержит» гораздо чаще из-за его простоты.
Однако добавлено одно замечание: если вы хотите добавить параметр «не содержит», вам нужно поставить «не» перед буквой столбца.
Объединение таблиц
SQL: ВЫБЕРИТЕ a.column_name, b.column_name FROM table1 a, table2 b WHERE a.column_name = b.column_name
Листы: 9000 ???4
Больше всего меня раздражает то, что запросы Sheets не поддерживают объединение двух таблиц с помощью уникального ключа. Я перепробовал все обходные пути, которые только мог придумать, и пока не нашел способа.
Команда Google Sheets, если вы читаете это — помогите!
Соответствие из списка вариантов
SQL: SELECT column_name FROM table WHERE column_name IN (‘match2’, ‘match3’, ‘match4’)
Sheets: ‘t=ab’A!A :D, ‘ВЫБЕРИТЕ ГДЕ A = ‘match2’ ИЛИ A = ‘match3’ ИЛИ A = ‘match4’ ‘)
К сожалению, в Google Sheets нет такого понятия, как фраза IN в SQL, поэтому вам нужно перечислите значения, которые вы хотите сопоставить, одно за другим.
9. Вложение запросов вместе, как в SQL
При некотором хитром подходе вы можете аппроксимировать функцию вложения SQL в Sheets:
недостаток.
Допустим, у вас есть данные на двух разных вкладках, и вы хотите получить все значения из одной таблицы, которые существуют в другой.
Я думал, что это невозможно. Я был неправ.
Это хакерский способ имитации вложенности SQL-запросов, но он работает.
Вы объединяете формулу массива и конкатенацию, чтобы получить длинную строку в вашем запросе (ИЛИ A = «значение1» ИЛИ A = «значение2» и т. д.).
Окончательная формула выглядит так:
=query(tab1!A:Z,’выберите A,sum(B) где A != ‘ and (B = ‘blah’ ‘ & concatenate(arrayformula(‘ OR B = “ & tab2!C2:C & “’)) & ‘) group by B’,0)
Это берет все значения, присутствующие на вкладке 2, столбце C, и ищет их в столбце B на вкладке 1.