 Re: Collapse date records into groups -
06-12-2011
, 11:00 PM
This showed lower cost than Sample 3.
Sample 4:
------------------------------ Commands Entered
------------------------------
WITH
sample_data(id , cnt , date_col) AS (
SELECT id
, SMALLINT(cnt)
, DATE(date_col)
FROM (
VALUES
(9000000 , 3 , '10/25/2002')
, (9000000 , 3 , '07/22/2005')
, (9000000 , 3 , '02/15/2008')
....
....
, (9009305 , 3 , '09/07/2001')
, (9009305 , 3 , '10/25/2002')
, (9009305 , 3 , '02/15/2008')
, (9009306 , 3 , '09/07/2001')
, (9009306 , 3 , '02/15/2008') -- change order of rows
, (9009306 , 3 , '10/25/2002') -- change order of rows
, (9009307 , 3 , '09/07/2001')
, (9009307 , 3 , '10/25/2002')
, (9009307 , 3 , '02/15/2008')
) s(id , cnt , date_col)
)
SELECT DISTINCT
DENSE_RANK()
OVER(ORDER BY
LISTAGG( CHAR(date_col) )
-- WITHIN GROUP(ORDER BY date_col)
OVER(PARTITION BY id
ORDER BY date_col
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING)
) AS group_id
, cnt
, date_col
FROM sample_data
ORDER BY
group_id
;
------------------------------------------------------------------------------
GROUP_ID CNT DATE_COL
-------------------- ------ ----------
1 3 2001-09-07
1 3 2002-10-25
1 3 2008-02-15
2 3 2001-09-07
2 3 2005-02-10
2 3 2008-02-15
3 3 2001-09-07
3 3 2005-05-09
3 3 2008-02-15
4 3 2002-10-25
4 3 2004-08-04
4 3 2008-02-15
5 3 2002-10-25
5 3 2005-07-22
5 3 2008-02-15
6 3 2008-02-15
6 3 2009-06-08
6 3 2010-06-15
7 3 2008-02-15
7 3 2010-04-08
7 3 2010-06-15
21 record(s) selected.
 |
Linear Mode
