SELECT d, category, goods_id, sales, sales_10th_before, rnk, 1.0*sales/sales_10th_before AS ratio
FROM
(
  SELECT d, category, goods_id, sales,
    RANK() OVER (PARTITION BY d, category ORDER BY sales DESC) AS rnk,
    LAST_VALUE(sales) OVER (PARTITION BY d,category ORDER BY sales DESC ROWS BETWEEN CURRENT ROW AND 10 FOLLOWING) as sales_10th_before
  FROM
  (
    SELECT
      TD_TIME_FORMAT(time,'yyyy-MM-dd','JST') AS d, category, sub_category, goods_id, SUM(price*amount) AS sales
    FROM  sales_slip
    WHERE TD_TIME_RANGE(time, '2013-01-01', '2013-01-02', 'JST')
    GROUP BY TD_TIME_FORMAT(time,'yyyy-MM-dd','JST') , category, sub_category, goods_id
  ) t1
) t2
WHERE rnk <= 5
ORDER BY d, category, rnk