Home » SQL & PL/SQL » SQL & PL/SQL » problem in analytic query (10.2.0.1)
problem in analytic query [message #424294] Thu, 01 October 2009 06:19 Go to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
I have to add all values M, that are distinct in TID for a given SID.

The first example works fine. The inner subquery finds the rows with rownum=1 and the outer query adds the values M. But it needs the subquery and the real table has millions of rows...

The second example eliminates the subquery, with analytic FIRST and adds the only first rows (TID,SID) too. But it gives the wrong result Shocked .

Is it possible to add the M values for DISTINCT TID for each SID values without subquery ?

--1. with suquery and GROUP BY, gives correct result
WITH data AS
(
  SELECT 1 nr, 1 sid, 101 tid,  1 m FROM dual UNION ALL
  SELECT 2   , 1    , 102    ,  2   FROM dual UNION ALL
  SELECT 3   , 1    , 101    ,  1   FROM dual UNION ALL
  SELECT 4   , 1    , 102    ,  2   FROM dual UNION ALL
  SELECT 5   , 1    , 101    ,  1   FROM dual UNION ALL
  SELECT 6   , 2    , 201    ,  1   FROM dual UNION ALL
  SELECT 7   , 2    , 202    ,  2   FROM dual UNION ALL
  SELECT 8   , 2    , 203    ,  3   FROM dual UNION ALL
  SELECT 9   , 2    , 201    ,  1   FROM dual
)
SELECT sid, sum(d_m) sum_m  
  FROM 
  (
  SELECT sid, 
         decode(row_number() over (partition by sid, tid order by sid, tid),1,m,NULL) d_m
    FROM data  
  )
  GROUP BY sid;

SID	SUM_M
---------
 1      3
 2      6

--2. with analytic function, gives wrong result
WITH data AS
(
  SELECT 1 nr, 1 sid, 101 tid,  1 m FROM dual UNION ALL
  SELECT 2   , 1    , 102    ,  2   FROM dual UNION ALL
  SELECT 3   , 1    , 101    ,  1   FROM dual UNION ALL
  SELECT 4   , 1    , 102    ,  2   FROM dual UNION ALL
  SELECT 5   , 1    , 101    ,  1   FROM dual UNION ALL
  SELECT 6   , 2    , 201    ,  1   FROM dual UNION ALL
  SELECT 7   , 2    , 202    ,  2   FROM dual UNION ALL
  SELECT 8   , 2    , 203    ,  3   FROM dual UNION ALL
  SELECT 9   , 2    , 201    ,  1   FROM dual
)
   SELECT sid, 
          SUM(m) KEEP (DENSE_RANK FIRST ORDER BY sid, tid) sum_m
     FROM data
 GROUP BY sid;  

SID	SUM_M
---------
 1      3
 2      2


Re: problem in analytic query [message #424296 is a reply to message #424294] Thu, 01 October 2009 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> WITH data AS
  2  (
  3    SELECT 1 nr, 1 sid, 101 tid,  1 m FROM dual UNION ALL
  4    SELECT 2   , 1    , 102    ,  2   FROM dual UNION ALL
  5    SELECT 3   , 1    , 101    ,  1   FROM dual UNION ALL
  6    SELECT 4   , 1    , 102    ,  2   FROM dual UNION ALL
  7    SELECT 5   , 1    , 101    ,  1   FROM dual UNION ALL
  8    SELECT 6   , 2    , 201    ,  1   FROM dual UNION ALL
  9    SELECT 7   , 2    , 202    ,  2   FROM dual UNION ALL
 10    SELECT 8   , 2    , 203    ,  3   FROM dual UNION ALL
 11    SELECT 9   , 2    , 201    ,  1   FROM dual
 12  ),
 13  d2 as (select distinct sid, tid, m from data)
 14  select sid, sum(m) from d2 group by sid
 15  /
       SID     SUM(M)
---------- ----------
         1          3
         2          6

2 rows selected.

Regards
Michel
Re: problem in analytic query [message #424299 is a reply to message #424296] Thu, 01 October 2009 06:35 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
Thanks @Micheal - but so I have a subquery again. Is it possible without query the (large) table twice?

[Updated on: Thu, 01 October 2009 06:41]

Report message to a moderator

Re: problem in analytic query [message #424303 is a reply to message #424299] Thu, 01 October 2009 07:08 Go to previous message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't query the table twice!
But you can't do it another way as you have bad data. You have a model issue you should fix instead of trying to speed up the query.

Regards
Michel
Previous Topic: long query runing
Next Topic: Can this be possible through sql ?
Goto Forum:
  


Current Time: Mon Sep 26 12:50:27 CDT 2016

Total time taken to generate the page: 0.07246 seconds