内容简介:Let’s say we have a database table with date ranges, each range designated by a RANGE_START and a RANGE_END column:We are now interested in finding the gaps between these date ranges. If we look at this example data set we can see that there are two gaps:W
Let’s say we have a database table with date ranges, each range designated by a RANGE_START and a RANGE_END column:
CREATE TABLE date_ranges ( range_start DATE, range_end DATE );
RANGE_START RANGE_END ----------- --------- 05/02/2020 01/04/2020 02/04/2020 15/04/2020 16/04/2020 01/05/2020 01/06/2020 20/06/2020 21/06/2020 01/07/2020 02/07/2020 31/07/2020 05/08/2020 30/08/2020
We are now interested in finding the gaps between these date ranges. If we look at this example data set we can see that there are two gaps:
RANGE_START RANGE_END 05/02/2020 01/04/2020 02/04/2020 15/04/2020 16/04/2020 01/05/2020 -- gap -- 01/06/2020 20/06/2020 21/06/2020 01/07/2020 02/07/2020 31/07/2020 -- gap -- 05/08/2020 30/08/2020
What would be the SQL query to find these automatically? With standard SQL this would be a difficult task. However, there are some special functions in Oracle SQL called analytic functions that greatly help with this task. Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. In this case we will use the analytic functions MAX and LEAD:
SELECT * FROM ( SELECT MAX(range_end) OVER(ORDER BY range_start) + 1 gap_start, LEAD(range_start) OVER(ORDER BY range_start) - 1 gap_end FROM date_ranges ) WHERE gap_start <= gap_end;
The result of this query are the date range gaps we are interested in:
GAP_START GAP_END --------- ------- 02/05/2020 31/05/2020 01/08/2020 04/08/2020
Note that the MAX function in the query is the analytic MAX function, not the aggregate MAX function, indicated by the OVER keyword with an analytic clause. It operates on a sliding window. The LEAD analytic function allows you to access the following row from the current row without using a self-join.
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网
猜你喜欢:本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
Thirty-three Miniatures
Jiří Matoušek / American Mathematical Socity / 2010-6-18 / USD 24.60
This volume contains a collection of clever mathematical applications of linear algebra, mainly in combinatorics, geometry, and algorithms. Each chapter covers a single main result with motivation and......一起来看看 《Thirty-three Miniatures》 这本书的介绍吧!