Учимся применять оконные функции

UPDATE несколько записей

Именно предложение WHERE определяет, сколько записей будет обновлено.

Следующая инструкция SQL обновит имя контакта до «Juan» для всех записей, где страна — «Mexico»:

Пример

UPDATE Customers
SET ContactName=’Juan’
WHERE Country=’Mexico’;

Выбор из таблицы «Customers» теперь будет выглядеть следующим образом:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Alfred Schmidt	Obere Str. 57	Frankfurt	12209	Germany
2	Ana Trujillo Emparedados y helados	Juan	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Juan	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden

Синтаксис

Окно определяется с помощью обязательной инструкции OVER(). Давайте рассмотрим синтаксис этой инструкции:

SELECT
Название функции (столбец для вычислений) 
OVER (
      PARTITION BY столбец для группировки
      ORDER BY столбец для сортировки
      ROWS или RANGE выражение для ограничения строк в пределах группы
      )

Теперь разберем как поведет себя множество строк при использовании того или иного ключевого слова функции. А тренироваться будем на простой табличке содержащей дату, канал с которого пришел пользователь и количество конверсий:

OVER()

Откроем окно при помощи OVER() и просуммируем столбец «Conversions»:

SELECT 
  Date
, Medium
, Conversions
, SUM(Conversions) OVER() AS 'Sum' 
FROM Orders

Мы использовали инструкцию OVER() без предложений. В таком варианте окном будет весь набор данных и никакая сортировка не применяется. Появился новый столбец «Sum» и для каждой строки выводится одно и то же значение 14. Это сквозная сумма всех значений колонки «Conversions».

PARTITION BY

Теперь применим инструкцию PARTITION BY, которая определяет столбец, по которому будет производиться группировка и является ключевой в разделении набора строк на окна:

SELECT 
  Date
, Medium
, Conversions
, SUM(Conversions) OVER(PARTITION BY Date) AS 'Sum' 
FROM Orders

Инструкция PARTITION BY сгруппировала строки по полю «Date». Теперь для каждой группы рассчитывается своя сумма значений столбца «Conversions».

ORDER BY

Попробуем отсортировать значения внутри окна при помощи ORDER BY:

SELECT 
  Date
, Medium
, Conversions
, SUM(Conversions) OVER(PARTITION BY Date ORDER BY Medium) AS 'Sum' 
FROM Orders

К предложению PARTITION BY добавилось ORDER BY по полю «Medium». Таким образом мы указали, что хотим видеть сумму не всех значений в окне, а для каждого значения «Conversions» сумму со всеми предыдущими. То есть мы посчитали нарастающий итог.

ROWS или RANGE

Инструкция ROWS позволяет ограничить строки в окне, указывая фиксированное количество строк, предшествующих или следующих за текущей.

Инструкция RANGE, в отличие от ROWS, работает не со строками, а с диапазоном строк в инструкции ORDER BY. То есть под одной строкой для RANGE могут пониматься несколько физических строк одинаковых по рангу.

Обе инструкции ROWS и RANGE всегда используются вместе с ORDER BY.

В выражении для ограничения строк ROWS или RANGE также можно использовать следующие ключевые слова:

• UNBOUNDED PRECEDING — указывает, что окно начинается с первой строки группы;
• UNBOUNDED FOLLOWING – с помощью данной инструкции можно указать, что окно заканчивается на последней строке группы;
• CURRENT ROW – инструкция указывает, что окно начинается или заканчивается на текущей строке;
• BETWEEN «граница окна» AND «граница окна» — указывает нижнюю и верхнюю границу окна;
• «Значение» PRECEDING – определяет число строк перед текущей строкой (не допускается в предложении RANGE).;
• «Значение» FOLLOWING — определяет число строк после текущей строки (не допускается в предложении RANGE).

Разберем на примере:

SELECT 
  Date
, Medium
, Conversions
, SUM(Conversions) OVER(PARTITION BY Date ORDER BY Conversions ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS 'Sum' 
FROM Orders

В данном случае сумма рассчитывается по текущей и следующей ячейке в окне. А последняя строка в окне имеет то же значение, что и столбец «Conversions», потому что больше не с чем складывать.

Комбинируя ключевые слова, вы можете подогнать диапазон работы оконной функции под вашу специфическую задачу.

Пример — сортировка по относительной позиции

Вы также можете использовать SQLite оператор ORDER BY для сортировки по относительной позиции в результирующем наборе, где первое поле в наборе результатов равно 1. Следующее поле равно 2 и т.д.

Например:

PgSQL

SELECT employee_id,
last_name,
first_name
FROM employees
WHERE employee_id > 45
ORDER BY 3 DESC;

1 2 3 4 5 6

SELECTemployee_id, last_name, first_name FROMemployees WHEREemployee_id>45 ORDERBY3DESC;

Этот SQLite пример ORDER BY будет возвращать результирующий набор, отсортированный по полю first_name в порядке убывания, поскольку поле first_name находится в позиции № 3 в результирующем наборе и будет эквивалентно следующему ORDER BY:

PgSQL

SELECT employee_id,
last_name,
first_name
FROM employees
WHERE employee_id > 45
ORDER BY first_name DESC;

1 2 3 4 5 6

SELECTemployee_id, last_name, first_name FROMemployees WHEREemployee_id>45 ORDERBYfirst_nameDESC;

SQL ORDER BY with expressions

The  clause can also accept expressions. For example, you can use the string function to construct full names of employees, and then sort the result set by the full name as the following query:

1 2 3 4 5

SELECT CONCAT(lastname,’,’,firstname)fullname FROM employees ORDERBYCONCAT(lastname,’,’,firstname);

The column alias is used for formatting the output of the result set. You can use the column alias in the ORDER BY clause rather than expression. The following query produces the same output:

1 2 3 4 5

SELECT CONCAT(lastname,’,’,firstname)fullname FROM employees ORDERBYCONCAT(lastname,’,’,firstname);

SQL ORDER BY with positional number

The positional number is the position of the column in the clause. The position number starts with 1, 2, 3, etc. SQL allows you to use these positional numbers rather than columns or expressions to sort the result set.

The following statement sorts the employees by hired date in descending order to find the most junior employees in the company:

1 2 3 4 5

SELECT lastname,firstname,DATE(hiredate) FROM employees ORDERBY3DESC;

SQL sorts the result set by column, which has positional number 3.

The positional number that refers to a specific column is changed when you change the columns in the  clause. This may lead to an unexpected result if you forget to change the positional number. Therefore, it is not recommended to use the positional number in the   clause. You only use it if you have no choice.

In this tutorial, you’ve learned how to use the  clause to sort result sets returned by a statement.

• Was this tutorial helpful ?

Examples

I think the easiest way to learn is to see examples. So, I’ll show you a few ways you can use the SQL ORDER BY clause in Oracle.

I’ll be using this table to perform the SELECT queries on.

SALESPERSON_ID	FIRST_NAME	SALARY	COMMISSION	HIRE_DATE
1	John	90000	1000	1-Jan-16
2	Sally	95000	500	5-Sep-16
3	Mark	101000	800	12-Aug-16
4	Tina	87000	900	24-Oct-16
5	Steve	100000	500	2-Feb-16
6	Michelle	120000	600	3-Dec-16
7	Alex	85000	(null)	17-Jan-16
8	Jo	115000	1200	30-Oct-16

Example 1 – ORDER BY Column Name

This example orders by a single column name.

SALESPERSON_ID	FIRST_NAME	SALARY	COMMISSION	HIRE_DATE
7	Alex	85000	(null)	17-Jan-16
8	Jo	115000	1200	30-Oct-16
1	John	90000	1000	1-Jan-16
3	Mark	101000	800	12-Aug-16
6	Michelle	120000	600	3-Dec-16
2	Sally	95000	500	5-Sep-16
5	Steve	100000	500	2-Feb-16
4	Tina	87000	900	24-Oct-16

All of the records are ordered by the first_name column. ASC or DESC was not specified, so by default, they are ordered in ASC order.

Example 2 – ORDER BY Column Name using ASC

This example orders by a column name and uses the ASC keyword.

SALESPERSON_ID	FIRST_NAME	SALARY	COMMISSION	HIRE_DATE
7	Alex	85000	(null)	17-Jan-16
4	Tina	87000	900	24-Oct-16
1	John	90000	1000	1-Jan-16
2	Sally	95000	500	5-Sep-16
5	Steve	100000	500	2-Feb-16
3	Mark	101000	800	12-Aug-16
8	Jo	115000	1200	30-Oct-16
6	Michelle	120000	600	3-Dec-16

This sorts the data in the table by salary in ascending order, which for numbers, is from smallest to highest.

Introduction to MySQL ORDER BY clause

When you use the statement to query data from a table, the result set is not sorted. It means that the rows in the result set can be in any order.

To sort the result set, you add the clause to the statement. The following illustrates the syntax of the  clause:

In this syntax, you specify the one or more columns which you want to sort after the clause.

The stands for ascending and the  stands for descending. You use to sort the result set in ascending order and to sort the result set in descending order.

This clause sorts the result set in ascending order:

And this clause sorts the result set in descending order:

By default, the clause uses if you don’t explicitly specify any option.

Therefore, the following clauses are equivalent:

and

If you want to sort the result set by multiple columns, you specify a comma-separated list of columns in the clause:

It is possible to sort the result by a column in ascending order, and then by another column in descending order:

In this case, the clause:

• First, sort the result set by the values in the in ascending order.
• Then, sort the sorted result set by the values in the   in descending order. Note that the order of values in the will not change in this step, only the order of values in the changes.

Note that the clause is always evaluated after the and clause.

SQL Учебник

SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии

SQL ORDER BY clause examples

We will use the table in the sample database for the demonstration.

1) Using SQL clause to sort values in one column example

The following statement retrieves the employee id, first name, last name, hire date, and salary from the table:

It seems that the rows appear as they are stored in the   table. To sort employees by first names alphabetically, you add an clause to query as follows:

The result set now is sorted by the column.

2) Using SQL  clause to sort values in multiple columns example

To sort by the employees by the first name in ascending order and the last name in descending order, you use the following statement:

First, the database system sorts the result set by the first name in ascending order, then it sorts the sorted result set by the last name in descending order. Notice the change in position of two employees: and

3) Using SQL clause to sort values in a numeric column example

SQL allows you to sort data alphabetically as shown in the previous example and also sort data numerically. For example, the following statement selects employee data and sorts the result set by salary in the descending order:

4) Using SQL to sort dates example

Besides the character and numeric, SQL allows you to sort the result set by date. The following statement sorts the employees by values in the column in the ascending order.

To view the latest employees who have just joined the company, you sort the employees by the hire dates in the descending order as shown in the following statement:

In this tutorial, you have learned how to use the SQL clause to sort the result set based on one or more columns in the ascending or descending order.

RDBMS

RDBMS stands for Relational Database Management System.

RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.

Look at the «Customers» table:

Example

SELECT * FROM Customers;

Every table is broken up into smaller entities called fields. The fields in
the Customers table consist of CustomerID, CustomerName, ContactName, Address,
City, PostalCode and Country. A field is a column in a table that is designed to maintain
specific information about every record in the table.

A record, also called a row, is each individual entry that exists in a table.
For example, there are 91 records in the above Customers table. A record is a
horizontal entity in a table.

A column is a vertical entity in a table that contains all information
associated with a specific field in a table.

❮ Previous
Next ❯

MySQL ORDER BY Descending

To sort data in Descending order, use Order By statement followed by the DESC keyword. The following are the list of ways we can sort data in Descending order.

For example, If you are searching for shoes on Amazon. If you type shoe in the search bar, it displays shoes by Rating. It means, Shoes are sorted as per the Rating. Technically,

MySQL Sort in Descending Order Example

In this MySQL Order By Desc example, we are going to sort customer’s data in Descending Order using the Sales column.

From the above screenshot, you can see data sorted by Sales in Descending order.

MySQL Order By Multiple Columns in Descending Order

In this order by DESC example, we are sorting the Data using multiple columns. First, data sorted by Education in Descending Order and then sorted by Yearly Income in Descending Order.

Sort in Descending Order using Alias Column

In this MySQL Order By Desc example, We are going to sort the table Data in Descending Order using the Alias Column Name.

We added 12500 to each yearly income column and used Alias to assign a New Income name. Next, we used the Alias name in the Order By clause. It means, Data sort by New Income in Descending Order.

