Showing posts with label mysql. Show all posts
Showing posts with label mysql. Show all posts

Saturday, May 12, 2012

Create running totals with data.

This is a fairly common requirement when reporting on data. The data analyst will typically extract data using sql and then use the SUM formula within Excel (with the aid of fixed and relative references, that are copied down). For example, supposing we are looking at a table that records number of starters by dates. We could have data like the following:
"record_date";"yr1starter"
"2004-01-01";"820"
"2004-01-02";"1891"
"2004-01-03";"1667"
"2004-01-04";"1607"
"2004-01-05";"1542"
We copy this into a spreadsheet (normally by using text-to-columns so the data is in separate columns). In order to get a running balance we can then use the following formula: =SUM(B$2:B2) for cell C2. We then copy down, so for instance the formula in cell C3 becomes: =SUM(B$2:B3) It's not the only way to do this in Excel - you could always add the subtotal above to the current record field, but that runs the danger of a gap in the figures. The attached graphic shows what the cumulative formula list will look like
Excel will normally display the calculations for example:
yr1starters cumulative total
820 820
1891 2711
1667 4378
1607 5985
1542 7527
However, it is possible to create cumulative/running totals in SQL (via an inner query, normally called a sub-query). I will first show this using MySql syntax:
SET @runtotal:=0;
SELECT qry1.r_date, qry1.day_starts,
(@runtotal := @runtotal + qry1.day_starts) AS runningtotal
FROM
(SELECT
record_date AS r_date, yr1starts AS day_starts
FROM startstops
WHERE record_date between '2004-01-01' AND '2004-01-31'
ORDER BY record_date) AS qry1
The key is to set the initial variable @runtototal to 0, so it can then total up the running total (the ORDER BY clause at the end of the select statement is also important). Incidentally, this query (and other ways to achieve it using Mysql) can be seen at StackOverflow. It is actually a bit more difficult to achieve this using sql*server. One way is to use a cursor, which I feel is OTT for this. Another way is to use a cross-join, and so join the table to itself. Performance can be an issue here if the data set is large (helped possibly by indexes)
SELECT a.record_date, a.yr1starter, SUM(b.yr1starter) AS running_total
FROM dbo.startstops a
CROSS JOIN dbo.startstops b
WHERE (b.record_date <= a.record_date)
AND a.record_date between '2004-01-01' AND '2004-01-31' AND b.record_date between '2004-01-01' AND '2004-01-31'
GROUP BY a.record_date,a.yr1starter ORDER BY a.record_date,a.yr1starter
You can read about different ways of calculating running totals in sql*server (including the usage of cursors) at the sqlteam link.

Saturday, November 21, 2009

Using Perl and Mysql to create a webpage

The normal way to produce a website that accesses data from a Mysql database is via the scripting language PHP. I wondered if it were possible to use Perl instead, and found that with the use of a couple of modules, it's not so hard to get started. (Here's a brief introduction to using modules with web programming.
Here's the Perl I wrote (I called the file empnew.cgi). I made sure the program was executable, and placed the file in a directory on my web server (make sure your path points to this directory):


#!/usr/bin/perl
use CGI;
use HTML::Template;
use DBI;
use strict;

my $dsn = 'dbi:mysql:one:localhost:3306:mysql_socket=/opt/lampp/var/mysql/mysqld.sock';
# the database here is called 'one'

my $CGI = CGI->new();
my $user = 'root';
my $pass = 'passwordx';

my $dbh = DBI->connect($dsn, $user, $pass)
or die "Can’t connect to the DB: $DBI::errstr\n";

my $sth = $dbh->prepare("select empno, ename, job, sal from emp order by ename");
$sth->execute;

# Prepare a data structure for HTML::Template
my $rows;
push @{$rows}, $_ while $_ = $sth->fetchrow_hashref();

#
# initiate the template and substitute the values:

my $template = HTML::Template->new(filename => 'empnew.tmpl',
die_on_bad_params => 0, );
# call param to fill in the loop with the loop data by reference.
$template->param(ROWS => $rows);

print $CGI->header();
# print the template
print $template->output();

$dbh->disconnect();


The module CGI deals quickly and efficiently with http headers.

The module HTML::Template that is called, allows you to state the Html layout in a separate file. In our example this calls empnew.tmpl

<!-- empnew.tmpl -->

<html>

<head>

<title>Employee Listing</title>

</head>

<body>

<h1>Employees</h1>

<table border=1>

<tr>

<td bgcolor="#ffff00"><font COLOR="#800500"><b>Emp Number</b></font></td>

<td bgcolor="#ffff00"><font COLOR="#800500"><b>Emp Name</b></font></td>

<td bgcolor="#ffff00"><font COLOR="#800500"><b>Job</b></font></td>

<td bgcolor="#ffff00"><font COLOR="#800500"><b>Salary</b></font></td>

</tr>



<!-- TMPL_LOOP NAME=ROWS -->

<tr>

<td><!-- TMPL_VAR NAME=EMPNO --></td>

<td><!-- TMPL_VAR NAME=ENAME --></td>

<td><!-- TMPL_VAR NAME=JOB --></td>

<td><!-- TMPL_VAR NAME=SAL --></td>

</tr>

<!-- /TMPL_LOOP -->

</table>

</body>

</html>

There are basically 3 new kinds of tags to be aware of when making a .tmpl file for access by HTML::Template. These are

# * VARIABLES defined by <tmp_var>
# * LOOPS defined by <tmpl_loop>
# * CONDITIONALS you can use <tmpl_if> <tmpl_unless> or <tmpl_else>

Variable tags such as <tmpl_var name="FOO"> will be replaced by whatever value is assigned to parameters when calling the HTMP::Template to the parameter FOO

The other tags can be standard html formatting stuff. Here's a good introduction to using the HTML::Template

Finally, in order to access data from the Mysql database we need to use the DBI module. You can see from the Perl script that we connect to the database using parameters given (not a good idea from the security pov to have the password in this script in real life - better to bring in as a parameter from a file that can't be seen by public eyes), prepare the sql script, and then execute. For more information on starting with DBI see this tutorial

Once this is setup, it's fairly simple to change the layout by editing the tmpl file directly without needing to change the program code.

Lastly - want to introduce some way of bringing back different results according to a parameter you pass over? Simply modify the previous Perl code as follows ($sal here to be parameter you can change according to your program, the ? serves as a placeholder for this argument passed to the sql execution - this helps stop sql injection as quote characters etc are escaped):

my $sal = 2900;

my $dbh = DBI->connect($dsn, $user, $pass)
or die "Can’t connect to the DB: $DBI::errstr\n";

my $sth = $dbh->prepare("select empno, ename, job, sal from emp where sal > ? order by empno");
my $rc = $sth->execute($sal);

# Prepare a data structure for HTML::Template


I hope this has been of interest! Good luck with your web programming!

NB. Whilst reproducing this entry I had a little difficulty in getting Blogger to print the HTML source code. There's a conversion utility on this site that allows you to do this.

Monday, August 31, 2009

Using Mysql to find prime factors

On my Mysql blog I've come up against the poser from the Euler Project : "What is the largest prime factor of the number 600851475143 ".

I think I've cracked this in Mysql, but I'm not 100% confident (well it works in this case, but will it always work?)

Anyway, here it is (I use the table Orders only as it contains plenty of rows):

*** set variables ***
set @var1=2;
set @varreset=2;
set @bignuma= 600851475143;

*** sql code ***
select a1factor from
(select
case when mod(@bignuma,@var1) = 0 then @var1:=@varreset else @var1:=@var1+1 end as a1factor,
case when mod(@bignuma,@var1) = 0 then @bignuma:=@bignuma/@var1 end as new_factor
from orders where @var1<@bignuma ) tmp
where new_factor is not null
order by a1factor desc;