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]
The Window Ranking functions: ROW_NUMBER(),
RANK(), and DENSE_RANK() each
rank rows with an increasing integer value. I wrote a
previous blog post, ROW_NUMBER() Window Function – find duplicate
values, where I covered how the ROW_NUMBER()
window function can be used to target any duplicate rows, with
the use of the PARTITION BY clause in the
OVER() clause. In this post, I cover the differences
between RANK(), and DENSE_RANK() in
handling any ties according to the sorting performed by the
ORDER BY clause with regards to the assigned
increasing integer. Continue reading and see examples…
…
[Read more]
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:
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]
In Use MySQL BLOB column with PHP to store .pdf
file, I covered an example of how to store a .pdf file in the
actual database table using the MySQL BLOB datatype
and PHP. Now that we have .pdf’s stored in the database, how do
we display them in the browser? This blog post answers that exact
question. Continue reading to see a working example using PHP…
Self-Promotion:
If you …
[Read more]
There may come a time you need to rename one or more columns in
an existing MySQL table for a variety of reasons. Using
ALTER TABLE, to rename a column is an easy enough
command. But, suppose there are multiple tables in the same
database/schema that have the same-named column and all of those
columns need to be renamed. That could be a lot of ALTER
TABLE statements to type out. Save your energy and time,
avoiding all of those ALTER TABLE commands
all-together. If you are lucky enough to be working with a MySQL
version > 8.0 then the Shell is your salvation. With just a
few lines of Python code in \py mode, all of your
trouble(s) and headache(s) are no more…
Photo by Ibrahim Rifath on …
[Read more]Writing MySQL Proxy in GO for self-learning: Part 2 — decoding handshake packet
After we built a generic TCP proxy, we can continue with our journey. Today’s goal will be to understand the MySQL Protocol, receive, decode, encode and send to the client the first packet sent by the MySQL Server.
MySQL connections threads
Each client connection to MySQL Server handled by a thread. MySQL is portable, so the underhood threads implementation is system dependent (Windows, macOS, and Linux have their own threads implementation).
What important to us, it’s to understand that a single client connection …
[Read more]Apache Spark is the de facto framework of the big data world. Any serious organization that’s dealing with big data uses Spark almost exclusively. Though, it has some caveats. For the starter, it’s hard to use. And it’s very confusing to get started with, even for those with a solid …
The post MySQL table to JSON with 10 lines of Spark appeared first on Geeky Hacker.
Writing MySQL Proxy in GO for self-learning: Part 1 — TCP Proxy
Weekend. Quarantine. If you bored like me, and want to learn something new, you can join my journey of writing yet another MySQL Proxy Server.
In the past, I had a crazy idea of writing MySQL Proxy Server. The first reason was to understand the MySQL Communication Protocol to let me write more efficient programs. The second one was to learn more about low-level network programming concepts in general. Finally, I have time at least to start… and you can join me!
The real usage of MySQL Proxy can vary:
Prelude
I’m not sure if I can …
[Read more]
