Re: Control break reporting using analytical functions
- From: Gints Plivna <gints.plivna@xxxxxxxxx>
- To: Rich.Jesse@xxxxxxxxxxxxxxxxx
- Date: Thu, 14 Jul 2005 11:58:04 +0300
Probably it could be achieved using less levels of included FROMs but
I leave that task of optimization to You :)))
SELECT
ASSEMBLY,
ROUTE,
WORKcenter,
opno,
runtimeSum,
setuptimeSum
FROM (
SELECT
ASSEMBLY,
ROUTE,
WORKcenter,
opno,
sum(runtime) OVER (PARTITION BY rn) runtimeSum,
sum(setuptime) OVER (PARTITION BY rn) setuptimeSum,
flag
FROM (
SELECT
ASSEMBLY,
ROUTE,
WORKcenter,
opno,
runtime,
setuptime,
max(rn) OVER (ORDER BY opno) rn,
flag
FROM (
select
ASSEMBLY,
ROUTE,
WORKcenter,
opno,
runtime,
setuptime,
case when prev_workcenter = WORKcenter THEN 0
ELSE 1 END flag,
case when prev_workcenter = WORKcenter THEN NULL
ELSE rn END rn
FROM (
select
ASSEMBLY,
ROUTE,
WORKcenter,
opno,
runtime,
setuptime,
lag(workcenter) OVER (ORDER BY opno) prev_workcenter,
row_number() OVER (ORDER BY opno) rn
from mytable
order by opno
)
)
)
)
WHERE flag = 1
/
ASSEMBLY RO WORK OPNO RUNTIMESUM SETUPTIMESUM
--------------- -- ---- ------- --------------- ---------------
33858 00 4003 0995 1,9 2,7
33858 00 4051 1010 0 ,1
33858 00 4091 1020 1,25 2,5
33858 00 4620 1030 0 0
33858 00 4052 1040 ,5 ,3
33858 00 4640 1050 2 0
33858 00 4003 1055 3,5 7
33858 00 4006 1070 1,5 2
BTW I think that your given example of sums is a bit wrong or
otherwise I haven't undestood you correctly.
Gints Plivna
On 7/13/05, Jesse, Rich <Rich.Jesse@xxxxxxxxxxxxxxxxx> wrote:
>
>
> Hey all,
>
>
>
> Using 9.2.0.5, we've got this table and data (don't ask about the
> datatypes):
>
>
>
> CREATE TABLE MYTABLE
> (
> ASSEMBLY CHAR(15),
> OPNO CHAR(7),
> ROUTE CHAR(2),
> WORKCENTER CHAR(4),
> RUNTIME FLOAT(126),
> SETUPTIME FLOAT(126)
> )/
>
> INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME, SETUPTIME
> ) VALUES (
> '33858 ', '0995 ', '00', '4003', 0, 2.5);
> INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME, SETUPTIME
> ) VALUES (
> '33858 ', '1000 ', '00', '4003', 1.25, 0);
> INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME, SETUPTIME
> ) VALUES (
> '33858 ', '1004 ', '00', '4003', 0, 0.2);
> INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME, SETUPTIME
> ) VALUES (
> '33858 ', '1005 ', '00', '4003', 0.65, 0);
> INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME, SETUPTIME
> ) VALUES (
> '33858 ', '1010 ', '00', '4051', 0, 0.1);
> INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME, SETUPTIME
> ) VALUES (
> '33858 ', '1020 ', '00', '4091', 1.25, 2.5);
> INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME, SETUPTIME
> ) VALUES (
> '33858 ', '1030 ', '00', '4620', 0, 0);
> INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME, SETUPTIME
> ) VALUES (
> '33858 ', '1040 ', '00', '4052', 0.5, 0.3);
> INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME, SETUPTIME
> ) VALUES (
> '33858 ', '1050 ', '00', '4640', 2, 0);
> INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME, SETUPTIME
> ) VALUES (
> '33858 ', '1055 ', '00', '4003', 0, 5);
> INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME, SETUPTIME
> ) VALUES (
> '33858 ', '1060 ', '00', '4003', 3, 0);
> INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME, SETUPTIME
> ) VALUES (
> '33858 ', '1064 ', '00', '4003', 0, 2);
> INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME, SETUPTIME
> ) VALUES (
> '33858 ', '1065 ', '00', '4003', 0.5, 0);
> INSERT INTO MYTABLE ( ASSEMBLY, OPNO, ROUTE, WORKCENTER, RUNTIME, SETUPTIME
> ) VALUES (
> '33858 ', '1070 ', '00', '4006', 1.5, 2);
> COMMIT;
>
>
>
> The user wants to see the workcenters this assembly travels through, but
> without duplicates within each occurrence of that group -- and order is
> important! So, instead of:
>
>
>
> ASSYPARTNO ROUTINGNO WORKCENTER
> 33858 00 4003
> 33858 00 4003 <- Duplicate
> 33858 00 4003 <- Duplicate
> 33858 00 4003 <- Duplicate
> 33858 00 4051
> 33858 00 4091
> 33858 00 4620
> 33858 00 4052
> 33858 00 4640
> 33858 00 4003 <- NOT a duplicate
> 33858 00 4003 <- Duplicate
> 33858 00 4003 <- Duplicate
> 33858 00 4003 <- Duplicate
> 33858 00 4006
>
>
>
> ...the user wants:
>
>
>
> ASSYPARTNO ROUTINGNO WORKCENTER SEQ
> 33858 00 4003 1
> 33858 00 4051 2
> 33858 00 4091 3
> 33858 00 4620 4
> 33858 00 4052 5
> 33858 00 4640 6
> 33858 00 4003 7
> 33858 00 4006 8
>
> We got that to work by using some fancy analytical functions:
>
> SELECT assembly, route, workcenter, ROWNUM AS "SEQ"
> FROM
> (
> SELECT DISTINCT assembly, route, workcenter,
> CASE WHEN LAG(workcenter) OVER (ORDER BY seqx) != workcenter
> THEN ROW_NUMBER() OVER (ORDER BY seqx)
> ELSE FIRST_VALUE(seqx) OVER (ORDER BY seqx) END AS
> "SEQ2"
> FROM
> (
> SELECT assembly, route, workcenter, ROWNUM AS "SEQX"
> FROM mytable
> ORDER BY opno
> )
> ORDER BY seq2
> )
>
>
>
> This works very well (at least in this case), but now we need to SUM the
> runtime and setuptime columns for each grouping. The user would like to
> see:
>
>
> ASSYPARTNO ROUTINGNO WORKCENTER SEQ RUNTIME SETUPTIME
> 33858 00 4003 1 1.9 2.7
> 33858 00 4051 2 0 0.1
> 33858 00 4091 3 1.25 2.5
> 33858 00 4620 4 0 0
> 33858 00 4052 5 0.5 0.3
> 33858 00 4640 6 3 7
> 33858 00 4003 7 0.5 0
> 33858 00 4006 8 1.5 2
>
>
>
> Note how the two groups of "4003" workcenters need to remain separate and in
> the correct order, since the assembly will physically be transported to
> these workcenters in this order.
>
>
>
> Using our CASE...FIRST_VALUE trick, if I could manage a "LAG(seqx,
> ROWNUM-FIRST, 1)" type clause, it would be what I'm looking for, but I
> haven't had enough SQueaL Lubricant (aka "Beer") to figure that one out yet.
>
>
>
> Sure, this is a simple procedural control break report, but there's got to
> be a way to get this output in a SQL statement, doesn't there?
>
>
>
> TIA,
> Rich
>
>
>
> Rich Jesse System/Database Administrator
> rich.jesse@xxxxxxxxxxxxxxxxx QuadTech, Sussex, WI USA
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
