Re: Left Outer Join, group by, and date-based queries

bbcrock_at_gmail.com schrieb:
> Thanks, but none of the examples address doing a fake outer join on a
> month. My half-assed result is:
>
> select Months.dt, count(t2.month) total
> from ( select to_char(to_date(level,'MM'),'YYYY Mon') AS dt from
> dual connect by level <= 12 ) Months,
> (select to_char(trunc(user_date, 'MM'), 'YYYY Mon') Month
> from userlog
> where event = 'LOGIN'
> ) t2
> where Months.dt = t2.month (+)
> group by rollup( Months.dt )
>
> however, I need to do this for month and year together, in other words,
> the above query filters out data only by month- not month/year. If
> anyone can point me in the right direction, that would be cool. my
> brain hurts.
>
>
> Don
>

I don't quite understand , what you mean by 'filter' - you query gives you aggregates for every month and for the whole year. Maybe you want to go deeper in the past than 1 year, in that case, you could consider this example ( you can customize the date interval in the WHERE clause , as well the depth of TIME_DIMENSION in CONNECT BY LEVEL claues) :

SQL> CREATE TABLE userlog(user_date DATE,event VARCHAR2(20));

Table created.

SQL> INSERT INTO userlog VALUES(SYSDATE -31,'LOGIN');

1 row created.

SQL> INSERT INTO userlog VALUES(SYSDATE -51,'LOGIN');

1 row created.

SQL> INSERT INTO userlog VALUES(SYSDATE -61,'LOGIN');

1 row created.

SQL> INSERT INTO userlog VALUES(SYSDATE -62,'LOGIN');

1 row created.

SQL> INSERT INTO userlog VALUES(SYSDATE -71,'LOGIN');

1 row created.

SQL> INSERT INTO userlog VALUES(SYSDATE -72,'LOGIN');

1 row created.

SQL> INSERT INTO userlog VALUES(SYSDATE -81,'LOGIN');

1 row created.

SQL> INSERT INTO userlog VALUES(SYSDATE -102,'LOGIN');

1 row created.

SQL> INSERT INTO userlog VALUES(SYSDATE -103,'LOGIN');

1 row created.

SQL> INSERT INTO userlog VALUES(SYSDATE -243,'LOGIN');

1 row created.

SQL> INSERT INTO userlog VALUES(SYSDATE -324,'LOGIN');

1 row created.

SQL> INSERT INTO userlog VALUES(SYSDATE -326,'LOGIN');

1 row created.

SQL> INSERT INTO userlog VALUES(SYSDATE -521,'LOGIN');

1 row created.

SQL> INSERT INTO userlog VALUES(SYSDATE -524,'LOGIN');

1 row created.

SQL> INSERT INTO userlog VALUES(SYSDATE -526,'LOGIN');

1 row created.

SQL> COMMIT; Commit complete.

SQL>
SQL> col date_dimension for a15
SQL> col aggregation_level for a25
SQL> col count_event for 99999
SQL> set pagesize 999
SQL> SELECT

   2
DECODE(GROUPING(DT.MONTH_ID),0,DT.MONTH_ID,1,TRUNC(DT.MONTH_ID,'YYYY')) DATE_DIMENSION,
   3 DECODE(GROUPING(DT.MONTH_ID),0,'Monthly',1,'Yearly') AGGREGATION_LEVEL,
   4 COUNT(EVENT) COUNT_EVENT
   5 FROM
   6 ( SELECT ADD_MONTHS(TRUNC(SYSDATE,'MM'),1-LEVEL) MONTH_ID    7 FROM DUAL
   8 CONNECT BY LEVEL <=50) DT
   9 LEFT OUTER JOIN
  10 (
  11 SELECT EVENT,
  12 TRUNC(USER_DATE,'MM') MONTH_ID
  13 FROM
  14 USERLOG
  15 WHERE EVENT='LOGIN'
  16 ) UL
  17 ON DT.MONTH_ID = UL.MONTH_ID
  18 WHERE DT.MONTH_ID BETWEEN DATE '2004-06-01' AND DATE '2006-01-01'   19 GROUP BY GROUPING SETS (DT.MONTH_ID,TRUNC(DT.MONTH_ID,'YYYY'))   20 ORDER BY DT.MONTH_ID DESC
  21 ;

DATE_DIMENSION AGGREGATION_LEVEL COUNT_EVENT

--------------- ------------------------- -----------
01.01.04        Yearly                              3
01.01.05        Yearly                             12
01.11.05        Monthly                             0
01.10.05        Monthly                             2
01.09.05        Monthly                             5
01.08.05        Monthly                             2
01.07.05        Monthly                             0
01.06.05        Monthly                             0
01.05.05        Monthly                             0
01.04.05        Monthly                             0
01.03.05        Monthly                             1
01.02.05        Monthly                             0
01.01.05        Monthly                             2
01.12.04        Monthly                             0
01.11.04        Monthly                             0
01.10.04        Monthly                             0
01.09.04        Monthly                             0
01.08.04        Monthly                             0
01.07.04        Monthly                             0
01.06.04        Monthly                             3

20 rows selected.

SQL> Best regards

Maxim Received on Tue Nov 29 2005 - 04:19:49 CST