Sql-де сапарларды қалай қорытындылауға болады. SQL агрегаттық функциялары - SUM, MIN, MAX, AVG, COUNT

Бұл оқулықта сіз қалай пайдалану керектігін үйренесіз SUM функциясы SQL серверінде (Transact-SQL) синтаксиспен және мысалдармен.

Сипаттама

SQL серверінде (Transact-SQL) SUM функциясыөрнектің жалпы мәнін қайтарады.

Синтаксис

SQL серверіндегі (Transact-SQL) SUM функциясының синтаксисі:

НЕМЕСЕ Нәтижелерді бір немесе бірнеше бағандар бойынша топтағанда SUM функциясының синтаксисі:

Параметрлер немесе аргументтер

өрнек1 , өрнек2 , ... өрнек_n - SUM функциясына қосылмаған өрнектер және SQL операторының соңында GROUP BY сөйлеміне қосылуы керек.
aggregate_expression - біріктірілетін баған немесе өрнек.
кестелер - жазбаларды алғыңыз келетін кестелер. FROM тармағында тізімделген кем дегенде бір кесте болуы керек.
WHERE шарттары - міндетті емес. Бұл таңдалған жазбалар үшін орындалуы керек шарттар.

Қолдану

SUM функциясын SQL Server (Transact-SQL) келесі нұсқаларында пайдалануға болады:
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Бір өрісі бар мысал

SQL серверінде (Transact-SQL) SUM функциясын пайдалану жолын түсіну үшін SQL Server SUM функциясының кейбір мысалдарын қарастырайық.

Мысалы, саны 10-нан асатын барлық өнімдердің жалпы санын білуге ​​болады.

SUM функциясының осы мысалында біз SUM(сан) өрнегін "Жалпы сан" деп атадық. Нәтижелер жиынын қайтарған кезде – өріс атауы ретінде «Жалпы саны» пайда болады.

DISTINCT пайдалану мысалы

SUM функциясында DISTINCT операторын пайдалануға болады. Мысалы, төмендегі SQL мәлімдемесі жалақы жылына 29 000 доллардан төмен болатын бірегей жалақы мәндері бар жалпы жалақыны қайтарады.

Егер екі жалақы жылына $24,000 болса, SUM функциясында осы мәндердің тек біреуі ғана пайдаланылады.

Формула қолдану мысалы

SUM функциясындағы өрнектің бір өріс болуы міндетті емес. Сондай-ақ формуланы қолдануға болады. Мысалы, сіз жалпы комиссияны есептей аласыз.

Transact-SQL

Тапсырыстардан "Жалпы комиссия" РЕТІНДЕ СУМ(сатылым * 0,03) ТАҢДАУ;

СОМДЫ ТАҢДАУ (сатылым * 0,03 ) «Жалпы комиссия» РЕТІНДЕ

тапсырыстардан;

GROUP BY қолдану мысалы

Кейбір жағдайларда SUM функциясымен GROUP BY операторын пайдалану қажет болады.

ЕСЕПТЕУ

Жиынтық функциялар

SQL сұрау өрнектері жиі деректерді алдын ала өңдеуді қажет етеді. Ол үшін арнайы функциялар мен өрнектер қолданылады.

Көбінесе белгілі бір сұрауға қанша жазба сәйкес келетінін білу керек,белгілі бір сандық бағанның мәндерінің қосындысы қандай, оның максималды, ең төменгі және орташа мәндері. Осы мақсатта қорытынды (статистикалық, жиынтық) деп аталатын функциялар қолданылады. Жиынтық функциялары, мысалы, WHERE сөйлемі арқылы көрсетілген жазбалар жиынын өңдейді. Егер сіз оларды SELECT мәлімдемесінен кейін бағандар тізіміне қоссаңыз, нәтиже кестеде дерекқор кестесінің бағандары ғана емес, сонымен қатар осы функциялармен есептелген мәндер де болады. Келесі болып табыладыжиынтық функциялар тізімі.

  • COUNT (параметр ) параметрде көрсетілген жазбалар санын қайтарады. Егер сіз барлық жазбалардың санын алғыңыз келсе, параметр ретінде жұлдызша (*) белгісін көрсетуіңіз керек. Параметр ретінде баған атын көрсетсеңіз, функция осы бағанның NULL мәнінен басқа мәндері бар жазбалар санын қайтарады. Бағанның қанша түрлі мән барын білу үшін баған атауының алдына DISTINCT кілт сөзін қойыңыз. Мысалы:

Клиенттерден COUNT(*) ТАҢДАУ;

Клиенттерден COUNT(Order_Amount) ТАҢДАУ;

Клиенттерден COUNT(DISTINCT Order_Amount) ТАҢДАУ;

Келесі сұрауды іске қосу қате туралы хабарға әкеледі:

Аймақты ТАҢДАУ , COUNT(*) Клиенттерден ;

  • SUM (параметр ) параметрде көрсетілген баған мәндерінің қосындысын қайтарады. Параметр сонымен қатар баған атауын қамтитын өрнек болуы мүмкін. Мысалы:

СОМДАНЫ ТАҢДАУ (Тапсырыс_сомасы) Клиенттерден;

Бұл SQL мәлімдемесі Тұтынушылар кестесіндегі Тапсырыс_сомасы бағанының барлық анықталған мәндерінің қосындысын қамтитын бір бағанды, бір жазбадан тұратын кестені қайтарады.

Бастапқы кестеде Тапсырыс_Сомасы бағанының мәндері рубльмен көрсетілген және жалпы соманы доллармен есептеу керек делік. Егер ағымдағы айырбас бағамы, мысалы, 27,8 болса, онда сіз өрнек арқылы қажетті нәтижені ала аласыз:

СОМДАНЫ ТАҢДАУ (Тапсырыс_сомасы*27.8) Клиенттерден;

  • AVG (параметр ) параметрде көрсетілген бағанның барлық мәндерінің орташа арифметикалық мәнін береді. Параметр баған атауын қамтитын өрнек болуы мүмкін. Мысалы:

Тұтынушылардан AVG (Тапсырыс_сомасы) ТАҢДАУ;

Клиенттерден AVG (Тапсырыс_сомасы*27,8) ТАҢДАҢЫЗ

ҚАЙДА Аймақ<>"Солтүстік_3батыс";

  • MAX (параметр ) параметрде көрсетілген бағандағы ең үлкен мәнді қайтарады. Параметр сонымен қатар баған атауын қамтитын өрнек болуы мүмкін. Мысалы:

Клиенттерден MAX (Тапсырыс_сомасы) ТАҢДАУ;

Клиенттерден МАКС (Тапсырыс_сомасы*27,8) ТАҢДАҢЫЗ

ҚАЙДА Аймақ<>"Солтүстік_3батыс";

  • MIN (параметр ) параметрде көрсетілген бағандағы ең аз мәнді қайтарады. Параметр баған атауын қамтитын өрнек болуы мүмкін. Мысалы:

Клиенттерден MIN(Order_Amount) ТАҢДАУ;

Клиенттерден MIN (Тапсырыс сомасы*27 . 8) ТАҢДАҢЫЗ

ҚАЙДА Аймақ<>"Солтүстік_3батыс";

Іс жүзінде сандық бағандардың жалпы, орташа, максималды және ең аз мәндерін қамтитын қорытынды кестені алу қажет. Ол үшін топтау (GROUP BY) және жиынтық функцияларын пайдалану керек.

Аймақты ТАҢДАУ, СУМ (Тапсырыс_сомасы) Клиенттерден

Аймақ БОЙЫНША ТОП;

Осы сұраныстың нәтижелер кестесінде аймақтардың атаулары және сәйкес аймақтардан барлық тұтынушылардан түскен тапсырыстардың жалпы (жалпы) сомалары бар (5-сурет).

Енді аймақ бойынша барлық жиынтық деректерді алу сұрауын қарастырыңыз:

Аймақты ТАҢДАУ, СУМ (Тапсырыс_сомасы), AVG (Тапсырыс_сомасы), MAX (Тапсырыс_сомасы), MIN (тапсырыс_сомасы)

Клиенттерден

Аймақ БОЙЫНША ТОП;

