Всемогъщата функция за заявки - Подробно ръководство

Мисля, че всеки е чувал за правилото на Парето. Във всяка област 20% от усилията дават 80% от резултатите. Например, 20% от гардероба ви носите през 80% от времето, 20% от клиентите ви носят 80% от приходите. Същото е и с Google Sheets: познавайки 20% от съществуващите функции, можете да разрешите 80% от всички възможни проблеми.

Смятам Query за една от най-полезните функции на Google Sheets. Но в помощта на Google е описано много повърхностно и пълната мощност на тази функция не се разкрива. При по-подробно запознаване става ясно, че той е в състояние да замени повечето от съществуващите функции.

За да работите с QUERY ще ви трябват основни познания по SQL. За тези, които не знаят, не се плашете, функцията QUERY всъщност поддържа най-основните SQL функции.

QUERY Синтаксис

  • данни е диапазон от клетки, които ще служат като база данни за SQL заявката;
  • заявка — текст на SQL заявка;
  • Headers е незадължителен аргумент, в който можете да посочите колко първи реда на масива съдържат заглавки.

За максимално възприемане на допълнителна информация предлагам да отворите и копирате следната електронна таблица на Google. За да създадете копие, използвайте менюто "Файл" и изберете елемента "Създаване на копие" в него.

подробно
В току-що копирания документ има няколко листа. DB листът е базата данни, до която ще имаме достъп чрез функцията QUERY. Листовете с нива съдържат примерите, които ще разгледаме в тази статия. С всяко ново ниво примерът ще става все по-труден.

План за SQL заявка във функция Query

Всяка SQL заявка се състои от отделни блокове, които често се наричат ​​клевети. SQL за функцията Query се основава на синтаксиса на Google Visualization API Query Language, който поддържа следното:клевета:

  • select - изброяване на полетата, които ще бъдат върнати от заявката;
  • където - съдържа списък с условия, които ще се използват за филтриране на масива от данни, обработван от заявката;
  • group by - съдържа списък с полета, по които искате да групирате резултата;
  • pivot - помага за изграждане на кръстосани таблици, използвайки стойността на една колона като имена на колони на финалната таблица;
  • order by - отговаря за сортирането на резултатите;
  • limit - използвайки тази част от заявката, можете да зададете ограничение за броя на редовете, върнати от заявката;
  • отместване - използвайки тази клевета, можете да зададете броя на първите редове, които не трябва да се обработват от заявката;
  • етикет - тази клевета отговаря за името на полетата, върнати от заявката;
  • format - отговаря за формата на изходните данни;
  • опции — ви позволява да зададете допълнителни опции за извеждане на данни.

Hello World за функцията Query (Select).

Нека отидем на листа Level_1 и да разгледаме формулата в клетка A1.

Част от формулата "DB!A1:L1143" отговаря за базата данни, от която ще направим селекция. Втората част на "select * limit 100" съдържа самия текст на заявката. Символът "*" в този случай означава да се върнат всички полета, съдържащи се в базата данни. С "limit 100" ограничаваме изхода до максимум 100 реда. Това е пример за най-простата заявка. Избрали сме първите 100 реда от базата данни. Това е нещо като "Hello world" за функцията Query.

Използване на филтри и сортиране (Къде, Подреждане по)

Полетата на базата данни са достъпни чрез имената на колоните на работния лист, на който се намира базата данни.

В нашия случай даннитеразположени на DB листа, а достъпът до определени полета се изписват като име на колоните на листа. По този начин задължителните полета се намират в следните колони:

  • полеДата - колона A;
  • полеКампания - колона B;
  • полеСесии - колона G.

Съответно, частта от заявката, отговорна за списъка с изходни данни като резултат, ще изглежда така:

С помощта на скоби разделихме филтрирането на данни на две логически части: първата филтрира по дата, втората по име на кампания. На този етап формулата, описваща данните за избор и условията за филтриране на данните изглежда така:

Можете да го копирате и поставите в, например, нов лист от документа, който се използва като пример в тази публикация, и ще получите следния резултат:

функция

В допълнение към обичайните логически оператори (=, ), клаузата WHERE поддържа допълнителни филтриращи оператори:

Заявката вече е филтрирала данни за определен период и е оставила само нужните ни кампании. Остава само да сортирате резултата в низходящ ред в зависимост от броя на сесиите. Сортирането в тези заявки се извършва традиционно за SQL с помощта на клеветатаПодреждане по. Синтаксисът е доста прост: трябва само да посочите полетата, по които искате да сортирате резултата, както и да посочите реда на сортиране. По подразбиране редът е възходящ, тоест възходящ. Ако укажете параметъра desc след името на полето, заявката ще върне резултата в низходящ ред на полетата, посочени в клаузатаПодреждане по.

В нашия случай редът в текста на заявката ще отговаря за филтрирането:

Съответно крайният резултат от формулата на листа Level_2, която решава проблема, от който се нуждаем, изглежда така:

Сега знаете как даизползвайки най-простия SQL синтаксис и функцията QUERY за филтриране и сортиране на данните.

Агрегирани функции, групиране на данни и преименуване на колони (Групиране по, Етикет)

Отидете на лист Level_3 и усложнете задачата. В заявките можете не само да правите селекции, но и да извършвате различни изчисления и агрегиране на данни. За да направите това, функцията SQL Query има редица агрегатни функции и клаузатаGroup by. Агрегиращи функции: