T-SQL, вложени изявления в съвкупни функции
Допълнителни опции за филтриране
Не забравяйте, че различните елементи на дефиницията на прозореца (разделяне, подреждане и изрязване) са по същество различни опции за филтриране. Има и други нужди от филтриране, на които тези определения не отговарят. Някои от тези нужди могат да бъдат удовлетворени с клаузата FILTER, която не е внедрена в SQL Server 2012. Съществуват и опити за разрешаване на този проблем, като се правят предложения за разширяване на стандарта, които се надявам по някакъв начин да се появят в стандарта и SQL Server.
Ще започна с предложението за ФИЛТЪР. Стандартът определя, че във функциите за агрегиране тази клауза ви позволява да филтрирате набора от редове, към който е приложено агрегирането, използвайки предикат, форматът на тази клауза изглежда така:
Като пример ще дам заявка, която изчислява разликата между текущото количество и средното месечно количество за служител до текущата дата (а не месеца от текущия ред):
SQL Server 2012 все още не поддържа клаузата FILTER. За да бъда честен, не познавам СУБД, която го е поддържала. Ако имате нужда от такава възможност, има доста просто алтернативно решение - използвайте израза CASE като вход за агрегиращата функция:
Ето пълна заявка, която прави същото:
Това, което все още липсва в стандарта (от SQL 2008) и SQL Server 2012, е възможността за препращане към елементите на текущия ред за целите на филтрирането. Това може да се приложи в клауза FILTER, в алтернативно решение, използващо CASE израз, и в други случаи, когато е необходимо филтриране.
За да демонстрирате тази необходимост, представете си за секунда, че към текущия елемент на реда може да се направи препратка с помощта на префикса $ current_row. Сега си представете, че искате да напишете заявка за изгледа Sales.OrderValues, която изчислява за всяка поръчка разликата между стойността на текущата поръчка и средната стойност за конкретен служител за всички клиенти с изключение на клиента, който притежава поръчката . Тази задача се решава от следната заявка с клаузата FILTER:
Като алтернатива можете да използвате израза CASE:
Позволете ми да ви припомня още веднъж, че това са само моите изобретения, за да илюстрират липсата на езиковия стандарт, затова, както се казва, „не се опитвайте да повтаряте това у дома“.
Предложение за подобрение
Принципът на сравнителния прозорец изглежда интересен. Той е доста прост и задоволява необходимостта от препращане към елементи от текущия ред. Но това, което наистина ви кара да мърдате с глава, е това изключително готино предложение за разширяване на стандарта, наречен „разпознаване на шаблони в низове“ и решава проблема с достъпа до елементи от текущия низ, както и много други задачи.
Ключова дума DISTINCT в съвкупни функции
SQL Server 2012 не поддържа параметъра DISTINCT в обобщените прозоречни функции. Представете си, че искате да направите заявка за преглед на Sales.OrderValues и да получите за всяка поръчка броя на конкретните клиенти, с които текущият служител е работил от началото до днес. Трябва да изпълните заявка като тази:
Но тъй като това искане не се поддържа, трябва да се потърси решение. Една от опциите е да използвате функцията ROW_NUMBER. Ще говоря за това по-подробно малко по-късно, но засега е достатъчно да се каже, че връща уникална целочислена стойност за всеки ред в раздела, започвайки от един и на стъпки от 1, в съответствие с дефиницията за подреждане в прозореца. Използвайки функцията ROW_NUMBER, можете да присвоявате номера на редове, разделени от empid и custid и подредени по дата на поръчка. Това означава, че ред номер 1 се отнася за първия път, когато служител е работил с този клиент при поръчка на поръчки по дата. Използвайки израз CASE, можете да върнете custid само ако номерът на реда е 1 и в противен случай да върнете NULL. Ето заявка, която реализира описаната логика, с резултата от нейната работа: