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

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

17 июля 2013, 18:35 (1650 дней назад, №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


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

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

6017 дней назад, 00:322 августа 2001    Итак, на официальном сайте Assembly скоро уже не будет видна надпись "Asm01 is coming. Are you?". Уже меньше чем через 12 часов будут открыты для гостей двери Hartwall Areena, крупнейшего стадиона Финляндии.    В этом году будет юбилей акции - она проводится уже в десятый раз. Крупнейшее в мире событие, посвященное компьютерному underground искусству ...далее

Избранное

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

1767 дней назад, 00:5922 марта 2013 Прочёл тут книжку - iWoz ( ссылка ) , 2006 года. Это автобиография Стива Возняка. Похоже, что на русский её не переводили (в отличие от книг про Стива Джобса). В этой парочке, как известно, Возняк был инженером (собственно, и спроектировавшим Apple I и II), а Джобс - скорее предпринимателем. В книге есть довольно интересные ...далее

871 день назад, 23:404 сентября 2015 Небольшое видео про CC'2015 ( версия без фоновой музыки здесь: ссылка )

1239 дней назад, 03:121 сентября 2014 Мой семинар на Chaos Constructions'2014 (слайды можно в виде PDF скачать здесь: ссылка ) и несколько интервью с разными людьми: Вячеслав Славинский (svo) о Vectrex: ссылка Вячеслав Славинский (svo) о 3D Imager для Vectrex: ссылка Вячеслав Славинский (svo) о световом пере для Vectrex: ссылка ...далее