Блог за онлайн маркетинг за бизнеса

Уеб анализ

Как се работи с прозоречните функции в Google BigQuery — подробно ръководство

33
0
0
1

Количеството данни стремително расте. Расте и интересът към услугите за тяхното съхраняване и обработка.

Един от сравнително новите и качествени инструменти в областта на съхраняване и обработка на големи масиви от информация е облачната база данни Google BigQuery. Инструментът може да обработва стотици хиляди реда в секунда.

При работа, BigQuery е по-опростен инструмент от MySQL и PostgreSQL, а и от повечето подобни. Всичко е заради това, че в инструмента липсва част от работата, свързана с администрирането на сървъра. Оптимизирането на работата на базата се прави направо от Google.

Вторият приятен бонус — вие няма да наемате скъпоструващи сървъри, тъй като цялата информация в случая се съхранява и обработва на сървърите на Google.

Относителен минус — учебният материал за работа с BigQuery е много малък. На български език практически липсва. Реших да поправя този факт, затова ви запознавам с някои възможности на тази база данни.

За работата по-нататък трябва да изтеглите csv файл, който сега ще заредим в BigQuery.

Зареждане на данни в Google BigQuery от CSV файл

Ще се научим да зареждаме данни в BigQuery. Това умение ще е нужно за практическите примери на прилагане на заявките, описани по-нататък в статията.

  1. Избираме проекта, в който трябва да заредим таблицата. Натискаме бутона от менюто срещу името на проекта и избираме «Create new dataset». как-да-зареждаме-данни-в-BigQuery
  2. Въвеждаме име на набора с данни и натискаме «OK». как-да-зареждаме-данни-в-BigQuery
  3.  След това в създадения набор от данни натискаме «+». как-да-зареждаме-данни-в-BigQuery
  4.  В секцията «Choose destination» въвеждаме име на таблица и натискаме «Next» в долната част на диалоговия прозорец. как-да-зареждаме-данни-в-BigQuery
  5. В секцията «Select data» натискаме бутона «Choose file». как-да-зареждаме-данни-в-BigQuery
  6. Избираме изтегления по-рано файл и натискаме «Next». как-да-зареждаме-данни-в-BigQuery
  7. В секцията «Specify schema» описваме структурата на таблицата, добавяйки полета с натискане на бутон «Add fields». как-да-зареждаме-данни-в-BigQuery
  8. В секцията «Advanced options» ние трябва да определим разделител на полетата. В нашия случай, това е точка и запетая. Поставяме превключвателя «Field delimiter» в положение «Other» и в текстовото поле въвеждаме «;». Също така можем да въведем други настройки. Например, в нашия начален файл първия ред съдържа заглавия, които не трябва да се зареждат в таблицата. Затова в текстовото поле за настройка «Header rows to skip» въвеждаме 1 и натискаме «Submit». как-да-зареждаме-данни-в-BigQuery
  9. След това ще бъде стартиран процесът на зареждане на данни в създадената таблица. как-да-зареждаме-данни-в-BigQuery
  10. Натискаме бутона «Compose query». как-да-зареждаме-данни-в-BigQuery Сега вече вие можете да напишете своята първа заявка, към току-що качените данни:
    select *
    from Test_WF.test_structure
    как-да-зареждаме-данни-в-BigQuery

Прозоречните функции в Google BigQuery

Прозоречните функциите са, може би, най-полезните в BigQuery. Тяхното действие прилича на агрегиращите функции. Разликата се състои в това, че при използване на агрегиращи функции резултатът трябва да се групира по полетата, към които агрегиращите функции не се прилагат. А прозоречните функциите извършват подобни изчисления без групиране, те връщат агрегиран по нужния ви начин резултат за всеки ред. Така, този тип функции не сменя селекцията, а само добавя в нея нужната информация.

Нека да разясним, какво в този случай се подразбира под прозорец.

Прозорец е някакъв израз, описващ разделяне на цялата таблица на отделните набори редове, а също така описващи сортирането на редовете в тези набори.

Схематично работата на прозоречните функциите може да се покаже по следния начин. Вземаме функция SUM (Salary) OVER (PARTITION BY Development), стартирана в заредените по-рано тестови данни.

Резултат: Прозоречните-функции-в-Google-BigQuery Синтаксисът на всички прозоречни функции, съществуващи в Google BigQuery, изисква задължителния OVER. Тази точка дава възможност да се опишат три допълнителни параметъра за разделяне на данните от таблицата на прозорци.

  1. Partition позволява да се посочи признак за формиране на прозорец, тоест признак, по който вие ще разделяте данните за агрегиране.
  2. Order ви дава възможност да задавате сортирането вътре в прозореца.
  3. Window Frame дава възможност да се включва изместване на редовете вътре в прозореца по различни признаци, например, по време. Синтаксисът на прозоречните функциите изглежда така: Прозоречните-функции-в-Google-BigQuery Да разгледаме отделните блокове и тяхното предназначение.

1. PARTITION BY

Определя на какъв принцип ще бъде разделен масивът от данни на прозореца. Трябва да запишете списък от полета, по който искате да разделите таблицата, към която се отнася заявката.

При използване на прозоречните функции, вие не можете да групирате данни с помощта на GROUP BY в същата подзаявка, в която се прилага прозоречната функция.

Ако трябва да групирате получения резултат, използвайте GROUP BY в подзаявката, която се намира на нивото по-нагоре. По-подробно за това ще разкажа малко по-късно.

2. ORDER BY

ORDER BY позволява да се определя сортиране вътре във всеки създаден с помощта на PARTITION BY прозорец.

3. WINDOW FRAME

С помощта на тази незадължителна опция вие можете да давате отместване вътре във всеки прозорец.

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

