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 之间