MySQL: Columns as Ordered Week Dates
March 8, 2016
Let’s say you have data containing some metrics and their values across an ordered set of dates in a week. Since most screens are longer horizontally than vertically, it’s sometimes better to present data where one metric lies in a row and the dates lie in columns, rather than the usual way around.
The usual way we show tables is like this:
Because most screens are in landscape mode and because we read from left to right, there are times when it makes sense to pivot the table as follows:
This may not be “tidy data” as defined by Hadley Wickham in his excellent paper, but pivoting as such results in easier navigation/scrolling when you have more metrics than dates.
For example, let’s say you have a weekly report with 7 consecutive dates from Monday to Sunday of that week, and 30 metrics. You’ve seen the 7-day trend on the first metric, and would like to see the trend on the 27th metric. All you need to do is scroll down to the 27th metric, and read as you would (from left to right), to see if there’s any trend seen across that week.
Here’s the full MySQL query for the pivot table above, where the rows are metrics and the columns comprise of an ordered set of dates in a week.
rank variable (an integer) allows you to show the metrics in a specific order. A metric with a certain rank will be shown before another metric with a higher numbered rank.
Hope this helps!