SQL Server ORDER BY clause example

We will use the table in the sample database from the demonstration.

A) Sort a result set by one column in ascending order

The following statement sorts the customer list by the first name in ascending order:

In this example, because we did not specify or , the clause used by default.

B) Sort a result set by one column in descending order

The following statement sorts the customer list by the first name in descending order.

In this example, because we specified the explicitly, the clause sorted the result set by values in the column in the descending order.

C) Sort a result set by multiple columns

The following statement retrieves the first name, last name, and city of the customers. It sorts the customer list by the city first and then by the first name.

D) Sort a result set by multiple columns and different orders

The following statement sorts the customers by the city in descending order and the sort the sorted result set by the first name in ascending order.

E) Sort a result set by a column that is not in the select list

It is possible to sort the result set by a column that does not appear on the select list. For example, the following statement sorts the customer by the state even though the column does not appear on the select list.

Note that the column is defined in the   table. If it was not, then you would have an invalid query.

F) Sort a result set by an expression

The function returns the number of characters of a string. The following statement uses the function in the clause to retrieve a customer list sorted by the length of the first name.

G) Sort by ordinal positions of columns

SQL Server allows you to sort the result set based on the ordinal positions of columns that appear in the select list.

The following statement sorts the customers by first name and last name. But instead of specifying the column names explicitly, it uses the ordinal positions of the columns:

In this example, 1 means the column and 2 means the column.

Using the ordinal positions of columns in the clause is considered as bad programming practice for a couple of reasons.

• First, the columns in a table don’t have ordinal positions and need to be referenced by name.
• Second, when you modify the select list, you may forget to make the corresponding changes in the clause.

Therefore, it is a good practice to always specify the column names explicitly in the clause.

In this tutorial, you have learned how to use the SQL Server clause to sort a result set by columns in ascending or descending order.

SQL References

SQL Keywords
ADD
ADD CONSTRAINT
ALTER
ALTER COLUMN
ALTER TABLE
ALL
AND
ANY
AS
ASC
BACKUP DATABASE
BETWEEN
CASE
CHECK
COLUMN
CONSTRAINT
CREATE
CREATE DATABASE
CREATE INDEX
CREATE OR REPLACE VIEW
CREATE TABLE
CREATE PROCEDURE
CREATE UNIQUE INDEX
CREATE VIEW
DATABASE
DEFAULT
DELETE
DESC
DISTINCT
DROP
DROP COLUMN
DROP CONSTRAINT
DROP DATABASE
DROP DEFAULT
DROP INDEX
DROP TABLE
DROP VIEW
EXEC
EXISTS
FOREIGN KEY
FROM
FULL OUTER JOIN
GROUP BY
HAVING
IN
INDEX
INNER JOIN
INSERT INTO
INSERT INTO SELECT
IS NULL
IS NOT NULL
JOIN
LEFT JOIN
LIKE
LIMIT
NOT
NOT NULL
OR
ORDER BY
OUTER JOIN
PRIMARY KEY
PROCEDURE
RIGHT JOIN
ROWNUM
SELECT
SELECT DISTINCT
SELECT INTO
SELECT TOP
SET
TABLE
TOP
TRUNCATE TABLE
UNION
UNION ALL
UNIQUE
UPDATE
VALUES
VIEW
WHERE

MySQL Functions
String Functions
ASCII
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
CONCAT_WS
FIELD
FIND_IN_SET
FORMAT
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MID
POSITION
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UPPER

Numeric Functions
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
CEIL
CEILING
COS
COT
COUNT
DEGREES
DIV
EXP
FLOOR
GREATEST
LEAST
LN
LOG
LOG10
LOG2
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SUM
TAN
TRUNCATE

Date Functions
ADDDATE
ADDTIME
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIME_FORMAT
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TO_DAYS
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK

Advanced Functions
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION

SQL Server Functions
String Functions
ASCII
CHAR
CHARINDEX
CONCAT
Concat with +
CONCAT_WS
DATALENGTH
DIFFERENCE
FORMAT
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
QUOTENAME
REPLACE
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
TRANSLATE
TRIM
UNICODE
UPPER

Numeric Functions
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN

Date Functions
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR

Advanced Functions
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME

MS Access Functions
String Functions
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase

Numeric Functions
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val

Date Functions
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year

Other Functions
CurrentUser
Environ
IsDate
IsNull
IsNumeric

SQL Quick Ref

SQL INNER JOIN

Команда возвращает строки, имеющие совпадающие значения в обеих таблицах.

Следующий SQL выбирает все заказы с информацией о клиенте:

Пример

SELECT Orders.OrderID, Customers.CustomerNameFROM OrdersINNER JOIN
Customers ON Orders.CustomerID = Customers.CustomerID;

Примечание: Ключевое слово INNER JOIN выбирает все строки из обеих таблиц до тех пор, пока существует соответствие между столбцами.
Если в таблице «Заказы» есть записи, которые не имеют совпадений в разделе «Клиенты», то эти заказы не будут показаны!

Следующая инструкция SQL выбирает все заказы с информацией о клиенте и грузоотправителе:

Пример

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperNameFROM
((OrdersINNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

Introduction to the SQL Server ORDER BY clause

When you use the statement to query data from a table, the order of rows in the result set is not guaranteed. It means that SQL Server can return a result set with an unspecified order of rows.

The only way for you to guarantee that the rows in the result set are sorted is to use the clause. The following illustrates the clause syntax:

In this syntax:

|

First, you specify a column name or an expression on which to sort the result set of the query. If you specify multiple columns, the result set is sorted by the first column and then that sorted result set is sorted by the second column, and so on.

The columns that appear in the clause must correspond to either column in the select list or to columns defined in the table specified in the clause.

|

Second, use or to specify the whether the values in the specified column should be sorted in ascending or descending order.

The sorts the result from the lowest value to the highest value while the  sorts the result set from the highest value to the lowest one.

If you don’t explicitly specify or , SQL Server uses as the default sort order. Also, SQL Server treats NULL as the lowest values.

When processing the statement that has an clause, the clause is the very last clause to be processed.

Синтаксис

Синтаксис для предложения ORDER BY в PostgreSQL:

SELECT expressions
FROM tables

ORDER BY expression ;

Параметры или аргументы

expressions

Столбцы или вычисления, которые вы хотите получить.

tables

Таблицы, из которых вы хотите получить записи. В операторе FROM должна быть указана хотя бы одна таблица.

WHERE conditions

Необязательный. Условия, которые должны быть выполнены для записей, которые будут выбраны.

ASC

Необязательный. Он сортирует результирующий набор в порядке возрастания по expression (по умолчанию, если модификатор не указан).

DESC

Необязательный. Он сортирует результирующий набор в порядке убывания по expression.

NULLS FIRST

Необязательный. Если указано, все значения NULL сортируются перед значениями, отличных от NULL, в результирующем наборе.

NULLS LAST

Необязательный. Если указано, все значения NULL сортируются после значений, отличных от NULL, в результирующем наборе.

Кейс. Модели атрибуции

Благодаря модели атрибуции можно обоснованно оценить вклад каждого канала в достижение конверсии. Давайте попробуем посчитать две разных модели атрибуции с помощью оконных функций.

У нас есть таблица с id посетителя (им может быть Client ID, номер телефона и тп.), датами и количеством посещений сайта, а также с информацией о достигнутых конверсиях.

Первый клик

В Google Analytics стандартной моделью атрибуции является последний непрямой клик. И в данном случае 100% ценности конверсии присваивается последнему каналу в цепочке взаимодействий.

Попробуем посчитать модель по первому взаимодействию, когда 100% ценности конверсии присваивается первому каналу в цепочке при помощи функции FIRST_VALUE.

SELECT 
  Date
, Client_ID
, Medium
, FIRST_VALUE(Medium) OVER(PARTITION BY Client_ID ORDER BY Date) AS 'First_Click'
, Sessions
, Conversions
FROM Orders

Рядом со столбцом «Medium» появился новый столбец «First_Click», в котором указан канал в первый раз приведший посетителя к нам на сайт и вся ценность зачтена данному каналу.

Произведем агрегацию и получим отчет.

WITH First AS (
SELECT 
  Date 
, Client_ID 
, Medium 
, FIRST_VALUE(Medium) OVER(PARTITION BY Client_ID ORDER BY Date) AS 'First_Click' 
, Sessions 
, Conversions
FROM Orders
)

SELECT
  First_Click
, SUM(Conversions) AS 'Conversions'
FROM First
GROUP BY First_Click

С учетом давности взаимодействий

В этом случае работает правило: чем ближе к конверсии находится точка взаимодействия, тем более ценной она считается. Попробуем рассчитать эту модель при помощи функции DENSE_RANK.

SELECT 
  Date
, Client_ID
, Medium
-- Присваиваем ранг в зависимости от близости к дате конверсии
, DENSE_RANK() OVER(PARTITION BY Client_ID ORDER BY Date) AS 'Ranks'
, Sessions
, Conversions
FROM Orders

Рядом со столбцом «Medium» появился новый столбец «Ranks», в котором указан ранг каждой строки в зависимости от близости к дате конверсии.

Теперь используем этот запрос для того, чтобы распределить ценность равную 1 (100%) по всем точкам на пути к конверсии.

SELECT 
  Date
, Client_ID
, Medium
-- Делим ранг определенной строки на сумму рангов по пользователю
, ROUND(CAST(DENSE_RANK() OVER(PARTITION BY Client_ID ORDER BY Date) AS FLOAT) / CAST(SUM(ranks) OVER(PARTITION BY Client_ID) AS FLOAT), 2) AS 'Time_Decay' 
, Sessions
, Conversions
FROM (
      SELECT 
      Date
    , Client_ID
    , Medium
    -- Присваиваем ранг в зависимости от близости к дате конверсии
    , DENSE_RANK() OVER(PARTITION BY Client_ID ORDER BY Date) AS 'Ranks'
    , Sessions
    , Conversions
      FROM Orders
     ) rank_table

Рядом со столбцом «Medium» появился новый столбец «Time_Decay» с распределенной ценностью.

И теперь, если сделать агрегацию, можно увидеть как распределилась ценность по каналам.

WITH Ranks AS (
SELECT 
  Date
, Client_ID
, Medium
-- Делим ранг определенной строки на сумму рангов по пользователю
, ROUND(CAST(DENSE_RANK() OVER(PARTITION BY Client_ID ORDER BY Date) AS FLOAT) / CAST(SUM(ranks) OVER(PARTITION BY Client_ID) AS FLOAT), 2) AS 'Time_Decay' 
, Sessions
, Conversions
FROM (
      SELECT 
      Date
    , Client_ID
    , Medium
  -- Присваиваем ранг в зависимости от близости к дате конверсии
    , DENSE_RANK() OVER(PARTITION BY Client_ID ORDER BY Date) AS 'Ranks'
    , Sessions
    , Conversions
      FROM Orders
     ) rank_table
)

SELECT 
  Medium
, SUM(Time_Decay) AS 'Value'
, SUM(Conversions) AS 'Conversions'
FROM Ranks
GROUP BY Medium
ORDER BY Value DESC

Из получившегося отчета видно, что самым весомым каналом является канал «cpc», а канал «cpa», который был бы исключен при применении стандартной модели атрибуции, тоже получил свою долю при распределении ценности.

Полезные ссылки:

• SELECT — предложение OVER (Transact-SQL)
• Как работать с оконными функциями в Google BigQuery — подробное руководство
• Модель атрибуции на основе онлайн/офлайн данных в Google BigQuery

Роман Романчук

Digital-аналитик и иногда спортсмен.

• Учимся применять оконные функции — 29.09.2020
• Автоматизация отчетности при помощи SQL и Power BI — 05.04.2020
• Зачем аналитику нужно программирование на SQL? — 22.10.2019

SQL ORDER BY examples

Let’s take look at some examples of sorting result sets using the  clause.

SQL ORDER BY one column example

For example, you can sort all employees by the last name in ascending order as the following query:

1 2 3 4 5

SELECT lastname,firstname FROM employees ORDERBYlastname;

SQL ORDER BY multiple columns example

You can sort the result set by multiple columns. The following query sorts employees by the last name in descending order and first name in ascending order:

1 2 3 4 5

SELECT lastname,firstname FROM employees ORDERBYlastnameDESC,firstnameASC;

The database engine sorts the result set based on the last name in descending order first. And then it sorts the sorted result set by the first name in ascending order to produce the final result set.