Обратите внимание, что новости можно получать по RSS.
X
-

Информационные технологии, LiveJournal cr_it - архив

17 июля 2013, 18:35 (1924 дня назад, №8829)SQL-like запросы к Google Spreadsheets

Scr

Наряду с традиционными для электронных таблиц функциями (повторяющими Excel), в Google Spreadsheets есть некоторое количество дополнительных. Одна из них, под названием "=QUERY()", позволяет рассматривать любую часть таблицы как базу данных и осуществлять к ней простые запросы на языке, напоминающим SQL. Особенно полезно, что возвращаемые данные доступны не только внутри таблиц, но и по запросу через внешний URL (т.е. таблицу можно использовать как готовый DB сервер для сайта или мобильного приложения.

Итак, допустим, у нас есть простая табличка:

Fig 0

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

=QUERY(A1:C9,"select A,count(B) where B < date '2012-12-05' group by A order by count(B)")

Первый параметр у QUERY определяет что считать базой данных, второй - собственно запрос. Есть ещё необязательный третий - можно указать, сколько строчек в исходных данных считать заголовками столбцов (по умолчанию - догадывается сам).

Соответственно, получим посчитанное и отсортированное количестве собак, лягушек и котов зарегистрированных до 5 декабря 2012 года.

Fig 1

Обратите внимание, что над данными появилось слово "count". Это - название поля. Оно добавляется в качестве заголовка для колонок автоматически. Можно сделать чтобы это выглядело более цивилизованно, добавив в запрос 'label':

=QUERY(A1:C9,"select A,count(B) where B < date '2012-12-05' group by A order by count(B) label A 'Beasts', count(B) 'Quantity' ")

Получится почти красиво:

Fig 2

Поскольку язык запросов примитивный, alias'ы не предусмотрены. Именно поэтому в order by и в label не слишком красиво указана строчка "count(B)" - здесь это всего лишь строчка.

Все доступные функции перечислены в документации, но сразу скажу что, конечно, JOIN-ов здесь нет. Кроме того, в одном запросе можно обращаться только к одному листу (необязательно текущему).

И ещё один важный момент - если в качестве базы данных указаны не просто ячейки, а результат работы некоей другой функции, то внутри запроса к столбцам нужно обращаться уже не по их именам (A,B,C,...) а как Col1,Col2,Col3,...

К примеру:

=QUERY(FILTER(A1:C9;C1:C9=0);"select Col1,Col2,Col3 where Col2 < date '2012-12-05' ")

Здесь мы сначала при помощи FILTER выбираем только строчки в которых последний столбец равен 0, а уже к тому что осталось, применяем select, ограничивающий еще и по дате. Получаем:

Fig 3

Теперь пару слов о том, как можно всем этим воспользоваться с пользой для своего сайта/приложения.

Как-то я уже писал о доступе к данным Google Spreadsheets. Здесь всё очень похоже, просто ещё указывается сам SQL запрос. URL выглядит примерно так (если у вас Google Apps аккаунт):

https://docs.google.com/a/domain.ru/spreadsheet/tq?tq=_sql_query_&key=_key_

Здесь _sql_query_ - urlencoded запрос. Т.е. select A будет выглядеть как select%20A key - идентификатор таблицы, который можно получить сделав её публичной (File / Publish to the Web и далее внимательно посмотреть в появившийся URL)

В ответ на запрос select A,B,C к исходной таблице мы получим обыкновенный jsonp вида:

google.visualization.Query.setResponse(
{"version":"0.6",
"status":"ok",
"sig":"497643005",
"table":{"cols":[{"id":"A","label":"","type":&
quot;string","pattern":""},{"id":"B","label":"","type":"date","pattern":"dd
/MM/yyyy"},
{"id":"C","label":"","type":"number","pattern":"#0.###############"}],
"rows":[
{"c":[{"v":"cat"},{"v":new Date(2012,10,27),"f":"27/11/2012"},
{"v":1.0,"f":"1"}]},
{"c":[{"v":"cat"},{"v":new Date(2012,10,28),"f":"28/11/2012"},
{"v":0.0,"f":"0"}]},
{"c":[{"v":"dog"},{"v":new Date(2012,10,29),"f":"29/11/2012"},

{"v":0.0,"f":"0"}]},
{"c":[{"v":"frog"},{"v":new Date(2012,10,30),"f":"30/11/2012"},
{"v":0.0,"f":"0"}]},
{"c":[{"v":"frog"},{"v":new Date(2012,11,1),"f":"01/12/2012"},

{"v":1.0,"f":"1"}]},
{"c":[{"v":"frog"},{"v":new Date(2012,11,2),"f":"02/12/2012"},
{"v":0.0,"f":"0"}]},
{"c":[{"v":"cat"},{"v":new Date(2012,11,3),"f":"03/12/2012"},
{"v":1.0,"f":"1"}]},
{"c":[{"v":"cat"},{"v":new Date(2012,11,4),"f":"04/12/2012"},
{"v":0.0,"f":"0"}]},
{"c":[{"v":"cat"},{"v":new Date(2012,11,5),"f":"05/12/2012"},
{"v":0.0,"f":"0"}]}
]}} );

Проблема, которая может возникнуть - данные отдаются только в jsonp. Т.е. их нельзя получить просто в json - параметр alt=json здесь не работает (в отличие от случая, когда просто получаем данные из таблицы). Довольно странно.

Другой неочевидный момент - некоторый функционал в запросах не работает изнутри таблиц (только снаружи). К примеру - format и option.

Конечно, здесь показан наиболее простой и быстрый способ сделать запрос. Можно и иначе - см. google.visualization.Query


Опубликовано: Пётр Соболев

Случайная заметка

1734 дня назад, 02:3623 января 2014 "Stages to Saturn - a technological history of the Apollo/Saturn Launch Vehicles". Roger E.Bilstein, 1980 Книжка о технических аспектах истории создания ракетоносителей Saturn I, IB, V (в 1960-е, в рамках лунной программы США). Главным образом, о ступенях (т.е. SM, CM, LM вообще не рассматриваются). Цитаты: Об испытаниях двигателя H1 - как они в камеру сгорания засовывали взрывчатку ...далее

Избранное

536 дней назад, 01:575 мая 2017 Часть 1: От четырёх до восьми Я люблю читать воспоминания людей, заставших первые шаги вычислительной техники в их стране. В них всегда есть какая-то романтика, причём какого она рода — сильно зависит от того, с каких компьютеров люди начали. Обычно это определяется обстоятельствами — местом работы, учёбы, а иногда и вовсе — ...далее

48 дней назад, 20:305 сентября 2018 "Finally, we come to the instruction we've all been waiting for – SEX!" / из статьи про микропроцессор CDP1802 / В начале 1970-х в США были весьма популярны простые электронные игры типа Pong (в СССР их аналоги появились в продаже через 5-10 лет). Как правило, такие игры не имели микропроцессора и памяти в современном понимании этих слов, а строились на жёсткой ...далее