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:
Even in times of a growing market of specialized NoSQL databases, the relevance of traditional RDBMS doesn't decline. Especially when it comes to the calculation of aggregates based on complex data sets that can not be processed as a batch like Map&Reduce. MySQL is already bringing in a handful of aggregate functions that can be useful for a statistical analysis. The best known of this type are certainly:
Even in times of a growing market of specialized NoSQL databases, the relevance of traditional RDBMS doesn't decline. Especially when it comes to the calculation of aggregates based on complex data sets that can not be processed as a batch like Map&Reduce. MySQL is already bringing in a handful of aggregate functions that can be useful for a statistical analysis. The best known of this type are certainly:
The internet really is full of stuff, and we are starting to fill
the Clouds with all sorts of software that moves from the
serverrooms and in to the clouds. One aspect here that is often
missing is what a Cloud environment and the Internet in itself
can add to what we already know and have. Or in other words,
running in the cloud is not only about lowering costs, and if you
look at it by lowering costs by running things in the cloud the
same way you used to run it on your private server, then chances
are you might not gain anything, running in a cloud is also about
having distinct advantages not easily available somewhere
else.
Christopher Ahlberg here at Recorded Future just blogged about
another way of looking at it: What do we have access to on the
internet that is not readily available in the Server room, and
the answer is: data. A lot of data. For someone in the field of
BI, not using Internet data, and sticking to traditional …
Phew, here we go, this blog post has been long time coming! A few months ago I started toying around with the idea of analyzing the PlanetMySQL public blog feed. It doesn’t take long to extract the data and prepare it for analysis but between lots of work and procrastination this blog post was left unfinished.
It was partly out of pure curiosity and partly the fact that it seemed to me there were less posts than previous years that I decided to trend out the number of posts over the past years and here we go.
The blue line shows the blog posts per month over the past six
years and the black line is a polynomial trend line. There are a
few points of interest which are visible and I’ll be listing here
(to all their understanding):
1. The first thing which struck me negatively is …
Just a quick note to let everyone know that our new benchmarking script now supports OSX 10.6 on Intel hardware. That means you can run one simple command and get all of the sequential and random INSERT and SELECT performance statistics about your database performance. As usual the script is open source and released under the new BSD license. Give is a try by downloading now! See the download page for more details.
You can download the first release of the benchmarking script here: http://code.google.com/p/dbbenchmark/
Please read the README file or consult the Support page before running the benchmarks.
I was actually exploring inotify-tools for something else, but they can also be handy for seeing what goes on below a mysqld process. inotify hooks into the filesystem handlers, and sees which files are accessed. You can then set triggers, or just display a tally over a certain period.
It has been a standard Linux kernel module since 2.6.13
(2005, wow that’s a long time ago already) and can be
used through calls or the inotify-tools (commandline). So with
the instrumentation already in the kernel, apt-get install
inotify-tools is all you need to get started.
# inotifywatch -v -t 20 -r /var/lib/mysql/* /var/lib/mysql/zabbix/* Establishing watches... Setting up watch(es) on /var/lib/mysql/mysql/user.frm OK, /var/lib/mysql/mysql/user.frm is now being watched. [...] Total of 212 watches. Finished establishing watches, now collecting …[Read more]
