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…

Image by …

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 …

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…

…

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…

…

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 …

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…

Photo by Ben on Unsplash

Self-Promotion:

If you …

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 …

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 …

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.