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

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

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


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

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

6859 дней назад, 03:5415 июля 1999 Вот еще интересная тема, также связанная с лазерами. Lucent Technologies разработал систему для передачи данных по воздуху. Называется OpticAir. Дальность - до 4.4км. Сколько будет стоить эта железка неизвестно, говорится лишь что \"сравнимо со стоимостью высокоскоростных волоконно-оптических линий связи\". Хотя скорость при этом ...далее

Избранное

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

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

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

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