I love Markus Winand.  Use the Index, Luke taught me a lot about SQL.

Markus has produced a coping strategy for some of MySQL’s limitations in generating sequences.  I present an alternative form which produces arbitrary sized ranges, which he seems to believe is not possible.  Ah, Markus: it is.

So, this article is actually wrong.  There’s a call-time limitation I didn’t know about: apparently in MySQL you can’t call a stored procedure from a query.  That renders this seriously useless.  I’m leaving the article up just for the sake of permanence.

This advice is wrong.  Do not believe it.  I was in error.  It is kept only for posterity.

-- -------
--
--  in re: http://use-the-index-luke.com/blog/2011-07-30/mysql-row-generator
--
--  No, this way is not our only hope: there is another.

drop procedure if exists nseq;

delimiter //

-- -------
--
--  Use union all to generate a 2-row table.  
--  Use log 2 to find power expansion count to cap the desired size.
--  Use cross joins to expand our 2-row to the smallest superior table.
--  Select the diminished part to get our sequence.

create procedure nseq(in upto integer)
begin

    declare itLeft integer default -1;

    set itLeft = ceil(log2(upto));

    set @front := 'select rownum from (select @rownum := @rownum+1 as rownum
                   from (SELECT @rownum := 0) r, (select 1 as hidden union
                   all select 2) a0';
    set @upTo  := upto;

    set @interior := '';
    while (itLeft > 1) do
        set @interior := concat(@interior, ' cross join (select 1 as hidden
                         union all select 2) a', cast(itLeft as char));
        set itLeft    := itLeft - 1;
    end while;

    set @back  := ') sl where rownum <= ?;';
    set @query := concat(@front, @interior, @back);

    prepare stmt from @query;
    execute stmt using @upTo;  
    deallocate prepare stmt;  

end //

delimiter ;

Hopefully he will agree, or explain what I didn’t understand in his needs. 

This turns out to be pretty easy.  You might also want to read Implementing Harmonic Mean in MySQL.

Why bother blogging something this simple?  Because I couldn’t find one pre-made, and that means it’s time to bring my mighty google rank of like negative two to bear to fix the problem.

CREATE TABLE example(val integer);
INSERT INTO example VALUES(1),(2),(4),(8),(16);
SELECT exp(avg(ln(val))) as gmean from example;

That’s it.  You should see output like this:

mysql> CREATE TABLE example(val integer);
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO example VALUES(1),(2),(4),(8),(16);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT exp(avg(ln(val))) as gmean from example;
+- - - -+
| gmean |
+- - - -+
|     4 |
+- - - -+
1 row in set (0.00 sec)

Still haven’t figured out how to do central moments, skewness or kurtosis.

This turns out to be pretty easy.  You might also want to read Implementing Geometric Mean in MySQL.

Why bother blogging something this simple?  Because I couldn’t find one pre-made, and that means it’s time to bring my mighty google rank of like negative two to bear to fix the problem.

CREATE TABLE example(val integer);
INSERT INTO example VALUES(1),(2),(4),(8),(16);
SELECT count(val) / sum(1/val) as hmean from example;

That’s it.  You should see output like this:

mysql> CREATE TABLE example(val integer);
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO example VALUES(1),(2),(4),(8),(16);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT count(val)/sum(1/val) as hmean from example;
+- - - - +
| hmean  |
+- - - - +
| 2.5806 |
+- - - - +
1 row in set (0.00 sec)

Still haven’t figured out how to do central moments, skewness or kurtosis.