Window function (SQL)

In SQL, a window function or analytic function[1] is a function which uses values from one or multiple rows to return a value for each row. (This contrasts with an aggregate function, which returns a single value for multiple rows.) Window functions have an OVER clause; any function without an OVER clause is not a window function, but rather an aggregate or single-row (scalar) function.[2]

Example

As an example, here is a query which uses a window function to compare the salary of each employee with the average salary of their department (example from the PostgreSQL documentation):[3]

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

Output:

 depname  | empno | salary |          avg          
----------+-------+--------+----------------------
develop   |    11 |   5200 | 5020.0000000000000000
develop   |     7 |   4200 | 5020.0000000000000000
develop   |     9 |   4500 | 5020.0000000000000000
develop   |     8 |   6000 | 5020.0000000000000000
develop   |    10 |   5200 | 5020.0000000000000000
personnel |     5 |   3500 | 3700.0000000000000000
personnel |     2 |   3900 | 3700.0000000000000000
sales     |     3 |   4800 | 4866.6666666666666667
sales     |     1 |   5000 | 4866.6666666666666667
sales     |     4 |   4800 | 4866.6666666666666667
(10 rows)

The PARTITION BY clause groups rows into partitions, and the function is applied to each partition separately. If the PARTITION BY clause is omitted (such as with an empty OVER() clause), then the entire result set is treated as a single partition.[4] For this query, the average salary reported would be the average taken over all rows.

Window functions are evaluated after aggregation (after the GROUP BY clause and non-window aggregate functions, for example).[1]

Syntax

According to the PostgreSQL documentation, a window function has the syntax of one of the following:[4]

function_name ([expression [, expression ... ]]) OVER window_name
function_name ([expression [, expression ... ]]) OVER ( window_definition )
function_name ( * ) OVER window_name
function_name ( * ) OVER ( window_definition )

where window_definition has syntax:

[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

frame_clause has the syntax of one of the following:

{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]

frame_start and frame_end can be UNBOUNDED PRECEDING, offset PRECEDING, CURRENT ROW, offset FOLLOWING, or UNBOUNDED FOLLOWING. frame_exclusion can be EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, or EXCLUDE NO OTHERS.

expression refers to any expression that does not contain a call to a window function.

Notation:

  • Brackets [] indicate optional clauses
  • Curly braces {} indicate a set of different possible options, with each option delimited by a vertical bar |

Example

Window functions allow access to data in the records right before and after the current record.[5][6][7][8] A window function defines a frame or window of rows with a given length around the current row, and performs a calculation across the set of data in the window.[9][10]

      NAME |
------------
      Aaron| <-- Preceding (unbounded)
     Andrew|
     Amelia|
      James|
       Jill|
     Johnny| <-- 1st preceding row
    Michael| <-- Current row
       Nick| <-- 1st following row
    Ophelia|
       Zach| <-- Following (unbounded)

In the above table, the next query extracts for each row the values of a window with one preceding and one following row:

 SELECT
  LAG(name, 1) 
    OVER(ORDER BY name) "prev",
  name, 
  LEAD(name, 1) 
    OVER(ORDER BY name) "next"
 FROM people
 ORDER BY name

The result query contains the following values:

|     PREV |     NAME |     NEXT |
|----------|----------|----------|
|    (null)|     Aaron|    Andrew|
|     Aaron|    Andrew|    Amelia|
|    Andrew|    Amelia|     James|
|    Amelia|     James|      Jill|
|     James|      Jill|    Johnny|
|      Jill|    Johnny|   Michael|
|    Johnny|   Michael|      Nick|
|   Michael|      Nick|   Ophelia|
|      Nick|   Ophelia|      Zach|
|   Ophelia|      Zach|    (null)|

History

Window functions were introduced to the SQL:2003 standard and had functionality expanded in later specifications.[11]

Support for particular database implementations was added as follows:

See also

References

  1. ^ a b "Analytic function concepts in Standard SQL | BigQuery". Google Cloud. Retrieved 2021-03-23.
  2. ^ "Window Functions". sqlite.org. Retrieved 2021-03-23.
  3. ^ "3.5. Window Functions". PostgreSQL Documentation. 2021-02-11. Retrieved 2021-03-23.
  4. ^ a b "4.2. Value Expressions". PostgreSQL Documentation. 2021-02-11. Retrieved 2021-03-23.
  5. ^ Leis, Viktor; Kundhikanjana, Kan; Kemper, Alfons; Neumann, Thomas (June 2015). "Efficient Processing of Window Functions in Analytical SQL Queries". Proc. VLDB Endow. 8 (10): 1058–1069. doi:10.14778/2794367.2794375. ISSN 2150-8097.
  6. ^ Cao, Yu; Chan, Chee-Yong; Li, Jie; Tan, Kian-Lee (July 2012). "Optimization of Analytic Window Functions". Proc. VLDB Endow. 5 (11): 1244–1255. arXiv:1208.0086. doi:10.14778/2350229.2350243. ISSN 2150-8097.
  7. ^ "Probably the Coolest SQL Feature: Window Functions". Java, SQL and jOOQ. 2013-11-03. Retrieved 2017-09-26.
  8. ^ "Window Functions in SQL - Simple Talk". Simple Talk. 2013-10-31. Retrieved 2017-09-26.
  9. ^ "SQL Window Functions Introduction". Apache Drill.
  10. ^ "PostgreSQL: Documentation: Window Functions". www.postgresql.org. Retrieved 2020-04-04.
  11. ^ "Window Functions Overview". MariaDB KnowledgeBase. Retrieved 2021-03-23.
  12. ^ "PostgreSQL Release 8.4". www.postgresql.org. 24 July 2014. Retrieved 2024-03-10.
  13. ^ "MySQL :: What's New in MySQL 8.0? (Generally Available)". dev.mysql.com. Retrieved 2022-11-21.
  14. ^ "MySQL :: MySQL 8.0 Reference Manual :: 12.21.2 Window Function Concepts and Syntax". dev.mysql.com.
  15. ^ "MariaDB 10.2.0 Release Notes". mariadb.com. Retrieved 2024-03-10.

Read other articles:

Feylinia elegans Status konservasiRisiko rendahIUCN13152717 TaksonomiKerajaanAnimaliaFilumChordataKelasReptiliaOrdoSquamataFamiliScincidaeGenusFeyliniaSpesiesFeylinia elegans Tata namaSinonim takson Acontias elegans Hallowell, 1854 Sphenorhina elegans — Hallowell, 1857 Anelytrops elegans — Bocage, 1866 lbs Feylinia elegans, atau elegant feylinia, adalah sebuah kadal Afrika dalam keluarga Scincidae[1] yang lebih dikenal sebagai skink. Spesies tersebut ditemukan di Guinea Khatulisti...

 

Artikel ini sebatang kara, artinya tidak ada artikel lain yang memiliki pranala balik ke halaman ini.Bantulah menambah pranala ke artikel ini dari artikel yang berhubungan atau coba peralatan pencari pranala.Tag ini diberikan pada Januari 2023. Formula Rossa adalah roller coaster yang terletak di Ferrari World Abu Dhabi, Uni Emirat Arab. Formula Rossa dibuka untuk umum pada November 2010. Saat ini Formula Rossa adalah roller coaster tercepat di dunia dengan kecepatan maksimal 240 km / ja...

 

Dewa-Dewi dalam kebudayaan Nordik hidup abadi dengan memakan buah apel dari Iðunn dan akan hidup abadi hingga Ragnarok tiba Mitologi Nordik merupakan kepercayaan masyarakat Eropa Utara (negara Denmark, Norwegia, Islandia, dan Swedia) sebelum kedatangan agama Kristen. Kepercayaan dan legenda ini menyebar ke negara-negara Eropa Utara lain, termasuk Islandia yang memiliki sumber-sumber mitologi tersebut. Mitologi tersebut merupakan kumpulan dari kepercayaan kuno orang-orang Eropa Utara yang ber...

لاوري أولونن معلومات شخصية اسم الولادة (بالفنلندية: Lauri Johannes Ylönen)‏  الميلاد 23 أبريل 1979 (العمر 44 سنة)هلسنكي مواطنة فنلندا  عضو في ذا راسموس  عدد الأولاد 3   الحياة العملية المهنة مغن مؤلف،  ومغني،  وموسيقي،  ومنتج أسطوانات  اللغات الفنلندية  المواقع الم...

 

ThailandJulukanช้างศึก (Changsuek) (Gajah Perang)AsosiasiAsosiasi Sepak Bola ThailandKonfederasiAFC (Asia)Sub-konfederasiAFF (Asia Tenggara)PelatihMasatada IshiiPenampilan terbanyakKiatisuk Senamuang (134)Pencetak gol terbanyakKiatisuk Senamuang (71)Stadion kandangStadion RajamangalaKode FIFATHAPeringkat FIFATerkini 101 12 (15 Februari 2024)[1]Tertinggi43 (September 1998)Terendah165 (Oktober 2014)Peringkat EloTerkini 105 3 (19 Januari 2024)[2] Warna pertama Warna...

 

Sarah Polley OCPolley di Festival Film Internasional Venesia ke-66 pada 11 September 2009Lahir8 Januari 1979 (umur 45)Toronto, Ontario, KanadaPekerjaanPemeran, penulis, sutradara, penggiat politikTahun aktif1985–sekarangSuami/istriDavid Wharnsby ​ ​(m. 2003; bercerai 2008)​ David Sandomierski ​(m. 2011)​Anak2Orang tuaHarry Gulkin (ayah kandung), Diane Polley (ibu) Sarah Ellen Polley OC (lahir 8 Januari 1...

Leo IIKaisar Romawi TimurAugustus17 November 473 – November 474PendahuluLeo IPenerusZenoWaliLeo I (sampai 18 Januari)Zeno (dari 29 Januari)Gliserius (Barat, 473–474)Julius Nepos (Barat, 474)CaesarOktober 472 – November 473Informasi pribadiKelahiransekitar 467KematianNovember 474 (usia 7)KonstantinopelPemakamanGereja Rasul SuciAyahZenoIbuAriadneLeo II (Yunani: Λέων, diromanisasi: Leōn; sek. 467 – November 474) adalah kaisar Romawi yang singkat pada tahun 474 ketika ia masih anak-a...

 

This article relies largely or entirely on a single source. Relevant discussion may be found on the talk page. Please help improve this article by introducing citations to additional sources.Find sources: South Africa at the 2011 All-Africa Games – news · newspapers · books · scholar · JSTOR (August 2023) Sporting event delegationSouth Africa at the2011 All-Africa GamesIOC codeRSANOCSouth African Sports Confederation and Olympic Committeein MaputoMedal...

 

Former Australian regional music festival Triple J's One Night StandGenreIndie rockindie pophip hopelectronicLocation(s)AustraliaYears active2004–2019; 2024Organised byTriple J (Australian Broadcasting Corporation) Triple J's One Night Stand is an annual music festival held in various regional Australian cities and towns that is promoted and organised by national youth radio station Triple J. The event ran for 15 years from its debut in 2004 until 2019, with one break in 2015 for Triple J's...

Beane pada 2013 William Lamar Beane III (lahir 29 Maret 1962) adalah seorang mantan pemain bisbol profesional Amerika dan sekarang menjadi eksekutif kantor depan. Ia merupakan Wakil Presiden Eksekutif Baseball Operations dan pemilik minoritas Oakland Athletics dari Major League Baseball (MLB). Referensi Daftar pustaka Lewis, Michael D. (2003). Moneyball: The Art of Winning an Unfair Game. New York: W. W. Norton. ISBN 0-393-05765-8.  Kutipan Pranala luar Wikimedia Commons memiliki me...

 

Unincorporated community in Tennessee, US US-70 in DeRossett DeRossett (also spelled De Rossett) is an unincorporated community in White County, Tennessee, United States. It is concentrated along U.S. Route 70 in White County's Cumberland Plateau region between Sparta and Crossville.[1] Along with the adjacent areas of Bon Air and Ravenscroft, DeRossett is part of a greater community known as BonDeCroft.[2] References ^ U.S. Geological Survey Geographic Names Information Syste...

 

В Википедии есть статьи о других людях с такой фамилией, см. Арсеньев. Илья Александрович Арсеньев Портрет 1862 г. Дата рождения 1820(1820) Место рождения Москва Дата смерти 16 (28) февраля 1887(1887-02-28) Место смерти Санкт-Петербург Подданство  Российская империя Род деятель...

Ninshubur 𒀭𒊩𒌆𒋚Sukkal Anu, Inanna dan majelis dewaSegel silinder Akkadia yang menggambarkan Inanna meletakkan kakinya di punggung singa sementara Ninshubur berdiri di depan menghormatinya, skt. 2334-2154 SM[1]Pusat pemujaanAkkil, Uruk, LagashSimbolstaffbiasanya tidak ada, tetapi jarang NergalPadananAkkadiaIlabratSeleukia UrukPapsukkal Ninshubur (aksara paku: 𒀭𒊩𒌆𒋚; juga dieja Ninšubur, Ninšubura atau Nincubur)[2] adalah sukkal (wazir) dewi Inanna dalam ...

 

United States historic placeMeridian HighwayU.S. National Register of Historic Places Bridge on the Meridian Highway in Pierce County, Nebraska; seen from the southwestNearest cityPierce, NebraskaArea36 acres (15 ha)Built1911 (1911)NRHP reference No.01001273[1]Added to NRHPNovember 29, 2001 Meridian Highway was a United States auto trail in the early twentieth century. It roughly followed the path of present-day U.S. Route 81 from Pembina, North Dakota to Fort Wort...

 

Hotel in Manhattan, New York This article is about the hotel in New York City. For the Native American reservation in upstate New York, sometimes known as St. Regis, New York, see St. Regis Mohawk Reservation. St. Regis New YorkThe hotel's Fifth Avenue facade in 2021General informationLocation2 East 55th StreetManhattan, New YorkCoordinates40°45′41″N 73°58′29″W / 40.76139°N 73.97472°W / 40.76139; -73.97472OpenedSeptember 4, 1904; 119 years ago&...

American boxer (1858–1918) Not to be confused with John L. O'Sullivan. For other uses, see John Sullivan. John L. SullivanSullivan in 1882BornJohn Lawrence Sullivan(1858-10-15)October 15, 1858Boston, Massachusetts, U.S.DiedFebruary 2, 1918(1918-02-02) (aged 59)Abington, Massachusetts, U.S.Other names Boston Strong Boy His Fistic Holiness StatisticsWeight(s) Heavyweight Height5 ft 10+1⁄2 in (179 cm)[1]Reach74 in (188 cm)StanceOrthodox Boxing r...

 

Church of Ireland cathedral in County Kilkenny, Ireland This article is about the Church of Ireland cathedral in Kilkenny. For the Catholic cathedral, see St Mary's Cathedral, Kilkenny. Church in County Kilkenny, IrelandSt Canice's Cathedral52°39′24.40″N 07°15′25.72″W / 52.6567778°N 7.2571444°W / 52.6567778; -7.2571444LocationCounty KilkennyCountryIrelandDenominationChurch of IrelandPrevious denominationRoman CatholicWebsitehttps://www.stcanicescathedral.ie...

 

Wikipedia bahasa PortugisURLhttp://pt.wikipedia.org/TipeProyek ensiklopedia internetBersifat komersial?BukanPendaftaranOpsionalBahasaBahasa PortugisLisensiCreative Commons Atribusi-BerbagiSerupa 3.0 Tanpa Adaptasi dan Lisensi Dokumentasi Bebas GNU PemilikYayasan WikimediaBerdiri sejak11 Mei 2001 Wikipedia bahasa Portugis adalah wikipedia edisi bahasa Portugis. Sejak bulan November 2020, Wikipedia ini memiliki lebih dari 1.040.000 artikel. Wikipedia ini merupakan Wikipedia terbesar ke-18. Pro...

كارتر ليك     الإحداثيات 41°17′34″N 95°54′50″W / 41.292777777778°N 95.913888888889°W / 41.292777777778; -95.913888888889   [1] تقسيم إداري  البلد الولايات المتحدة[2]  التقسيم الأعلى مقاطعة بوتاواتامي  خصائص جغرافية  المساحة 5.228567 كيلومتر مربع  ارتفاع 299 متر  عدد السكان ...

 

Field hockeyat the Games of the XXXI OlympiadVenueOlympic Hockey CentreDates6–19 August 2016No. of events2← 20122020 → Field hockey at the2016 Summer OlympicsQualificationmenwomenTournamentmenwomenSquadsmenwomenvte Field hockey at the 2016 Summer Olympics in Rio de Janeiro took place from 6 to 19 August at the Olympic Hockey Centre in Deodoro. The competition had instituted several changes in the format and structure from the 2012 Summer Olympics. Twenty-four teams (t...