RE: SUM totals by relative appearance in output

Just thought I'd share the final query, now that I've "mmoore'ed" it:

DEFINE DISKGROUP=&1
DEFINE DISK_NBR=&2

SELECT disk_nbr, filename, file_nbr, MIN(au_pos) first_au_pos, COUNT(*) cnt
  FROM (SELECT disk_nbr, au_pos, filename, file_nbr
             , MAX(new_filename_section) OVER (ORDER BY au_pos) max_rn
          FROM (SELECT disk_nbr, au_pos, filename, file_nbr
                     , CASE
                          WHEN LAG(filename, 1) OVER (ORDER BY au_pos) = 
filename THEN NULL
                          ELSE ROW_NUMBER() OVER (ORDER BY au_pos)
                       END new_filename_section
                  FROM (SELECT x.number_kfdat disk_nbr
                             , CASE
                                  WHEN aa.name IS NULL THEN DECODE(x.v_kfdat, 
'V', '<Internal>', 'F', '<Free>', '<Unknown>')
                                  ELSE aa.name
                               END filename
                             , x.fnum_kfdat file_nbr, x.aunum_kfdat au_pos
                          FROM x$kfdat x
                             , v$asm_alias aa
                         WHERE x.group_kfdat = &DISKGROUP
                           AND x.number_kfdat = &DISK_NBR
                           AND (    x.group_kfdat = aa.group_number(+)
                                AND x.fnum_kfdat = aa.file_number(+))
                       )
               )
       )
 GROUP BY disk_nbr, filename, file_nbr, max_rn
 ORDER BY first_au_pos;

DAVID HERRING
DBA
Acxiom Corporation
EML   dave.herring@xxxxxxxxxx
TEL    630.944.4762
MBL   630.430.5988 
1501 Opus Pl, Downers Grove, IL 60515, USA
WWW.ACXIOM.COM  
________________________________________
The information contained in this communication is confidential, is intended 
only for the use of the recipient named above, and may be legally privileged. 
If the reader of this message is not the intended recipient, you are hereby 
notified that any dissemination, distribution or copying of this communication 
is strictly prohibited. If you have received this communication in error, 
please resend this communication to the sender and delete the original message 
or any copy of it from your computer system. Thank you.

From: Michael Moore [mailto:michaeljmoore@xxxxxxxxx] 
Sent: Friday, July 01, 2011 10:53 AM
To: Herring Dave - dherri
Cc: 'oracle-l@xxxxxxxxxxxxx' (oracle-l@xxxxxxxxxxxxx)
Subject: Re: SUM totals by relative appearance in output

Andy's may be more concise than min. I didn't have much time. ;-) This is what 
I like to call the "group by change in value" problem.

Mike
On Fri, Jul 1, 2011 at 7:47 AM, Herring Dave - dherri <Dave.Herring@xxxxxxxxxx> 
wrote:

Excellent, Michael!!  Initial tests show this is what I'm looking for, based on 
a 10-row table.  Now onto 100K's per disk.  I haven't tested Andy's suggestion, 
but may get to it tomorrow. 

BTW Brandon, you were correct - a LAG function was needed in the query. :-)
DAVID HERRING
DBA
Acxiom Corporation
EML   dave.herring@xxxxxxxxxx
TEL    630.944.4762
MBL   630.430.5988
1501 Opus Pl, Downers Grove, IL 60515, USA
WWW.ACXIOM.COM 


________________________________________
The information contained in this communication is confidential, is intended 
only for the use of the recipient named above, and may be legally privileged. 
If the reader of this message is not the intended recipient, you are hereby 
notified that any dissemination, distribution or copying of this communication 
is strictly prohibited. If you have received this communication in error, 
please resend this communication to the sender and delete the original message 
or any copy of it from your computer system. Thank you.
From: Michael Moore [mailto:michaeljmoore@xxxxxxxxx]
Sent: Thursday, June 30, 2011 6:13 PM
To: Herring Dave - dherri
Cc: 'oracle-l@xxxxxxxxxxxxx' (oracle-l@xxxxxxxxxxxxx)
Subject: Re: SUM totals by relative appearance in output
 SELECT MIN (dt) imin,count(*) cnt,  st
            FROM (SELECT dt, st, MAX (rn) OVER (ORDER BY dt) max_rn
                    FROM (SELECT dt,
                                 st,
                                 CASE
                                    WHEN LAG (st, 1) OVER (ORDER BY dt) = st 
THEN NULL
                                    ELSE ROW_NUMBER () OVER (ORDER BY dt)
                                 END
                                    rn
                            FROM k2))
        GROUP BY st, max_rn
        ORDER BY 1;

where st is filename, dt is starting,

Mike


On Thu, Jun 30, 2011 at 3:11 PM, Herring Dave - dherri 
<Dave.Herring@xxxxxxxxxx> wrote:
Folks,

I'm having a bit of a mental block dealing with a query I'm trying to write. 
 What I'd like to do is for data in a table, ordered by some value when 
queried, to only display totals when rows are repeating by a value, but only 
doing this totalling by their relative position in the output.  (I'm sure 
there's a better way to say that, but right now even that's beyond my scope.)

Let's say I was trying to map out relative position of files in ASM by their AU 
position.  FILE1 could have 1-to-many consecutive AUs, then another file or 
free space, then FILE1 could repeat.  For example:

FILE NAME       AU POS
---------       ------
FILE1   1
FILE1   2
FILE2           3
FILE3           4
FILE3           5
FILE3           6
FILE1           7
       ...

For the above data I'd want to see:

FILE NAME       STARTING AU POS LENGTH
---------       --------------- ------
FILE1           1                       2
FILE2           3                       1
FILE3           4                       3
FILE1           7                       1
       ...

I'm convinced I can use an analytic function with the right windowing option to 
do this all in SQL, but so far I've failed miserably.  Obviously I could write 
PL/SQL to do this, but I've overflown DBMS_OUTPUT's capacity and ended up 
moving on to capturing output and manipulating it with perl.  But that seems 
like a waste because again, I'm convinced I can do this in SQL without forcing 
a fork onto something else.

Does any of this make sense and does anyone have a way to do this with SQL?

DAVID HERRING
DBA
Acxiom Corporation
EML   dave.herring@xxxxxxxxxx
TEL    630.944.4762
MBL   630.430.5988
1501 Opus Pl, Downers Grove, IL 60515, USA
WWW.ACXIOM.COM

The information contained in this communication is confidential, is intended 
only for the use of the recipient named above, and may be legally privileged. 
If the reader of this message is not the intended recipient, you are hereby 
notified that any dissemination, distribution or copying of this communication 
is strictly prohibited. If you have received this communication in error, 
please resend this communication to the sender and delete the original message 
or any copy of it from your computer system. Thank you.

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l


Other related posts: