Need OLAP-type Ordering
in [DB2]
|
From: esmith2112 on 23 Nov 2009 14:33 Given sorted data that looks like this: COL1 COL2 COL3 ----------- ----------- ---------- 1 2 11/16/2009 1 2 11/17/2009 2 300 11/18/2009 1 300 11/19/2009 1 2 11/20/2009 I would like to find an OLAP-ordering or grouping function that would group things into new grouping each time the value of COL1 changed. The resultant output would look like this: COL1 COL2 COL3 GROUP_ID ----------- ----------- ---------- ---------------- 1 2 11/16/2009 1 1 2 11/17/2009 1 23 300 11/18/2009 2 1 300 11/19/2009 3 1 2 11/20/2009 3 Is this possible? Running DB2 9.5 AIX 5.3 Thanks, Evan
From: jefftyzzer on 23 Nov 2009 15:16 On Nov 23, 11:33 am, esmith2112 <esmith2...(a)gmail.com> wrote: > Given sorted data that looks like this: > > COL1 COL2 COL3 > ----------- ----------- ---------- > 1 2 11/16/2009 > 1 2 11/17/2009 > 2 300 11/18/2009 > 1 300 11/19/2009 > 1 2 11/20/2009 > > I would like to find an OLAP-ordering or grouping function that would > group things into new grouping each time the value of COL1 changed. > The resultant output would look like this: > > COL1 COL2 COL3 GROUP_ID > ----------- ----------- ---------- ---------------- > 1 2 11/16/2009 1 > 1 2 11/17/2009 1 > 23 300 11/18/2009 2 > 1 300 11/19/2009 3 > 1 2 11/20/2009 3 > > Is this possible? > > Running DB2 9.5 AIX 5.3 > > Thanks, > Evan Evan, What are the grouping columns? Also, are you looking for a trigger- based solution, or just a query that would merely reflect the current data? --Jeff
From: esmith2112 on 23 Nov 2009 16:16 > Evan, > > What are the grouping columns? Also, are you looking for a trigger- > based solution, or just a query that would merely reflect the current > data? > > --Jeff The data from the first listing above is the output from another complicated business logic query it's been sorted by all three columns. The second listing (the 23 is actually a typo--should have been 2) is what is what my target results would ideally be. The only thing I have to go on is the value from COL1. Humanly I can see that rows one and two share the same value. They belong in the first group. The value row three is different from the previous two rows. Therefore it would be next logical grouping. Rows three and four share the same value and represent a change from row three. However, they do share the same value as rows one and two, but should NOT be grouped with those rows. I know I could write procedural code to iterate through the result set, summarizing when I see the row change. I was just hoping that there might be a construct in SQL using the OLAP functions that might be able to take the pain and need for external programming away from the equation. Evan
From: jefftyzzer on 23 Nov 2009 17:10 On Nov 23, 1:16 pm, esmith2112 <esmith2...(a)gmail.com> wrote: > > Evan, > > > What are the grouping columns? Also, are you looking for a trigger- > > based solution, or just a query that would merely reflect the current > > data? > > > --Jeff > > The data from the first listing above is the output from another > complicated business logic query it's been sorted by all three > columns. The second listing (the 23 is actually a typo--should have > been 2) is what is what my target results would ideally be. The only > thing I have to go on is the value from COL1. > > Humanly I can see that rows one and two share the same value. They > belong in the first group. The value row three is different from the > previous two rows. Therefore it would be next logical grouping. Rows > three and four share the same value and represent a change from row > three. However, they do share the same value as rows one and two, but > should NOT be grouped with those rows. > > I know I could write procedural code to iterate through the result > set, summarizing when I see the row change. I was just hoping that > there might be a construct in SQL using the OLAP functions that might > be able to take the pain and need for external programming away from > the equation. > > Evan OK--so basically a break on COL1 when the current value differs from its predecessor.... I'll put my thinking cap on, but, meantime, based on a recent posting of inspired SQL legerdemain by Tonkuma, I'm thinking the solution to your problem will involve the LAG or LEAD function, so you might want to have a look at those (if you've not already). --Jeff
From: Tonkuma on 23 Nov 2009 20:04 Here is an example using OLAP functions. Another ways would be use of loop by Procedure statements or by Recursive CTE. I tried a way without nested table expression. But, it looks the spec. of current DB2 SQL that OLAP functions can't nest each other, then I used a nested table expression. ------------------------------ Commands Entered ------------------------------ WITH /************************************************** ***** Start of sample data ***** **************************************************/ sample_data(COL1, COL2, COL3) AS ( VALUES ( 1, 2, '11/16/2009') ,( 1, 2, '11/17/2009') ,( 2, 300, '11/18/2009') ,( 1, 300, '11/19/2009') ,( 1, 2, '11/20/2009') ) /************************************************** ***** End of sample data ***** **************************************************/ SELECT col1, col2, col3 , COUNT(col1_changed) OVER(ORDER BY col3, col1, col2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) + 1 AS group_id FROM (SELECT s.* , CASE WHEN col1 <> LAG(col1) OVER(ORDER BY col3, col1, col2) THEN 'Y' /* ELSE NULL */ END col1_changed FROM sample_data s ) q ORDER BY col3, col1, col2 ; ------------------------------------------------------------------------------ COL1 COL2 COL3 GROUP_ID ----------- ----------- ---------- ----------- 1 2 11/16/2009 1 1 2 11/17/2009 1 2 300 11/18/2009 2 1 300 11/19/2009 3 1 2 11/20/2009 3 5 record(s) selected.
|
Next
|
Last
Pages: 1 2 3 Prev: GET SNAPSHOT blows up monitor switches/returns incomplete data Next: SQL0902C on db2 connect |