rank() over

查詢同一個使用者下，最近時間訂購(最近一次操作) 的集合

不适用rank() over 

SELECT COUNT(*)
FROM (
	SELECT t1.*, t2.createtime
	FROM i_Order_Relation_Log t1, i_ismp_sp_engine_log t2
	WHERE t1.streamingno = t2.streamingno
) tt1
WHERE tt1.createtime = (
		SELECT MAX(createtime)
		FROM (
			SELECT t1.*, t2.createtime
			FROM i_Order_Relation_Log t1, i_ismp_sp_engine_log t2
			WHERE t1.streamingno = t2.streamingno
		) tt2
		WHERE tt2.userid = tt1.userid
		)
	AND tt1.optype = '0'
           

  查詢時間：多次測試大概在 0.8-0.9 之間

适用rank() over 

SELECT COUNT(*)
FROM (
	SELECT t.*, RANK() OVER (PARTITION BY userid ORDER BY createtime DESC) AS ran
	FROM (
		SELECT t1.*, t2.createtime
		FROM i_Order_Relation_Log t1, i_ismp_sp_engine_log t2
		WHERE t1.streamingno = t2.streamingno
	) t
) tt
WHERE tt.ran = 1
	AND tt.optype = '0'
           

   查詢時間：多次測試大概在 1.0-1.1 之間