StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Analyze of such data (name,date) could be seen as issuing ad-hoc SQL queries to get timeseries information.</p> <p>You will 'sample' your information by a date/time frame (day/week/month/year or more detailled by hour/minute) depending of how large is your dataset.</p> <p>I often use such query where the date field is truncate to the sample rate, in mysql DATE_FORMAT function is cool for that (postgres and oracle use date_trunc and trunc respectivly)</p> <p>What you want to see in your data is in your your WHERE conditions.</p> <pre><code>select DATE_FORMAT(date_field,'%Y-%m-%d') as day, COUNT(*) as nb_event FROM yourtable WHERE name = 'specific_value_to_analyze' GROUP BY DATE_FORMAT(date_field,'%Y-%m-%d'); </code></pre> <p>execute this query and output to a csv file. You could use direct mysql commands for that, but I recommend to make a python script that execute such query, and you can use getopt options for output formatting (with or without columns headers, use different separator than default one, etc). And even you can build dynamically the query based on some options.</p> <p>To plot such information, look at time series tools. If you have missing data (date that won't appears in result of such sql query) you should take care for the choice. Excel is not the correct one for that, I think (or not master enough it), but could be a start.</p> <p>Personaly I found dygraph, a javascript library, really cool for time series plotting, and it can be used with a csv file as source. Careful in such configuration, due to crossdomain security constraint, the csv file and html page that display the Dygraph object should be on the same server (or whatever the security constraint of your browser want to accept).</p> <p>I used to build such webapp using django, as it's my favourite web framework, where I wrap url call as this :</p> <pre><code>GET /timeserie/view/<category>/<value_to_plot> GET /timeserie/csv/<category>/<value_to_plot> </code></pre> <p>The first url call a view that simply output a template file with a variable that reference the url to get the csv file for the Dygraph object :</p> <pre><code><script type="text/javascript"> g3 = new Dygraph( document.getElementById("graphdiv3"), "{{ csv_url }}", { rollPeriod: 15, showRoller: true } ); </script> </code></pre> <p>The second url call a view that generate the sql query and output the result as text/csv to be rendered by Dygraph.</p> <p>It's "home made" could stand simple or be extended, run easily on any desktop computer, could be extended to output json format for use by others javascript libraries/framework.</p> <p>Else there is tool in opensource, related to such reporting (but timeseries capabilities are often not enough for my need) like Pentaho, JasperReport, SOFA. You make the query as datasource inside a report in such tool and build a graph that output timeserie.</p> <p>I found that today web technique with correct javascript library/framework is really start to be correct to challenge that old fashion of reporting by such classical BI tools and it make things interactive :-)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload