.svg)
.svg)
SQL is incredibly powerful, but it can be hard to remember what output you get from different parts of a SQL query. Here's my mental cheat-sheet for writing and reading non-trivial SQL queries.
The Tricks
1. The absence of 'group by' means you will get one output record per input, after predicates like 'where' clauses are applied.
2. If a query has a 'group by', you will get one record per group.
3. 'Over' clauses generate a field for each output record. They operate on a window defined by either a) the 'partition by' expression - which works like a 'group by' - or b) a range of records defined by 'rows between'.
4. Subqueries can be put in a bunch of different places in SQL. They can also appear in predicates in the 'where' clause, where their output acts as part of the filtering condition.
5. In a select list, a subquery (usually) runs once, before the outer query, and its output is inserted as a field.
6. In a 'from', a subquery's output acts as an input table. You can alias the subquery and reference its fields.
Here are a few examples of SQL tricks demonstrating these tricks, written for clarity not performance:
Example: Tweet Likes / All Likes
Example: Current Order Price vs Average Over The Last 10
Example: HTTP Errors By Path And Status, And By Path
There are lots (and lots) of intermediate SQL bits that can greatly simplify life. I've oversimplified a bunch of things and focused on some common cases. Performance can vary greatly based on the DBMS, data, indexes, and config, but hopefully, this helps! Join me on the Decodable community slack with questions, suggestions or comments!
You can get started with Decodable for free - our developer account includes enough for you to build a useful pipeline and - unlike a trial - it never expires.
Learn more:
- Read the docs
- Check out our other blogs
- Subscribe to our YouTube Channel
Join the community Slack
Demo Day - Intro to Decodable and the new UX
Recorded from the Demo Day on March 10th 2022, Decodable CEO Eric Sammer demonstrates the capabilities of the real-time data engineering platform through the new user experience.
.svg)
Streaming Data Pipelines with SQL
Streaming data systems have been growing more capable and flexible over the past few years. Despite this, it is still challenging to build reliable pipelines for stream processing. In this Data Engineering Podcast hosted by Tobias Macey, Eric Sammer, discusses the shortcomings of the current set of streaming engines and how they force engineers to work at an extremely low level of abstraction. He explains how Decodable addresses that limitation and lets data engineers build streaming pipelines entirely in SQL.
The Top 5 Streaming ETL Patterns
ETL and ELT are traditionally scheduled batch operations, but as the need for always-on, always-current data services becomes the norm, realtime ELT operating on streams of data is the goal of many organizations - if not the reality, yet.In real world usage, the ‘T’ in ETL represents a wide range of patterns assembled from primitive operations. In this blog we’ll explore these operations and see examples of how they’re implemented as SQL statements.
This Microservice Should Have Been a SQL Statement
In event-driven architecture, functions can be used to replace backend microservices. In this blog, we will demonstrate how to use Decodable as a serverless function written in SQL performing stateful transformations, replacing unnecessary microservices in your application. "That microservice could have been a SQL statement" made real, with Decodable.
Heading
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse varius enim in eros elementum tristique. Duis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere.
.svg){kind=small}
