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

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

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


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

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

2868 дней назад, 02:4825 мая 2015 Около года назад взял себе Boogie Board - простое устройство для рукописных заметок. Там использовалась довольно специфическая технология - малоконтрастный Reflex LCD экран, на котором можно писать как на бумаге, но нельзя сохранять или стирать написанное (очистить можно только сразу весь экран). То есть, его даже сложно назвать ...далее

Избранное

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

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