Түпнұсқа және нәтиже кестелері суретте көрсетілген. 8. Мысалда тек Солтүстік-Батыс аймағы бастапқы кестеде бірден көп жазбамен ұсынылған. Сондықтан оған арналған нәтижелер кестесінде әртүрлі жиынтық функциялар әртүрлі мәндерді береді.

Күріш. 8. Өңірлер бойынша тапсырыс сомаларының қорытынды кестесі

SELECT мәлімдемесінде бағандар тізімінде жиынтық функцияларды пайдаланған кезде нәтижелер кестесіндегі олардың сәйкес бағандарының тақырыптары Expr1001, Expr1002 және т.б. болады. (немесе SQL енгізуіне байланысты ұқсас нәрсе). Дегенмен, жиынтық функциялардың мәндері мен басқа бағандар үшін тақырыптарды өз қалауыңыз бойынша орнатуға болады. Бұл әрекетті орындау үшін, SELECT операторындағы бағаннан кейін пішіннің өрнегін көрсетіңіз:

AS баған_тақырыбы

AS (as) түйінді сөзі нәтижелер кестесіндегі сәйкес бағанда AS-тан кейін көрсетілген тақырып болуы керек екенін білдіреді. Тағайындалған тақырып бүркеншік ат деп те аталады. Келесі мысал (9-сурет) барлық есептелген бағандар үшін бүркеншік аттарды орнатады:

Аймақты ТАҢДАУ,

СУМ (Тапсырыс_сомасы) AS [Тапсырыстың жалпы сомасы],

AVG (Тапсырыс_сомасы) AS [Орташа тапсырыс сомасы],

MAX(Тапсырыс_сомасы) AS Ең көп,

MIN (Тапсырыс_сомасы) AS Минималды,

Клиенттерден

Аймақ БОЙЫНША ТОП;

Күріш. 9. Баған бүркеншік атын пайдалана отырып, өңірлер бойынша тапсырыс сомаларының қорытынды кестесі

Арасындағы бос орындармен бөлінген бірнеше сөзден тұратын лақап аттар төртбұрышты жақшаға алынады.

Жиынтық функцияларды SELECT және HAVING сөйлемдерінде қолдануға болады, бірақ оларды WHERE сөйлемдерінде қолдануға болмайды. HAVING операторы WHERE операторына ұқсас, бірақ WHERE операторынан айырмашылығы ол жазбаларды топтарда таңдайды.

Қай аймақтарда бірнеше клиент бар екенін анықтағыңыз келеді делік. Осы мақсатта сіз келесі сұрауды пайдалана аласыз:

Аймақты ТАҢДАУ, санау(*)

Клиенттерден

COUNT(*) > 1 АЙМАҚ БОЙЫНША ТОПТАУ;

Мәндерді өңдеу функциялары

Деректермен жұмыс істеу кезінде жиі оны өңдеуге (оны қажетті пішінге түрлендіруге) тура келеді: жолдағы ішкі жолды таңдау, алдыңғы және кейінгі бос орындарды жою, санды дөңгелектеу, квадрат түбірді есептеу, ағымдағы уақытты анықтау және т.б. SQL келесі үш функция түрі бар:

  • жол функциялары;
  • сандық функциялар;
  • күн-уақыт функциялары.

Жолдық функциялар

Жол функциялары жолды параметр ретінде қабылдайды және оны өңдегеннен кейін жолды немесе NULL мәнін қайтарады.

  • SUBSTRING (басынан бастап жол)параметр ретінде көрсетілген жолдан алынған ішкі жолды қайтарадытүзу . Ішкі жол сериялық нөмірі бастау параметрінде көрсетілген таңбадан басталады және ұзындық параметрінде көрсетілген ұзындығы бар. Жолдағы таңбалар 1-ден бастап солдан оңға қарай нөмірленеді. Мұндағы төртбұрышты жақшалар тек олардың ішіндегі өрнектің міндетті емес екенін көрсетеді. Егер өрнекҰЗЫНДЫҚ ҮШІН пайдаланылмайды, содан кейін ішінен ішкі жолБастау және бастапқы жолдың соңына дейін. Параметр мәндерібасталуы және ұзақтығы ізделетін ішкі жол шын мәнінде бастапқы жолдың ішінде болатындай етіп таңдалуы керек. Әйтпесе, SUBSTRING функциясы NULL мәнін қайтарады.

Мысалы:

SUBSTRING («Қымбатты Маша!» FROM 9 FOR 4) «Маша» қайтарады;

SUBSTRING («Құрметті Маша!» FROM 9) «Маша!» қайтарады;

SUBSTRING («Құрметті Маша!» FROM 15) NULL мәнін қайтарады.

Сіз бұл функцияны SQL өрнегінде пайдалана аласыз, мысалы, келесідей:

Клиенттерден * ТАҢДАУ

WHERE SUBSTRING(Region FROM 1 FOR 5) = "Солтүстік";

  • ЖОҒАРЫ(жол ) параметрде көрсетілген жолдың барлық таңбаларын бас әріпке түрлендіреді.
  • LOWER(жол ) параметрде көрсетілген жолдың барлық таңбаларын кіші әріпке түрлендіреді.
  • TRIM (ЖЕТЕКШІ | АРТЫ | BOTH ["таңба"] жолдан ) жолдан алдыңғы (LEADING), кейінгі (TRAILING) немесе екеуін де (BOTH) таңбаларды жояды. Әдепкі бойынша, жойылатын таңба бос орын (" "), сондықтан оны өткізіп жіберуге болады. Көбінесе бұл функция бос орындарды жою үшін қолданылады.

Мысалы:

TRIM (ЖЕТЕКШІ " " "Санкт-Петербор қаласы") "Санкт-Петербург қаласын" айналдырады;

TRIM(TRALING " " FROM "city of Petersburg") "city of Petersburg" қайтарады;

TRIM (ЕКІ " " "ҚАЛАДАН" Санкт-Петербург ") қайтарады "қала Санкт-Петербург";

TRIM(BOTH FROM " city of Petersburg ") "city of Петербург" қайтарады;

TRIM(BOTH "g" FROM "city of Petersburg") "city of Petersburg" қайтарады.

Осы функциялардың ішінде ең жиі қолданылатындары SUBSTRING() ЖӘНЕ TRIM() болып табылады.

Сандық функциялар

Сандық функциялар тек сандық түрдегі деректерді параметр ретінде қабылдай алмайды, бірақ әрқашан санды немесе NULL (анықталмаған мән) қайтарады.

  • POSITION ( targetString IN жолы) көрсетілген жолда мақсатты жолдың пайда болуын іздейді. Іздеу сәтті болса, оның бірінші таңбасының позиция нөмірін қайтарады, әйтпесе 0. Мақсатты жолдың ұзындығы нөлге тең болса (мысалы, « » жолы), онда функция 1 мәнін қайтарады. Параметрлердің кем дегенде біреуі NULL болса. , содан кейін NULL қайтарылады. Жол таңбалары 1-ден бастап солдан оңға қарай нөмірленеді.

Мысалы:

POSITION("e" В "Бәріне сәлем") 5 қайтарады;

POSITION («Бәріне» «Бәріне сәлем») 8 қайтарады;

POSITION(" " Барлығына сәлем") 1 қайтарады;

POSITION («Сәлеметсіз бе!» В «Бәріне сәлем») 0 мәнін қайтарады.

Клиенттер кестесінде (1-суретті қараңыз) Мекенжай бағанында қала атауынан басқа пошталық индекс, көше атауы және басқа да деректер бар. Белгілі бір қалада тұратын тұтынушылар үшін жазбаларды таңдау қажет болуы мүмкін. Сонымен, егер сіз Санкт-Петербургте тұратын клиенттерге қатысты жазбаларды таңдағыңыз келсе, келесі SQL сұрау өрнегін пайдалана аласыз:

Клиенттерден * ТАҢДАУ

ҚАЙДАҒЫ ПОЗИЦИЯ («Санкт-Петербург» IN Мекен-жайы ) > 0;

Бұл қарапайым деректерді іздеу сұрауын басқаша тұжырымдауға болатындығын ескеріңіз:

Клиенттерден * ТАҢДАУ

ҚАЙДА "%Петербург%" LIKE мекенжайы;

  • EXTRACT (параметр ) элементті күн-уақыт мәнінен немесе интервалдан шығарады. Мысалы:

ҮШІНШІ («2005-10-25» КҮНІ БАСТАП АЙ) 10 қайтарады.

  • CHARACTER_LENGTH(жол ) жолдағы таңбалар санын қайтарады.

Мысалы:

CHARACTER_LENGTH("Бәріне сәлем") 11 мәнін қайтарады.

  • OCTET_LENGTH(жол ) жолдағы октеттердің (байттардың) санын қайтарады. Әрбір латын немесе кириллица таңбалары бір байтпен, ал қытай әліпбиінің таңбасы екі байтпен берілген.
  • CARDINALITY (параметр ) параметр ретінде элементтер жиынын қабылдайды және коллекциядағы элементтердің санын қайтарады (негізгі сан). Жинақ, мысалы, массив немесе әртүрлі типтегі элементтерді қамтитын көп жиын болуы мүмкін.
  • ABS (сан ) санның абсолютті мәнін береді. Мысалы:

ABS (-123) 123 қайтарады;

ABS (2 - 5) 3 қайтарады.

  • MO D (сан 1, нөмір 2 ) бірінші санның екінші санға бөлінуінің қалған бөлігін қайтарады. Мысалы:

MOD(5, h) 2 қайтарады;

MOD(2, h) 0 қайтарады.

  • LN (сан ) санның натурал логарифмін береді.
  • EXP (сан) нөмірді қайтарады (санның дәрежесіне натурал логарифмнің негізі).
  • ҚУАТ (сан 1, сан 2 ) 1 санын қайтарадысаны2 (1 саны 2 санының дәрежесіне).
  • SQRT (сан ) санның квадрат түбірін береді.
  • FLOOR (сан ) параметрмен көрсетілгеннен аспайтын ең үлкен бүтін санды қайтарады (төмен қарай дөңгелектеу). Мысалы:

FLOOR (5.123) 5.0 қайтарады.

  • CEIL (сан) немесе CEILING (сан ) дөңгелектеу параметрімен көрсетілген мәннен кем емес ең кіші бүтін санды қайтарады). Мысалы:

CEIL(5,123) 6,0 қайтарады.

  • WIDTH_BUCKET (number1, number2, number3, number4) 0 мен сан4 + 1 арасындағы диапазондағы бүтін санды қайтарады. Сан2 және сан3 параметрлері тең интервалдарға бөлінген сандық интервалды көрсетеді, олардың саны сан4 параметрімен белгіленеді мән түсетін интервалдың саны1 саны. Егер 1 саны көрсетілген ауқымнан тыс болса, функция 0 немесе 4 + 1 санын қайтарады. Мысалы:

WIDTH_BUCKET(3.14, 0, 9, 5) 2 қайтарады.

Күн-уақыт функциялары

SQL-де ағымдағы күн мен уақытты қайтаратын үш функция бар.

  • CURRENT_DATE ағымдағы күнді қайтарады (күн түрі).

Мысалы: 2005-06-18.

  • CURRENT_TIME (сан ) ағымдағы уақытты қайтарады (TIME түрі). Integer параметрі секундтар көрсетілімінің дәлдігін анықтайды. Мысалы, 2 мәні ең жақын жүздікке дейінгі секундты білдіреді (екі ондық таңба):

12:39:45.27.

  • CURRENT_TIMESTAMP (сан ) күн мен уақытты қайтарады (TIMESTAMP түрі). Мысалы, 2005-06-18 12:39:45.27. Integer параметрі секундтар көрсетілімінің дәлдігін анықтайды.

Бұл функциялар қайтаратын күн мен уақыт таңба түрі емес екенін ескеріңіз. Егер сіз оларды таңбалар жолы ретінде көрсеткіңіз келсе, мұны істеу үшін CAST() түріндегі түрлендіру функциясын пайдалануыңыз керек.

Күн-уақыт функциялары әдетте деректерді кірістіру, жаңарту және жою үшін сұрауларда қолданылады. Мысалы, сату туралы ақпаратты жазу кезінде осы мақсат үшін берілген бағанға ағымдағы күн мен уақыт енгізіледі. Бір айдың немесе тоқсанның нәтижелерін шығарғаннан кейін есепті кезеңдегі сату деректерін жоюға болады.

Есептелген өрнектер

Есептелетін өрнектер тұрақты мәндерден (сандық, жолдық, логикалық), функциялардан, өріс атауларынан және басқа деректер түрлерінен арифметикалық, жолдық, логикалық және басқа операторлармен байланыстыру арқылы құрастырылады. Өз кезегінде өрнектерді операторлар арқылы күрделірек (құрама) өрнектерге біріктіруге болады. Жақшалар өрнектердің бағалану ретін басқару үшін пайдаланылады.

Логикалық операторларЖӘНЕ, НЕМЕСЕ және ЕМЕС және функциялары бұрын талқыланған.

Арифметикалық операторлар:

  • + қосу;
  • - азайту;
  • * көбейту;
  • /бөлім.

Жол операторытек бір жалғау немесе жолды жалғау операторы (| |). Кейбір SQL іске асырулары (мысалы, Microsoft Access) (| |) орнына (+) таңбасын пайдаланады. Біріктіру операторы екінші жолды бірінші мысалдың, өрнектің соңына қосады:

«Саша» | | «сүйеді» | | «Қол бұлғау»

нәтижесінде «Саша Машаны жақсы көреді» жолын қайтарады.

Өрнектерді құрастыру кезінде операторлардың операндтары жарамды типте болуын қамтамасыз ету керек. Мысалы, өрнек: 123 + «Саша» дұрыс емес, себебі арифметикалық қосу операторы жол операндына қолданылады.

Есептелген өрнектер SELECT операторынан кейін, сондай-ақ WHERE және HAVI операторларының шарт өрнектерінде пайда болуы мүмкін.Н.Г.

Бірнеше мысалды қарастырайық.

Сатылымдар кестесінде Өнім түрі, Саны және Баға бағандарына рұқсат етіңіз және біз әрбір өнім түріне арналған табысты білгіміз келеді. Ол үшін SELECT операторынан кейінгі бағандар тізіміне Quantity*Price өрнегін енгізіңіз:

Өнім_түрін, Саны, Бағаны, Саны*Бағаны ТАҢДАҢЫЗ AS

Сатудан түскен жалпы сомасы;

Бұл есептелген деректер бағанының бүркеншік атын көрсету үшін AS (as) кілт сөзін пайдаланады.

Суретте. 10-суретте бастапқы Сатылымдар кестесі және сұрау нәтижелерінің кестесі көрсетілген.

Күріш. 10. Өнімнің әрбір түрі бойынша кірісті есептеумен сұраудың нәтижесі

Егер сіз барлық тауарларды сатудан түскен жалпы кірісті білгіңіз келсе, келесі сұрауды пайдаланыңыз:

СОМДАНЫ ТАҢДАУ (Саны*Бағасы) Сатудан;

Келесі сұрауда бағандар тізімінде де, WHERE сөйлемінің шартында да есептелген өрнектер бар. Ол сату кестесінен сатудан түскен түсім 1000-нан асатын өнімдерді таңдайды:

Өнімнің_түрін ТАҢДАҢЫЗ, Саны*Бағасы Барлығы

Сатудан

ҚАЙДА Саны*Бағасы > 1000;

Екі бағанасы бар кестені алғыңыз келеді делік:

Өнім түрі мен бағасын қамтитын өнім;

Табысты қамтитын жиынтық.

Бастапқы сатылымдар кестесіндегі Өнім_Түрі бағанасы таңба (CHAR түрі) және Баға бағаны сандық болады деп болжанатындықтан, осы бағандардағы деректерді біріктіру (жабыстыру) кезінде сан түрін таңба түріне көшіру қажет. CAST() функциясы. Бұл тапсырманы орындайтын сұрау келесідей болады (Cурет 11):

Өнім_түрін ТАҢДАУ | | " (Бағасы: " | | CAST(Бағасы CHAR(5)) | | ")" AS Өнім, Саны*Бағасы AS Барлығы

Сатудан;

Күріш. 11. Бір бағандағы әртүрлі деректер түрлерін біріктіретін сұрау нәтижесі

Ескерту. Microsoft Access бағдарламасында ұқсас сұрау келесідей болады:

Өнім_түрін ТАҢДАҢЫЗ + " (Бағасы: " + C Str (Бағасы) + ")" AS элементі,

Саны*Баға AS Барлығы

Сатудан;

CASE операторы бар шартты өрнектер

Кәдімгі бағдарламалау тілдерінде кейбір шарттардың дұрыс немесе дұрыс еместігіне байланысты есептеу процесін басқаруға мүмкіндік беретін шартты өту операторлары бар. SQL тілінде бұл оператор CASE (жағдай, жағдай, мысал). SQL:2003 нұсқасында бұл оператор мәнді қайтарады, сондықтан оны өрнектерде пайдалануға болады. Оның екі негізгі формасы бар, біз оларды осы бөлімде қарастырамыз.

Мәндері бар CASE операторы

Мәндері бар CASE операторында келесі синтаксис бар:

CASE тексерілген_мәні

WHEN мәні1 СОНДА нәтиже1

ҚАҒАН мән2 СОНДА нәтиже2

. . .

ҚАШАН N мәні содан кейін N нәтижесі

БАСҚА нәтижеX

Егер тексерілген_мән 1 мәніне тең , CASE операторы мәнді қайтарадынәтиже 1 , THEN кілт сөзінен кейін көрсетілген. Әйтпесе, тексерілген_мәнмен салыстырыладымән2 , және олар тең болса, нәтиже2 мәні қайтарылады. Әйтпесе, тексерілетін мән WHEN кілт сөзінен кейін көрсетілген келесі мәнмен және т.б. салыстырылады. Егер тексерілген_мән осы мәндердің ешқайсысына тең болмаса, мән қайтарылады.нәтиже X , ELSE (else) кілт сөзінен кейін көрсетілген.

ELSE кілт сөзі міндетті емес. Егер ол жоқ болса және салыстырылатын мәндердің ешқайсысы тексерілетін мәнге тең болмаса, CASE операторы NULL мәнін қайтарады.

Айталық, Клиенттер кестесінің негізінде (1-суретті қараңыз) сіз аймақтардың атаулары олардың код нөмірлерімен ауыстырылатын кестені алғыңыз келеді. Егер бастапқы кестеде әртүрлі аймақтар тым көп болмаса, онда бұл мәселені шешу үшін CASE операторымен сұранысты пайдалану ыңғайлы:

ТАҢДАУ Аты, мекенжайы,

CASE аймағы

«Мәскеу» КЕЗІНДЕ «77»

«Тверь облысы» КЕЗІНДЕ «69»

. . .

Басқа аймақ

AS аймақ коды

Клиенттерден;

Іздеу шарттары бар CASE мәлімдемесі

CASE операторының екінші формасы белгілі бір шартты қанағаттандыратын жазбалар үшін кестені іздеу кезінде оны пайдалануды қамтиды:

CASE

ҚАШАН 1-шарт содан кейін нәтиже1

CATCH2 ҚАШАН СОНДА нәтиже2

. . .

КЕЗДЕ N шарты ОНДА Нәтиже N

БАСҚА нәтижеX

CASE операторы WHERE сөйлемімен анықталған жиынның бірінші жазбасы үшін 1-шарттың дұрыстығын немесе WHERE болмаса, бүкіл кестені тексереді. Егер иә болса, CASE нәтижесі 1 қайтарады. Әйтпесе, бұл жазба үшін 2-шарт тексеріледі. Егер ол шын болса, нәтиже2 мәні қайтарылады, т.б.. Шарттардың ешқайсысы ақиқат болмаса, мән нәтижесі қайтарылады. X , ELSE кілт сөзінен кейін көрсетілген.

ELSE кілт сөзі міндетті емес. Егер ол жоқ болса және шарттардың ешқайсысы дұрыс болмаса, CASE операторы NULL мәніне айналады. CASE бар оператор бірінші жазба үшін орындалғаннан кейін ол келесі жазбаға өтеді. Бұл жазбалардың бүкіл жинағы өңделгенше жалғасады.

Кітап кестесінде (Тақырып, Баға) сәйкес кітап қоймада жоқ болса, баған NULL болады делік. Келесі сұрау NULL орнына «Қорда жоқ» көрсететін кестені қайтарады:

ТАҢДАУ Тақырыбы,

CASE

БАҒА НӨЛ БОЛҒАН КЕЗДЕ, "Қоймада жоқ"

БАСҚА КӨРСЕТУ(бағасы CHAR(8))

AS бағасы

Кітаптардан;

Бір бағандағы барлық мәндер бір типте болуы керек. Сондықтан, бұл сұрау Баға бағанының сандық мәндерін таңба түріне шығару үшін CAST түрін түрлендіру функциясын пайдаланады.

Біріншісінің орнына әрқашан CASE мәлімдемесінің екінші түрін пайдалануға болатынын ескеріңіз:

CASE

Сынақталған_мән = мән1 СОДАН СОДАН 1 нәтиже

Сынақталған_мән = мән2 СОДАН 2 нәтиже

. . .

WHEN checked_value = мән N СОНДА нәтижеN

ELSE нәтиже

NULLIF және COALESCE функциялары

Кейбір жағдайларда, әсіресе деректерді жаңарту сұрауларында (UPDATE операторы) ауыр CASE операторының орнына ықшам NULLIF() (NULL болса) және COALESCE() (біріктіру) функцияларын пайдалану ыңғайлы.

NULLIF функциясы ( мән1, мән2) NULL мәнін қайтарады, егер бірінші параметрдің мәні сәйкес келмеген жағдайда екінші параметрдің мәніне сәйкес келсе, бірінші параметрдің мәні өзгеріссіз қайтарылады; Яғни, теңдік мәні1 = мән2 ақиқат болса, функция NULL мәнін қайтарады, әйтпесе мән1 мәні.

Бұл функция келесі екі пішіндегі CASE операторына баламалы:

  • CASE мәні1

ҚАШАН мәні2 ОНДА NULL

ELSE мәні1

  • CASE

ҚАШАН мән1 = мән2 ОДАН NULL

ELSE мәні1

COALESCE функциясы( мән1, мән2, ... ,мәні N) NULL немесе NULL болуы мүмкін мәндер тізімін қабылдайды. Функция тізімнен көрсетілген мәнді қайтарады немесе барлық мәндер анықталмаған болса NULL.

Бұл функция келесі CASE операторына баламалы:

CASE

1-мән НҰЛ БОЛМАСА, 1-мән

2-мән НҰЛ БОЛМАСА, 2-мән

. . .

КЕЗДЕ N мәні НҰЛ БОЛМАСА, N мәні

ELSE NULL

Кітаптар (атауы, бағасы) кестесіндегі сәйкес кітап қоймада жоқ болса, Баға бағаны NULL болсын делік. Келесі сұрау орнына кестені қайтарады NULL «Қорда жоқ» мәтіні көрсетіледі:

ТАҢДАУ Аты, COALESCE (CAST(бағасы CHAR(8)),

«Қорда жоқ») AS Бағасы

Кітаптардан;

Бұл тағы бір ортақ міндет. Негізгі принцип - басқа атрибут немесе атрибуттар (реттеу элементі) бойынша реттілік негізінде бір атрибуттың (жиынтық элемент) мәндерін жинақтау, мүмкін басқа атрибут немесе атрибуттар (бөлу элементі) негізінде анықталған жол бөлімдері бар. . Өмірде жинақталған қорытындыларды есептеудің көптеген мысалдары бар, мысалы, банк шотындағы қалдықтарды есептеу, қоймадағы тауарлардың бар-жоғын қадағалау немесе ағымдағы сату көрсеткіштері және т.б.

SQL Server 2012 нұсқасына дейін іске қосылған жиынтықтарды есептеу үшін пайдаланылатын жиынтыққа негізделген шешімдер ресурстарды өте қажет ететін. Сондықтан адамдар баяу, бірақ кейбір жағдайларда жиынтық шешімдерге қарағанда тезірек болатын итеративті шешімдерге жүгінуге бейім болды. SQL Server 2012 жүйесіндегі терезе функцияларын кеңейтілген қолдау арқылы іске қосылатын қорытындыларды ескі T-SQL негізіндегі шешімдерге қарағанда әлдеқайда жақсырақ жұмыс істейтін қарапайым жиынға негізделген код арқылы есептеуге болады (жиынға негізделген және итеративті). Мен жаңа шешімді көрсетіп, келесі бөлімге өте аламын; бірақ өзгеріс ауқымын шынымен түсінуге көмектесу үшін мен ескі әдістерді сипаттаймын және олардың өнімділігін жаңа тәсілмен салыстырамын. Әрине, сіз жаңа тәсілді сипаттайтын бірінші бөлімді ғана оқып, мақаланың қалған бөлігін өткізіп жібере аласыз.

Мен әртүрлі шешімдерді көрсету үшін шот баланстарын қолданамын. Міне, аздаған сынақ деректерімен транзакциялар кестесін жасайтын және толтыратын код:

NO COUNT ҚОСУ; TSQL2012 ПАЙДАЛАНУ; ЕГЕР OBJECT_ID("dbo.Transactions", "U") NULL TROP 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 жолында транзакция сомаларын жинақтау арқылы уақыттың әрбір нүктесінде шоттың қалдығын есептеу болып табылады және бұл әрбір шот үшін бөлек жасалуы керек. Қажетті нәтиже келесідей болуы керек:

Екі шешімді де тексеру үшін қосымша деректер қажет. Мұны келесідей сұрау арқылы жасауға болады:

@num_partitions INT = 10, @rows_per_partition ретінде INT = 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)) dbo.GetNums(1, @num_partitions) FROM NP CROSS JOIN AS RPP AS dbo.GetNums(1, @rows_per_partition);

Бөлімдегі бөлімдердің (есептік жазбалардың) және жолдардың (транзакциялардың) санын өзгерту үшін енгізулерді орнатуға болады.

Терезе функцияларын пайдаланатын жиынтық шешім

Мен SUM терезесін біріктіру функциясын пайдаланатын жиынға негізделген шешімнен бастаймын. Мұнда терезенің анықтамасы анық: терезені actid бойынша бөлу керек, транид бойынша тапсырыс беру және сүзгіні пайдаланып кадрдағы жолдарды ең төменгі (ШЕКСІЗ АЛДЫДА) ағымдағыға дейін таңдау керек. Міне, сәйкес сұрау:

ТАҢДАУ actid, tranid, val, SUM(val) OVER(PARTITION BY BY actid ORDER БОЙЫНША ТРАНИД БОЙЫНША ШЕКСІЗ АЛДЫНДАҒЫ ЖӘНЕ АҒЫМДАҒЫ ҚАТТАР АРАСЫНДАҒЫ ҚАТТАР) dbo.Transactions-тен теңгерім ретінде;

Бұл код қарапайым және қарапайым ғана емес, сонымен қатар жылдам. Бұл сұраудың жоспары суретте көрсетілген:

Кестеде POC талаптарына сәйкес келетін және терезе функциялары арқылы пайдалануға болатын кластерленген индексі бар. Атап айтқанда, индекс кілттер тізімі бөлу элементіне (actid) және одан кейін тапсырыс элементіне (tranid) негізделген және индекс қамтуды қамтамасыз ету үшін сұраудағы (val) барлық басқа бағандарды да қамтиды. Жоспар реттелген сканерлеуді, одан кейін ішкі қажеттіліктер үшін жол нөмірін есептеуді, содан кейін терезе жиынын қамтиды. POC индексі болғандықтан, оңтайландырушы жоспарға сұрыптау операторын қосудың қажеті жоқ. Бұл өте тиімді жоспар. Сонымен қатар, ол сызықты түрде масштабталады. Кейінірек өнімділікті салыстыру нәтижелерін көрсеткенде, бұл әдіс ескі шешімдермен салыстырғанда қаншалықты тиімді екенін көресіз.

SQL Server 2012 бағдарламасына дейін ішкі сұраулар немесе біріктірулер пайдаланылды. Ішкі сұрауды пайдаланған кезде орындалатын қорытындылар сыртқы жолдағыдай актид мәні және сыртқы жолдағы мәннен кіші немесе оған тең транид мәні бар барлық жолдарды сүзу арқылы есептеледі. Содан кейін біріктіру сүзгіден өткен жолдарға қолданылады. Міне, сәйкес сұрау:

Ұқсас тәсіл қосылымдар арқылы жүзеге асырылуы мүмкін. Біріктірудің ON сөйлеміндегі ішкі сұраудың WHERE сөйлеміндегідей предикат қолданылады. Бұл жағдайда, T1 белгіленген данасында сол A есептік жазбасының N-ші транзакциясы үшін T2 данасында 1-ден N-ге дейінгі транзакция нөмірлері бар N сәйкестікті табасыз. Сәйкестіктердің нәтижесінде T1 жолдары қайталанады, сондықтан ағымдағы транзакция туралы ақпарат алу үшін T1-ден барлық элементтер бойынша жолдарды топтау керек және орындалатын жиынтықты есептеу үшін T2-ден val атрибутына біріктіруді қолдану керек. Аяқталған сұрау келесідей көрінеді:

T1.actid, T1.tranid, T1.val, T1.val, SUM(T2.val) dbo. Transactions AS T1 JOIN dbo. Transactions AS T2 AS ON T2.actid = T1.actid ЖӘНЕ T2.tranid ТАҢДАҢЫЗ.<= T1.tranid GROUP BY T1.actid, T1.tranid, T1.val;

Төмендегі суретте екі шешімнің жоспарлары көрсетілген:

Екі жағдайда да кластерленген индексті толық сканерлеу T1 данасында орындалатынын ескеріңіз. Содан кейін жоспардағы әрбір жол үшін индекстің соңғы бетіндегі ағымдағы шот бөлімінің басының индексінде T2.tranid T1 мәнінен аз немесе оған тең болатын барлық транзакцияларды оқитын іздеу операциясы бар. тұрақты. Жолдарды біріктіру орын алатын нүкте жоспарларда сәл өзгеше, бірақ оқылатын жолдар саны бірдей.

Қанша жол қаралып жатқанын түсіну үшін деректер элементтерінің санын ескеру қажет. Бөлімдердің (шоттардың) саны 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 бойынша сұрыптайтын сұрау негізінде жарияланады. Осыдан кейін курсор жазбалары арқылы қайталанатын өту орындалады. Жаңа тіркелгі анықталғанда, жиынтықты қамтитын айнымалы мән қалпына келтіріледі. Әрбір итерацияда айнымалыға жаңа транзакция сомасы қосылады, одан кейін жол ағымдағы транзакция туралы ақпаратпен және орындалатын жиынтықтың ағымдағы мәнімен кесте айнымалысында сақталады. Итеративті өтуден кейін кесте айнымалысының нәтижесі қайтарылады. Міне, аяқталған шешімнің коды:

@Nәтижені Кесте ретінде ЖАРИЯЛАУ (actid INT, tranid INT, val MONEY, ақша балансы); @actid - INT, @prvactid - INT, @tranid - INT, @val - АҚША, @balance - АҚША РЕТІНДЕ ЖАРИЯЛАУ; ТАҢДАУ ҮШІН C МЕҢГЕРІСІН ЖЫЛДАМ_АЛҒА ЖАРИЯЛАУ. АШЫҚ C КЕЛІСІМДЕРІНЕН КЕЛЕСІ АЛУ @actid, @tranid, @val; ТАҢДАУ @prvactid = @actid, @balance = 0; WHILE @@fetch_status = 0 БАСТАҢЫЗ IF @actid<>@prvactid SELECT @prvactid = @actid, @balance = 0; SET @balance = @balance + @val; INSERT INTO @Result VALUES(@actid, @tranid, @val, @balance); C-ДЕН КЕЛЕСІ АЛУ @actid, @tranid, @val; АЯҚТАУ CLOSE C; DEALLOCATE C; ТАҢДАУ * FROM @Result;

Курсорды қолданатын сұрау жоспары суретте көрсетілген:

Бұл жоспар сызықтық масштабталады, себебі индекстегі деректер белгілі бір ретпен тек бір рет сканерленеді. Сондай-ақ, курсордан жолды шығаруға арналған әрбір әрекет жолдың шамамен бірдей құнына ие. Егер курсордың бір жолын өңдеу арқылы жасалған жүктемені g-ге тең деп алсақ, бұл шешімнің құнын p*r + p*r*g ретінде бағалауға болады (есіңізде болса, p - бөлімдер саны, ал r - бұл бөлімдегі жолдар саны). Сонымен, егер сіз секциядағы жолдар санын f есе арттырсаңыз, жүйеге жүктеме p*r*f + p*r*f*g болады, яғни ол сызықты түрде өседі. Бір жолды өңдеу құны жоғары, бірақ масштабтаудың сызықтық сипатына байланысты белгілі бір бөлім өлшемінен бұл шешім кірістірілген сұрауға қарағанда жақсырақ масштабталады және осы шешімдердің квадраттық масштабталуына байланысты біріктіруге негізделген шешімдерге ие болады. Мен орындаған өнімділік өлшемдері курсор шешімі жылдамырақ болатын сан бөлім үшін бірнеше жүз жолды құрайтынын көрсетеді.

Меңзерге негізделген шешімдермен қамтамасыз етілген өнімділік артықшылықтарына қарамастан, олар реляциялық емес болғандықтан, әдетте олардан аулақ болу керек.

CLR негізіндегі шешімдер

Бір ықтимал шешім негізінде CLR (жалпы тілдің орындалу уақыты)негізінен курсорды қолданатын шешім түрі болып табылады. Айырмашылығы мынада: келесі жолды алу және қайталау үшін көп ресурстарды ысырап ететін T-SQL курсорын пайдаланудың орнына сіз әлдеқайда жылдамырақ .NET SQLDataReader және .NET итерацияларын пайдаланасыз. Бұл опцияны жылдамдататын CLR мүмкіндіктерінің бірі - нәтиже жолының уақытша кестеде қажет еместігі - нәтижелер тікелей шақыру процесіне жіберіледі. CLR негізіндегі шешімнің логикасы курсор мен T-SQL шешіміне ұқсас. Мұнда шешудің сақталған процедурасын анықтайтын C# коды берілген:

Жүйені пайдалану; System.Data пайдалану; System.Data.SqlClient пайдалану; System.Data.SqlTypes пайдалану; Microsoft.SqlServer.Server пайдалану; жалпыға ортақ ішінара сынып StoredProcedures ( public static void AccountBalances() (пайдалану (SqlConnection conn = new SqlConnection("context connection=true;")) ( SqlCommand comm = new SqlCommand(); comm.Connection = conn; comm.CommandText = @" " + "SELECT actid, tranid, val " + "FROM dbo.Transactions " + "ORDER BY actid, tranid;"; SqlMetaData бағандары = жаңа SqlMetaData; бағандар = жаңа SqlMetaData("actid" , SqlDbType.Int); бағандар = жаңа SqlMetaData("tranid" , SqlDbType.Int бағандары = new SqlMetaData("val", SqlDbType.Money бағандары = new SqlMetaData("balance", SqlDataRecord(columns); Pipe.SendResultsStarq(recorder); .ExecuteReader(); SqlInt32 prvactid = 0 while (reader.Read()) ; prvactid = actid.SetSqlInt32(0, reader.GetSqlInt32(0)); record.SetSqlInt32(1, reader.GetSqlInt32(1)); record.SetSqlMoney(2, val); record.SetSqlMoney(3, баланс); SqlContext.Pipe.SendResultsRow(жазба); ) SqlContext.Pipe.SendResultsEnd(); )))

Бұл сақталған процедураны SQL серверінде орындау үшін алдымен осы код негізінде AccountBalances деп аталатын жинақты құрастырып, оны TSQL2012 дерекқорына орналастыру керек. SQL серверінде жинақтарды қолданумен таныс болмасаңыз, Сақталған процедуралар мақаласындағы Сақталған процедуралар және CLR бөлімін оқығыңыз келуі мүмкін.

Жинақты AccountBalances деп атасаңыз және құрастыру файлына апаратын жол "C:\Projects\AccountBalances\bin\Debug\AccountBalances.dll" болса, жинақты дерекқорға жүктеп, сақталған процедураны келесі кодпен тіркей аласыз:

"C:\Projects\AccountBalances\bin\Debug\AccountBalances.dll" ҚОЙЫНАН ЖИНАУ Тіркелгі баланстарын ЖАСАУ; ӨТУ ПРОЦЕДУРАСЫ dbo.AccountBalances СЫРТҚЫ АТ РЕТІНДЕ AccountBalances.StoredProcedures.AccountBalances;

Жинақты орналастырып, процедураны тіркегеннен кейін оны келесі кодпен орындауға болады:

EXEC dbo.AccountBalances;

Жоғарыда айтқанымдай, SQLDataReader - бұл курсордың басқа түрі, бірақ бұл нұсқа T-SQL-де дәстүрлі курсорды пайдаланудан гөрі жолдарды оқуға жұмсалатын шығынды азайтады. Итерациялар .NET-те T-SQL-ге қарағанда әлдеқайда жылдамырақ. Осылайша, CLR негізіндегі шешімдер де сызықты түрде масштабталады. Тестілеу бұл шешімнің өнімділігі бөлімдегі жолдар саны 15-тен асқанда ішкі сұрауларды және біріктіруді пайдаланатын шешімдердің өнімділігінен жоғары болатынын көрсетті.

Аяқтаған кезде келесі тазалау кодын іске қосу керек:

TROP PROCEDURE dbo.AccountBalances; DROP ASSEMBLY Account Balances;

Кірістірілген итерациялар

Осы уақытқа дейін мен итеративті және жиынтыққа негізделген шешімдерді көрсеттім. Келесі шешім кірістірілген итерацияларға негізделген, ол итерациялық және жиынтыққа негізделген тәсілдердің гибриді болып табылады. Бұл идея алдымен бастапқы кестедегі жолдарды (біздің жағдайда банктік шоттар) ROW_NUMBER функциясы арқылы есептелетін rownum деп аталатын жаңа атрибутпен бірге уақытша кестеге көшіру болып табылады. Жол нөмірлері actid арқылы бөлінген және транид бойынша реттелген, сондықтан әрбір банктік шоттағы бірінші операцияға 1 нөмірі, екінші транзакцияға 2 нөмірі және т.б. Содан кейін уақытша кестеде кілттер тізімі (rownum, actid) бар кластерленген индекс құрылады. Рекурсивті CTE өрнегі немесе арнайы жасалған цикл барлық тіркелгілерде итерацияға бір жолды өңдеу үшін пайдаланылады. Ағымдағы жолға қатысты мәнді алдыңғы жолға қатысты мәнге қосу арқылы орындалатын жиынтық есептеледі. Міне, осы логиканың рекурсивті CTE көмегімен жүзеге асырылуы:

ТАҢДАУ actid, tranid, val, ROW_NUMBER() AVER(PARTITION BY BY actid ORDER БY tranid) rownum INTO #Transactions FROM dbo.Transactions; Бірегей КЛАСТЕРЛІК ИНДЕКС ЖАСАУ idx_rownum_actid ON #Transactions(rownum, actid); C AS МЕН (ТАҢДАУ 1 AS ROwnum, actid, tranid, val, AS sumqty FROM #Transactions WHERE rownum = 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 = PRV.rownum + 1 ЖӘНЕ CUR.actid = PRV.actid) C OPTION FROM actid, tranid, val, sumqty SELECT (MAXRECURSION 0); DROP TABLE #Транзакциялар;

Және бұл айқын циклды қолданатын іске асыру:

ROW_NUMBER() АРТТЫ (АКТИД БОЙЫНША ТАПСЫРЫС БОЙЫНША БӨЛІМ) ROwnum, actid, tranid, val, CAST(val AS BYGINT) ЕСЕП ТАҢДАҢЫЗ #Transactions FROM dbo.Transactions; Бірегей КЛАСТЕРЛІК ИНДЕКС ЖАСАУ idx_rownum_actid ON #Transactions(rownum, actid); @rownum INT РЕТІНДЕ ЖАРИЯЛАУ; SET @rownum = 1; WHILE 1 = 1 BAGIN SET @rownum = @rownum + 1; ЖАҢАРТУ CUR SET sumqty = PRV.sumqty + CUR.val FROM #Transactions CUR JOIN AS PRV AS CUR.rownum = @rownum ЖӘНЕ PRV.rownum = @rownum - 1 ЖӘНЕ CUR.actid = PRV.actid; ЕГЕР @@rowcount = 0 BREAK; END SELECT actid, tranid, val, sumqty FROM #Transactions; DROP TABLE #Транзакциялар;

Бұл шешім бөлімде аз жолдар саны бар бөлімдердің көп саны болған кезде жақсы өнімділікті қамтамасыз етеді. Сонда қайталану саны аз, ал жұмыстың негізгі бөлігін бір жол нөмірімен байланысты жолдарды алдыңғы қатар нөмірімен байланыстыратын жолдармен байланыстыратын шешімнің жиынтыққа негізделген бөлігі орындайды.

Айнымалылары бар көп жолды жаңарту

Осы нүктеге дейін көрсетілген жиынтық қорытындыларды есептеу әдістері дұрыс нәтиже беретініне кепілдік беріледі. Бұл бөлімде сипатталған әдіс қарама-қайшы, өйткені ол құжатталған емес, бақыланатын жүйелік мінез-құлыққа негізделген және ол салыстырмалылық принциптеріне де қайшы келеді. Оның жоғары тартымдылығы жұмыстың жоғары жылдамдығына байланысты.

Бұл әдіс айнымалылары бар UPDATE мәлімдемесін пайдаланады. UPDATE мәлімдемесі бағанның мәніне негізделген айнымалыларға өрнектерді тағайындай алады, сонымен қатар айнымалысы бар өрнекке бағандардағы мәндерді тағайындай алады. Шешім actid, tranid, val және баланс атрибуттары бар транзакциялар деп аталатын уақытша кестені және кілттер тізімі (actid, tranid) бар кластерленген индексті жасаудан басталады. Содан кейін уақытша кесте бастапқы Transactions дерекқорындағы барлық жолдармен толтырылады және 0,00 мәні барлық жолдардың баланс бағанына енгізіледі. Одан кейін орындалатын қорытындыларды есептеу және есептелген мәнді баланс бағанына кірістіру үшін уақытша кестемен байланысты айнымалылармен UPDATE мәлімдемесі шақырылады.

@prevaccount және @prevbalance айнымалы мәндері пайдаланылады және баланс бағанындағы мән келесі өрнек арқылы есептеледі:

SET @prevbalance = баланс = ЖАҒДАЙ ҚАҒИДА actid = @prevaccount ОДАН СОҢ @prevbalance + ALSE val END

CASE өрнегі ағымдағы және алдыңғы шот идентификаторларының бірдей екенін тексереді және егер олар болса, баланс бағанындағы алдыңғы және ағымдағы мәндердің қосындысын қайтарады. Тіркелгі идентификаторлары әртүрлі болса, ағымдағы транзакция сомасы қайтарылады. Әрі қарай, CASE өрнегі нәтижесі баланс бағанына енгізіледі және @prevbalance айнымалысына тағайындалады. Бөлек өрнекте ©prevaccount айнымалысына ағымдағы шоттың идентификаторы тағайындалады.

UPDATE мәлімдемесінен кейін шешім уақытша кестедегі жолдарды ұсынады және соңғысын жояды. Міне, аяқталған шешімнің коды:

КЕСТЕ ҚҰРУ #Транзакциялар (actid INT, tranid INT, val MONEY, теңгерім ақша); idx_actid_tranid ON #Транзакциялар (actid, tranid); INSERT INTO #Transactions WITH (TABLOCK) (actid, tranid, val, баланс) SELECT actid, tranid, val, 0,00 FROM dbo. Transactions ORDER BY actid, tranid; @prevaccount - INT, @prevbalance - АҚША РЕТІНДЕ ЖАРИЯЛАУ; ЖАҢАРТУ #Транзакциялар ОРНАТУ @prevbalance = баланс = CASE WHEN actid = @prevaccount THEN @prevbalance + val ELSE val END, @prevaccount = actid FROM #Transactions WITH(INDEX(1), TABLOCKX) OPTION (MAXDOP 1); #Транзакциялардан * ТАҢДАУ; DROP TABLE #Транзакциялар;

Бұл шешімнің сұлбасы келесі суретте көрсетілген. Бірінші бөлік INSERT операторымен, екіншісі UPDATE операторымен, үшінші бөлігі SELECT операторымен ұсынылған:

Бұл шешім ЖАҢАЛЫҚТЫ орындауды оңтайландыру әрқашан кластерленген индексті реттелген сканерлеуді орындайтынын болжайды және шешім параллельдік сияқты бұған жол бермеуі мүмкін жағдайларды болдырмау үшін бірқатар кеңестер береді. Мәселе мынада, оңтайландырушы әрқашан кластерленген индекс ретімен қарайтынына ресми кепілдік жоқ. Кодта анықтамасы бойынша сол әрекетке кепілдік бере алатын логикалық элементтер болмаса, кодтың логикалық дұрыстығына көз жеткізу үшін физикалық есептеуге сене алмайсыз. Бұл кодта бұл әрекетке кепілдік беретін логикалық мүмкіндік жоқ. Әрине, бұл әдісті қолдану немесе қолданбау сіздің ар-ұжданыңызға байланысты. Менің ойымша, оны мыңдаған рет тексеріп, «бәрі дұрыс жұмыс істейтін сияқты» болса да, оны пайдалану жауапсыздық.

Бақытымызға орай, SQL Server 2012 бұл таңдауды іс жүзінде қажетсіз етеді. Терезелік біріктіру функцияларын пайдаланатын өте тиімді шешім болған кезде, басқа шешімдер туралы ойланудың қажеті жоқ.

өнімділікті өлшеу

Мен әртүрлі техникалардың өнімділігін өлшеп, салыстырдым. Нәтижелер төмендегі суреттерде көрсетілген:

Нәтижелерді екі графикке бөлдім, себебі ішкі сұрау/қосылу әдісі басқаларға қарағанда әлдеқайда баяу болғандықтан, ол үшін басқа масштабты қолдануға тура келді. Кез келген жағдайда, шешімдердің көпшілігі жұмыс жүктемесі мен бөлім өлшемі арасындағы сызықтық қатынасты көрсететінін және тек ішкі сұрау немесе біріктіру шешімі квадраттық қатынасты көрсететінін ескеріңіз. Терезелік біріктіру функциясына негізделген жаңа шешімнің қаншалықты тиімдірек екені де анық. Айнымалылары бар UPDATE шешімі де өте жылдам, бірақ жоғарыда сипатталған себептерге байланысты мен оны пайдалануды ұсынбаймын. CLR шешімі де өте жылдам, бірақ сіз барлық .NET кодын жазып, жинақты дерекқорға орналастыруыңыз керек. Қалай қарасаңыз да, терезе блоктарын пайдаланатын жиынтыққа негізделген шешім ең қолайлы болып қала береді.

SQL тіліндегі SUM функциясы, оның қарапайымдылығына қарамастан, мәліметтер қорымен жұмыс істегенде жиі қолданылады. Оның көмегімен көмекші ДҚБЖ құралдарының көмегіне жүгінбей кейбір аралық немесе соңғы нәтижелерді алу ыңғайлы.

Функция синтаксисі

Көптеген SQL тілдерінде қосынды синтаксисі бірдей - тек өрістің атауы немесе олардың бірнешеуінің кейбір арифметикалық амалдары аргумент ретінде пайдаланылады, оның үстіне қосынды қажет.

Ерекше жағдайларда белгілі бір мәнді сан немесе айнымалы ретінде беруге болады, бірақ мұндай «схемалар» іс жүзінде қолданылмайды, өйткені олар көп мән бермейді. Төменде SQL тіліндегі функция синтаксисі берілген:

sum(a) - мұнда кейбір сандық мән немесе өрнек a параметрі ретінде пайдаланылады

Айта кету керек, параметр алдында кілт сөздерді орнатуға болады, мысалы, DISTINCT немесе ALL, олар сәйкесінше тек бірегей немесе барлық мәндерді қабылдайды.

SQL жүйесінде SUM қолдану мысалы

Функцияның қалай жұмыс істейтінін толық түсіну үшін бірнеше мысалды қарастырған жөн. SQL тілінде SUM қайтару нәтижесі ретінде де, аралық мән ретінде де пайдаланылуы мүмкін, мысалы, шартты тексеру үшін.

Бірінші жағдайда, сатып алулар саны көпше түрде болуы мүмкін екенін ескере отырып, әрбір өнім үшін сату сомасын қайтару қажет болған кезде опцияны қарастырыңыз. Нәтижені алу үшін келесі сұрауды орындау жеткілікті:

Өнімді, соманы (сатып алу сомасы) сату тобынан өнім бойынша ТАҢДАҢЫЗ;

Бұл пәрменге жауап олардың әрқайсысы үшін жалпы сатып алу сомасы бар өнімдердің бірегей тізімі болады.

Екінші мысал үшін сату көлемі белгілі бір мәннен асатын өнімдердің тізімін алу керек, мысалы, 100. Бұл тапсырма бойынша нәтижені бірнеше жолмен алуға болады, олардың ең оңтайлысы бір сұранысты орындау болып табылады:

Өнімді ТАҢДАҢЫЗ (Өнімді ТАҢДАУ, сома (Сатып алу сомасы) Сатудан түскен сома ретінде) ҚАЙДА Сома > 100.

Белгілі бір жеткізуші шығарған ДК үлгілерінің санын қалай білуге ​​болады? Техникалық сипаттамалары бірдей компьютерлердің орташа бағасы қалай анықталады? Кейбір статистикалық ақпаратқа қатысты осы және басқа да көптеген сұрақтарды қолдану арқылы жауап беруге болады қорытынды (жиынтық) функциялар. Стандарт келесі жиынтық функцияларды қамтамасыз етеді:

Барлық осы функциялар бір мәнді қайтарады. Сонымен қатар, функциялар COUNT, MINЖәне МАКСкез келген деректер түріне қолданылады, ал СУМЖәне AVGтек сандық өрістер үшін қолданылады. Функция арасындағы айырмашылық COUNT(*)Және COUNT(<имя поля>) Екіншісі есептеу кезінде NULL мәндерін есепке алмайды.

Мысал. Дербес компьютерлердің минималды және максималды бағасын табыңыз:

Мысал. А өндірушісі шығарған компьютерлердің қолжетімді санын табыңыз:

Мысал. Егер бізді A өндірушісі шығарған әртүрлі модельдер саны қызықтырса, онда сұранысты келесідей тұжырымдауға болады (Өнім кестесінде әрбір модель бір рет жазылатынын пайдалана отырып):

Мысал. Өндіруші А шығарған қолжетімді әртүрлі модельдердің санын табыңыз. Сұрау алдыңғыға ұқсас, онда өндіруші A шығарған модельдердің жалпы санын анықтау қажет болды. Мұнда сонымен қатар әртүрлі модельдердің санын табу керек. ДК кестесі (яғни, сатуға болатындар).

Статистикалық көрсеткіштерді алу кезінде тек бірегей мәндердің пайдаланылуын қамтамасыз ету үшін жиынтық функциялардың аргументіқолдануға болады DISTINCT параметрі. Басқа ALL параметріәдепкі болып табылады және бағандағы барлық қайтарылған мәндер есептеледі деп есептейді. Оператор,

Бізге шығарылатын ДК үлгілерінің санын алу қажет болса барлығыөндіруші, пайдалану қажет болады GROUP BY сөйлемі, синтаксистік жағынан кейін WHERE сөйлемдері.

GROUP BY сөйлемі

GROUP BY сөйлеміқолдануға болатын шығыс сызықтарының топтарын анықтау үшін пайдаланылады жиынтық функциялар (COUNT, MIN, MAX, AVG және SUM). Егер бұл тармақ жоқ болса және біріктіру функциялары пайдаланылса, онда аталған атаулары бар барлық бағандар ТАҢДАУ, құрамына енуі керек агрегаттық функциялар, және бұл функциялар сұрау предикатын қанағаттандыратын жолдардың бүкіл жинағына қолданылады. Әйтпесе, ТАҢДАУ тізімінің барлық бағандары қосылмағанфункцияларды жиынтықта көрсету керек GROUP BY сөйлемінде. Нәтижесінде барлық шығыс сұрау жолдары осы бағандардағы мәндердің бірдей комбинацияларымен сипатталатын топтарға бөлінеді. Осыдан кейін әр топқа жиынтық функциялар қолданылады. GROUP BY үшін барлық NULL мәндері тең деп есептелетінін ескеріңіз, яғни. NULL мәндері бар өріс бойынша топтастыру кезінде мұндай жолдардың барлығы бір топқа түседі.
Егер GROUP BY сөйлемі болса, SELECT сөйлемінде жиынтық функциялары жоқ, содан кейін сұрау жай ғана әр топтан бір жолды қайтарады. Бұл мүмкіндікті DISTINCT кілт сөзімен бірге нәтижелер жиынындағы қайталанатын жолдарды жою үшін пайдалануға болады.
Қарапайым мысалды қарастырайық:
ТАҢДАУ үлгісі, COUNT(модель) АС Саны_моделі, AVG(бағасы) AS Орт_бағасы
ДКДЕН
GROUP BY моделі;

Бұл сұраныста әрбір ДК моделі үшін олардың саны мен орташа құны анықталады. Үлгінің бірдей мәні бар барлық жолдар топты құрайды және SELECT шығысы әр топ үшін мәндердің санын және орташа баға мәндерін есептейді. Сұрау нәтижесі келесі кесте болады:
үлгі Саны_моделі Орташа_баға
1121 3 850.0
1232 4 425.0
1233 3 843.33333333333337
1260 1 350.0

Егер ТАҢДАУ күн бағанына ие болса, онда бұл көрсеткіштерді әрбір нақты күн үшін есептеуге болады. Бұл әрекетті орындау үшін топтау бағанасы ретінде күнді қосу керек, содан кейін жиынтық функциялар мәндердің әрбір комбинациясы үшін есептеледі (модель-күн).

Бірнеше нақты бар жиынтық функцияларды орындау ережелері:

  • Егер сұраныстың нәтижесінде жолдар алынған жоқ(немесе берілген топ үшін бір жолдан көп), онда жиынтық функциялардың кез келгенін есептеу үшін бастапқы деректер жоқ. Бұл жағдайда COUNT функцияларының нәтижесі нөлге тең болады, ал қалған барлық функциялардың нәтижесі NULL болады.
  • Аргументагрегаттық функция өзі жиынтық функцияларды қамти алмайды(функциядан функция). Анау. бір сұрауда, айталық, орташа мәндердің максимумын алу мүмкін емес.
  • COUNT функциясының орындалу нәтижесі болып табылады бүтін сан(БҮТІН). Басқа жиынтық функциялар олар өңдейтін мәндердің деректер түрлерін мұра етеді.
  • SUM функциясы пайдаланылған деректер түрінің ең үлкен мәнінен үлкен нәтиже берсе, қате.

Сонымен, егер сұрауда жоқ болса GROUP BY сөйлемдері, Бұл агрегаттық функцияларкіреді SELECT сөйлемі, нәтиже барлық сұрау жолдарында орындалады. Егер сұрауда болса GROUP BY сөйлемі, көрсетілген бағанның немесе бағандар тобының мәндері бірдей жолдардың әрбір жинағы GROUP BY сөйлемі, топты құрайды және агрегаттық функцияларәр топ үшін жеке орындалады.

Ұсыныс БАР

Егер WHERE сөйлеміжолдарды сүзуге арналған предикатты анықтайды, содан кейін Ұсыныс БАРқолданылады топтастырудан кейінтоптарды мәндер бойынша сүзетін ұқсас предикатты анықтау агрегаттық функциялар. Бұл тармақ көмегімен алынған мәндерді тексеру үшін қажет агрегаттық функцияішінде анықталған жазба көзінің жеке жолдарынан емес FROM сөйлемі, және бастап осындай жолдардың топтары. Сондықтан мұндай тексеруді қамту мүмкін емес WHERE сөйлемі.