From: Sean Chittenden <sean@...>
Date: 2002-04-11T08:26:36+09:00
Subject: Re: Switching from PHP to Ruby - Comments Please

> Since I am planning on using MySQL (may switch to PostgreSQL, but
> not likely), what is the best interface to use.  rubymysql or dbi?

ruby-dbi's really nice.  For extremely intensive stuff you may want to
use rubypostgres (::hint hint::) or rubymysql if you have to, but
DBI's likely the easiest most portable interface.  -sc






PS I know I obfuscated the table names and column names, there's no
way you could do anything even close to the following in MySQL.
::grin:: 500K rows, 0.12s to complete the query: check out PostgreSQL
if you get a chance.  IMHO, PostgreSQL is to MySQL what Ruby is to
Perl.


SELECT z.a, z.b,
         (z.b::numeric / hourly_total.b::numeric) * 100::numeric AS hourly_percentage,
         (z.b::numeric / daily_total.b::numeric) * 100::numeric AS daily_percentage
       FROM
         (SELECT c.a AS a,
            sum(f.b) AS b
          FROM h AS c,
            (SELECT f.b AS b,
               f.d AS d
             FROM e AS f
             WHERE EXTRACT(YEAR FROM f.utc_date) = ?
               AND EXTRACT(MONTH FROM f.utc_date) = ?
               AND EXTRACT(DAY FROM f.utc_date) = ?
               AND EXTRACT(HOUR FROM f.utc_date) = ?) AS f
          WHERE c.d = f.d
          GROUP BY c.a) AS z,
         (SELECT sum(f.b) AS b
          FROM e AS f
          WHERE EXTRACT(YEAR FROM f.utc_date) = ? AND
            EXTRACT(MONTH FROM f.utc_date) = ? AND
            EXTRACT(DAY FROM f.utc_date) = ? AND
            EXTRACT(HOUR FROM f.utc_date) = ?) AS hourly_total,
         (SELECT sum(f.b) AS b
            FROM e AS f
          WHERE EXTRACT(YEAR FROM f.utc_date) = ? AND
            EXTRACT(MONTH FROM f.utc_date) = ? AND
            EXTRACT(DAY FROM f.utc_date) = ?) AS daily_total,
         h AS c
       WHERE z.a = c.a
       ORDER BY c.a

-- 
Sean Chittenden