We're sorry but this page doesn't work properly without JavaScript enabled. Please enable it to continue.
Feedback

Optimizing Queries Using CTEs and Window Functions

Formal Metadata

Title
Optimizing Queries Using CTEs and Window Functions
Alternative Title
Speeding Up Analytical Queries in MariaDB: CTE's and Window Functions Highlight
Title of Series
Number of Parts
611
Author
License
CC Attribution 2.0 Belgium:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Identifiers
Publisher
Release Date
Language
Production Year2017

Content Metadata

Subject Area
Genre
Abstract
Window functions are an important tool for analytical queries. Windowfunctions act as regular functions but have access to more than one row duringcomputation. This makes them highly useful for optimising queries involvingself-joins, as well as queries that need to extract data from multiple relatedrows from the same table. The performance speedups obtained are sometimes 100fold. This talk will explain how that is possible, as well as use cases forwindow functions. On top of that, window functions pair well with another feature that MariaDB10.2 has introduced - Common Table Expressions. This feature was contributedby a community member and helps make queries more maintainable and sometimesfaster. This feature will be covered, mostly through the use of windowfunctions, but also introduce new use cases. The agenda for the talk is: * What are CTEs * What are window functions * Solving problems with regular SQL and window functions * Smoothing data * Top-N Queries * Difference between consecutive rows