Re: SUM totals by relative appearance in output

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Fri, 1 Jul 2011 08:52:47 -0700
Message-ID: <BANLkTinK-8QkFHFgM-UNi3RzHC6MEBPMwA_at_mail.gmail.com>



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_at_acxiom.com> 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_at_acxiom.com
> 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_at_gmail.com]
> Sent: Thursday, June 30, 2011 6:13 PM
> To: Herring Dave - dherri
> Cc: 'oracle-l_at_freelists.org' (oracle-l_at_freelists.org)
> 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_at_acxiom.com> 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_at_acxiom.com
> 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
Received on Fri Jul 01 2011 - 10:52:47 CDT

Original text of this message