Ms sql 2011

FOR JSON

JSON
Укажите FOR JSON, чтобы вернуть результаты запроса в формате текста JSON. Также нужно указать один из следующих режимов JSON: AUTO или PATH. Дополнительные сведения о предложении FOR JSON см. в разделе Форматирование результатов запроса как JSON с помощью предложения FOR JSON (SQL Server).

AUTO
Форматируйте выходные данные JSON автоматически на основе структуры инструкции SELECT,
указав FOR JSON AUTO. Дополнительные сведения и примеры см. в разделе Автоматическое форматирование выходных данных JSON в режиме AUTO (SQL Server).

PATH
Чтобы сохранить полный контроль над форматом выходных данных JSON, используйтеFOR JSON PATH. Режим PATH позволяет создавать объекты-оболочки и вкладывать сложные свойства друг в друга. Дополнительные сведения и примеры см. в разделе Форматирование вложенных выходных данных JSON в режиме PATH (SQL Server).

INCLUDE_NULL_VALUES
Включайте значения NULL в выходные данные JSON, указав параметр INCLUDE_NULL_VALUES для предложения FOR JSON. Если не указать этот параметр, в выходные данные не будут включены свойства JSON для значений NULL в результатах запроса. Дополнительные сведения и примеры см. в статье Использование параметра INCLUDE_NULL_VALUES для включения значений NULL в выходные данные JSON (SQL Server).

ROOT
Добавьте один элемент верхнего уровня к выходным данным JSON, указав параметр ROOT с предложение FOR JSON. Если не указать параметр ROOT , выходные данные JSON не будут содержать корневой элемент. Дополнительные сведения и примеры см. в разделе Добавление корневого узла в выходные данные JSON с параметром ROOT (SQL Server).

WITHOUT_ARRAY_WRAPPER
Удалите квадратные скобки, в которые заключаются выходные данные JSON по умолчанию, указав параметр WITHOUT_ARRAY_WRAPPER с предложением FOR JSON. Если не указать этот параметр, выходные данные JSON будут заключены в квадратные скобки. Параметр WITHOUT_ARRAY_WRAPPER позволяет создать в качестве выходных данных единый объект JSON. Дополнительные сведения см. в разделе Удаление квадратных скобок из выходных данных JSON с помощью параметра WITHOUT_ARRAY_WRAPPER (SQL Server).

Дополнительные сведения см. в разделе Форматирование результатов запроса как JSON с помощью предложения FOR JSON (SQL Server).

SQL Справочник

Mysql distinct

SQL Ключевые слова
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 Функции
Функции строк
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
Функции чисел
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
Функции дат
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
Функции расширений
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 функции
Функции строк
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
Функции чисел
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
Функции дат
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR
Функции расширений
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME

MS Access функции
Функции строк
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase
Функции чисел
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val
Функции дат
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year
Другие функции
CurrentUser
Environ
IsDate
IsNull
IsNumeric

SQL ОператорыSQL Типы данныхSQL Краткий справочник

Подзапросы SQL-примеры

Isdate (transact-sql)

В этом разделе мы рассмотрим, как использовать подзапросы. У нас есть следующие две таблицы: ‘student‘ и ‘marks‘ с общим полем ‘StudentID‘:

студенты

отметки

Теперь нужно составить запрос, определяющий всех студентов, которые получают лучшие отметки, чем студент со StudentID — «V002». Но мы не знаем отметок студента «V002».

Поэтому нужно составить два SQL подзапроса в Select. Один запрос возвращает отметки (хранятся в поле «Total_marks») для «V002», а второй запрос выбирает учеников, которые получают лучшие оценки, чем результат первого запроса.

Первый запрос:

SELECT *  
FROM `marks`  
WHERE studentid = 'V002';

Результат запроса:

Используя результат этого запроса, мы написали еще один запрос, чтобы определить учеников, которые получают оценки лучше, чем 80.

Второй запрос:

SELECT a.studentid, a.name, b.total_marks
FROM student a, marks b
WHERE a.studentid = b.studentid
AND b.total_marks >80;

Результат запроса:

StudentIDV002Abhay

Можно объединить эти два запроса, вложив один запрос в другой. Подзапрос — это запрос внутри круглых скобок. Рассмотрим подзапроса в SQL пример:

Код SQL:

SELECT a.studentid, a.name, b.total_marks
FROM student a, marks b
WHERE a.studentid = b.studentid AND b.total_marks >
(SELECT total_marks
FROM marks
WHERE studentid =  'V002');

Результат запроса:

Графическое представление подзапроса SQL

Примеры HAVING

[psp] оффициальная прошивка 6.60 [2011, софт]

В следующей инструкции SQL перечислены сотрудники, зарегистрировавшие более 10 заказов:

Пример

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrdersFROM
(OrdersINNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastNameHAVING COUNT(Orders.OrderID) > 10;

В следующей инструкции SQL перечислены случаи, когда сотрудники «Davolio» или «Fuller» зарегистрировали более 25 заказов:

Пример

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrdersFROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = ‘Davolio’ OR LastName = ‘Fuller’GROUP BY LastNameHAVING
COUNT(Orders.OrderID) > 25;

Использование SELECT и предикатов IN, OR, BETWEEN, LIKE

Предикаты — слова IN, OR, BETWEEN, LIKE в секции WHERE — также позволяют выбрать определённые диапазоны значений (IN, OR, BETWEEN) или
значения в строках (LIKE), которые требуется выбрать из таблицы. Запросы с предикатами IN, OR, BETWEEN имеют
следующий синтаксис:

SELECT ИМЯ_СТОЛБЦА FROM ИМЯ_ТАБЛИЦЫ
WHERE ЗНАЧЕНИЕ
ПРЕДИКАТ (IN, OR, BETWEEN) (ЗНАЧЕНИЯ, УКАЗЫВАЮЩИЕ ДИАПАЗОН)

Запросы с предикатом LIKE имеют следующий синтаксис:

SELECT ИМЯ_СТОЛБЦА FROM ИМЯ_ТАБЛИЦЫ
WHERE ИМЯ_СТОЛБЦА LIKE
ВЫРАЖЕНИЕ

Пример 7. Пусть требуется выбрать из таблицы Staff имена, должности
и число отработанных лет сотрудников, работающих в отделах с номерами 20 или 84.
Это можно сделать следующим запросом (на MS SQL Server — с предваряющей конструкцией USE company1;):

SELECT Name, Job, Years
FROM Staff
WHERE Dept IN (20, 84)

Результат выполнения запроса:

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

Пример 8. Пусть теперь требуется выбрать из таблицы Staff те же данные,
что и в предыдущем примере. Запрос со словом OR аналогичен запросу со словом IN и перечислением интересующих
значений в скобках. Запрос будет следующим (на MS SQL Server — с предваряющей конструкцией USE company1;):

SELECT Name, Job, Years
FROM Staff
WHERE Dept=20 OR Dept=84

Пример 9. Выберем из той же таблицы имена, должности
и число отработанных лет сотрудников, зарплата которых между 15000 и 17000 включительно (на MS SQL Server — с предваряющей конструкцией USE company1;):

SELECT Name, Job, Years
FROM Staff
WHERE Salary BETWEEN 15000 AND 17000

Результат выполнения запроса:

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

Предикат LIKE используется для выборки тех строк, в значениях которых встречаются символы, указанные
после предиката между апострофами (‘).

Пример 10. Выберем из той же таблицы имена, должности
и число отработанных лет сотрудников, имена которых начинаются с буквы S и состоят из 7 символов
(на MS SQL Server — с предваряющей конструкцией USE company1;):

SELECT Name, Job, Years
FROM Staff
WHERE Name LIKE ‘S_ _ _ _ _ _’

Символ подчёркивания (_) означает любой символ. Результат выполнения запроса:

Пример 11. Выберем из той же таблицы имена, должности
и число отработанных лет сотрудников, имена которых начинаются с буквы S и содержат любые другие буквы
в любом количестве (на MS SQL Server — с предваряющей конструкцией USE company1;):

SELECT Name, Job, Years
FROM Staff
WHERE Name LIKE ‘S%’

Символ процентов (%) означает любое количество символов. Результат выполнения запроса:

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

Значения, указанные с использованием предикатов IN, OR, BETWEEN, LIKE можно инвертировать при помощи
слова NOT. Тогда запрашиваемые данные будут иметь противоположный смысл. Если мы используем NOT IN (20, 84),
то будут выведены данные сотрудников, которые работают во всех отделах, кроме имеющих номера 20 и 84.
С использованием NOT BETWEEN 15000 AND 17000 можно получить данные сотрудников, зарплата которых не
входит в интервал от 15000 до 17000. Запрос с NOT LIKE выведет данные сотрудников, чьи имена не начинаются
или не содержат символов, указанных с NOT LIKE.

Select Data With PDO (+ Prepared Statements)

The following example uses prepared statements.

It selects the id, firstname and lastname columns from the MyGuests table
where the lastname is «Doe», and
displays it in an HTML table:

Example (PDO)

<?phpecho «<table style=’border: solid 1px black;’>»;
echo «<tr><th>Id</th><th>Firstname</th><th>Lastname</th></tr>»;class TableRows extends RecursiveIteratorIterator {
function __construct($it) {     parent::__construct($it, self::LEAVES_ONLY);
} function current() {    return «<td style=’width:150px;border:1px solid black;’>» . parent::current(). «</td>»;
  }  function beginChildren() {     echo «<tr>»;
  } function endChildren() {     echo «</tr>» . «\n»;
  } } $servername = «localhost»;
$username = «username»;$password = «password»;$dbname = «myDBPDO»;
try {  $conn = new PDO(«mysql:host=$servername;dbname=$dbname», $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  $stmt = $conn->prepare(«SELECT id, firstname, lastname FROM MyGuests
WHERE lastname=’Doe'»);   $stmt->execute(); // set the resulting array to associative $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);   foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {
    echo $v;  }}catch(PDOException $e) {  echo «Error: » . $e->getMessage();}$conn = null;echo «</table>»;?>

❮ Previous
Next ❯

Индексы ROWID

Индексы ROWID — это объекты базы данных, обеспечивающие отображение всех значений столбца таблицы, а также идентификаторов ROWID всех строк таблицы, в которых содержатся значения столбца.

ROWID — это псевдостолбец, который является уникальным идентификатором строки в таблице и фактически описывает точное физическое расположение данной конкретной строки. На основе этой информации Oracle впоследствии может найти данные, связанные со строкой таблицы. При каждом перемещении, экспорте, импорте строки, а также при выполнении любых других операций, которые приводят к изменению ее местонахождения, изменяется ROWID строки, поскольку она занимает другое физическое положение. Для хранения данных ROWID требуется 80 бит (10 байт). Идентификаторы ROWID состоят из четырех компонентов: номера объекта (32 бита), относительного номера файла (10 бит), номера блока (22 бита) и номера строки (16 бит). Эти идентификаторы отображаются как 18-символьные последовательности, указывающие местонахождение данных в БД, причем каждый символ представлен в формате base-64, состоящем из символов A-Z, a-z, 0-9, + и /. Первые шесть символов – это номер объекта данных, следующие три – относительный номер файла, следующие шесть – номер блока, последние три – номер строки.

Пример:

В базе данных Oracle индексы используются для разных целей: для обеспечения уникальности значений в базе данных, для повышения производительности поиска записей в таблице и др. Производительность повышается благодаря тому, что в критерии поиска данных в таблице включается ссылка на индексированный столбец или столбцы. В Oracle индексы можно создавать по любому столбцу таблицы, кроме столбцов типа LONG. Индексы проводят различие между приложениями, для которых скорость не важна, и интенсивно функционирующими приложениями, что особенно касается работы с большими таблицами. Однако, прежде чем принять решение о создании индекса, необходимо взвесить все «за» и «против» в отношении производительности системы. Производительность не повысится, если просто ввести индекс и забыть о нем.

Рекомендации по созданию индексов ROWID:

Хотя наибольшее повышение производительности достигается созданием индекса по столбцу, все значения которого уникальны, похожий результат можно получить и для столбцов, содержащих одинаковые значения или NULL-значения. Для создания индекса совсем не обязательно, чтобы значения столбца были уникальны. Приведем ряд рекомендаций, обеспечивающих нужное повышение производительности при использовании стандартного индекса, а также рассмотрим вопросы, связанные с балансом между производительностью и расходованием дискового пространства при создании индекса.

Использование индексов для поиска информации в таблицах может дать значительное повышение производительности по сравнению с просмотром таблиц, столбцы которых неиндексированы. Однако выбрать правильный индекс совсем непросто. Конечно, для индексирования с помощью индекса В-дерева предпочтителен столбец, все значения которого уникальны, но и столбец, не отвечающий этим требованиям,— неплохой кандидат, если только одинаковые значения содержатся примерно в 10% его строк и никак не более. Столбцы-«переключатели», или «флаги», например те в которых хранятся сведения о поле человека, для индексов В-дерева не годятся Не подходят и те столбцы, которые используются для хранения небольшого числа «достоверных значений», а также хранящие какие-то признаки, например «достоверность» или «недостоверность», «активность» или «неактивность», «да» или «нет» и т. д, и т. п. Наконец, индексы с обратными ключами применяются, как правило, там, где установлен и функционирует Oracle Parallel Server и нужно до максимума повысить уровень параллельности в базе данных.

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

Примеры SQL LIKE

Следующая инструкция SQL выбирает всех клиентов с именем клиента, начинающимся с «a»:

SELECT * FROM Customers
WHERE CustomerName LIKE ‘a%’;

Следующая инструкция SQL выбирает всех клиентов с именем клиента, оканчивающимся на «a»:

SELECT * FROM Customers
WHERE CustomerName LIKE ‘%a’;

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

SELECT * FROM Customers
WHERE CustomerName LIKE ‘%or%’;

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

SELECT * FROM Customers
WHERE CustomerName LIKE ‘_r%’;

Следующая инструкция SQL выбирает всех клиентов с именем клиента, которое начинается с буквы «a» и имеет длину не менее 3 символов:

SELECT * FROM Customers
WHERE CustomerName LIKE ‘a__%’;

Следующая инструкция SQL выбирает всех клиентов с именем контакта, которое начинается с «a» и заканчивается на «o»

SELECT * FROM Customers
WHERE ContactName LIKE ‘a%o’;

Следующая инструкция SQL выбирает всех клиентов с именем клиента, которое не начинается с «a»:

SELECT для выбора столбцов таблицы

Запрос с оператором SELECT для выбора всех столбцов таблицы имеет следующий синтаксис:

SELECT * FROM ИМЯ_ТАБЛИЦЫ

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

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД
не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.

Работать будем с базой данных фирмы — Company1. Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными —
в файле по этой ссылке.

Пример 1. Итак, есть база данных фирмы — Company1. В ней есть таблица
Org (Структура фирмы) и Staff (Сотрудники). Требуется выбрать из таблиц все столбцы. Соответствующий
запрос для выбора всех столбцов из таблицы Org выглядит следующим образом (на MS SQL Server — с предваряющей конструкцией USE company1;):

SELECT * FROM Org

Этот запрос вернёт следующее (для увеличения картинки щёлкнуть по ней левой кнопкой мыши):

Запрос для выбора всех столбцов из таблицы Staff выглядит следующим образом (на MS SQL Server — с предваряющей конструкцией USE company1;):

SELECT * FROM Staff

Этот запрос вернёт следующее:

Для выбора определённых столбцов таблицы нам потребуется вместо звёздочки перечислить через запятую названия
всех столбцов, которые требуется выбрать:

SELECT ВЫБИРАЕМЫЕ_СТОЛБЦЫ FROM ИМЯ_ТАБЛИЦЫ

Пример 2. Пусть требуется из таблицы Org выбрать столбцы Depnumb и
Deptname, в которых содержатся данные соответственно о номерах отделов фирмы и об их названиях. Запрос для получения такой
выборки будет следующим (на MS SQL Server — с предваряющей конструкцией USE company1;):

SELECT Deptnumb, Deptname, FROM Org

А из таблицы Staff нужно выбрать столбцы Dept, Name, Job, в которых содержатся
соответственно данные о номере отдела, в котором трудится сотрудник, его имени и должности (на MS SQL Server — с предваряющей конструкцией USE company1;):

SELECT Dept, Name, Job FROM Staff

SQL Справочник

SQL ОператорыSQL Типы данныхSQL Краткий справочник

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

Синтаксис

Окно определяется с помощью обязательной инструкции 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», потому что больше не с чем складывать.

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