Top n Window Function queries over a specific subset of data are common in analysis and reporting requirements. Luckily, in MySQL, there are Window functions we can use for this type of query. To be quite honest, you don’t necessarily need Window Functions. You can retrieve those top 3 (or whatever) types of results with a regular SQL query. But, since we have those powerful Window Functions, why not use them? My thoughts exactly! Besides, no one wants a spaghetti code mess of SQL to try and understand. Not to mention, Window functions are often better optimized for querying larger data sets. Continue reading and see example queries for more understanding…
[Read more]I’m switching to MySQL and leveraging Alan Beaulieu’s Learning SQL as a supporting reference for my Database Design and Development course. While reviewing Alan’s Chapter 5: Querying Multiple Tables, I found his coverage of using self-joins minimal.
In fact, he adds a prequel_film_id
column to the
film
table in the sakila
database and
then a single row to demonstrate a minimal self-join query. I
wanted to show them how to view a series of rows interconnected
by a self-join, like the following:
SELECT f.title AS film , fp.title AS prequel FROM film f LEFT JOIN film fp ON f.prequel_id = fp.film_id WHERE f.series_name = 'Harry Potter' AND fp.series_name = 'Harry Potter' ORDER BY f.series_number;
It returns the following result set:
…[Read more]
Rolling sum and average query results are possible by combining
the aggregate functions SUM()
or AVG()
with the OVER()
clause, making for powerful analytic
queries. I recently learned how to compute a rolling
average or sum of values by using the Windowing option
of the OVER()
clause, applying the concepts to a
data set I am familiar with. I wanted to share my learning with
any readers who might be interested…
Image by Steve Buissinne from …
[Read more]Having recently installed a fresh copy of MySQL 8.0.21 on Windows, I took careful note of parsing changes in the MySQL Shell. It was tedious that we lost multiple statement processing, which is supported in the MySQL Client and MySQL Workbench because it uses MySQL Client.
It was frustrating when I subsequently discovered that the MySQL
Shell took away the ability to write log files by removing the
TEE
and NOTEE
commands. I suspected
that since MySQL Workbench was still using the MySQL Client that
it should be in the code tree. In fact, the
mysql.exe
client is in this directory:
C:\Program Files\MySQL\MySQL Server 8.0\bin
So, I immediately created a batch file to put the MySQL Client
into my %PATH%
environment variable when needed. I
used this time tested DOS command: …
While I’m switching labs next term after more than a decade with more comprehensive lab set, I’m hoping the new exercises build the students’ core SQL skill set. Next term, I hope to see whether the change is successful. I’ve opted for using Alan Beaulieu’s Learning SQL: Generate, Manipulate, and Retrieve Data, 3rd Edition, because it’s a great book and uses the MySQL database.
One exercise that the students will lose is a data migration
exercise from a badly designed common_lookup
table
to a well designed common_lookup
table. The starting
point is shown below on the left and the fixed version is on the
right.
Many times, we do not want duplicate rows or values in our SQL
tables. On the other hand, in some situations, it does not matter
if there are duplicates present. For whatever reason, suppose
duplicates have found their way into one of your tables. How can
you find them quickly and easily? The ROW_NUMBER()
Window function is a fantastic tool to use. Continue
reading and see example queries you can apply to your own tables
and find those duplicates…
Photo by Joe Green on Unsplash
OS and DB used:
- …
Introduction In this article, I’m going to explain how the MySQL JSON_TABLE function works, and how you can use it to transform a JSON object into a relational database table. When using a relational database system, it’s best to design the database schema according to the relational model. However, there are certain use cases when the relational model is too strict, and we are better off storing data in a JSON column type. For instance, as I explained in this article, when designing an audit log table, it’s much more convenient to... Read More
The post MySQL JSON_TABLE – Map a JSON object to a relational database table appeared first on Vlad Mihalcea.
These days, I mostly program in PHP with MySQL as the database,
which is just fine by me. I have had a long-time interest in
MySQL (SQL in general), and after several steady months
of programming in PHP, I must say I have really come into the
language and developed a fondness for it. All that being said, I
still enjoy using and learning the MySQL Shell in Python mode. As
Database Developers, we often need the LAST INSERT
ID
value from a previous INSERT
statement on
a column that has the AUTO_INCREMENT
attribute.
MySQL Shell has a get_auto_increment_value()
method
we can call against a Shell object result and retrieve that
value. Continue reading and see examples of the MySQL Shell
get_auto_increment_value()
method used in Python
mode…
…
[Read more]
PHP provides a handy function, empty()
, that is used
to determine whether a variable is empty. Perhaps that
is a bit confusing to someone unfamiliar with the
empty()
function and I can see how. In this blog
post, I will cover: what empty()
means in PHP, what
the empty()
function does, and a use case pairing up
empty()
with the PHP ternary operator conditional
construct. Both used in combination with the MySQL
NULL
value. Continue reading and see examples of
empty()
…
Photo by Debby Hudson on …
[Read more]
I’m glad that testing new MySQL releases is so frequent for me.
Each testing cycle let me catch warning messages about deprecated
behaviors before they’re removed. This one announced the deprecation of digits for floating point data
types, like double
. The following column
definition for a table in my code tree triggered the warning
message:
, amount DOUBLE(10,2)
MySQL 8 (8.0.21) raised the following warning message:
Warning (code 1681): Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
Recognizing the deprecation, I redefined the column as:
, amount DOUBLE
I’m glad the MySQL development team is focused on alerting us to deprecations through warning messages. Naturally, I fixed all of …
[Read more]