子查询作为表时，表外加条件查询效率特别慢。

Question

SELECT M.STOCK_NAME, M.TRADE_DAYS, M.STOCK_COUNT, M.TF
  FROM (SELECT T.STOCK_NAME,
               TIME_TO_MARKET(T.STOCK_NAME) TRADE_DAYS,
               T2.STOCK_COUNT,
               CASE
                 WHEN TIME_TO_MARKET(T.STOCK_NAME) = T2.STOCK_COUNT THEN
                  1
                 ELSE
                  0
               END TF
          FROM (SELECT STOCK_NAME
                  FROM JQDATA.ORDER_ACTIVITIES
                 GROUP BY STOCK_NAME) T
          LEFT JOIN (SELECT STOCK_NAME, COUNT(1) STOCK_COUNT
                      FROM JQDATA.ORDER_ACTIVITIES
                     GROUP BY STOCK_NAME) T2
            ON T2.STOCK_NAME = T.STOCK_NAME) M
 WHERE M.TF = 0

Answer 1

以其它字段作为条件的时候没啥问题，case when字段作为条件字段查询特别慢。