bahay » Solusyon

Mga kalkulasyon sa sql. SQL sum function: SUM Halimbawa ng paggamit GROUP BY

Sa tutorial na ito matututunan mo kung paano gamitin SUM function sa SQL Server (Transact-SQL) na may syntax at mga halimbawa.

Paglalarawan

Sa SQL Server (Transact-SQL) SUM function ibinabalik ang kabuuang halaga ng isang expression.

Syntax

Ang syntax para sa SUM function sa SQL Server (Transact-SQL) ay:

O ang syntax para sa SUM function kapag ang pagpapangkat ng mga resulta ayon sa isa o higit pang mga column ay:

Mga Parameter o Argumento

expression1 , expression2 , ... expression_n ay mga expression na hindi kasama sa SUM function at dapat isama sa GROUP BY clause sa dulo ng SQL statement.
aggregate_expression ay ang column o expression na pagsasama-samahin.
mga talahanayan - mga talahanayan kung saan mo gustong kumuha ng mga tala. Dapat mayroong kahit isang talahanayan na nakalista sa sugnay na FROM.
WHERE kundisyon - opsyonal. Ito ang mga kundisyon na dapat matugunan para sa mga napiling talaan.

Aplikasyon

Ang SUM function ay maaaring gamitin sa mga sumusunod na bersyon ng 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

Halimbawa na may isang field

Tingnan natin ang ilang halimbawa ng function ng SQL Server SUM upang maunawaan kung paano gamitin ang function na SUM sa SQL Server (Transact-SQL).

Halimbawa, maaari mong malaman ang kabuuang dami ng lahat ng mga produkto na ang dami ay higit sa 10.

Sa halimbawang ito ng function na SUM, binigyan namin ng alias ang expression na SUM(dami) na "Kabuuang Dami". Kapag nagbabalik ng set ng resulta - lalabas ang "Kabuuang Dami" bilang pangalan ng field.

Halimbawa ng paggamit ng DISTINCT

Maaari mong gamitin ang DISTINCT operator sa SUM function. Halimbawa, ibinabalik ng SQL statement sa ibaba ang kabuuang suweldo na may mga natatanging halaga ng suweldo kung saan ang suweldo ay mas mababa sa $29,000 bawat taon.

Kung ang dalawang suweldo ay $24,000 bawat taon, isa lamang sa mga halagang iyon ang gagamitin sa SUM function.

Halimbawa ng paggamit ng formula

Ang expression na nakapaloob sa SUM function ay hindi kailangang maging isang field. Maaari mo ring gamitin ang formula. Halimbawa, maaari mong kalkulahin ang kabuuang komisyon.

Transact-SQL

PUMILI NG SUM(sales * 0.03) BILANG "Kabuuang Komisyon" MULA sa mga order;

PUMILI NG SUM (benta * 0.03 ) BILANG "Kabuuang Komisyon"

MULA sa mga order;

Halimbawa ng paggamit GROUP BY

Sa ilang mga kaso, kakailanganin mong gamitin ang GROUP BY operator na may function na SUM.

SQL - Aralin 11. Kabuuang mga function, kalkuladong column at view

Ang kabuuang function ay tinatawag ding statistical, aggregate, o sum functions. Pinoproseso ng mga function na ito ang isang hanay ng mga string upang mabilang at ibalik ang isang solong halaga. Mayroon lamang limang ganoong mga pag-andar:
  • Ibinabalik ng AVG() Function ang average na halaga ng isang column.

  • Ibinabalik ng COUNT() Function ang bilang ng mga row sa isang column.

  • Ang MAX() Function ay nagbabalik ng pinakamalaking halaga sa isang column.

  • Ibinabalik ng MIN() Function ang pinakamaliit na value sa column.

  • SUM() Ibinabalik ng function ang kabuuan ng mga value ng column.

Nakilala na namin ang isa sa kanila - COUNT() - sa aralin 8. Ngayon, kilalanin natin ang iba. Sabihin nating gusto naming malaman ang minimum, maximum at average na presyo ng mga aklat sa aming tindahan. Pagkatapos mula sa talahanayan ng mga presyo kailangan mong kunin ang pinakamababa, maximum at average na mga halaga para sa hanay ng presyo. Ang kahilingan ay simple:

PUMILI NG MIN(presyo), MAX(presyo), AVG(presyo) MULA sa mga presyo;

Ngayon, gusto naming malaman kung magkano ang mga kalakal na dinala sa amin ng supplier na "House of Printing" (id=2). Ang paggawa ng ganoong kahilingan ay hindi ganoon kadali. Pag-isipan natin kung paano ito ibubuo:

1. Una, mula sa Supplies (incoming) table, piliin ang mga identifier (id_incoming) ng mga delivery na iyon na isinagawa ng supplier na "Print House" (id=2):

2. Ngayon mula sa talahanayan ng Supply Journal (magazine_incoming) kailangan mong piliin ang mga kalakal (id_product) at ang kanilang mga dami (quantity), na isinagawa sa mga paghahatid na makikita sa punto 1. Iyon ay, ang query mula sa punto 1 ay nagiging nested:

3. Ngayon ay kailangan nating idagdag sa resultang talahanayan ang mga presyo para sa mga nahanap na produkto, na nakaimbak sa talahanayan ng Mga Presyo. Ibig sabihin, kakailanganin nating sumali sa Supply Magazine (magazine_incoming) at mga talahanayan ng Mga Presyo gamit ang column na id_product:

4. Ang resultang talahanayan ay malinaw na kulang sa Halaga ng haligi, iyon ay kalkuladong hanay. Ang kakayahang lumikha ng mga naturang column ay ibinibigay sa MySQL. Upang gawin ito, kailangan mo lamang na tukuyin sa query ang pangalan ng kinakalkula na hanay at kung ano ang dapat nitong kalkulahin. Sa aming halimbawa, ang naturang column ay tatawaging summa, at kakalkulahin nito ang produkto ng mga column ng dami at presyo. Ang pangalan ng bagong column ay pinaghihiwalay ng salitang AS:

PUMILI ng magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AT id_incoming= (SELECT id_incoming MULA sa papasok na_WHERE2d);

5. Mahusay, ang kailangan lang nating gawin ay dagdagan ang column ng summa at sa wakas ay alamin kung magkano ang dinala sa atin ng supplier na "House of Printing." Ang syntax para sa paggamit ng SUM() function ay ang mga sumusunod:

PUMILI SUM(column_name) MULA sa table_name;

Alam namin ang pangalan ng column - summa, ngunit wala kaming pangalan ng table, dahil ito ay resulta ng isang query. Anong gagawin? Para sa mga ganitong kaso, ang MySQL ay may Views. Ang view ay isang query sa pagpili na binibigyan ng isang natatanging pangalan at maaaring maimbak sa isang database para magamit sa ibang pagkakataon.

Ang syntax para sa paglikha ng isang view ay ang mga sumusunod:

GUMAWA NG VIEW view_name BILANG kahilingan;

I-save natin ang aming kahilingan bilang isang view na pinangalanang report_vendor:

GUMAWA NG TINGNAN ang report_vendor BILANG PUMILI ng magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price BILANG summa MULA sa magazine_incoming, mga presyo WHERE magazine_incoming.id_product= prices.id_product AT id_incoming= (NAPUMILI sa id_incoming2 );

6. Ngayon ay maaari mong gamitin ang panghuling function na SUM():

PUMILI SUM(summa) MULA sa report_vendor;

Kaya't nakamit namin ang resulta, bagama't para dito kailangan naming gumamit ng mga nested na query, pagsali, kalkuladong column at view. Oo, minsan kailangan mong mag-isip para makakuha ng resulta, kung wala ito hindi ka makakarating kahit saan. Ngunit hinawakan namin ang dalawang napakahalagang paksa - mga kalkuladong column at view. Pag-usapan natin ang mga ito nang mas detalyado.

Mga kalkuladong field (column)

Gamit ang isang halimbawa, tumingin kami sa isang mathematical na kalkuladong field ngayon. Dito nais kong idagdag na maaari mong gamitin hindi lamang ang pagpaparami ng operasyon (*), kundi pati na rin ang pagbabawas (-), karagdagan (+), at paghahati (/). Ang syntax ay ang mga sumusunod:

PUMILI ng column_name 1, column_name 2, column_name 1 * column_name 2 BILANG kinakalkula_column_name MULA sa table_name;

Ang pangalawang nuance ay ang AS keyword, ginamit namin ito upang itakda ang pangalan ng kinakalkula na column. Sa katunayan, ang keyword na ito ay ginagamit upang magtakda ng mga alias para sa anumang mga column. Bakit kailangan ito? Para sa pagbabawas ng code at pagiging madaling mabasa. Halimbawa, maaaring ganito ang hitsura ng aming view:

GUMAWA NG VIEW report_vendor BILANG PUMILI A.id_product, A.quantity, B.price, A.quantity*B.price AS summa FROM magazine_incoming AS A, mga presyo AS B WHERE A.id_product= B.id_product AT id_incoming= (SELECT id_incoming FROM incoming WHERE id_vendor=2);

Sumang-ayon na ito ay mas maikli at mas malinaw.

Representasyon

Tiningnan na namin ang syntax para sa paglikha ng mga view. Kapag nagawa na ang mga view, magagamit ang mga ito sa parehong paraan tulad ng mga talahanayan. Iyon ay, magpatakbo ng mga query laban sa kanila, i-filter at pag-uri-uriin ang data, at pagsamahin ang ilang mga view sa iba. Sa isang banda, ito ay isang napaka-maginhawang paraan upang mag-imbak ng mga madalas na ginagamit na kumplikadong mga query (tulad ng sa aming halimbawa).

Ngunit tandaan na ang mga view ay hindi mga talahanayan, iyon ay, hindi sila nag-iimbak ng data, ngunit kinukuha lamang ito mula sa iba pang mga talahanayan. Kaya, una, kapag nagbago ang data sa mga talahanayan, magbabago rin ang mga resulta ng pagtatanghal. At pangalawa, kapag ang isang kahilingan ay ginawa sa isang view, ang kinakailangang data ay hinahanap, iyon ay, ang pagganap ng DBMS ay nabawasan. Samakatuwid, hindi mo sila dapat abusuhin.

COMPUTING

Mga function ng buod

Ang mga expression ng SQL query ay madalas na nangangailangan ng preprocessing ng data. Para sa layuning ito, ginagamit ang mga espesyal na function at expression.

Madalas kailangan mong malaman kung gaano karaming mga tala ang tumutugma sa isang partikular na query,ano ang kabuuan ng mga halaga ng isang tiyak na hanay ng numero, ang maximum, minimum at average na mga halaga nito. Para sa layuning ito, ginagamit ang tinatawag na panghuling (statistical, aggregate) function. Ang mga function ng buod ay nagpoproseso ng mga set ng mga tala na tinukoy, halimbawa, ng isang sugnay na WHERE. Kung isasama mo ang mga ito sa listahan ng mga column kasunod ng isang SELECT statement, ang resultang table ay maglalaman hindi lamang ng mga column ng database table, kundi pati na rin ang mga value na kinakalkula ng mga function na ito. Ang sumusunod aylistahan ng mga function ng buod.

  • COUNT (parameter ) ibinabalik ang bilang ng mga tala na tinukoy sa parameter. Kung gusto mong makuha ang bilang ng lahat ng record, dapat mong tukuyin ang asterisk (*) na simbolo bilang isang parameter. Kung tumukoy ka ng pangalan ng column bilang parameter, ibabalik ng function ang bilang ng mga record kung saan may mga value ang column na ito maliban sa NULL. Upang malaman kung gaano karaming iba't ibang mga halaga ang naglalaman ng isang column, unahan ang pangalan ng column gamit ang DISTINCT na keyword. Halimbawa:

PUMILI NG BILANG(*) MULA SA Mga Kliyente;

PUMILI NG COUNT(Order_Amount) MULA SA Mga Customer;

PUMILI NG BILANG(DISTINCT Order_Amount) MULA SA Mga Customer;

Ang pagsubok na patakbuhin ang sumusunod na query ay magreresulta sa isang mensahe ng error:

PUMILI ng Rehiyon , BILANG(*) MULA SA Mga Kliyente ;

  • SUM (parameter ) ibinabalik ang kabuuan ng mga halaga ng haligi na tinukoy sa parameter. Ang parameter ay maaari ding isang expression na naglalaman ng pangalan ng column. Halimbawa:

PUMILI NG SUM (Order_Amount) MULA sa mga Customer;

Ang SQL statement na ito ay nagbabalik ng isang column, isang talaan na talahanayan na naglalaman ng kabuuan ng lahat ng tinukoy na halaga para sa Order_Amount column mula sa Customers table.

Sabihin natin na sa source table ang mga halaga ng Order_Amount column ay ipinahayag sa rubles, at kailangan nating kalkulahin ang kabuuang halaga sa dolyar. Kung ang kasalukuyang halaga ng palitan ay, halimbawa, 27.8, maaari mong makuha ang kinakailangang resulta gamit ang expression:

PUMILI NG SUM (Order_amount*27.8) MULA SA Mga Kliyente;

  • AVG (parameter ) ibinabalik ang arithmetic mean ng lahat ng value ng column na tinukoy sa parameter. Ang parameter ay maaaring isang expression na naglalaman ng pangalan ng column. Halimbawa:

PUMILI NG AVG (Order_Amount) MULA SA Mga Customer;

PUMILI NG AVG (Order_Amount*27.8) MULA SA Mga Kliyente

SAAN Rehiyon<>"North_3west";

  • MAX (parameter ) ibinabalik ang maximum na halaga sa column na tinukoy sa parameter. Ang parameter ay maaari ding isang expression na naglalaman ng pangalan ng column. Halimbawa:

PUMILI NG MAX(Order_Amount) MULA SA Mga Kliyente;

PUMILI NG MAX(Order_Amount*27.8) MULA SA Mga Kliyente

SAAN Rehiyon<>"North_3west";

  • MIN (parameter ) ay nagbabalik ng pinakamababang halaga sa column na tinukoy sa parameter. Ang parameter ay maaaring isang expression na naglalaman ng pangalan ng column. Halimbawa:

PUMILI NG MIN(Order_Amount) MULA SA Mga Customer;

PUMILI NG MIN (Halaga ng Order*27.8) MULA SA Mga Kliyente

SAAN Rehiyon<>"North_3west";

Sa pagsasagawa, madalas na kinakailangan upang makakuha ng isang pangwakas na talahanayan na naglalaman ng kabuuang, average, maximum at minimum na mga halaga ng mga numerong haligi. Upang gawin ito, dapat mong gamitin ang pagpapangkat (GROUP BY) at mga function ng buod.

PUMILI ng Rehiyon, SUM (Order_amount) MULA sa Mga Customer

GROUP BY Rehiyon;

Ang talahanayan ng resulta para sa query na ito ay naglalaman ng mga pangalan ng mga rehiyon at ang kabuuang (kabuuang) halaga ng mga order mula sa lahat ng mga customer mula sa kaukulang mga rehiyon (Fig. 5).

Ngayon isaalang-alang ang isang kahilingan upang makuha ang lahat ng buod ng data ayon sa rehiyon:

PUMILI ng Rehiyon, SUM (Order_Amount), AVG (Order_amount), MAX(Order_amount), MIN (Halaga_order)

MULA sa mga Kliyente

GROUP BY Rehiyon;

Ang orihinal at mga talahanayan ng resulta ay ipinapakita sa Fig. 8. Sa halimbawa, tanging ang North-West na rehiyon ang kinakatawan sa source table ng higit sa isang record. Samakatuwid, sa talahanayan ng resulta para dito, ang iba't ibang mga function ng buod ay nagbibigay ng iba't ibang mga halaga.

kanin. 8. Panghuling talahanayan ng mga halaga ng order ayon sa rehiyon

Kapag gumamit ka ng mga function ng buod sa isang listahan ng column sa isang SELECT statement, ang mga header ng kanilang mga kaukulang column sa talahanayan ng resulta ay Expr1001, Expr1002, at iba pa. (o katulad nito, depende sa pagpapatupad ng SQL). Gayunpaman, maaari kang magtakda ng mga header para sa mga halaga ng mga function ng buod at iba pang mga column sa iyong paghuhusga. Upang gawin ito, pagkatapos lamang ng column sa SELECT statement, tukuyin ang isang expression ng form:

AS column_heading

Ang keyword na AS (as) ay nangangahulugan na sa talahanayan ng resulta, ang kaukulang column ay dapat na may isang heading na tinukoy pagkatapos ng AS. Ang itinalagang titulo ay tinatawag ding alias. Ang sumusunod na halimbawa (Figure 9) ay nagtatakda ng mga alias para sa lahat ng nakalkulang column:

PUMILI ng Rehiyon,

SUM (Halaga_Order) BILANG [Kabuuang Halaga ng Order],

AVG (Order_Amount) BILANG [Average na Halaga ng Order],

MAX(Order_Amount) AS Maximum,

MIN (Order_amount) BILANG Minimum,

MULA sa mga Kliyente

GROUP BY Rehiyon;

kanin. 9. Panghuling talahanayan ng mga halaga ng order ayon sa rehiyon gamit ang mga alias ng column

Ang mga palayaw na binubuo ng ilang salita na pinaghihiwalay ng mga puwang ay nakapaloob sa mga square bracket.

Maaaring gamitin ang mga function ng buod sa SELECT at HAVING clause, ngunit hindi ito magagamit sa WHERE clause. Ang operator ng HAVING ay katulad ng operator na WHERE, ngunit hindi katulad ng WHERE na pumipili ito ng mga tala sa mga grupo.

Sabihin nating gusto mong matukoy kung aling mga rehiyon ang may higit sa isang kliyente. Para sa layuning ito, maaari mong gamitin ang sumusunod na query:

PUMILI Rehiyon , Bilang(*)

MULA sa mga Kliyente

GROUP BY Rehiyon NA MAY COUNT(*) > 1;

Mga function sa pagpoproseso ng halaga

Kapag nagtatrabaho sa data, madalas mong kailangang iproseso ito (i-convert ito sa nais na form): pumili ng substring sa isang string, alisin ang mga puwang sa unahan at trailing, bilugan ang isang numero, kalkulahin ang square root, tukuyin ang kasalukuyang oras, atbp. SQL ay may sumusunod na tatlong uri ng mga function:

  • mga function ng string;
  • mga function ng numero;
  • mga function ng petsa-oras.

Mga function ng string

Kinukuha ng mga string function ang isang string bilang parameter at nagbabalik ng string o NULL pagkatapos itong iproseso.

  • SUBSTRING (linya MULA sa simula)nagbabalik ng substring na nagreresulta mula sa string na tinukoy bilang isang parameter linya . Substring nagsisimula sa character na ang serial number ay tinukoy sa start parameter, at may haba na tinukoy sa length parameter. Ang mga character sa string ay binibilang mula kaliwa hanggang kanan, simula sa 1. Ang mga square bracket dito ay nagpapahiwatig lamang na ang expression na nakapaloob sa mga ito ay opsyonal. Kung ang expression PARA sa haba ay hindi ginagamit, pagkatapos ay isang substring mula sa Magsimula at hanggang sa dulo ng orihinal na linya. Mga halaga ng parameter simula at haba dapat piliin upang ang hinanap na substring ay nasa loob ng orihinal na string. Kung hindi, ang SUBSTRING function ay magbabalik ng NULL.

Halimbawa:

SUBSTRING ("Dear Masha!" FROM 9 FOR 4) ay nagbabalik ng "Masha";

Ang SUBSTRING ("Dear Masha!" FROM 9) ay nagbabalik ng "Masha!";

SUBSTRING("Dear Masha!" FROM 15) returns NULL.

Maaari mong gamitin ang function na ito sa isang SQL expression, halimbawa, tulad nito:

PUMILI * MULA SA Mga Kliyente

WHERE SUBSTRING(Rehiyon MULA 1 PARA 5) = "Hilaga";

  • UPPER(string ) kino-convert ang lahat ng mga character ng string na tinukoy sa parameter sa uppercase.
  • LOWER(string ) kino-convert ang lahat ng mga character ng string na tinukoy sa parameter sa lowercase.
  • TRIM (LEADING | TRAILING | BOTH ["character"] MULA sa string ) inaalis ang nangunguna (LEADING), trailing (TRAILING) o pareho (BOTH) na mga character mula sa isang string. Bilang default, ang karakter na aalisin ay isang puwang (" "), kaya maaari itong alisin. Kadalasan, ang function na ito ay ginagamit upang alisin ang mga puwang.

Halimbawa:

TRIM (LEADING " " FROM "city of St. Petersburg") rotates "city of St. Petersburg";

TRIM(TRALING " " FROM "city of St. Petersburg") returns "city of St. Petersburg";

TRIM (BOTH " " FROM " city St. Petersburg ") ay nagbabalik ng "city St. Petersburg";

TRIM(BOTH FROM " city of St. Petersburg ") returns "city of St. Petersburg";

TRIM(BOTH "g" FROM "city of St. Petersburg") ay nagbabalik ng "city of St. Petersburg".

Sa mga function na ito, ang pinakakaraniwang ginagamit ay ang SUBSTRING() AT TRIM().

Mga function ng numero

Ang mga function ng numero ay maaaring tumanggap ng data hindi lamang ng uri ng numero bilang isang parameter, ngunit palaging nagbabalik ng isang numero o NULL (hindi natukoy na halaga).

  • POSITION ( targetString IN string) ay naghahanap ng paglitaw ng target na string sa tinukoy na string. Kung matagumpay ang paghahanap, ibinabalik ang numero ng posisyon ng unang karakter nito, kung hindi 0. Kung ang target na string ay may zero na haba (halimbawa, ang string " "), ang function ay nagbabalik ng 1. Kung kahit isa sa mga parameter ay NULL , pagkatapos ay ibinalik ang NULL. Ang mga line character ay binibilang mula kaliwa hanggang kanan, simula sa 1.

Halimbawa:

POSITION("e" SA "Hello everyone") ay nagbabalik ng 5;

POSITION ("lahat" SA "Hello everyone") ay nagbabalik ng 8;

POSITION(" " Hello everyone") ay nagbabalik ng 1;

POSITION("Hello!" SA "Hello everyone") ay nagbabalik ng 0.

Sa talahanayan ng Mga Kliyente (tingnan ang Fig. 1), naglalaman ang column ng Address, bilang karagdagan sa pangalan ng lungsod, postal code, pangalan ng kalye at iba pang data. Maaaring kailanganin mong pumili ng mga talaan para sa mga customer na nakatira sa isang partikular na lungsod. Kaya, kung gusto mong pumili ng mga talaan na nauugnay sa mga kliyenteng naninirahan sa St. Petersburg, maaari mong gamitin ang sumusunod na SQL query expression:

PUMILI * MULA SA Mga Kliyente

SAAN POSITION (" St. Petersburg " IN Address ) > 0;

Tandaan na ang simpleng kahilingan sa pagkuha ng data na ito ay maaaring iba-iba ang formula:

PUMILI * MULA SA Mga Kliyente

WHERE Address LIKE "%Petersburg%";

  • EXTRACT (parameter ) kinukuha ang isang elemento mula sa isang halaga ng petsa-oras o mula sa isang pagitan. Halimbawa:

EXTRACT (BUWAN MULA SA PETSA "2005-10-25") nagbabalik 10.

  • CHARACTER_LENGTH(string ) ibinabalik ang bilang ng mga character sa string.

Halimbawa:

CHARACTER_LENGTH("Kumusta sa lahat") ay nagbabalik ng 11.

  • OCTET_LENGTH(string ) ibinabalik ang bilang ng mga octet (bytes) sa string. Ang bawat Latin o Cyrillic character ay kinakatawan ng isang byte, at ang Chinese alphabet character ay kinakatawan ng dalawang byte.
  • CARDINALITY (parameter ) kumukuha ng koleksyon ng mga elemento bilang parameter at ibinabalik ang bilang ng mga elemento sa koleksyon (cardinal number). Ang isang koleksyon ay maaaring, halimbawa, isang array o isang multiset na naglalaman ng mga elemento ng iba't ibang uri.
  • ABS (numero ) ibinabalik ang ganap na halaga ng isang numero. Halimbawa:

Ang ABS (-123) ay nagbabalik ng 123;

Nagbabalik ang ABS (2 - 5) 3.

  • MO D (number1, number2 ) ibinabalik ang natitira sa isang integer division ng unang numero sa pamamagitan ng pangalawa. Halimbawa:

Ang MOD(5, h) ay nagbabalik ng 2;

Ang MOD(2, h) ay nagbabalik ng 0.

  • LN (numero ) ibinabalik ang natural na logarithm ng isang numero.
  • Ibinabalik ng EXP (number) ang numero (ang base ng natural na logarithm sa kapangyarihan ng numero).
  • KAPANGYARIHAN (number1, numero2 ) ay nagbabalik ng numero1 numero2 (number1 sa kapangyarihan ng number2).
  • SQRT (numero ) ibinabalik ang square root ng isang numero.
  • FLOOR (numero ) ay nagbabalik ng pinakamalaking integer na hindi lalampas sa tinukoy ng parameter (pag-round down). Halimbawa:

FLOOR (5.123) ay nagbabalik ng 5.0.

  • CEIL (numero) o CEILING (numero ) ay nagbabalik ng pinakamaliit na integer na hindi bababa sa halagang tinukoy ng round up na parameter). Halimbawa:

Ang CEIL(5.123) ay nagbabalik ng 6.0.

  • WIDTH_BUCKET (number1, number2, number3, number4) ay nagbabalik ng integer sa hanay sa pagitan ng 0 at number4 + 1. Tinutukoy ng number2 at number3 na mga parameter ang isang numerical interval na nahahati sa pantay na mga pagitan, ang bilang nito ay tinukoy ng number4 na parameter. Tinutukoy ng function ang bilang ng pagitan kung saan bumaba ang halaga bilang1. Kung ang numero1 ay nasa labas ng tinukoy na hanay, ang function ay nagbabalik ng 0 o numero 4 + 1. Halimbawa:

WIDTH_BUCKET(3.14, 0, 9, 5) ay nagbabalik ng 2.

Mga function ng petsa-oras

Ang SQL ay may tatlong function na nagbabalik ng kasalukuyang petsa at oras.

  • CURRENT_DATE ibinabalik ang kasalukuyang petsa (uri DATE).

Halimbawa: 2005-06-18.

  • CURRENT_TIME (numero ) ibinabalik ang kasalukuyang oras (uri ng ORAS). Tinutukoy ng integer parameter ang katumpakan ng representasyon ng segundo. Halimbawa, ang halaga ng 2 ay kakatawan ng mga segundo hanggang sa pinakamalapit na daan (dalawang decimal na lugar):

12:39:45.27.

  • CURRENT_TIMESTAMP (numero ) ibinabalik ang petsa at oras (uri ng TIMESTAMP). Halimbawa, 2005-06-18 12:39:45.27. Tinutukoy ng integer parameter ang katumpakan ng representasyon ng segundo.

Tandaan na ang petsa at oras na ibinalik ng mga function na ito ay hindi isang uri ng character. Kung gusto mong katawanin ang mga ito bilang mga string ng character, dapat mong gamitin ang function ng conversion na uri ng CAST() para magawa ito.

Ang mga function ng petsa-oras ay karaniwang ginagamit sa mga query para magpasok, mag-update, at magtanggal ng data. Halimbawa, kapag nagre-record ng impormasyon sa pagbebenta, ang kasalukuyang petsa at oras ay ipinasok sa hanay na ibinigay para sa layuning ito. Pagkatapos isama ang mga resulta para sa isang buwan o quarter, ang data ng mga benta para sa panahon ng pag-uulat ay maaaring tanggalin.

Computed Expressions

Ang mga computed expression ay binuo mula sa mga constant (numeric, string, logical), function, field name at iba pang uri ng data sa pamamagitan ng pagkonekta sa mga ito gamit ang arithmetic, string, logical at iba pang operator. Sa turn, ang mga expression ay maaaring pagsamahin gamit ang mga operator sa mas kumplikadong (compound) na mga expression. Ginagamit ang mga panaklong upang kontrolin ang pagkakasunud-sunod kung saan sinusuri ang mga expression.

Mga lohikal na operator AT, O at HINDI at mga function napag-usapan na dati.

Mga operator ng aritmetika:

  • + karagdagan;
  • - pagbabawas;
  • * pagpaparami;
  • / dibisyon.

Operator ng stringisa lamang concatenation o string concatenation operator (| |). Ang ilang mga pagpapatupad ng SQL (tulad ng Microsoft Access) ay gumagamit ng (+) na character sa halip na (| |). Ang concatenation operator ay nagdaragdag ng pangalawang string sa dulo ng unang halimbawa, ang expression:

"Sasha" | | "nagmamahal" | | "Kumakaway"

ay ibabalik ang string na "Sasha loves Masha" bilang isang resulta.

Kapag bumubuo ng mga expression, dapat mong tiyakin na ang mga operand ng mga operator ay may wastong uri. Halimbawa, ang expression na: 123 + "Sasha" ay hindi wasto dahil ang arithmetic addition operator ay inilapat sa isang string operand.

Maaaring lumabas ang mga computed expression pagkatapos ng isang SELECT statement, gayundin sa mga condition na expression ng WHERE at HAVI na mga pahayag N.G.

Tingnan natin ang ilang halimbawa.

Hayaang maglaman ang talahanayan ng Pagbebenta ng mga column na ProductType, Dami, at Presyo, at gusto naming malaman ang kita para sa bawat uri ng produkto. Para magawa ito, isama lang ang expression na Dami*Presyo sa listahan ng mga column pagkatapos ng SELECT statement:

PUMILI ng Product_type, Dami, Presyo, Dami*Presyo AS

Kabuuang MULA SA Mga Benta;

Ginagamit nito ang AS (as) na keyword upang tumukoy ng alias para sa nakalkulang column ng data.

Sa Fig. Ipinapakita ng Figure 10 ang orihinal na talahanayan ng Sales at ang talahanayan ng resulta ng query.

kanin. 10. Resulta ng query na may pagkalkula ng kita para sa bawat uri ng produkto

Kung gusto mong malaman ang kabuuang kita mula sa pagbebenta ng lahat ng produkto, gamitin lang ang sumusunod na query:

PUMILI NG SUM (Dami*Presyo) MULA SA Benta;

Ang sumusunod na query ay naglalaman ng mga kinakalkula na expression sa parehong listahan ng column at ang kundisyon ng WHERE clause. Pinipili niya mula sa talahanayan ng mga benta ang mga produktong iyon na ang kita sa benta ay higit sa 1000:

PUMILI ng Product_type, Dami*Presyo BILANG Kabuuan

MULA SA Sales

SAAN Dami*Presyo > 1000;

Ipagpalagay natin na gusto mong makakuha ng table na may dalawang column:

Produktong naglalaman ng uri at presyo ng produkto;

Kabuuang naglalaman ng kita.

Dahil sa orihinal na talahanayan ng mga benta, ipinapalagay na ang column ng Product_Type ay character (uri ng CHAR) at ang column na Presyo ay numeric, kapag pinagsasama-sama (nagdikit) ang data mula sa mga column na ito, kinakailangang i-cast ang uri ng numero sa isang uri ng character gamit ang CAST() function. Ang query na nagsasagawa ng gawaing ito ay ganito ang hitsura (Larawan 11):

PUMILI ng Product_Type | | " (Presyo: " | | CAST(Presyo AS CHAR(5)) | | ")" BILANG Produkto, Dami*Presyo AS Kabuuan

MULA sa Benta;

kanin. 11. Resulta ng isang query na pinagsasama-sama ang iba't ibang uri ng data sa isang column

Tandaan. Sa Microsoft Access, ang isang katulad na query ay magiging ganito:

PUMILI ng Product_type + " (Presyo: " + C Str (Presyo) + ")" BILANG Produkto,

Dami*Presyo BILANG Kabuuan

MULA sa Benta;

Mga Conditional Expression na may CASE Statement

Ang mga conventional programming language ay may mga conditional jump operator na nagbibigay-daan sa iyong kontrolin ang computational process depende sa kung ang ilang kundisyon ay totoo o hindi. Sa SQL, ang operator na ito ay CASE (kaso, pangyayari, halimbawa). Sa SQL:2003, ang operator na ito ay nagbabalik ng isang halaga at samakatuwid ay maaaring gamitin sa mga expression. Mayroon itong dalawang pangunahing anyo, na titingnan natin sa seksyong ito.

CASE statement na may mga value

Ang pahayag ng CASE na may mga halaga ay may sumusunod na syntax:

CASE checked_value

WHEN value1 THEN resulta1

WHEN value2 THEN resulta2

. . .

KAPAG ang halaga ng N THEN ang resulta ng N

IBANG resultaX

Kung sakali checked_value katumbas ng halaga1 , ibinabalik ng CASE statement ang halaga resulta1 , tinukoy pagkatapos ng keyword na THEN. Kung hindi, ang checked_value ay inihambing sa halaga2 , at kung pantay-pantay ang mga ito, ibabalik ang value na resulta2. Kung hindi, ang value na sinusubok ay inihahambing sa susunod na value na tinukoy pagkatapos ng WHEN keyword, atbp. Kung ang tested_value ay hindi katumbas ng alinman sa mga value na ito, ibabalik ang value resulta X , na tinukoy pagkatapos ng ELSE (else) na keyword.

Ang ELSE keyword ay opsyonal. Kung ito ay nawawala at wala sa mga value na inihahambing ay katumbas ng value na sinusuri, ang CASE statement ay nagbabalik ng NULL.

Sabihin nating, batay sa talahanayan ng Mga Kliyente (tingnan ang Fig. 1), gusto mong makakuha ng isang talahanayan kung saan ang mga pangalan ng mga rehiyon ay pinapalitan ng kanilang mga numero ng code. Kung walang masyadong maraming iba't ibang mga rehiyon sa talahanayan ng pinagmulan, kung gayon upang malutas ang problemang ito ay maginhawang gumamit ng isang query sa operator ng CASE:

PUMILI Pangalan, Address,

Rehiyon ng KASO

KUNG "Moscow" TAPOS "77"

KAPAG "rehiyon ng Tver" TAPOS "69"

. . .

IBANG Rehiyon

AS Region code

MULA sa mga Kliyente;

CASE statement na may mga kondisyon sa paghahanap

Ang pangalawang anyo ng operator ng CASE ay nagsasangkot ng paggamit nito kapag naghahanap sa isang talahanayan para sa mga talang iyon na nakakatugon sa isang partikular na kundisyon:

KASO

KAPAG kundisyon1 AY nagresulta1

KAPAG catch2 THEN result2

. . .

KAPAG kundisyon N, magreresulta N

IBANG resultaX

Ang CASE statement ay sumusubok kung ang condition1 ay totoo para sa unang tala sa set na tinukoy ng WHERE clause, o ang buong talahanayan kung WHERE ay wala. Kung oo, ibabalik ng CASE ang resulta1. Kung hindi, ang condition2 ay sinusuri para sa record na ito. Kung ito ay totoo, ibinalik ang value result2, atbp. Kung wala sa mga kundisyon ang totoo, ibinabalik ang value na resulta X , na tinukoy pagkatapos ng ELSE keyword.

Ang ELSE keyword ay opsyonal. Kung ito ay nawawala at wala sa mga kundisyon ay totoo, ang CASE statement ay umiikot sa NULL. Matapos maisagawa ang pahayag na naglalaman ng CASE para sa unang tala, lilipat ito sa susunod na tala. Nagpapatuloy ito hanggang sa maproseso ang buong hanay ng mga talaan.

Ipagpalagay na sa isang talahanayan ng aklat (Pamagat, Presyo), ang isang column ay NULL kung ang kaukulang aklat ay walang stock. Ang sumusunod na query ay nagbabalik ng isang talahanayan na nagpapakita ng "Out of stock" sa halip na NULL:

PUMILI ng Pamagat,

KASO

KAPAG NULL ANG Presyo, "Out of stock"

ELSE CAST(Presyo AS CHAR(8))

AS Presyo

MULA sa Mga Aklat;

Ang lahat ng mga halaga sa parehong column ay dapat na pareho ang uri. Samakatuwid, ginagamit ng query na ito ang function ng conversion ng uri ng CAST upang i-cast ang mga numerong halaga ng column na Presyo sa isang uri ng character.

Tandaan na palagi mong magagamit ang pangalawang anyo ng CASE statement sa halip na ang una:

KASO

WHEN tested_value = value1 THEN result1

WHEN tested_value = value2 THEN resulta2

. . .

WHEN checked_value = halaga N pagkatapos ay resultaN

IBANG resulta

NULLIF at COALESCE function

Sa ilang mga kaso, lalo na sa mga kahilingan sa pag-update ng data (UPDATE operator), ito ay maginhawang gamitin ang mas compact na NULLIF() (NULL if) at COALESCE() (combine) function sa halip na ang masalimuot na CASE operator.

NULLIF function ( halaga1, halaga2) ay nagbabalik ng NULL kung ang halaga ng unang parameter ay tumutugma sa halaga ng pangalawang parameter; sa kaso ng hindi pagkakatugma, ang halaga ng unang parameter ay ibinalik nang hindi nagbabago. Iyon ay, kung ang equality value1 = value2 ay totoo, ang function ay nagbabalik ng NULL, kung hindi, value ang value1.

Ang function na ito ay katumbas ng CASE statement sa sumusunod na dalawang anyo:

  • CASE value1

WHEN value2 THEN NULL

IBA na halaga1

  • KASO

WHEN value1 = value2 THEN NULL

IBA na halaga1

Function COALESCE( halaga1, halaga2, ... , N halaga) tumatanggap ng listahan ng mga halaga, na maaaring NULL o NULL. Ang function ay nagbabalik ng isang tinukoy na halaga mula sa isang listahan o NULL kung ang lahat ng mga halaga ay hindi natukoy.

Ang function na ito ay katumbas ng sumusunod na CASE statement:

KASO

KAPAG ang value 1 AY HINDI NULL THEN value 1

KAPAG ang value 2 AY HINDI NULL THEN value 2

. . .

KAPAG ang halaga N AY HINDI NULL KAYA ang halaga ng N

IBA NULL

Ipagpalagay na sa talahanayan ng Mga Aklat (Pamagat, Presyo), ang hanay ng Presyo ay NULL kung wala nang stock ang kaukulang aklat. Ang sumusunod na query ay nagbabalik ng isang talahanayan kung saan sa halip na WALA Ang tekstong "Out of stock" ay ipinapakita:

SELECT Name, COALESCE (CAST(Presyo AS CHAR(8)),

"Walang stock") AS Presyo

MULA sa Mga Aklat;

Ang function na SUM sa wikang SQL, sa kabila ng pagiging simple nito, ay madalas na ginagamit kapag nagtatrabaho sa isang database. Sa tulong nito, maginhawang makakuha ng ilang intermediate o panghuling resulta nang hindi gumagamit ng tulong ng mga pantulong na tool sa DBMS.

Syntax ng function

Sa karamihan ng mga wika ng SQL, ang sum syntax ay pareho - tanging ang pangalan ng field o ilang aritmetika na operasyon ng ilan sa mga ito ang ginagamit bilang argumento, kung saan kinakailangan ang kabuuan.

Sa mga pambihirang kaso, posible na magpadala ng isang tiyak na halaga bilang isang numero o variable, ngunit ang gayong "mga scheme" ay halos hindi ginagamit, dahil hindi sila nagdadala ng maraming halaga. Nasa ibaba ang function syntax sa SQL:

sum(a) - dito ginagamit ang ilang numeric value o expression bilang parameter a

Ito ay nagkakahalaga na tandaan na bago ang parameter maaari kang magtakda ng mga keyword, halimbawa, DISTINCT o LAHAT, na kukuha lamang ng natatangi o lahat ng mga halaga, ayon sa pagkakabanggit.

Halimbawa ng paggamit ng SUM sa SQL

Upang lubos na maunawaan kung paano gumagana ang function, ito ay nagkakahalaga ng pagsasaalang-alang ng ilang mga halimbawa. Sa SQL, maaaring gamitin ang SUM bilang resulta ng pagbabalik at bilang isang intermediate na halaga, halimbawa, upang subukan ang isang kundisyon.

Para sa unang kaso, isaalang-alang ang opsyon kapag kailangan mong ibalik ang halaga ng mga benta para sa bawat produkto, na isinasaalang-alang na ang bilang ng mga pagbili na ginawa ay maaaring nasa maramihan. Upang makuha ang resulta, ito ay sapat na upang patakbuhin ang sumusunod na query:

PUMILI ng Produkto, sum(PurchaseAmount) MULA SA Sales GroupBy Product;

Ang tugon sa command na ito ay isang natatanging listahan ng mga produkto na may kabuuang halaga ng pagbili para sa bawat isa sa kanila.

Para sa pangalawang halimbawa, kailangan mong makakuha ng isang listahan ng mga produkto na ang halaga ng mga benta ay lumampas sa isang tiyak na halaga, halimbawa, 100. Makukuha mo ang resulta para sa gawaing ito sa maraming paraan, ang pinakamainam kung saan ay ang magsagawa ng isang kahilingan:

PUMILI NG Produkto MULA SA (PUMILI NG Produkto, kabuuan(Halaga ng Binili) bilang Halaga MULA SA Mga Benta) KUNG SAAN Sum > 100.