I've stumbled across this fun SQL question on reddit, recently. The question was looking at a time series of data points where some events happened. For each event, we have the start time and the end time timestamp start end ----------------------------------- 2018-09-03 07:00:00 1 null 2018-09-03 08:00:00 null null 2018-09-03 09:00:00 null null 2018-09-03 10:00:00 … Continue reading Find the Next Non-NULL Row in a Series With SQL
Tag: Window Functions
PostgreSQL 11’s Support for SQL Standard GROUPS and EXCLUDE Window Function Clauses
Exciting discovery when playing around with PostgreSQL 11! New SQL standard window function clauses have been supported. If you want to play with this, you can do so very easily using docker: docker pull postgres:11 docker run --name POSTGRES11 -e POSTGRES_PASSWORD=postgres -d postgres:11 docker run -it --rm --link POSTGRES11:postgres postgres psql -h postgres -U postgres … Continue reading PostgreSQL 11’s Support for SQL Standard GROUPS and EXCLUDE Window Function Clauses
Using UNPIVOT to Traverse a Configuration Table’s Rows and Columns
Imagine you have a configuration table like the following: CREATE TABLE rule ( name VARCHAR2(50) NOT NULL PRIMARY KEY, enabled NUMBER(1) DEFAULT 1 NOT NULL CHECK (enabled IN (0,1)), priority NUMBER(10) DEFAULT 0 NOT NULL, flag1 NUMBER(3) DEFAULT 0 NOT NULL, flag2 NUMBER(3) DEFAULT 0 NOT NULL, flag3 NUMBER(3) DEFAULT 0 NOT NULL, flag4 NUMBER(3) … Continue reading Using UNPIVOT to Traverse a Configuration Table’s Rows and Columns
How to Avoid Excessive Sorts in Window Functions
Usually, this blog is 100% pro window functions and advocates using them at any occasion. But like any tool, window functions come at a price and we must carefully evaluate if that's a price we're willing to pay. That price can be a sort operation. And as we all know, sort operations are expensive. They … Continue reading How to Avoid Excessive Sorts in Window Functions
jOOQ 3.10 Supports Exciting MySQL 8.0 Features
In recent months, there had been some really exciting news from the MySQL team: (Recursive) Common Table Expressions in MySQL Introducing Window Functions These two SQL standard language features are among the most powerful SQL features that are available from most other databases. I frequently include them in conference talks about SQL (see my article … Continue reading jOOQ 3.10 Supports Exciting MySQL 8.0 Features
A Little Known SQL Feature: Use Logical Windowing to Aggregate Sliding Ranges
I'm frequently telling developers to put window functions almost everywhere, because they're so awesome! One feature that I rarely see in the wild (even if it is extremely useful for reporting) is called "logical windowing" in Oracle, and it's most useful when used with INTERVAL ranges. Let's see what we may want to do. I … Continue reading A Little Known SQL Feature: Use Logical Windowing to Aggregate Sliding Ranges
jOOQ Tuesdays: Chris Saxon Explains the 3 Things Every Developer Should Know About SQL
Welcome to the jOOQ Tuesdays series. In this series, we’ll publish an article on the third Tuesday every other month (today, exceptionally on a Wednesday because of technical issues) where we interview someone we find exciting in our industry from a jOOQ perspective. This includes people who work with SQL, Java, Open Source, and a … Continue reading jOOQ Tuesdays: Chris Saxon Explains the 3 Things Every Developer Should Know About SQL
10 SQL Tricks That You Didn’t Think Were Possible
Listicles like these do work - not only do they attract attention, if the content is also valuable (and in this case it is, trust me), the article format can be extremely entertaining. This article will bring you 10 SQL tricks that many of you might not have thought were possible. The article is a … Continue reading 10 SQL Tricks That You Didn’t Think Were Possible
Time for some Funky SQL: Prefix Sum Calculation
This Stack Overflow question has yet again nerd-sniped me [finding the] maximum element in the array that would result from performing all M operations Here's the question by John that was looking for a Java solution: With an array of N elements which are initialized to 0. we are given a sequence of M operations … Continue reading Time for some Funky SQL: Prefix Sum Calculation
How to Pattern-Match Files and Display Adjacent Lines in Java
Recently, we've published our article about the awesome window function support in jOOλ 0.9.9, which I believe is some of the best additions to the library that we've ever done. Today, we'll look into an awesome application of window functions in a use-case that is inspired by this Stack Overflow question Sean Nguyen: How to … Continue reading How to Pattern-Match Files and Display Adjacent Lines in Java