最新のMySQLでは分析関数が使えるらしいが古いMySQLでは使えなかった為作ってみた。
ユーザ変数を使わなかった理由について
MySQLのユーザ変数は評価順序が未定義の為、RowNumberやRankの動作に使ってはいけないという記事がちらほらあったのだが、マニュアル文の解説を読んでも動作保証されるのかどうか良く分からなかった。なのでとりあえず使用しないSQLで作成してみた。
『ユーザ変数では動作保証されないことを確認したわけではない』のでご注意を。
要件
商品ごとの最新の仕入情報を最新から3件づつ出したい
同一仕入日の場合、搬入回数が多いものを優先する
テーブル
仕入情報
| 商品コード | 仕入日 | 搬入回数 | 仕入数 |
|---|---|---|---|
| A0001 | 2018/11/01 | 1 | 5 |
| A0001 | 2018/11/02 | 1 | 5 |
| A0001 | 2018/11/03 | 1 | 5 |
| A0001 | 2018/11/04 | 1 | 5 |
| A0002 | 2018/11/01 | 1 | 5 |
| A0002 | 2018/11/01 | 2 | 5 |
| A0002 | 2018/11/01 | 3 | 5 |
| A0002 | 2018/11/01 | 4 | 5 |
| A0003 | 2018/11/01 | 1 | 5 |
| A0003 | 2018/11/01 | 2 | 5 |
| A0003 | 2018/11/02 | 1 | 5 |
| A0003 | 2018/11/02 | 2 | 5 |
サンプルSQL
SELECT
商品コード
, 仕入日
, 搬入回数
, 仕入数
, 仕入日降順
, 搬入回数順補正
, 仕入日降順 - 搬入回数順補正 AS 順位
FROM (
SELECT
商品コード
, 仕入日
, 搬入回数
, 仕入数
, (/* 仕入日降順での順位 */
SELECT
COUNT(*)
FROM 仕入情報 仕入日降順
WHERE 仕入情報.商品コード = 仕入日降順.商品コード
AND 仕入情報.仕入日 <= 仕入日降順.仕入日
) AS 仕入日降順
, (/* 仕入日が同じとき、仕入日ごとの搬入回数降順での順位 */
SELECT
COUNT(*)
FROM 仕入情報 搬入回数降順
WHERE 仕入情報.商品コード = 搬入回数降順.商品コード
AND 仕入情報.仕入日 = 搬入回数降順.仕入日
AND 仕入情報.搬入回数 < 搬入回数降順.搬入回数
) AS 搬入回数順補正
FROM 仕入情報
) 仕入情報_順位付き
WHERE 仕入日降順 - 搬入回数順補正 <= 3 /* 順位が3以内の物を表示 */
ORDER BY
商品コード
, 順位
実行結果
| 商品コード | 仕入日 | 搬入回数 | 仕入数 | 仕入日降順 | 搬入回数順補正 | 順位 |
|---|---|---|---|---|---|---|
| A0001 | 2018/11/04 | 1 | 5 | 1 | 0 | 1 |
| A0001 | 2018/11/03 | 1 | 5 | 2 | 0 | 2 |
| A0001 | 2018/11/02 | 1 | 5 | 3 | 0 | 3 |
| A0002 | 2018/11/01 | 4 | 5 | 4 | 3 | 1 |
| A0002 | 2018/11/01 | 3 | 5 | 4 | 2 | 2 |
| A0002 | 2018/11/01 | 2 | 5 | 4 | 1 | 3 |
| A0003 | 2018/11/02 | 2 | 5 | 2 | 1 | 1 |
| A0003 | 2018/11/02 | 1 | 5 | 2 | 0 | 2 |
| A0003 | 2018/11/01 | 2 | 5 | 4 | 1 | 3 |
解説
仕入日降順のサブクエリで商品コードごとの仕入日降順を取得している。
搬入回数順補正のサブクエリは、商品コード、仕入日ごとの搬入回数降順を取得している
仕入日降順は同一仕入日の場合は同じ値になるため、搬入回数順補正の結果分を減算することで順位をつけている