Sql oracle залишок від ділення. Довідник з MySQL

Це ще одна часто зустрічається завдання. Основний принцип полягає в накопиченні значень одного атрибута (агрегіруемий елемента) на основі упорядкування по іншому атрибуту або атрибутам (елемент упорядкування), можливо при наявності секцій рядків, визначених на основі ще одного атрибута або атрибутів (елемент секціонування). У житті існує багато прикладів обчислення наростаючих підсумків, наприклад обчислення залишків на банківських рахунках, відстеження наявності товарів на складі або поточних цифр продажів і т.п.

До SQL Server 2012 рішення, засновані на наборах і використовуються для обчислення наростаючих підсумків, були виключно ресурсоємними. Тому люди зазвичай зверталися до ітеративним рішеннями, які працювали нешвидко, але в деяких ситуаціях все-таки швидше, ніж рішення на основі наборів. Завдяки розширенню підтримки віконних функцій в SQL Server 2012 наростаючі підсумки можна обчислювати, використовуючи простий заснований на наборах код, продуктивність якого набагато вище, ніж в старих рішеннях на основі T-SQL - як заснованих на наборах, так і ітеративних. Я міг би показати нове рішення і перейти до наступного розділу; але щоб ви по-справжньому зрозуміли масштаб змін, я опишу старі способи і порівняю їх продуктивність з новим підходом. Природно, ви маєте право прочитати тільки першу частину, яка описує новий підхід, і пропустити решту статті.

Для демонстрації різних рішень я скористаюся залишками на рахунках. Ось код, який створює і наповнює таблицю Transactions невеликим об'ємом тестових даних:

SET NOCOUNT ON; USE TSQL2012; IF OBJECT_ID ( "dbo.Transactions", "U") IS NOT NULL DROP TABLE dbo.Transactions; CREATE TABLE dbo.Transactions (actid INT NOT NULL, - стовпець секціонування tranid INT NOT NULL, - стовпець упорядкування val MONEY NOT NULL, - міра CONSTRAINT PK_Transactions PRIMARY KEY (actid, tranid)); GO - невеликий набір тестових даних INSERT INTO dbo.Transactions (actid, tranid, val) VALUES (1, 1, 4.00), (1, 2, -2.00), (1, 3, 5.00), (1, 4, 2.00), (1, 5, 1.00), (1, 6, 3.00), (1, 7, -4.00), (1, 8, -1.00), (1, 9, -2.00), (1, 10 , -3.00), (2, 1, 2.00), (2, 2, 1.00), (2, 3, 5.00), (2, 4, 1.00), (2, 5, -5.00), (2, 6 , 4.00), (2, 7, 2.00), (2, 8, -4.00), (2, 9, -5.00), (2, 10, 4.00), (3, 1, -3.00), (3, 2, 3.00), (3, 3, -2.00), (3, 4, 1.00), (3, 5, 4.00), (3, 6, -1.00), (3, 7, 5.00), (3, 8, 3.00), (3, 9, 5.00), (3, 10, -3.00);

Кожен рядок таблиці являє банківську операцію на рахунку. Депозити відзначаються як транзакції з позитивним значенням у стовпці val, а зняття коштів - як негативне значення транзакції. Наше завдання - обчислити залишок на рахунку в кожен момент часу шляхом акумулювання сум операцій в рядку val при упорядкуванні по стовпцю tranid, причому це потрібно зробити для кожного рахунку окремо. Бажаний результат повинен виглядати так:

Для тестування обох рішень потрібен більший обсяг даних. Це можна зробити за допомогою такого запиту:

DECLARE @num_partitions AS INT \u003d 10, @rows_per_partition AS INT \u003d 10000; TRUNCATE TABLE dbo.Transactions; INSERT INTO dbo.Transactions WITH (TABLOCK) (actid, tranid, val) SELECT NP.n, RPP.n, (ABS (CHECKSUM (NEWID ())% 2) * 2-1) * (1 + ABS (CHECKSUM ( NEWID ())% 5)) FROM dbo.GetNums (1, @num_partitions) AS NP CROSS JOIN dbo.GetNums (1, @rows_per_partition) AS RPP;

Можете задати свої вхідні дані, щоб змінити число секцій (рахунків) та рядків (транзакцій) в секції.

Засноване на наборах рішення з використанням віконних функцій

Я почну розповідь з рішення на основі наборів, в якому використовується віконна функція агрегування SUM. Визначення вікна тут досить наочно: потрібно секціонувати вікно по actid, упорядкувати по tranid і фільтром відібрати рядки в кадрі з крайньої нижньої (UNBOUNDED PRECEDING) до поточної. Ось відповідний запит:

SELECT actid, tranid, val, SUM (val) OVER (PARTITION BY actid ORDER BY tranid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance FROM dbo.Transactions;

Цей код не тільки простий і прямолінійний - він і виконується швидко. План цього запиту показаний на малюнку:

У таблиці є кластерізованний індекс, який відповідає вимогам POC і придатний для використання віконними функціями. Зокрема, список ключів індексу засновано на елементі секціонування (actid), за яким слідує елемент впорядкування (tranid), також для забезпечення покриття індекс включає всі інші стовпці в запиті (val). План містить упорядкований перегляд, за яким слід обчислення номера рядка для внутрішніх потреб, а потім - віконного агрегату. Так як є POC-індекс, оптимізаторові не потрібно додавати до плану оператор сортування. Це дуже ефективний план. До того ж він лінійно масштабується. Пізніше, коли я покажу результати порівняння продуктивності, ви побачите, наскільки ефективніше цей спосіб в порівнянні зі старими рішеннями.

До SQL Server 2012 використовувалися або вкладені запити, або з'єднання. При використанні вкладеного запиту наростаючі підсумки обчислюються шляхом фільтрації всіх рядків з тим же значенням actid, що і у зовнішній рядку, і значенням tranid, яке менше або дорівнює значення у зовнішній рядку. Потім до відфільтрованим рядках застосовується агрегування. Ось відповідний запит:

Аналогічний підхід можна реалізувати з застосуванням з'єднань. Використовується той же предикат, що і в реченні WHERE вкладеного запиту в реченні ON з'єднання. В цьому випадку для N-ой транзакції одного і того ж рахунку A в екземплярі, позначеному як T1, ви будете знаходити N відповідностей в екземплярі T2, при цьому номери транзакцій пробігають від 1 до N. У результаті зіставлення рядка в T1 повторюються, тому потрібно згрупувати рядки по всіх елементах з T1, щоб отримати інформацію про поточну транзакції і застосувати агрегування до атрибуту val з T2 для обчислення наростаючого підсумку. Готовий запит виглядає приблизно так:

SELECT T1.actid, T1.tranid, T1.val, SUM (T2.val) AS balance FROM dbo.Transactions AS T1 JOIN dbo.Transactions AS T2 ON T2.actid \u003d T1.actid AND T2.tranid<= T1.tranid GROUP BY T1.actid, T1.tranid, T1.val;

На малюнку нижче наведені плани обох рішень:

Зауважте, що в обох випадках в екземплярі T1 виконується повний перегляд кластерізованного індексу. Потім для кожного рядка в плані передбачена операція пошуку в індексі початку розділу поточного рахунку на кінцевій сторінці індексу, при цьому зчитуються всі транзакції, в яких T2.tranid менше або дорівнює T1.tranid. Точка, де відбувається агрегування рядків, трохи відрізняється в планах, але число лічених рядків однаково.

Щоб зрозуміти, скільки рядків проглядається, треба врахувати число елементів даних. Нехай p - число секцій (рахунків), а r - число рядків в секції (транзакції). Тоді число рядків в таблиці приблизно дорівнює p * r, якщо вважати, що транзакції розподілені по рахунках рівномірно. Таким чином, наведений у верхній частині перегляд охоплює p * r рядків. Але найбільше нас цікавить те, що відбувається в Ітератор Nested Loops.

У кожній секції план передбачає читання 1 + 2 + ... + r рядків, що в сумі становить (r + r * 2) / 2. Загальна кількість оброблюваних в планах рядків становить p * r + p * (r + r2) / 2. Це означає, що число операцій в плані зростає в квадраті зі збільшенням розміру секції, тобто якщо збільшити розмір секції в f раз, обсяг роботи збільшиться приблизно в f 2 раз. Це погано. Для прикладу 100 рядках відповідає 10 тис. Рядків, а тисячі рядків відповідає мільйон і т.д. Простіше кажучи це призводить до сильного уповільнення виконання запитів при немаленькому розмірі секції, тому що квадратична функція зростає дуже швидко. Подібні рішення працюють задовільно при декількох десятках рядків на секцію, але не більше.

Рішення з використанням курсору

Рішення на основі курсора реалізуються «в лоб». Оголошується курсор на основі запиту, що упорядковує дані по actid і tranid. Після цього виконується ітеративний прохід записів курсора. При виявленні нового рахунку скидається змінна, яка містить агрегат. У кожній ітерації в змінну додається сума нової транзакції, після цього рядок зберігається в табличній змінної з інформацією про поточну транзакції плюс поточне значення наростаючого підсумку. Після итеративного проходу повертається результат з табличній змінної. Ось код закінченого рішення:

DECLARE @Result AS TABLE (actid INT, tranid INT, val MONEY, balance MONEY); DECLARE @actid AS INT, @prvactid AS INT, @tranid AS INT, @val AS MONEY, @balance AS MONEY; DECLARE C CURSOR FAST_FORWARD FOR SELECT actid, tranid, val FROM dbo.Transactions ORDER BY actid, tranid; OPEN C FETCH NEXT FROM C INTO @actid, @tranid, @val; SELECT @prvactid \u003d @actid, @balance \u003d 0; WHILE @@ fetch_status \u003d 0 BEGIN IF @actid<> @prvactid SELECT @prvactid \u003d @actid, @balance \u003d 0; SET @balance \u003d @balance + @val; INSERT INTO @Result VALUES (@actid, @tranid, @val, @balance); FETCH NEXT FROM C INTO @actid, @tranid, @val; END CLOSE C; DEALLOCATE C; SELECT * FROM @Result;

План запиту з використанням курсору показаний на малюнку:

Цей план масштабується лінійно, тому що дані з індексу проглядаються тільки раз в певному порядку. Також у кожній операції отримання рядка з курсору приблизно однакова вартість в розрахунку на кожен рядок. Якщо прийняти навантаження, створювану при обробці одного рядка курсору, рівній g, вартість цього рішення можна оцінити як p * r + p * r * g (як ви пам'ятаєте, p - це число секцій, а r - число рядків в секції). Так що, якщо збільшити число рядків на секцію в f раз, навантаження на систему складе p * r * f + p * r * f * g, тобто буде зростати лінійно. Вартість обробки в розрахунку на рядок висока, але через лінійного характеру масштабування, з певного розміру секції це рішення буде демонструвати кращу масштабованість, ніж рішення на основі вкладених запитів і з'єднань через квадратичного масштабування цих рішень. Проведене мною вимір продуктивності показало, що число, коли рішення з курсором працює швидше, так само кільком сотням рядків на секцію.

Незважаючи на виграш в продуктивності, що забезпечується рішеннями на основі курсора, в загальному випадку їх треба уникати, бо вони не є реляційними.

Рішення на основі CLR

Одне можливе рішення на основі CLR (Common Language Runtime) по суті є однією з форм вирішення з використанням курсору. Різниця в тому, що замість використання курсора T-SQL, який витрачає багато ресурсів на отримання чергового рядка і виконання ітерації, застосовуються ітераціі.NET SQLDataReader і.NET, які працюють набагато швидше. Одна з особливостей CLR яка робить цей варіант швидше, полягає в тому, що результуюча рядок в тимчасовій таблиці не потрібна - результати пересилаються безпосередньо викликає процесу. Логіка рішення на основі CLR схожа на логіку рішення з використанням курсору і T-SQL. Ось код C #, який визначає процедуру, що рішення:

Using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures (public static void AccountBalances () (using (SqlConnection conn \u003d new SqlConnection ( "context connection \u003d true;")) (SqlCommand comm \u003d new SqlCommand (); comm.Connection \u003d conn; comm.CommandText \u003d @ " "+" SELECT actid, tranid, val "+" FROM dbo.Transactions "+" ORDER BY actid, tranid; "; SqlMetaData columns \u003d new SqlMetaData; columns \u003d new SqlMetaData (" actid ", SqlDbType.Int); columns \u003d new SqlMetaData ( "tranid", SqlDbType.Int); columns \u003d new SqlMetaData ( "val", SqlDbType.Money); columns \u003d new SqlMetaData ( "balance", SqlDbType.Money); SqlDataRecord record \u003d new SqlDataRecord (columns); SqlContext. Pipe.SendResultsStart (record); conn.Open (); SqlDataReader reader \u003d comm.ExecuteReader (); SqlInt32 prvactid \u003d 0; SqlMoney balance \u003d 0; while (reader.Read ()) (SqlInt32 actid \u003d reader.GetSqlInt32 (0) ; SqlMoney val \u003d reader.GetSqlMoney (2); if (actid \u003d\u003d prvactid) (balance + \u003d val;) else (balance \u003d val;) prvactid \u003d actid; rec ord.SetSqlInt32 (0, reader.GetSqlInt32 (0)); record.SetSqlInt32 (1, reader.GetSqlInt32 (1)); record.SetSqlMoney (2, val); record.SetSqlMoney (3, balance); SqlContext.Pipe.SendResultsRow (record); ) SqlContext.Pipe.SendResultsEnd (); )))

Щоб мати можливість виконати цю процедуру, що зберігається в SQL Server, спочатку треба на основі цього коду побудувати збірку під назвою AccountBalances і розгорнути в базі даних TSQL2012. Якщо ви не знайомі з розгортанням збірок в SQL Server, можете почитати розділ «Процедури, що і середовище CLR» в статті «Збережені процедури».

Якщо ви назвали збірку AccountBalances, а шлях до файлу збірки - "C: \\ Projects \\ AccountBalances \\ bin \\ Debug \\ AccountBalances.dll", завантажити збірку в базу даних і зареєструвати збережену процедуру можна наступним кодом:

CREATE ASSEMBLY AccountBalances FROM "C: \\ Projects \\ AccountBalances \\ bin \\ Debug \\ AccountBalances.dll"; GO CREATE PROCEDURE dbo.AccountBalances AS EXTERNAL NAME AccountBalances.StoredProcedures.AccountBalances;

Після розгортання зборки та реєстрації процедури можна її виконати наступним кодом:

EXEC dbo.AccountBalances;

Як я вже говорив, SQLDataReader є всього лише ще однією формою курсору, але в цій версії витрати на читання рядків значно менше, ніж при використанні традиційного курсора в T-SQL. Також в.NET ітерації виконуються набагато швидше, ніж в T-SQL. Таким чином, рішення на основі CLR теж масштабируются лінійно. Тестування показало, що продуктивність цього рішення стає вище продуктивності рішень з використанням підзапитів і з'єднань, коли число рядків в секції перевалює через 15.

По завершенні треба виконати наступний код очищення:

DROP PROCEDURE dbo.AccountBalances; DROP ASSEMBLY AccountBalances;

вкладені ітерації

До цього моменту я показував ітеративні рішення і рішення на основі наборів. Наступне рішення засноване на вкладених ітераціях, які є гібридом ітеративного і заснованого на наборах підходів. Ідея полягає в тому, щоб попередньо скопіювати рядки з таблиці-джерела (в нашому випадку це банківські рахунки) в тимчасову таблицю разом з новим атрибутом на ім'я rownum, який обчислюється з використанням функції ROW_NUMBER. Номери рядків секціонуючою по actid і упорядковуються по tranid, тому першої транзакції в кожному банківському рахунку призначається номер 1, другий транзакції - 2 і т.д. Потім в тимчасовій таблиці створюється кластерізованний індекс зі списком ключів (rownum, actid). Потім використовується рекурсивне вираз CTE або спеціально створений цикл для обробки по одному рядку за ітерацію у всіх рахунках. Потім наростаючий підсумок обчислюється шляхом підсумовування значення, відповідного поточному рядку, зі значенням, пов'язаним з попередньою рядком. Ось реалізація цієї логіки з використанням рекурсивного CTE:

SELECT actid, tranid, val, ROW_NUMBER () OVER (PARTITION BY actid ORDER BY tranid) AS rownum INTO #Transactions FROM dbo.Transactions; CREATE UNIQUE CLUSTERED INDEX idx_rownum_actid ON #Transactions (rownum, actid); WITH C AS (SELECT 1 AS rownum, actid, tranid, val, val AS sumqty FROM #Transactions WHERE rownum \u003d 1 UNION ALL SELECT PRV.rownum + 1, PRV.actid, CUR.tranid, CUR.val, PRV.sumqty + CUR.val FROM C AS PRV JOIN #Transactions AS CUR ON CUR.rownum \u003d PRV.rownum + 1 AND CUR.actid \u003d PRV.actid) SELECT actid, tranid, val, sumqty FROM C OPTION (MAXRECURSION 0); DROP TABLE #Transactions;

А це реалізація з використанням явного циклу:

SELECT ROW_NUMBER () OVER (PARTITION BY actid ORDER BY tranid) AS rownum, actid, tranid, val, CAST (val AS BIGINT) AS sumqty INTO #Transactions FROM dbo.Transactions; CREATE UNIQUE CLUSTERED INDEX idx_rownum_actid ON #Transactions (rownum, actid); DECLARE @rownum AS INT; SET @rownum \u003d 1; WHILE 1 \u003d 1 BEGIN SET @rownum \u003d @rownum + 1; UPDATE CUR SET sumqty \u003d PRV.sumqty + CUR.val FROM #Transactions AS CUR JOIN #Transactions AS PRV ON CUR.rownum \u003d @rownum AND PRV.rownum \u003d @rownum - 1 AND CUR.actid \u003d PRV.actid; IF @@ rowcount \u003d 0 BREAK; END SELECT actid, tranid, val, sumqty FROM #Transactions; DROP TABLE #Transactions;

Це рішення забезпечує хорошу продуктивність, коли є велике число секцій з невеликим числом рядків в секціях. Тоді число ітерацій невелике, а основна робота виконується заснованої на наборах частиною рішення, яка з'єднує рядки, пов'язані з одним номером рядка, з рядками, пов'язаними з попереднім номером рядка.

Багаторядкове оновлення зі змінними

Показання до цього моменту прийоми обчислення наростаючих підсумків гарантовано дають правильний результат. Описувана в цьому розділі методика неоднозначна, тому що заснована на спостережуваному, а не задокументованому поведінці системи, крім того вона суперечить принципам релятивності. Висока її привабливість зумовлена \u200b\u200bвеликою швидкістю роботи.

У цьому способі використовується інструкція UPDATE зі змінними. Інструкція UPDATE може привласнювати змінним вираження на основі значення стовпця, а також привласнювати значенням в шпальтах вираз зі змінною. Рішення починається зі створення тимчасової таблиці по імені Transactions з атрибутами actid, tranid, val і balance і кластерізованного індексу зі списком ключів (actid, tranid). Потім тимчасова таблиця наповнюється усіма рядками з вихідної БД Transactions, причому в стовпець balance всіх рядків вводиться значення 0,00. Потім викликається інструкція UPDATE зі змінними, пов'язаними з тимчасової таблицею, для обчислення наростаючих підсумків і вставки обчисленого значення в стовпець balance.

Використовуються змінні @prevaccount і @prevbalance, а значення в стовпці balance обчислюється із застосуванням наступного виразу:

SET @prevbalance \u003d balance \u003d CASE WHEN actid \u003d @prevaccount THEN @prevbalance + val ELSE val END

Вираз CASE перевіряє, чи не збігаються ідентифікатори поточного і попереднього рахунків, і, якщо вони рівні, повертає суму попереднього і поточного значень в стовпці balance. Якщо ідентифікатори рахунків різні, повертається сума поточної транзакції. Далі результат виразу CASE вставляється в стовпець balance і присвоюється змінної @prevbalance. В окремому вираженні змінної © prevaccount присвоюється ідентифікатор поточного рахунку.

Після висловлення UPDATE рішення являє рядки з тимчасової таблиці і видаляє останню. Ось код закінченого рішення:

CREATE TABLE #Transactions (actid INT, tranid INT, val MONEY, balance MONEY); CREATE CLUSTERED INDEX idx_actid_tranid ON #Transactions (actid, tranid); INSERT INTO #Transactions WITH (TABLOCK) (actid, tranid, val, balance) SELECT actid, tranid, val, 0.00 FROM dbo.Transactions ORDER BY actid, tranid; DECLARE @prevaccount AS INT, @prevbalance AS MONEY; UPDATE #Transactions SET @prevbalance \u003d balance \u003d CASE WHEN actid \u003d @prevaccount THEN @prevbalance + val ELSE val END, @prevaccount \u003d actid FROM #Transactions WITH (INDEX (1), TABLOCKX) OPTION (MAXDOP 1); SELECT * FROM #Transactions; DROP TABLE #Transactions;

План цього рішення показаний на наступному малюнку. Перша частина представлена \u200b\u200bінструкцією INSERT, друга - UPDATE, а третя - SELECT:

У цьому рішенні передбачається, що при оптимізації виконання UPDATE завжди буде виконуватися упорядкований перегляд кластерізованного індексу, і в рішенні передбачено ряд підказок, щоб запобігти обставини, які можуть перешкодити цьому, наприклад паралелізм. Проблема в тому, що немає ніякої офіційної гарантії, що оптимізатор завжди буде поглядати в порядку кластерізованного індексу. Не можна покладатися на особливості фізичних обчислень, коли потрібно забезпечити логічну коректність коду, якщо тільки в коді немає логічних елементів, які за визначенням можуть гарантувати таку поведінку. В даному коді немає ніяких логічних особливостей, які могли б гарантувати саме така поведінка. Природно вибір, використовувати чи ні цей спосіб, лежить цілком на вашій совісті. Я вважаю, що безвідповідально використовувати його, навіть якщо ви тисячі разів перевіряли і «начебто все працює, як треба».

На щастя, в SQL Server 2012 цей вибір стає практично непотрібним. При наявності виключно ефективного вирішення з використанням віконних функцій агрегування не доводиться замислюватися про інші рішення.

Вимірювання продуктивності

Я провів вимір і порівняння продуктивності різних методик. Результати наведені на малюнках нижче:

Я розбив результати на два графіка через те, що спосіб з використанням вкладеного запиту або з'єднання настільки повільніше інших, що мені довелося використовувати для нього інший масштаб. У будь-якому випадку, зверніть увагу, що більшість рішень демонструють лінійну залежність обсягу роботи від розміру секції, і тільки рішення на основі вкладеного запиту або з'єднання показують квадратичную залежність. Також ясно видно, наскільки ефективніше нове рішення на основі віконної функції агрегування. Рішення на основі UPDATE зі змінними теж дуже швидке, але по описаним уже причин я не рекомендую його використовувати. Рішення з використанням CLR також цілком швидке, але в ньому потрібно писати весь цей код.NET і розгортати збірку в базі даних. Як не кинь оком, а засноване на наборах рішення з використанням віконних агрегатів залишається найкращим.

Всі математичні функції в разі помилки повертають NULL.

Унарний мінус. Змінює знак аргументу: mysql\u003e SELECT - 2; -\u003e -2 Необхідно враховувати, що якщо цей оператор використовується з даними типу BIGINT, повертається значення також буде мати тип BIGINT! Це означає, що слід уникати використання оператора для цілих чисел, які можуть мати величину -2 ^ 63! ABS (X) Повертає абсолютне значення величини X: mysql\u003e SELECT ABS (2); -\u003e 2 mysql\u003e SELECT ABS (-32); -\u003e 32 Цю функцію можна впевнено застосовувати для величин типу BIGINT. SIGN (X) Повертає знак аргументу у вигляді -1, 0 або 1, залежно від того, чи є X негативним, нулем або позитивним: mysql\u003e SELECT SIGN (-32); -\u003e -1 mysql\u003e SELECT SIGN (0); -\u003e 0 mysql\u003e SELECT SIGN (234); -\u003e 1 MOD (N, M)% Значення по модулю (подібно оператору% в C). Повертає залишок від ділення N на M: mysql\u003e SELECT MOD (234, 10); -\u003e 4 mysql\u003e SELECT 253% 7; -\u003e 1 mysql\u003e SELECT MOD (29,9); -\u003e 2 Цю функцію можна впевнено застосовувати для величин типу BIGINT. FLOOR (X) Повертає найбільше ціле число, яке не перевищує X: mysql\u003e SELECT FLOOR (1.23); -\u003e 1 mysql\u003e SELECT FLOOR (-1.23); -\u003e -2 Слід враховувати, що повертається величина перетворюється в BIGINT! CEILING (X) Повертає найменше ціле число, що не менше, ніж X: mysql\u003e SELECT CEILING (1.23); -\u003e 2 mysql\u003e SELECT CEILING (-1.23); -\u003e -1 Слід враховувати, що повертається величина перетворюється в BIGINT! ROUND (X) Повертає аргумент X, округлений до найближчого цілого числа: mysql\u003e SELECT ROUND (-1.23); -\u003e -1 mysql\u003e SELECT ROUND (-1.58); -\u003e -2 mysql\u003e SELECT ROUND (1.58); -\u003e 2 Слід враховувати, що поведінка функції ROUND () при значенні аргументу, що дорівнює середині між двома цілими числами, залежить від конкретної реалізації бібліотеки C. можуть статися внаслідок округлення виконуватися: до найближчого парним числом, завжди до найближчого більшого, завжди до найближчого меншого, завжди бути спрямованим до нуля. Щоб округлення завжди відбувалося тільки в одному напрямку, необхідно використовувати замість даної добре певні функції, такі як TRUNCATE () або FLOOR (). ROUND (X, D) Повертає аргумент X, округлений до числа з D десятковими знаками. Якщо D дорівнює 0, результат буде представлений без десяткового знака або дробової частини: mysql\u003e SELECT ROUND (1.298, 1); -\u003e 1.3 mysql\u003e SELECT ROUND (1.298, 0); -\u003e 1 EXP (X) Повертає значення e (основа натуральних логарифмів), зведена в ступінь X: mysql\u003e SELECT EXP (2); -\u003e 7.389056 mysql\u003e SELECT EXP (-2); -\u003e 0.135335 LOG (X) Повертає натуральний логарифм числа X: mysql\u003e SELECT LOG (2); -\u003e 0. 693147 mysql\u003e SELECT LOG (-2); -\u003e NULL Щоб отримати логарифм числа X для довільної основи логарифмів B, слід використовувати формулу LOG (X) / LOG (B). LOG10 (X) Повертає десятковий логарифм числа X: mysql\u003e SELECT LOG10 (2); -\u003e 0.301030 mysql\u003e SELECT LOG10 (100); -\u003e 2.000000 mysql\u003e SELECT LOG10 (-100); -\u003e NULL POW (X, Y) POWER (X, Y) Повертає значення аргументу X, зведена в ступінь Y: mysql\u003e SELECT POW (2,2); -\u003e 4.000000 mysql\u003e SELECT POW (2, -2); -\u003e 0.250000 SQRT (X) Повертає ненегативний квадратний корінь числа X: mysql\u003e SELECT SQRT (4); -\u003e 2.000000 mysql\u003e SELECT SQRT (20); -\u003e 4.472136 PI () Повертає значення числа "пі". За замовчуванням представлено 5 десяткових знаків, але в MySQL для представлення числа "пі" при внутрішніх обчисленнях використовується повна подвійна точність. mysql\u003e SELECT PI (); -\u003e 3.141593 mysql\u003e SELECT PI () + 0.000000000000000000; -\u003e 3.141592653589793116 COS (X) Повертає косинус числа X, де X задається в радіанах: mysql\u003e SELECT COS (PI ()); -\u003e -1.000000 SIN (X) Повертає синус числа X, де X задається в радіанах: mysql\u003e SELECT SIN (PI ()); -\u003e 0.000000 TAN (X) Повертає тангенс числа X, де X задається в радіанах: mysql\u003e SELECT TAN (PI () + 1); -\u003e 1.557408 ACOS (X) Повертає арккосинус числа X, тобто величину, косинус якого дорівнює X. Якщо X не перебуває у діапазоні від -1 до 1, повертає NULL: mysql\u003e SELECT ACOS (1); -\u003e 0.000000 mysql\u003e SELECT ACOS (1.0001); -\u003e NULL mysql\u003e SELECT ACOS (0); -\u003e 1.570796 ASIN (X) Повертає арксинус числа X, тобто величину, синус якого дорівнює X. Якщо X не перебуває у діапазоні від -1 до 1, повертає NULL: mysql\u003e SELECT ASIN (0.2); -\u003e 0.201358 mysql\u003e SELECT ASIN ( "foo"); -\u003e 0.000000 ATAN (X) Повертає арктангенс числа X, тобто величину, тангенс якого дорівнює X: mysql\u003e SELECT ATAN (2); -\u003e 1.107149 mysql\u003e SELECT ATAN (-2); -\u003e -1.107149 ATAN (Y, X) ATAN2 (Y, X) Повертає арктангенс двох змінних X і Y. Обчислення робиться так само, як і обчислення арктангенса Y / X, за винятком того, що знаки обох аргументів використовуються для визначення квадранта результату: mysql\u003e SELECT ATAN (-2,2); -\u003e -0.785398 mysql\u003e SELECT ATAN2 (PI (), 0); -\u003e 1.570796 COT (X) Повертає котангенс числа X: mysql\u003e SELECT COT (12); -\u003e -1.57267341 mysql\u003e SELECT COT (0); -\u003e NULL RAND () RAND (N) Повертає випадкову величину з плаваючою точкою в діапазоні від 0 до 1,0. Якщо цілочисельний аргумент N зазначений, то він використовується як початкове значення цієї величини: mysql\u003e SELECT RAND (); -\u003e 0. 9233482386203 mysql\u003e SELECT RAND (20); -\u003e 0.15888261251047 mysql\u003e SELECT RAND (20); -\u003e 0.15888261251047 mysql\u003e SELECT RAND (); -\u003e 0.63553050033332 mysql\u003e SELECT RAND (); -\u003e 0.70100469486881 У виразах виду ORDER BY не слід використовувати стовпець з величинами RAND (), оскільки застосування оператора ORDER BY призведе до багаторазових обчислень в цьому стовпці. У версії MySQL 3.23 можна, однак, виконати наступний оператор: SELECT * FROM table_name ORDER BY RAND (): він корисний для отримання випадкового примірника з безлічі SELECT * FROM table1, table2 WHERE a \u003d b AND c

Якщо повертається величина використовується в целочисленном контексті (INTEGER), або всі аргументи є цілочисельними, то вони порівнюються як цілі числа.

Якщо повертається величина використовується в контексті дійсних чисел (REAL) або всі аргументи є дійсними числами, то вони порівнюються як числа типу REAL.

Якщо один з аргументів є залежною від регістра рядком, то дані аргументи порівнюються з урахуванням регістру.

В інших випадках аргументи порівнюються як ланцюжки, незалежні від регістру. mysql\u003e SELECT LEAST (2,0); -\u003e 0 mysql\u003e SELECT LEAST (34.0,3.0,5.0,767.0); -\u003e 3.0 mysql\u003e SELECT LEAST ( "B", "A", "C"); -\u003e "A" В версіях MySQL до 3.22.5 можна використовувати MIN () замість LEAST. GREATEST (X, Y, ...) Повертає найбільший (з максимальним значенням) аргумент. Порівняння аргументів відбувається за тими ж правилами, що і для LEAST: mysql\u003e SELECT GREATEST (2,0); -\u003e 2 mysql\u003e SELECT GREATEST (34.0,3.0,5.0,767.0); -\u003e 767.0 mysql\u003e SELECT GREATEST ( "B", "A", "C"); -\u003e "C" У версіях MySQL до 3.22.5 можна використовувати MAX () замість GREATEST. DEGREES (X) Повертає аргумент X, перетворений з радіанів в градуси: mysql\u003e SELECT DEGREES (PI ()); -\u003e 180.000000 RADIANS (X) Повертає аргумент X, перетворений з градусів в радіани: mysql\u003e SELECT RADIANS (90); -\u003e 1.570796 TRUNCATE (X, D) Повертає число X, усеченное до D десяткових знаків. Якщо D дорівнює 0, результат буде представлений без десяткового знака або дробової частини: mysql\u003e SELECT TRUNCATE (1.223,1); -\u003e 1.2 mysql\u003e SELECT TRUNCATE (1.999,1); -\u003e 1.9 mysql\u003e SELECT TRUNCATE (1.999,0); -\u003e 1 Слід враховувати, що зазвичай в комп'ютерах десяткові числа зберігаються не так, як цілі, а як числа подвійної точності з плаваючим десятковим знаком (DOUBLE). Тому іноді результат може вводити в оману, як в наступному прикладі: mysql\u003e SELECT TRUNCATE (10.28 * 100,0); -\u003e тисячі двадцять сім Це відбувається тому, що в дійсності 10,28 зберігається як щось на кшталт +10,2799999999999999.

В даній статті наведено рішення оптимізації на Transact SQL завдання розрахунку залишки на складах. Застосовано: партіцірованіе таблиць і матеріалізованих уявлень.

Постановка задачі

Завдання необхідно вирішити на SQL Server 2014 Enterprise Edition (x64). У фірмі є багато складів. У кожному складі щодня по кілька тисяч відвантажень і приймань продуктів. Є таблиця рухів товарів на складі прихід / витрата. Необхідно реалізувати:

Розрахунок балансу на обрану дату і час (з точністю до години) по всіх / будь-якого складах по кожному продукту. Для аналітики необхідно створити об'єкт (функцію, таблицю, уявлення) за допомогою якого за вибраний діапазон дат вивести по всіх складах і продуктам дані вихідної таблиці і додаткову розрахункову колонку - залишок на складі позиції.

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

Технічні подробиці. Сама таблиця:

Create table dbo.Turnover (id int identity primary key, dt datetime not , ProductID int not , StorehouseID int not , Operation smallint not null check (Operation in (-1,1)), - +1 прихід на склад , -1 витрата зі складу Quantity numeric (20,2) not , Cost money not null)

Dt - Дата час надходження / списання на / зі складу.
ProductID - Продукт
StorehouseID - склад
Operation - 2 значення прихід або витрата
Quantity - кількість товару складі. Може бути речовим якщо продукт не в штуках, а, наприклад, в кілограмах.
Cost - вартість партії товару.

дослідження завдання

Створимо заповнену таблицю. Для того що б ти міг разом зі мною тестувати і дивитися отримані результати, пропоную створити і заповнити таблицю dbo.Turnover скриптом:

If object_id ( "dbo.Turnover", "U") is not null drop table dbo.Turnover; go with times as (select 1 id union all select id + 1 from times where id< 10*365*24*60 -- 10 лет * 365 дней * 24 часа * 60 минут = столько минут в 10 лет) , storehouse as (select 1 id union all select id+1 from storehouse where id < 100 -- количество складов) select identity(int,1,1) id, dateadd(minute, t.id, convert(datetime,"20060101",120)) dt, 1+abs(convert(int,convert(binary(4),newid()))%1000) ProductID, -- 1000 - количество разных продуктов s.id StorehouseID, case when abs(convert(int,convert(binary(4),newid()))%3) in (0,1) then 1 else -1 end Operation, -- какой то приход и расход, из случайных сделаем из 3х вариантов 2 приход 1 расход 1+abs(convert(int,convert(binary(4),newid()))%100) Quantity into dbo.Turnover from times t cross join storehouse s option(maxrecursion 0); go --- 15 min alter table dbo.Turnover alter column id int not null go alter table dbo.Turnover add constraint pk_turnover primary key (id) with(data_compression=page) go -- 6 min
У мене цей скрипт на ПК з SSD диском виконувався близько 22 хвилини, і розмір таблиці зайняв близько 8 Гб на жорсткому диску. Ти можеш зменшити кількість років, і кількість складів, для того що б час створення і заповнення таблиці скоротити. Але якийсь непоганий обсяг для оцінки планів запитів рекомендую залишити, хоча б 1-2 гігабайти.

Згрупуємо дані до години

Далі, нам потрібно згрупувати суми по продуктам на складі за досліджуваний період часу, в нашій постановці завдання це один годину (можна до хвилини, до 15 хвилин, дня. Але очевидно до мілісекунд навряд чи кому знадобиться звітність). Для порівнянь в сесії (вікні) де виконуємо наші запити виконаємо команду - set statistics time on ;. Далі виконуємо самі запити і дивимося плани запитів:

Вартість запиту - 12406
(Рядків оброблено 1000)
Час роботи SQL Server:
Час ЦП \u003d 2096594 мс, витрачений час \u003d 321797 мс.

Якщо ми зробимо результуючий запит з балансом, який вважається наростаючим підсумком від нашого кількості, то запит і план запиту будуть наступними:

Select top (1000) convert (datetime, convert (varchar (13), dt, 120) + ": 00", 120) as dt, - округляємо до години ProductID, StorehouseID, sum (Operation * Quantity) as Quantity, sum (sum (Operation * Quantity)) over (partition by StorehouseID, ProductID order by convert (datetime, convert (varchar (13), dt, 120) + ": 00", 120)) as Balance from dbo.Turnover group by convert (datetime, convert (varchar (13), dt, 120) + ": 00", 120), ProductID, StorehouseID

Вартість запиту - 19329
(Рядків оброблено 1000)
Час роботи SQL Server:
Час ЦП \u003d 2413155 мс, витрачений час \u003d 344631 мс.

оптимізація угруповання

Тут досить все просто. Сам запит без наростаючого підсумку можна оптимізувати матеріалізованим поданням (index view). Для побудови матеріалізованого уявлення, то що підсумовується не повинно мати значення NULL, у нас підсумовуються sum (Operation * Quantity), або кожне поле зробити NOT NULL або додати isnull / coalesce в вираз. Пропоную створити матеріалізоване уявлення.

Create view dbo.TurnoverHour with schemabinding as select convert (datetime, convert (varchar (13), dt, 120) + ": 00", 120) as dt, - округляємо до години ProductID, StorehouseID, sum (isnull (Operation * Quantity, 0)) as Quantity, count_big (*) qty from dbo.Turnover group by convert (datetime, convert (varchar (13), dt, 120) + ": 00", 120), ProductID, StorehouseID go
І побудувати по ньому кластерний індекс. В індексі порядок полів зазначимо так само як і в угрупованні (для угруповання стільки порядок не важливий, важливо що б все поля угруповання були в індексі) і наростаючому підсумку (тут важливий порядок - спочатку те, що в partition by, потім те, що в order by):

Create unique clustered index uix_TurnoverHour on dbo.TurnoverHour (StorehouseID, ProductID, dt) with (data_compression \u003d page) - 19 min

Тепер після побудови кластерного індексу ми можемо заново виконати запити, змінивши агрегацію суми як в поданні:

Select top (1000) convert (datetime, convert (varchar (13), dt, 120) + ": 00", 120) as dt, - округляємо до години ProductID, StorehouseID, sum (isnull (Operation * Quantity, 0) ) as Quantity from dbo.Turnover group by convert (datetime, convert (varchar (13), dt, 120) + ": 00", 120), ProductID, StorehouseID select top (1000) convert (datetime, convert (varchar (13 ), dt, 120) + ": 00", 120) as dt, - округляємо до години ProductID, StorehouseID, sum (isnull (Operation * Quantity, 0)) as Quantity, sum (sum (isnull (Operation * Quantity, 0))) over (partition by StorehouseID, ProductID order by convert (datetime, convert (varchar (13), dt, 120) + ": 00", 120)) as Balance from dbo.Turnover group by convert (datetime, convert (varchar (13), dt, 120) + ": 00", 120), ProductID, StorehouseID

Плани запитів стали:

вартість 0.008

вартість 0.01

Час роботи SQL Server:
Час ЦП \u003d 31 мс, витрачений час \u003d 116 мс.
(Рядків оброблено 1000)
Час роботи SQL Server:
Час ЦП \u003d 0 мс, витрачений час \u003d 151 мс.

Разом, ми бачимо, що з індексованої вьюха запит сканує не таблиці групуючи дані, а кластерний індекс, в якому вже все згруповано. І відповідно час виконання скоротилося з 321797 мілісекунд до 116 мс., Тобто в 2774 рази.

На цьому б можна було б і закінчити нашу оптимізацію, якби не той факт, що нам потрібна часто не вся таблиця (вьюха) а її частина за вибраний діапазон.

проміжні баланси

У підсумку нам потрібно швидке виконання наступного запиту:

Set dateformat ymd; declare @start datetime \u003d "2015-01-02", @finish datetime \u003d "2015-01-03" select * from (select dt, StorehouseID, ProductId, Quantity, sum (Quantity) over (partition by StorehouseID, ProductID order by dt) as Balance from dbo.TurnoverHour with (noexpand) where dt<= @finish) as tmp where dt >\u003d @start

Вартість плану \u003d 3103. А уяви що б було, якби не по матеріалізованих представлень пішов а по самій таблиці.

Висновок даних матеріалізованого уявлення і балансу по кожному продукту на складі на дату з часом округленої до години. Що б порахувати баланс - необхідно з самого початку (з нульового балансу) підсумувати все кількості до зазначеної останньої дати (@finish), а після вже в підсумованому резалтсете відсікти дані пізніше параметра start.

Тут, очевидно, допоможуть проміжні розраховані баланси. Наприклад, на 1е число кожного місяця або на щонеділі. Маючи такі баланси, завдання зводиться до того, що потрібно буде підсумовувати раніше розраховані баланси і розрахувати баланс не від початку, а від останньої розрахованої дати. Для експериментів і порівнянь побудуємо додатково не кластерний індекс за датою:

Create index ix_dt on dbo.TurnoverHour (dt) include (Quantity) with (data_compression \u003d page); --7 min І наш запит буде виду: set dateformat ymd; declare @start datetime \u003d "2015-01-02", @finish datetime \u003d "2015-01-03" declare @start_month datetime \u003d convert (datetime, convert (varchar (9), @ start, 120) + "1", 120) select * from (select dt, StorehouseID, ProductId, Quantity, sum (Quantity) over (partition by StorehouseID, ProductID order by dt) as Balance from dbo.TurnoverHour with (noexpand) where dt between @start_month and @finish) as tmp where dt\u003e
Взагалі цей запит маючи навіть індекс за датою повністю покриває всі які поставлені в запиті поля, вибере кластерний наш індекс і сканування. А не пошук за датою, сортуванню. Пропоную виконати наступні 2 запити і порівняти що у нас вийшло, далі проаналізуємо що все-таки краще:

Set dateformat ymd; declare @start datetime \u003d "2015-01-02", @finish datetime \u003d "2015-01-03" declare @start_month datetime \u003d convert (datetime, convert (varchar (9), @ start, 120) + "1", 120) select * from (select dt, StorehouseID, ProductId, Quantity, sum (Quantity) over (partition by StorehouseID, ProductID order by dt) as Balance from dbo.TurnoverHour with (noexpand) where dt between @start_month and @finish) as tmp where dt\u003e \u003d @start order by StorehouseID, ProductID, dt select * from (select dt, StorehouseID, ProductId, Quantity, sum (Quantity) over (partition by StorehouseID, ProductID order by dt) as Balance from dbo.TurnoverHour with ( noexpand, index \u003d ix_dt) where dt between @start_month and @finish) as tmp where dt\u003e \u003d @start order by StorehouseID, ProductID, dt

Час роботи SQL Server:
Час ЦП \u003d 33860 мс, витрачений час \u003d 24247 мс.
(Рядків оброблено: 145608)
(Рядків оброблено: 1)
Час роботи SQL Server:
Час ЦП \u003d 6374 мс, витрачений час \u003d 1718 мс.
час ЦП \u003d 0 мс, що минув час \u003d 0 мс.

З часу видно, що індекс за датою виконується значно швидше. Але плани запитів в порівнянні виглядають наступним чином:

Вартість 1-го запиту з автоматично обраним кластерним індексом \u003d 2752, а ось вартість з індексом за датою запиту \u003d 3119.

Як би там не було, тут нам потрібно від індексу два завдання: сортування та вибірка діапазону. Одним індексом з наявних нам це завдання не вирішити. В даному прикладі діапазон даних всього за 1 день, але якщо буде період більше, але далеко не весь, наприклад, за 2 місяці, то однозначно пошук за індексом буде не ефективний через витрати на сортування.

Тут з видимих \u200b\u200bоптимальних рішень я бачу:

Створити обчислюване поле Рік-Місяць і індекс створити (Рік-Місяць, інші поля кластерного індексу). В умови where dt between @start_month and finish замінити на рік-Місяць [Email protected]місяць, і після цього вже накласти фільтр на потрібні дати.
Фільтровані індекси - індекс сам як кластерний, але фільтр по даті, за потрібний місяць. І таких індексів зробити стільки, скільки у нас місяців всього. Ідея близька до вирішення, але тут якщо діапазон умов буде з 2х фільтрованих індексів, буде потрібно з'єднання і надалі все одно сортування неминуча.
Секціоніруем кластерний індекс так, щоб в кожній секції були дані тільки за один місяць.

У проекті в результаті я зробив 3-й варіант. Секціонування кластерного індексу матеріалізованого уявлення. І якщо вибірка йде за проміжок часу одного місяця, то по суті оптимізатор зачіпає тільки одну секцію, роблячи її сканування без сортування. А відсікання невикористовуваних даних відбувається на рівні відсікання невикористовуваних секцій. Тут якщо пошук з 10 по 20 число у нас не йде точний пошук цих дат, а пошук даних з 1-го по останній день місяця, далі сканування цього діапазону в відсортованому індексі з фільтрацією під час сканування за виставленими дат.

Секціоніруем кластерний індекс вьюха. Перш за все видалимо з вьюха все індекси:

Drop index ix_dt on dbo.TurnoverHour; drop index uix_TurnoverHour on dbo.TurnoverHour;
І створимо функцію і схему секціонування:

Set dateformat ymd; create partition function pf_TurnoverHour (datetime) as range right for values \u200b\u200b( "2006-01-01", "2006-02-01", "2006-03-01", "2006-04-01", "2006-05- 01 "," 2006-06-01 "," 2006-07-01 "," 2006-08-01 "," 2006-09-01 "," 2006-10-01 "," 2006-11-01 " , "2006-12-01", "2007-01-01", "2007-02-01", "2007-03-01", "2007-04-01", "2007-05-01", " 2007-06-01 "," 2007-07-01 "," 2007-08-01 "," 2007-09-01 "," 2007-10-01 "," 2007-11-01 "," 2007- 12-01 "," 2008-01-01 "," 2008-02-01 "," 2008-03-01 "," 2008-04-01 "," 2008-05-01 "," 2008-06- 01 "," 2008-07-01 "," 2008-08-01 "," 2008-09-01 "," 2008-10-01 "," 2008-11-01 "," 2008-12-01 " , "2009-01-01", "2009-02-01", "2009-03-01", "2009-04-01", "2009-05-01", "2009-06-01", " 2009-07-01 "," 2009-08-01 "," 2009-09-01 "," 2009-10-01 "," 2009-11-01 "," 2009-12-01 "," 2010- 01-01 "," 2010-02-01 "," 2010-03-01 "," 2010-04-01 "," 2010-05-01 "," 2010-06-01 "," 2010-07- 01 "," 2010-08-01 "," 2010-09-01 "," 2010-10-01 "," 2010-11-01 "," 2010-12-01 "," 2011-01-01 " , "2011-02-01", "2011-03-01", "2011-04-01", "2011-05-01", "2011-06-01 "," 2011-07-01 "," 2011-08-01 "," 2011-09-01 "," 2011-10-01 "," 2011-11-01 "," 2011-12-01 ", "2012-01-01", "2012-02-01", "2012-03-01", "2012-04-01", "2012-05-01", "2012-06-01", "2012 -07-01 "," 2012-08-01 "," 2012-09-01 "," 2012-10-01 "," 2012-11-01 "," 2012-12-01 "," 2013-01 -01 "," 2013-02-01 "," 2013-03-01 "," 2013-04-01 "," 2013-05-01 "," 2013-06-01 "," 2013-07-01 "," 2013-08-01 "," 2013-09-01 "," 2013-10-01 "," 2013-11-01 "," 2013-12-01 "," 2014-01-01 ", "2014-02-01", "2014-03-01", "2014-04-01", "2014-05-01", "2014-06-01", "2014-07-01", "2014 -08-01 "," 2014-09-01 "," 2014-10-01 "," 2014-11-01 "," 2014-12-01 "," 2015-01-01 "," 2015-02 -01 "," 2015-03-01 "," 2015-04-01 "," 2015-05-01 "," 2015-06-01 "," 2015-07-01 "," 2015-08-01 "," 2015-09-01 "," 2015-10-01 "," 2015-11-01 "," 2015-12-01 "," 2016-01-01 "," 2016-02-01 ", "2016-03-01", "2016-04-01", "2016-05-01", "2016-06-01", "2016-07-01", "2016-08-01", "2016 -09-01 "," 2016-10-01 "," 2016-11-01 "," 2016-12-01 "," 2017-01-01 "," 2017-02-01 "," 2017-03 -01 "," 2017-04-01 "," 2017-05-01 "," 20 17-06-01 "," 2017-07-01 "," 2017-08-01 "," 2017-09-01 "," 2017-10-01 "," 2017-11-01 "," 2017- 12-01 "," 2018-01-01 "," 2018-02-01 "," 2018-03-01 "," 2018-04-01 "," 2018-05-01 "," 2018-06- 01 "," 2018-07-01 "," 2018-08-01 "," 2018-09-01 "," 2018-10-01 "," 2018-11-01 "," 2018-12-01 " , "2019-01-01", "2019-02-01", "2019-03-01", "2019-04-01", "2019-05-01", "2019-06-01", " 2019-07-01 "," 2019-08-01 "," 2019-09-01 "," 2019-10-01 "," 2019-11-01 "," 2019-12-01 "); go create partition scheme ps_TurnoverHour as partition pf_TurnoverHour all to (); go Ну і вже відомий нам кластерний індекс тільки в створеній схемі секціонування: create unique clustered index uix_TurnoverHour on dbo. TurnoverHour (StorehouseID, ProductID, dt) with (data_compression \u003d page) on ps_TurnoverHour (dt); --- 19 min І тепер подивимося, що у нас вийшло. Сам запит: set dateformat ymd; declare @start datetime \u003d "2015-01-02", @finish datetime \u003d "2015-01-03" declare @start_month datetime \u003d convert (datetime, convert (varchar (9), @ start, 120) + "1", 120) select * from (select dt, StorehouseID, ProductId, Quantity, sum (Quantity) over (partition by StorehouseID, ProductID order by dt) as Balance from dbo.TurnoverHour with (noexpand) where dt between @start_month and @finish) as tmp where dt\u003e \u003d @start order by StorehouseID, ProductID, dt option (recompile);

Час роботи SQL Server:
Час ЦП \u003d 7860 мс, витрачений час \u003d +1725 мс.
Час синтаксичного аналізу та компіляції SQL Server:
час ЦП \u003d 0 мс, що минув час \u003d 0 мс.
Вартість плану запиту \u003d 9.4

По суті дані в одній секції вибираються і скануються за кластерним індексом досить швидко. Тут слід додати те, що коли запит параметризованих, виникає неприємний ефект parameter sniffing, лікується option (recompile).

Синтаксис SQL 2003 підтримують всі платформи.

FLOOR (вираз)

Якщо ви передаєте в функцію позитивне число, то дія функції складатиметься в видаленні всього, що стоїть після десяткового дробу.

SELECT FLOOR (100.1) FROM dual;

Однак запам'ятайте, що в разі негативних чисел округлення в меншу сторону відповідає збільшенню абсолютного значення.

SELECT FLOOR (-100.1) FROM dual;

Щоб отримати ефект, протилежний дії функції FLOOR, використовуйте функцію CEIL.

LN

Функція LN повертає натуральний логарифм числа, тобто ступінь, в яку потрібно звести математичну константу е (приблизно 2.718281), щоб в результаті отримати заданий число.

Синтаксис SQL 2003

LN (вираз)

DB2, Oracle, PostgreSQL

Платформи DB2, Oracle і PostgreSQL підтримують для функції LN синтаксис SQL 2003. DB2 і PostgreSQL також підтримують функцію LOG як синонім LN.

MySQL і SQL Server

В MySQL і SQL Server є своя власна функція для обчислення натурального логарифма - LOG.

LOG (вираз)

У наступному прикладі для Oracle обчислюється натуральний логарифм числа, приблизно рівного математичної константі.

SELECT LN (2.718281) FROM dual;

Щоб виконати протилежну операцію, потрібно скористатися функцією ЕХР.

MOD

Функція MOD повертає залишок від ділення діленого на дільник. Всі платформи підтримують синтаксис інструкції MOD стандарту SQL 2003.

Синтаксис SQL 2003

MOD (ділене, дільник)

Стандартна функція MOD призначена для отримання залишку від ділення діленого на дільник. Якщо дільник дорівнює нулю, то повертається ділене.

Нижче показано, як можна використовувати функцію MOD в інструкції SELECT.

SELECT MOD (12, 5) FROM NUMBERS 2;

POSITION

Функція POSITION повертає ціле число, що показує початкове положення рядка в рядку пошуку.

Синтаксис SQL 2003

POSITION (рядок 1 IN строка2)

Стандартна функція POSITION призначена для отримання положення першого входження заданого рядка (рядок!) В рядку пошуку (рядок!). Функція повертає 0, якщо рядок! не зустрічається в рядку !, і NULL - якщо будь-який з аргументів дорівнює NULL.

DB2

В DB2 є еквівалентна функція POSSTR.

MySQL

Платформа MySQL підтримує функцію POSITION відповідно до стандарту SQL 2003.

POWER

Функція POWER використовується для зведення числа в зазначену ступінь.

Синтаксис SQL 2003

POWER (підстава, показник)

Результатом виконання даної функції є підстава, зведена в ступінь, яка визначається показником. Якщо основа негативно, то показник повинен бути цілим числом.

DB2, Oracle, PostgreSQL і SQL Server

Всі ці виробники підтримують синтаксис SQL 2003.

Oracle

У Oracle є еквівалентна функція INSTR.

PostgreSQL

Платформа PostgreSQL підтримує функцію POSITION відповідно до стандарту SQL 2003.

MySQL

Платформа MySQL підтримує дану функціональність, що не цього ключове слово POW.

P0W (підстава, показник)

Зведення позитивного числа в ступінь досить очевидно.

SELECT POWER (10.3) FROM dual;

Будь-яке число, зведена в ступінь 0, дорівнює 1.

SELECT POWER (0.0) FROM dual;

Негативний показник зміщує десяткову точку вліво.

SELECT POWER (10, -3) FROM dual;

SORT

Функція SQRT повертає квадратний корінь числа.

Синтаксис SQL 2003

Всі платформи підтримують синтаксис SQL 2003.

SORT (вираз)

SELECT SQRT (100) FROM dual;

WIDTH BUCKET

Функція WIDTH BUCKET привласнює значення стовпцями равношірінной гістограми.

Синтаксис SQL 2003

У наступному синтаксисі вираження являє собою значення, яке присвоюється одну гістограми. Як правило, вираз грунтується на одному або декількох стовпцях таблиці, що повертаються запитом.

WIDTH BUCKET (вираз, min, max, столбци_гістограмми)

Параметр стовпці гістограми показує кількість створюваних стовпців гістограми в діапазоні значень від min до max. Значення параметра min включається в діапазон, а значення параметра max не включається. Значення виразу присвоюється одному з стовпців гістограми, після чого функція повертає номер відповідного стовпця гістограми. Якщо вираз не підпадає під вказаний діапазон стовпців, функція повертає або 0, або max + 1, в залежності від того, буде вираз меншим, ніж min, або більшим чи рівним max.

У наступному прикладі цілочисельні значення від 1 до 10 розподіляються між двома стовпцями гістограми.

Наступний приклад більш цікавий. 11 значень від 1 до 10 розподіляються між трьома стовпцями гістограми для ілюстрації відмінності між значенням min, яке включається в діапазон, і значенням max, яке в діапазон не включається.

SELECT х, WIDTH_BUCKET (x, 1.10.3) FROM pivot;

Особливу увагу зверніть на результати сХ \u003d, Х \u003d 9.9 і Х- 10. Вхідний значення параметра ними, тобто в даному прикладі - 1, потрапляє в перший стовпець, позначаючи нижню межу діапазону, оскільки стовпець № 1 визначається як х\u003e \u003d min. Однак вхідний значення параметра max не входить в стовпець з максимальними значеннями. В даному прикладі число 10 потрапляє в стовпець переповнення, з номером max + 1. Значення 9.9 потрапляє в стовпець № 3, і це ілюструє правило, згідно з яким верхня межа діапазону визначається як х< max.