sum(Salary) over (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

Синтаксисът на опцията Window Frame може да се опише отделно, той изглежда по следния начин:

{ROWS | RANGE} {BETWEEN start AND end | start | end}

Описанието на Window Frame започва с една от ключовите думи - ROWS или RANGE.

3.1. ROWS

Използва се, когато ви трябва прилагане на междуредово изчисляване. Например, сумиране на текущия ред от таблицата с няколко предишни. За целта е достатъчно да се въведе броят редове, които е необходимо да се агрегират. Пример за използване на ROWS в нашите тестови данни:

sum(Salary) over (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

3.2. RANGE

Дава възможност да се определя диапазон от значения за изчисляване. Например:

sum(Salary) over (order by Salary RANGE BETWEEN 1000 PRECEDING AND CURRENT ROW)

Формулата сумира заплатите на сътрудниците с всички заплати, които попадат в диапазона «с 1000 по-малко», отколкото е на сътрудника в текущия ред в рамките на всеки прозорец.

С избраните ключови думи ROW или RANGE, както вече забелязахте от примерите, трябва да се определя диапазонът на агрегиране по схема:

BETWEEN start AND end

3.3. BETWEEN START AND END

Start описва началното изместване в рамките на прозореца относно текущия ред. Start приема следните значения:

{UNBOUNDED PRECEDING | CURRENT ROW | expr PRECEDING | expr FOLLOWING}

където:

  • UNBOUNDED PRECEDING е първият ред от текущия прозорец;
  • CURRENT ROW е текущият ред;
  • expr PRECEDING — определя броя предишни, участващи в изчисленията редове. Вместо expr вие трябва да укажете броя предишни участващи в изчисленията редове относно текущия ред;
  • expr FOLLOWING — определя броя редове за агрегация сред тези, които следват текущия ред.

End описва крайното значение на диапазона на изместване и приема същите значения, както и Start. Например:

{UNBOUNDED FOLLOWING | CURRENT ROW | expr PRECEDING | expr FOLLOWING}

UNBOUNDED FOLLOWING в дадения случай се използва, за да зададе, в качеството на последен ред на агрегация, последния ред на текущия прозорец.

Пример на описание на Between Start and End във функция:

sum(Salary) over (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

В този случай, Start е «2 PRECEDING» а End — «CURRENT ROW». Тази функция казва - сумирай в рамките на текущия прозорец полетата Salary двата предишни реда и текущия. Доколкото точката PARTITION BY не е описан, текущият прозорец е цялата таблица.

Главното различие на прозоречните функции от агрегиращите е в това, че те не групират резултата, а посочват агрегираното значение за всеки отделен ред. Тази функционалност е много полезна за създаване на аналитични отчети, изчисления на пълзящ среден и нарастващ резултат.

Например, от следващата заявка ще бъде изкаран нарастващия резултат от пет реда.

SELECT name, value, SUM(value) OVER (ORDER BY value) AS RunningTotal
FROM
(SELECT "a" AS name, 0 AS value),
(SELECT "b" AS name, 1 AS value),
(SELECT "c" AS name, 2 AS value),
(SELECT "d" AS name, 3 AS value),
(SELECT "e" AS name, 4 AS value);

Резултат: Прозоречните-функции-в-Google-BigQuery

За да се пресметне нарастващия резултат по заплатите на сътрудниците от тестовите данни, качени от нас по-рано, пускаме следната заявка:

SELECT
CONCAT(First_name, " ", Last_name) as fullName,
Salary,
SUM(Salary) OVER (ORDER BY fullName) as runningTotal
FROM Test_WF.test_structure

Резултат: Прозоречните-функции-в-Google-BigQuery

Полето «runningTotal» върна нарастващия резултат по заплатите.

Внимание: за да получите нарастващия резултат, описвайте ORDER BY, иначе срещу всеки ред ще се показва сумата от заплатите на всички сътрудници.

Още един пример на използване на функцията sum като прозоречна, а не агрегираща. Например, искаме да разберем каква част от фонда на работна заплата във всеки отдел се пада на началника. Заявка:

SELECT
Department,
Position,
round(Salary / departmentSalarySum * 100 , 2) as headSalaryPercent
FROM
(SELECT
Position,
Department,
Salary,
SUM(Salary) OVER (PARTITION BY Department) as departmentSalarySum

FROM Test_WF.test_structure)

WHERE
Position in('Department head','CTO','CFO','Cheif Accountant')

Резултат: Прозоречните-функции-в-Google-BigQuery

Ако искате да правите някакви изчисления с резултати, които връща прозоречната функция, трябва да ги изнесете в заявка ниво по-нагоре, а самите изчисления да направите в отделна подзаявка.

SELECT
Position,
Department,
Salary,
SUM(Salary) OVER (PARTITION BY Department) as departmentSalarySum

FROM Test_WF.test_structure

Подзаявката връща следния резултат: Прозоречните-функции-в-Google-BigQuery

По-нататък вече работим с данните, които е върнала представената по-горе подзаявка. Изчисляваме процента от заплати на сътрудниците от фонд работна заплата на отдела:

SELECT
Department,
Position,
round(Salary / departmentSalarySum * 100 , 2) as headSalaryPercent[/code]
След това, остава само да се филтрира резултата от заявката, за да остане резултатна таблица съдържаща само ръководители на отдели.
[code lang="js"]WHERE

Да разгледаме всички съществуващи в момента на написване на статията прозоречни функции BigQuery.

1. Функции AVG(numeric_expr), COUNT(*), COUNT([DISTINCT] field), MAX(field), MIN(field), STDDEV(numeric_expr), SUM(field)

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

● AVG — средно аритметично;

● COUNT — брой значения;

● COUNT[Distinct] — брой уникални значения;

● MAX — максимално значение;

● MIN — минимално значение;

● STDDEV — средноквадратично отклонение (на английски: Root mean square, RMS или rms);

● SUM — сума.

Пример на заявка:

SELECT
  Department,
  AVG(Salary) OVER (PARTITION BY Department) as winAvg,
  COUNT(Salary) OVER (PARTITION BY Department) as winCount,
  COUNT(DISTINCT Position) OVER (PARTITION BY Department) as winCountDistinct,
  MAX(Salary) OVER (PARTITION BY Department) as winMax,
  MIN(Salary) OVER (PARTITION BY Department) as winMin,
  STDDEV(Salary) OVER (PARTITION BY Department) as winStDev,
  SUM(Salary) OVER (PARTITION BY Department) as winSum
FROM Test_WF.test_structure

Резултат от заявката: резултат-от-заявка-на-агрегираща-функция

Тази заявка ще върне всички изброени в описанията значения с разбивка по отдели за всеки ред от таблицата.

2. Функция CUME_DIST()

Връща кумулативно разпределение на значения в група значения. Пресмята се по формула / . За тази функция описанието ORDER BY в OVER е задължително. Пример на заявка:

SELECT
  Department,
  Salary,
  CUME_DIST(Salary) OVER (PARTITION BY Department ORDER BY Salary)
FROM Test_WF.test_structure
WHERE Department = 'Finance'

Резултат от заявката: функция-CUME-DIST

Частта от резултата на заявката, касаещ финансовия отдел, е отделен прозорец, тъй като сме указали PARTITION BY Department и в качеството на поле за пресмятане сме посочили Salary.

Също така сме дали сортиране по полето Salary, пресметнали сме значението на колоната f0_, което показва значения, върнати от функция CUME_DIST. Във финансовия отдел, както виждате, четири записа, от които три уникални по поле Salary (3000, 3900, 6700).

Във връзка с това, тези значения са изчислени по следния начин:

● 1 / 4 = 0,25 (1 ред на прозореца се дели на общото количество редове в прозореца);

● 3 / 4 = 0,75 (броя предишни редове в прозореца — това е 1 + 2, тъй като значението на втория ред на прозореца (3900) се среща два пъти. Дели се на общия брой редове в прозореца — 4.

● Пресмятането на третия ред е напълно идентичен на втория ред. Значението на полето Salary в тях е еднакво.

● 4 / 4 = 1 (количеството предишни значения — 3 плюс 1, тъй като значението на новия ред е уникален и по-рано не е отчитан. Дели се на общото количество значения в този прозорец.

3. Функция DENSE_RANK()

Връща ранг на значението на текущия ред относно значението на целия прозорец, в зависимост от зададеното сортиране.

За тази функция, описанието на сортирането в OVER с помощта на ORDER BY е задължително. Пример на заявка:

SELECT
Department,
Salary,
DENSE_RANK(Salary) OVER (PARTITION BY Department ORDER BY Salary)
FROM Test_WF.test_structure
WHERE Department in ('Finance','Sales')

Резултат от заявката: функция-DENSE-RANK

Както виждаме, резултата от заявката връща ранга на значение за всеки ред относно прозореца. В този случай таблицата се състои от два прозореца Department = ‘Finance’ и Department = ‘Sales’.

4. Функция FIRST_VALUE(field_name)

Връща значение от първия ред на прозореца, според наложеното сортиране. При това за тази функция описанието ORDER BY не е задължително.

Пример на заявка:

SELECT
Department,
Salary,
FIRST_VALUE(Salary) OVER (PARTITION BY Department)
FROM Test_WF.test_structure
WHERE Department in ('Finance','Sales')

Резултат от заявката: функция-FIRST-VALUE

Функцията връща първото значение на поле Salary за всеки прозорец. Това, че значението е най-голямо, е просто съвпадение в случая. Тъй като не сме определяли сортиране, функцията върна просто значението от първият ред на прозореца.

5. Функция LAG

Синтаксис:

LAG (field_name[, offset[, default_value]])

Връща значение от реда по посоченото поле, при това полето е изместено на посоченото във функцията брой редове до текущия ред.

Ако реда не е намерен, ще бъде върнато значението по подразбиране, значението по подразбиране задължително трябва да има формат INT64, тоест въведено с помощта на функция INTEGER(expr). Пример на заявка:

SELECT
Department,
Salary,
LAG(Salary, 2) OVER (PARTITION BY Department)
FROM Test_WF.test_structure
WHERE Department in ('Finance','Sales')

Резултат от заявката: функция-LAG

Функцията LAG върна значение с изместване от 2 реда нагоре относно текущия ред. По този начин бе върнато значение 6700 в ред 3, получено от поле Salary в ред 1.

6. Функция LAST_VALUE(field_name)

По смисъл функцията прилича на функция FIRST_VALUE, но в този случай функцията връща значение от последния ред на прозореца.

Пример на заявка:

SELECT
Department,
Salary,
LAST_VALUE(Salary) OVER (PARTITION BY Department)
FROM Test_WF.test_structure
WHERE Department in ('Finance','Sales')

Резултат от заявката: функция-LAST-VALUE

В редове 1-4 функцията върна значение 3000, тъй като това значение се намира в последния ред на прозореца ‘Finance’. В редове 5-11 ние получихме значение 3800, тъй като то се намира в последния ред на прозореца ‘Sales’.

7. Функция LEAD

Синтаксис:

(expr [, offset[, default_value]])

По смисъл прилича на функция LAG, но връща значение с изместване на посочения брой редове след текущия ред. Значението по подразбиране трябва да се посочва във формат INT64, тоест да е загърнато във функция INTEGER(), както е показано в примера за тази функция.

Пример на заявка:

SELECT
Department,
Salary,
LEAD(Salary, 2, INTEGER(0)) OVER (PARTITION BY Department)
FROM Test_WF.test_structure
WHERE Department in ('Finance','Sales')

Резултат от заявката: функция-LEAD

В първия ред, функцията върна значение 3900, тъй като това значение е получено от третия ред, който се намира на посоченото количество редове (2) след текущия първи ред. Аналогично, във втория ред, функцията върна значение 3000 от четвъртия ред.

8. Функция NTH_VALUE(expr, n)

Връща значение от ред в прозореца, съответстващ на зададения индекс вътре в този прозорец. В случай, че прозореца съдържа по-малко редове, отколкото сте посочили, като индекс, функцията връща null.

Пример на заявка:

SELECT
Department,
Salary,
NTH_VALUE(Salary, 2) OVER (PARTITION BY Department)
FROM Test_WF.test_structure
WHERE Department in ('Finance','Sales')

Резултат от заявката: функция-NTH-VALUE

В редове 1-4 функцията върна значение 3900, тъй като в качеството на индекс сме посочили 2. Cъответно, значението на втория ред в прозореца «Finance» съдържа 3900. Аналогично за прозорец «Sales». Функцията върна 4000, тъй като втория ред в този прозорец или шестия ред от нашата таблица, съдържа значение 4000.

9. Функция NTILE(num_buckets)

Дели прозореца на зададеното в num_buckets количество еднакви по брой редове раздели и връща номера на раздела в прозореца за всеки ред.

Пример на заявка:

SELECT
Department,
Salary,
NTILE(4) OVER (PARTITION BY Department)
FROM Test_WF.test_structure
WHERE Department in ('Finance','Sales')

Резултат от заявката: функция-NTILE

Доколкото в OVER в опция PARTITION BY в качеството на разделител на прозорци ние посочихме Department, функцията разби нашата таблица на два прозореца (във филтъра WHERE ние посочихме два отдела «Finance» и «Sales»). По нататък функция NTILE разби всеки прозорец на посочения брой раздели. Ние посочихме четири раздела. Първият прозорец «Finance» има само четири реда, съответно, всеки ред от прозореца се явява отдел и на всеки ред от дадения прозорец бе присвоен свой номер на раздел. Прозорецът «Sales» съдържа 7 реда. Ако разбием 7 реда на 4 раздела, се получава, че почти всички раздели, освен последния, ще се състоят от два реда. Съответно, редове 5 и 6 се явяват първи раздел в прозореца Sales, 7-8 редове — втори раздел, 9-10 редове — трети раздел, и последният, четвърти раздел, съдържа само един — 11 ред.

10. Функция PERCENT_RANK()

Връща ранг от текущия ред относно други редове от раздела в процентно изражение. Връща значение от 0 до 1 включително, първото значение от прозореца винаги е равно на 0.0, последните 1.0. За тази функция описанието ORDER BY е задължително. Пример на заявка:

SELECT
Department,
Salary,
PERCENT_RANK(Salary) OVER (PARTITION BY Department ORDER BY Salary)
FROM Test_WF.test_structure
WHERE Department in ('Finance','Sales')

Резултат от заявката: функция-PERCENT-RANK

Всеки първи ред на прозореца има нулев индекс. Индексът на всеки следващ ред се увеличава в случай, че значението на полето, посочено във функцията, е уникално за количествата предишни редове, в които значенията се повтарят. Ако значението се повтаря, то и индекса за пресмятане също се повтаря. Съответно, последният ред в прозореца има индекс за броя редове равен на единица. Нека по-подробно да разгледаме пресмятането на всеки ред в прозореца «Sales». Общият брой редове в този прозорец е 7, тоест индексът за последния е 7-1 = 6. Първият ред винаги има индекс 0. Пресмятането на петия ред или първия ред на прозореца «Sales», става по формула: Индексът на текущия ред / индекс на последния ред.

В нашия случай 0/6 = 0.0.

Шестият ред съдържа ново уникално значение на полето Salary. Индексът на този ред в този случай се пресмята по същия начин, както индекса на предишния, плюс една точка и е равен на единица. Следователно, функцията връща значение по формулата:

индексът на текущия ред / индекс на последния ред.

В нашия случай 1 / 6 = 0.16.

Тъй като значението в ред 7 се повтаря, то и индексът за този ред също се повтаря и е ще е равен на 1. Съответно, функцията ще върне значение 1 / 6 = 0.16.

Осмият ред отново съдържа уникално значение. Неговият индекс трябва да бъде увеличен относно индекса от миналия ред с броя на тези предишни редове, в които значението се е повтаряло. В нашия случай, значението 3600 се е повтаряло в два предишни реда. Значи, индексът от предишния първи ред трябва да увеличим с 2 и да получим индекса на осмия ред равен на 3. Пресметнатото значение ще е 3 / 6 = 0.5.

Деветият ред също съдържа ново значение, 4000. Увеличаваме индекса с 1, тъй като значението от миналия ред не се повтаря и съдържа само в един ред. Индексът на текущия ред е 3 + 1 = 4, а значението, което връща функцията: 4 / 6 = 0.66.

Десетият ред съдържа същото значение на полето Salary, както и предишния. Съответно, индексът остава 4 и значението на полето се пресмята така: 4 / 6 = 0.66.

Единадесетият ред съдържа ново значение, равно на 4800, доколкото миналото значение на полето Salary се повтаря. Два пъти увеличаваме индекса с 2. 4 + 2 = 6. Значението на полето, върнато от функцията PERCENT_RANK: 6 / 6 = 1.0

11. Функция PERCENTILE_CONT(percentile)

Връща значения, основани на линейна интерполация между значения в групата, след сортирането, описано в ORDER BY. Процентилът трябва да има значение в диапазона от 0 до 1.

За тази функция описанието на ORDER BY е задължително.

Пример на заявка:

SELECT
Department,
Salary,
PERCENTILE_CONT(0.5) OVER (PARTITION BY Department ORDER BY Salary)
FROM Test_WF.test_structure
WHERE Department in ('Finance','Sales')

Резултат от заявката: функция-PERCENTILE-CONT

Тъй като за процентил сме посочили 0,5, в нашия случай той ще е равен на медианата и връща значението на медианата. Вие можете да посочите всякакъв процентил в интервала от 0 до 1. Например, значението 0,25 ще съответства на втория квартил, а значението 0,75 — третия квартил.

12. Функция PERCENTILE_DISC(percentile)

Връща значение с най-малко кумулативно разпределение, по-голямо или равно на посоченото в процентила. За тази функция описанието на ORDER BY е задължително.

Пример на заявката:

SELECT
Department,
Salary,
PERCENTILE_DISC(0.25) OVER (PARTITION BY Department ORDER BY Salary)
FROM Test_WF.test_structure
WHERE Department in ('Finance','Sales')

Резултат от заявката: функция-PERCENTILE-DISC

Функцията връща значение, съответстващо посоченото в процентила. Доколкото ние въведохме в качеството на процентил 0,25, функцията върна значението на първия квартил. Значението 0,5 връща значението на втория квартил, който, на свой ред, е равен на медиана. Значението 0,75 връща значението на третия квартил.

13. Функция RANK()

Връща ранг на значение въз основа на сравнение с други значения в рамките на прозореца. Равнозначните значения се показват с еднакъв ранг. Рангът на следващото значение се увеличава с количеството значения, съответстващи на предишния ранг.

Например, ако две значения имат ранг 2, то рангът на следващото по величина значение ще е 4.

Ако трябва да получите непрекъснато ранжиране, използвайте функцията DENSE_RANK ().

Параметърът ORDER BY в OVER за тази функция е задължителен.

Пример на заявка:

SELECT
Department,
Salary,
RANK(Salary) OVER (PARTITION BY Department ORDER BY Salary)
FROM Test_WF.test_structure
WHERE Department in ('Finance','Sales')

Резултат от заявката: функция-RANK

Как функцията е пресметнала ранга за прозореца «Finance»?

Най-малкото значение се намира в първия ред и се равнява 3000, то има първи ранг.

По-нататък, във втория и третия ред има равнозначно значение 3900.

И двата реда имат ранг 2. Тъй като ранг 2 се повтаря два пъти, то рангът на следващото значение, намиращо се в четвъртия ред, се увеличава с 2 относно ранга в ред 3 и се равнява на 4.

14. Функция RATIO_TO_REPORT()

Връща отношението на текущото значение към сумата от значения на посоченото поле в дадения прозорец.

Пример на заявка:

SELECT
Department,
Salary,
RATIO_TO_REPORT(Salary) OVER (PARTITION BY Department ORDER BY Salary)
FROM Test_WF.test_structure
WHERE Department in ('Finance','Sales')

Резултат от заявката: функция-RATIO-TO-REPORT1

За първия ред вземаме текущото значение, тоест 3000, и делим на сумата от всички значения в прозореца (3000+3900+3900+6700).

Получаваме 0,17, тоест значението от първия ред е 17% от сумата от значенията в целия прозорец «Finance».

15. Функция ROW_NUMBER()

Връща номера на текущия ред в прозореца. Номерацията започва от единица.

Пример на заявка:

SELECT
Department,
Salary,
ROW_NUMBER() OVER (PARTITION BY Department)
FROM Test_WF.test_structure
WHERE Department in ('Finance','Sales')

Резултат от заявката: функция-ROW-NUMBER1

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

Този тип функции се поддържат не само в BigQuery. Голяма част от описаните функции, със запазване на описания по-горе синтаксис, работят и в PostgreSQL, и в MS SQL, а също така в другите достатъчно известни системи за управление на бази данни.

Коментари (0)

Оторизирай се, за да коментираш

Абонирай се

за най-интересен бюлетин за онлайн маркетинг

Най

обсъждано популярно четено