--уровень 3 - вычисляем итого оборот за день / остаток на конец дня
          select account_id, 
          trunc(c_date) c_date, 
          sum(C_TURNOVER_DBT) C_TURNOVER_DBT, 
          sum(C_TURNOVER_DBT_LCL) C_TURNOVER_DBT_LCL,
          sum(C_TURNOVER_CRT) C_TURNOVER_CRT, 
          sum(C_TURNOVER_CRT_LCL) C_TURNOVER_CRT_LCL,
          sum(C_BALANCE) C_BALANCE, 
          sum(C_BALANCE_LCL) C_BALANCE_LCL
          from (
              select  r2.*,
              --уровень 2 - для последней строки вычисляем баланс
              --баланс считаем только для последней записи во избежание размножения при SUM() на уровне выше:
               case when rn = 1 then nvl(C_START_SUM,0) +     direction * nvl(C_SUMMA,0)     end as C_BALANCE,
               case when rn = 1 then nvl(C_START_SUM_NAT,0) + direction * nvl(C_SUMMA_NAT,0) end as C_BALANCE_LCL
                    from (
                    select /*+ full(r) full(r.t) full(r.t.DA) full(r.t.DC) PARALLEL(16) */
                           r.*
                           --уровень 1 - нумеруем строки
                          ,row_number() over (partition by r.account_id, trunc(r.c_date) order by r.c_date desc, r.id desc) as rn --последняя запись на ключе Счет+Опердень
                           case when C_DT = '1' then -1 else 1 end direction --множитель для остатка 
                    from recs r --полупроводки
                    where r.c_date >= date '2015-01-04' and r.c_date <date '2015-01-04' +1 
                    ) r2
              ) r3
          group by account_id,trunc(c_date)

CREATE TABLE x (
  dept  NUMBER,
  name  VARCHAR2(20),
  sal   NUMBER
);

BEGIN
   FOR i IN 1..1000000 LOOP
      INSERT INTO x VALUES (
         MOD(i, 1000),
         dbms_random.string('A', 20),
         TRUNC(dbms_random.value(10000, 1000000))
      );
   END LOOP;

   COMMIT;
END;
/

set autotrace traceonly
set timing on

SELECT dept, name, sal
FROM   (
        SELECT dept, name, sal,
               row_number() OVER (PARTITION BY dept ORDER BY sal DESC) rn
        FROM   x
       )
WHERE  rn = 1;

1000 rows selected.

Elapsed: 00:00:06.04

Execution Plan
----------------------------------------------------------
Plan hash value: 4121446221

-----------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |   989K|    48M|       | 10876   (1)| 00:02:11 |
|*  1 |  VIEW                    |      |   989K|    48M|       | 10876   (1)| 00:02:11 |
|*  2 |   WINDOW SORT PUSHED RANK|      |   989K|    35M|    90M| 10876   (1)| 00:02:11 |
|   3 |    TABLE ACCESS FULL     | X    |   989K|    35M|       |  1079   (2)| 00:00:13 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "DEPT" ORDER BY
              INTERNAL_FUNCTION("SAL") DESC )<=1)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4850  consistent gets
          0  physical reads
          0  redo size
      40067  bytes sent via SQL*Net to client
       1111  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed


SELECT dept, 
       MAX(name) KEEP (DENSE_RANK FIRST ORDER BY sal DESC) name,
       MAX(sal) sal
FROM   x
GROUP  BY dept;

1000 rows selected.

Elapsed: 00:00:01.09

Execution Plan
----------------------------------------------------------
Plan hash value: 2477198225

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   989K|    35M|  1150   (8)| 00:00:14 |
|   1 |  SORT GROUP BY     |      |   989K|    35M|  1150   (8)| 00:00:14 |
|   2 |   TABLE ACCESS FULL| X    |   989K|    35M|  1079   (2)| 00:00:13 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4850  consistent gets
          0  physical reads
          0  redo size
      40067  bytes sent via SQL*Net to client
       1111  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed

set autotrace off
set timing off

SELECT dept, name, sal
FROM   ( SELECT dept, name, sal, row_number() OVER (PARTITION BY dept ORDER BY sal DESC) rn FROM   x) WHERE  rn = 1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       68      1.32       1.61         26        500          4        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       70      1.33       1.62         26        501          4        1000

Rows     Row Source Operation
-------  ---------------------------------------------------
   1000  VIEW  (cr=500 pr=26 pw=26 time=1569382 us)
   5752   WINDOW SORT PUSHED RANK (cr=500 pr=26 pw=26 time=1688642 us)
 100000    TABLE ACCESS FULL X (cr=500 pr=0 pw=0 time=2899972 us)
********************************************************************************
SELECT dept, MAX(name) KEEP (DENSE_RANK FIRST ORDER BY sal DESC) name, MAX(sal) sal FROM   x GROUP  BY dept

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       68      0.69       0.89          0        500          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       70      0.70       0.91          0        501          0        1000

Rows     Row Source Operation
-------  ---------------------------------------------------
   1000  SORT GROUP BY (cr=500 pr=0 pw=0 time=883211 us)
 100000   TABLE ACCESS FULL X (cr=500 pr=0 pw=0 time=2499976 us)