Help us understand the problem. What is going on with this article?

MySQLでユーザ変数を使わずにRankのような動きをするSQLを作ってみた

More than 1 year has passed since last update.

最新の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

解説

仕入日降順のサブクエリで商品コードごとの仕入日降順を取得している。
搬入回数順補正のサブクエリは、商品コード、仕入日ごとの搬入回数降順を取得している
仕入日降順は同一仕入日の場合は同じ値になるため、搬入回数順補正の結果分を減算することで順位をつけている

Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away