Запрос Dense_rank в sql (4 разных столбца) в

Используйте аналитическую функцию ROW_NUMBER (или RANK / DENSE_RANK, если это более уместно) в подзапросе, а затем выполните агрегирование во внешнем запросе:

SQL Fiddle

Настройка схемы Oracle 11g R2:

CREATE TABLE table_name ( Snno, t_time, Value, rate ) AS
SELECT 'ABC', TIMESTAMP '2018-05-17 08:00:00', 100.00, 3 FROM DUAL UNION ALL
SELECT 'ABC', TIMESTAMP '2018-05-17 22:00:00', 200.00, 1 FROM DUAL UNION ALL
SELECT 'ABC', TIMESTAMP '2018-05-16 08:00:00', 100.00, 1 FROM DUAL UNION ALL
SELECT 'XYZ', TIMESTAMP '2018-05-14 01:00:00', 700.00, 1 FROM DUAL UNION ALL
SELECT 'XYZ', TIMESTAMP '2018-05-15 10:00:00', 500.00, 2 FROM DUAL UNION ALL
SELECT 'XYZ', TIMESTAMP '2018-05-15 13:00:00', 100.00, 2 FROM DUAL;

Запрос 1:

SELECT snno,
       AVG( value * rate ) As new_value
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (
           PARTITION BY snno, value
           ORDER BY t_time DESC
         ) AS rn
  FROM   table_name t
)
WHERE rn = 1
GROUP BY snno

Полученные результаты:

| SNNO |         NEW_VALUE |
|------|-------------------|
|  ABC |               250 |
|  XYZ | 633.3333333333334 |

Вы можете использовать аналитическую функцию (row_number()) для достижения результата

SQL> WITH cte_table(Snno, t_time, Value, rate) AS (
  2    SELECT 'ABC', to_date('2018-05-17 08:00:00', 'YYYY-MM-DD HH24:MI:SS'), 100.00, 3 FROM DUAL UNION ALL
  3    SELECT 'ABC', to_date('2018-05-17 22:00:00', 'YYYY-MM-DD HH24:MI:SS'), 200.00, 1 FROM DUAL UNION ALL
  4    SELECT 'ABC', to_date('2018-05-16 08:00:00', 'YYYY-MM-DD HH24:MI:SS'), 100.00, 1 FROM DUAL UNION ALL
  5    SELECT 'XYZ', to_date('2018-05-14 01:00:00', 'YYYY-MM-DD HH24:MI:SS'), 700.00, 1 FROM DUAL UNION ALL
  6    SELECT 'XYZ', to_date('2018-05-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS'), 500.00, 2 FROM DUAL UNION ALL
  7    SELECT 'XYZ', to_date('2018-05-15 13:00:00', 'YYYY-MM-DD HH24:MI:SS'), 100.00, 2 FROM DUAL),
  8    --------------------------------
  9    -- End of data preparation
 10    --------------------------------
 11  rn_table AS (
 12    SELECT t.*, row_number() OVER (PARTITION BY TRUNC(t_time) ORDER BY t_time DESC) AS rn
 13      FROM cte_table t)
 14  SELECT snno,
 15         AVG(VALUE * rate) new_value
 16    FROM rn_table
 17   WHERE rn = 1
 18   GROUP BY snno;

Выход:

SNNO  NEW_VALUE
---- ----------
ABC         150
XYZ         450