重複行を排除したビューの作成について

Question

MySQLバージョン：5.6

テーブル定義
CREATE TABLE test (
id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL COMMENT '利用者',
input_date date NOT NULL COMMENT '入力日',
name_sei varchar(32) NOT NULL COMMENT '名前（姓）',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

データ定義
INSERT INTO test VALUES (1,63,'2015-07-01','相沢'),(2,63,'2015-08-11','相沢'),(3,63,'2015-08-10','相沢'),(4,64,'2015-07-04','大畑'),(5,64,'2015-08-09','大畑'),(6,65,'2015-08-10','菅野'),(7,63,'2015-07-15','相沢');

SELECT結果
+----+---------+------------+----------+
| id | user_id | input_date | name_sei |
+----+---------+------------+----------+
| 1 | 63 | 2015-07-01 | 相沢 |
| 2 | 63 | 2015-08-11 | 相沢 | ＊
| 3 | 63 | 2015-08-10 | 相沢 |
| 4 | 64 | 2015-07-04 | 大畑 |
| 5 | 64 | 2015-08-09 | 大畑 | ＊
| 6 | 65 | 2015-08-10 | 菅野 | ＊
| 7 | 63 | 2015-07-15 | 相沢 |
+----+---------+------------+----------+

上記SELECT結果の＊の行、user_idの重複を排除した上でinput_dateの最新を取得する
ビューを作成する必要があり、試行錯誤しております。
以下のクエリでビューを作ってみましたが、idが正常に取得できませんでした。

CREATE VIEW `view_test` AS
    select 
        `test`.`id` AS `id`,
        `test`.`user_id` AS `user_id`,
        max(`test`.`input_date`) AS `input_date`,
        `test`.`name_sei` AS `name_sei`
    from
        `test`
    group by `test`.`user_id`

SELECT結果
+----+---------+------------+----------+
| id | user_id | input_date | name_sei |
+----+---------+------------+----------+
| 1 | 63 | 2015-08-11 | 相沢 |
| 4 | 64 | 2015-08-09 | 大畑 |
| 6 | 65 | 2015-08-10 | 菅野 |
+----+---------+------------+----------+

＊の行を取得できるビューを作成する方法をご教示頂けないでしょうか？
宜しくお願い致します。

Answer 1

他にも定番の方法としては次のような相関サブクエリを使う方法があげられます。
この方法なら user_id と input_date で重複があっても user_id ごとに１行しか返りません。
ただしたいていの場合、こっちのほうが遅いです。

CREATE VIEW `view_test` AS
select * from test t
where t.id = (
  select x.id from test x
  where x.user_id = t.user_id
  order by x.input_date desc, x.id desc
  limit 1
)

---

また、kokardy さんの回答と考え方はほとんど同じですが、
サブクエリを使わずに group by と having で済ませる方法もあります。
ただし、この書き方は MySQL でしかできません。
（ほか RDBMS だと group by で select にアスタリスクは使えないし group by に test の列を羅列しないといけない）

CREATE VIEW `view_test` AS
select t.* from test t
inner join test x on x.user_id = t.user_id
group by t.id
having t.input_date = max(x.input_date)

Comments

[ariel200]

ご回答ありがとうございました。ビューを作成し、正常に値が取得できました。
ただお恥ずかしい話ですがご提示頂いたクエリが理解できない部分が多くあります。もしよろしければ、この2つのクエリを導き出した過程をご説明頂けないでしょうか？

[ngyuki]

グループごとのトップ１の行を取得する定番のパターンなので、、、導き出したというより、知っていたという感じです。

[ariel200]

申し訳ありません、メール通知が来なかったためお返事に気付きませんでした。これらのクエリは定番パターンなのですね。再度のお返事ありがとうございました。

Answer 2

MySQLの方言的に通るかわかりませんが、

SELECT t.*
FROM
(SELECT "user_id", 
 MAX("input_date") AS max_input_date
 FROM "test" GROUP BY "user_id") AS g
LEFT JOIN "test" AS t
ON t."user_id" = g."user_id" AND t."input_date" = g."max_input_date"

こういうのはいかがですか？

Comments

[ngyuki]

MySQL 的には識別子のクオートはダブルクオートではなくバッククオートなのでそこだけ注意が必要です

[ariel200]

クエリ自体は問題なく正常に値を取得できましたが、ビュー作成時に以下のエラーとなってしまいました。

ビュー作成クエリ
CREATE OR REPLACE VIEW `test_view` AS
SELECT t.*
FROM
(SELECT user_id,
MAX(input_date) AS max_input_date
FROM test GROUP BY user_id) AS g
LEFT JOIN test AS t
ON t.user_id = g.user_id AND t.input_date = g.max_input_date

エラー内容
ERROR 1349: View's SELECT contains a subquery in the FROM clause

MySQLではビュー作成時はFROM句にサブクエリを使用できないようです。
ですがご提示頂いたクエリは大変勉強になりました、ありがとうございました。