My Photo

« PostgreSQLのコマンドラインクライアントpsql | Main | RESTとは?クロスドメイン通信の禁止とは? »

November 25, 2012

ウィンドウ関数 ROW_NUMBER()

SQLで相関サブクエリや自己結合を使ったSQL文を、ウィンドウ関数のROW_NUMBER()で書き直すことができる。
一般的にはROW_NUMBER()を使ったほうがパフォーマンスが良いそうだ。

sales、customers、productsの3つのテーブルがあって、customersの顧客毎に最新の販売を取得する例を考える。
各テーブルのデータは以下のようになっている。

sales
saleID quqntity customerID productID date
1311 2012-10-01
2132 2012-10-02
3212 2012-10-02
4211 2012-10-05
5232 2012-10-10
customers
customerID customerName
1Suzuki
2Tanaka
3Yamashita
products
productID productName price
1AAA100
2BBB200

customersの顧客毎に最新の販売を取得するSQLを自己結合、相関サブクエリで書くとそれぞれ以下のようになる。

SELECT c.customerName, s.date, p.productName,
       p.price, s.quantity
FROM customers c
LEFT OUTER JOIN (
 SELECT * FROM sales
 JOIN (
   SELECT MAX(saleID) AS maxID FROM sales 
   GROUP BY customerID) max
 ON sales.saleID = max.maxID
) s
  ON c.customerID = s.customerID
LEFT OUTER JOIN products p
  ON s.productID = p.productID
ORDER BY c.customerID;
SELECT c.customerName, s.date, p.productName,
       p.price, s.quantity 
FROM customers c
LEFT OUTER JOIN (
 SELECT * FROM sales
 WHERE sales.saleID
       = (SELECT MAX(saleID) FROM sales ss
          WHERE sales.customerID = ss.customerID)
) s
  ON c.customerID = s.customerID
LEFT OUTER JOIN products p
  ON s.productID = p.productID
ORDER BY c.customerID;

ROW_NUMBER()を使うとこうなる。自己結合、相関サブクエリは使っていない。

SELECT c.customerName, s.date, p.productName,
       p.price, s.quantity, s.maxID
FROM customers c
LEFT OUTER JOIN (
 SELECT *,
        ROW_NUMBER() OVER
          (PARTITION BY customerID
           ORDER BY saleID DESC) AS maxID
 FROM sales
) s
  ON c.customerID = s.customerID
     AND s.maxID = 1
LEFT OUTER JOIN products p
  ON s.productID = p.productID
ORDER BY c.customerID;

実行結果は以下。

 customername |        date         | productname | price | quantity | maxid
--------------+---------------------+-------------+-------+----------+-------
 Suzuki       | 2012-10-05 00:00:00 | AAA         |   100 |        2 |     1
 Tanaka       |                     |             |       |          |
 Yamashita    | 2012-10-10 00:00:00 | BBB         |   200 |        2 |     1

自分は仕事で、当初 ・テーブルAとBがあってAとBの各レコードは1対1に対応する。 と思っていたが、実は ・Aの1レコードに対してBが2レコード存在する場合がある。 ということがわかり、Aとそれに対応するBのうち最新のものを結合してもってこなければならないという場合にROW_NUMBER()を使ってうまいこと行った。

参考文献:WEB+DB PRESS Vol.62 SQL緊急救命室 第1回

« PostgreSQLのコマンドラインクライアントpsql | Main | RESTとは?クロスドメイン通信の禁止とは? »

SQL」カテゴリの記事

プログラミング、技術情報」カテゴリの記事

Comments

Post a comment

(Not displayed with comment.)

TrackBack

TrackBack URL for this entry:
http://app.cocolog-nifty.com/t/trackback/26461/56188398

Listed below are links to weblogs that reference ウィンドウ関数 ROW_NUMBER():

« PostgreSQLのコマンドラインクライアントpsql | Main | RESTとは?クロスドメイン通信の禁止とは? »

March 2017
Sun Mon Tue Wed Thu Fri Sat
      1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31  
無料ブログはココログ

日本blog村

  • にほんブログ村 IT技術ブログへ
  • にほんブログ村 アニメブログへ
  • にほんブログ村 サッカーブログ アルビレックス新潟へ

好きな音楽家

メモ

XI-Prof