グループ毎に件数を指定して抽出するMySQL UDF
Objective-C C PHP C++
Switch branches/tags
Nothing to show
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
cstl
.gitignore
Doxyfile
Makefile
README.md
glimit.c
glimit.def
test.php

README.md

glimit - グループ毎に件数を指定して抽出するMySQL UDF

 year | month | sales
------+-------+-------
 2010 |  01   | 586
 2010 |  02   | 437
 2010 |  03   |  78
 2010 |  04   | 524
 2010 |  05   | 615
 2010 |  06   | 928
 2010 |  07   | 958
 2010 |  08   | 575
 2010 |  09   | 804
 2010 |  10   | 270
 2010 |  11   | 393
 2010 |  12   | 592
 2011 |  01   |  11
 2011 |  02   | 537
 2011 |  03   | 493
 2011 |  04   | 562
 2011 |  05   |  10
          .
          .
          .

上記のようなテーブルで、年(year)毎の売り上げ(sales)月トップ3ランキング

 year | month | sales
------+-------+-------
 2010 |  07   | 958
 2010 |  06   | 928
 2010 |  09   | 804
 2011 |  09   | 998
 2011 |  12   | 898
 2011 |  10   | 763
 2012 |  06   | 991
 2012 |  08   | 846
 2012 |  12   | 831
          .
          .
          .

を出したい場合、

select * from sample where year = '2010' order by sales desc limit 3;
select * from sample where year = '2011' order by sales desc limit 3;
select * from sample where year = '2012' order by sales desc limit 3;

というように、各年毎にorder byとlimitを使ったSQLを年数分だけ実行する必要があります。

また、@変数を使用して1発で出す方法もあるようですが、慣れた人でないと理解しにくい(メンテナンスしにくい)という問題があります。

このUDFは、指定した件数で各グループ毎にデータを抽出する関数glimitを提供します。

glimitを使用すると先の抽出をこのようなSQL1行で実現できます。

select * from (select * from sample order by year, sales desc) as sorted where glimit(3, year) = 1;

glimit(n, field1 [, field2, field3 ...])

  • 第1引数nに、グループ毎に抽出したい件数

  • 第2引数以降field1``field2``field3...に、グループ化する項目(最低でも1つ)

を指定してください。

戻り値は、指定したグループの出現カウントがn以下の場合は1nを超えたら0が返ります。

上記サンプルの例だと、2010年のデータが上から3つ目までは1が返り、4つ目以降の2010年のデータは0が返ります。

使用(仕様)上の注意

  • glimitは通常のlimitと同様に、データの出現順にグループカウントをしていくので、抽出したい条件順にソートした状態のテーブルやサブクエリを指定する必要があります。なお、サブクエリの代わりにソートを実行するVIEWを指定しても、先にwhere glimit(~が評価されてしまうので、ソート前の状態のデータから抽出されてしまいます。正しい結果を得るにはソート済みのテーブルかソートを実行するサブクエリを指定してください。

  • MySQLは標準で大文字小文字の違いを無視しますが、glimitは大文字小文字を区別します('aaa'と'AAA'を別グループとして扱う)。glimitで大文字小文字を区別しないようにするには、以下のようにucaselcaseで統一した値を使用してください。

      glimit(3, ucase(field1), ucase(field2))
    
  • グループ化したい項目にNULLが1つでも含まれていると、そのレコードはグループ化判別対象から除外されます。つまりNULL同士が同じグループとして扱われる事はありません。

  • glimit内部では、グループ化に指定した項目内容を0x01で連結した値をキーとして保持します。したがって、グループ化したい値の中に0x01を含む場合、意図しないグループ化をする可能性があります。

      (項目の値) '山田[0x01]太郎', '東京都' => (内部キー) '山田[0x01]太郎[0x01]東京都'
      (項目の値) '山田', '太郎[0x01]東京都' => (内部キー) '山田[0x01]太郎[0x01]東京都'
      ※同一のグループとして扱われてしまう
    

    ただし、SJIS, EUC-JP, UTF-8の複数バイト文字中で0x01を含む文字は存在しない(と思われる)ので、壊れたデータでもない限りこのような誤判定をすることはありません。心配な場合は、使用しているシステムで絶対に使われない文字を区切り文字としてソースを変更してください。

      #define SEPARATOR '\1' <= この部分を変更
    

性能

標準のSQLを駆使した方法からglimitに切り替えて劇的な効果があったので、正確なベンチを取って比較まではしていません(比較するまでもない!的な)。

ただ、すべての環境、すべてのテーブル構造での効果を保証するものではありません。

インストール

  1. MakefileのMYSQL_INC, MYSQL_BIN, INSTALL_DIRの値を使用している環境に合わせて変更してください。INSTALL_DIRはMySQLがUDFをロードできるパスである必要があります。

  2. (sudo) make installを実行するとglimit.so(.dll)のコピーとMySQLへのUDF関数登録まで行います。その際、MySQLのrootパスワードを聞かれますのでパスワードを入力してください。

アンインストール

(sudo) make uninstallを実行してください。UDF関数登録の解除とglimit.so(.dll)の削除まで行います。なお、インストール時と同様にrootパスワードを聞かれます。

使用ライブラリ

CSTLを使用しています(修正BSDライセンス)。

動作確認環境

  • Windows 7(MinGW) & MySQL 5.0
  • ubuntu 12.04 & MySQL 5.5
  • CentOS 5.9 & MySQL 5.0

Changelog

1.0 (2013-03-12)

  • 初版リリース

ライセンス

修正BSDライセンスで配布します。

© 2013 ktty1220