Dive into Oracle : Data Grouping and Merging

In this post we are going to discuss about commonly used Oracle database queries for manipulate data mainly grouping and merging. If the database structure is normalized and when it comes to visualization of data, it is required to group data based on their common characteristics. Similarly in most of the database systems including Oracle, Merging is the functionality provided in order to migrate data between two databases, schema or tables.

Let's create a simple database structure as follows. We can use a tool like SQL Fiddle to run queries and see how it works. Make sure you have selected Oracle 11g R2 as DB type before running any queries.

CREATE TABLE SEMISTER_GPA(
    STUDENT_ID VARCHAR2(8),
    SEMISTER_ID VARCHAR2(4),
    GPA NUMBER(5,4),
    PRIMARY KEY (STUDENT_ID,SEMISTER_ID)
);

Adding some values to above tables.

INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000001','L1S1',3.5245);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000002','L1S1',3.6100);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000003','L1S1',3.0589);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000001','L1S2',3.7241);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000002','L1S3',3.8180);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000003','L1S2',3.2589);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000002','L1S4',3.8675);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000002','L2S1',3.2369);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000003','L1S4',3.6523);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000001','L1S4',3.2675);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000003','L2S1',3.4389);
INSERT INTO SEMISTER_GPA(STUDENT_ID,SEMISTER_ID,GPA) VALUES ('ST000003','L2S2',3.0523);

See in SQL Fiddle.

01. Simple grouping: Get the no. of semesters each student has completed.

SELECT STUDENT_ID,COUNT(*) FROM SEMISTER_GPA GROUP BY STUDENT_ID;

02. DENSE_RANK() and PARTITION BY

'PARTITION BY' is used partition a table into different segments which makes easier to manipulate each segment individually. Here 'SEMISTER_GPA' table can be partition by STUDENT_ID. In order to get the row which has the highest 'GPA' for each student, each segment is ordered by 'GPA' in descending order. Then the row which has the DENSE_RANK()=1 gives the expected result.

    SELECT DT.STUDENT_ID,
           DT.SEMISTER_ID,
           DT.GPA
    FROM   (SELECT STUDENT_ID,
                   SEMISTER_ID,
                   GPA,
                   DENSE_RANK()
                     OVER (
                       PARTITION BY STUDENT_ID
                       ORDER BY GPA DESC) RNK
            FROM   SEMISTER_GPA) DT
    WHERE  ( DT.RNK = 1 )

03. LISTAGG () function

This function gives a single row, concatenated using specified column within each group ordered. To get the list of semesters each student has completed, we can group table using STUDENT_ID order each group by GPA and concatenate column values using above function.

    SELECT STUDENT_ID,
           LISTAGG(SEMISTER_ID, ',')
             WITHIN GROUP (ORDER BY SEMISTER_ID) AS SEMISTER_LIST
    FROM   SEMISTER_GPA
    GROUP  BY STUDENT_ID

Below mentioned is another sample data set. Timezone data is stored in two tables  TIME_ZONE and TIMEZONE_DESCRIPTIONS. In order to migrate data into a single denormalized table 'MERGE INTO .. USING.. ' statement can be used. Merging can handle insert or update unconditionally without knowing it's presence in target table. This is a more flexible way of moving large amounts of data extracted from one or more tables. See in SQL Fiddle.

CREATE TABLE TIME_ZONE
  (
     TIMEZONE_ID     NUMBER(3) NOT NULL,
     TIMEZONE_OFFSET VARCHAR2(10),
     MODIFIED_DATE   DATE DEFAULT SYSDATE NOT NULL,
     PRIMARY KEY (TIMEZONE_ID)
  );

CREATE TABLE TIMEZONE_DESCRIPTIONS
  (
     TIMEZONE_ID   NUMBER(3) NOT NULL,
     LANGUAGE_ID   VARCHAR2(2) DEFAULT 'EN' NOT NULL,
     DESCRIPTION   NVARCHAR2(100),
     MODIFIED_DATE DATE DEFAULT SYSDATE NOT NULL,
     PRIMARY KEY (TIMEZONE_ID, LANGUAGE_ID)
  );

Insert into TIME_ZONE (TIMEZONE_ID,TIMEZONE_OFFSET,MODIFIED_DATE) values (1,'+0300',SYSDATE);
Insert into TIME_ZONE (TIMEZONE_ID,TIMEZONE_OFFSET,MODIFIED_DATE) values (2,'+0200',SYSDATE);
Insert into TIME_ZONE (TIMEZONE_ID,TIMEZONE_OFFSET,MODIFIED_DATE) values (3,'+0400',SYSDATE);
Insert into TIME_ZONE (TIMEZONE_ID,TIMEZONE_OFFSET,MODIFIED_DATE) values (5,'-0400',SYSDATE);
Insert into TIME_ZONE (TIMEZONE_ID,TIMEZONE_OFFSET,MODIFIED_DATE) values (6,'+0400',SYSDATE);

Insert into TIMEZONE_DESCRIPTIONS (TIMEZONE_ID,LANGUAGE_ID,DESCRIPTION,MODIFIED_DATE) values (1,'EN','Saudi Arabia',SYSDATE);
Insert into TIMEZONE_DESCRIPTIONS (TIMEZONE_ID,LANGUAGE_ID,DESCRIPTION,MODIFIED_DATE) values (2,'EN','Egypt',SYSDATE);
Insert into TIMEZONE_DESCRIPTIONS (TIMEZONE_ID,LANGUAGE_ID,DESCRIPTION,MODIFIED_DATE) values (3,'EN','UAE',SYSDATE);
Insert into TIMEZONE_DESCRIPTIONS (TIMEZONE_ID,LANGUAGE_ID,DESCRIPTION,MODIFIED_DATE) values (5,'EN','USA',SYSDATE);
Insert into TIMEZONE_DESCRIPTIONS (TIMEZONE_ID,LANGUAGE_ID,DESCRIPTION,MODIFIED_DATE) values (6,'EN','Oman',SYSDATE);

Insert into TIMEZONE_DESCRIPTIONS (TIMEZONE_ID,LANGUAGE_ID,DESCRIPTION,MODIFIED_DATE) values (1,'FR','Arabie Saoudite',SYSDATE);
Insert into TIMEZONE_DESCRIPTIONS (TIMEZONE_ID,LANGUAGE_ID,DESCRIPTION,MODIFIED_DATE) values (2,'FR','Egypte',SYSDATE);
Insert into TIMEZONE_DESCRIPTIONS (TIMEZONE_ID,LANGUAGE_ID,DESCRIPTION,MODIFIED_DATE) values (3,'FR','Émirats arabes unis',SYSDATE);
Insert into TIMEZONE_DESCRIPTIONS (TIMEZONE_ID,LANGUAGE_ID,DESCRIPTION,MODIFIED_DATE) values (5,'FR','USA',SYSDATE);
Insert into TIMEZONE_DESCRIPTIONS (TIMEZONE_ID,LANGUAGE_ID,DESCRIPTION,MODIFIED_DATE) values (6,'FR','Oman',SYSDATE);

Denormalized table structure.

CREATE TABLE TIME_ZONE_MASTER (
    TIMEZONE_ID NUMBER(3) NOT NULL,
    TIMEZONE_OFFSET VARCHAR2(10),
    DESCRIPTION NVARCHAR2(100),
    MODIFIED_DATE DATE DEFAULT SYSDATE  NOT NULL,
    PRIMARY KEY (TIMEZONE_ID)
);

Select data set from TIMEZONE_DESCRIPTIONS table to join with TIME_ZONE table. In the target table, language wise descriptions will be stored in the same row according to a predefined format.

    SELECT X.TIMEZONE_ID,
           MAX(X.MODIFIED_DATE) AS MODIFIED_DATE,
           'EN:'
           || MAX(DECODE(X.LANGUAGE_ID, 'EN', X.DESCRIPTION))
           || ',FR:'
           || MAX(DECODE(X.LANGUAGE_ID, 'FR', X.DESCRIPTION)) TIME_ZONE_DESC
    FROM   (SELECT TZD.TIMEZONE_ID,
                   TZD.LANGUAGE_ID,
                   TZD.DESCRIPTION,
                   TZD.MODIFIED_DATE
            FROM   TIMEZONE_DESCRIPTIONS TZD) X
    GROUP  BY X.TIMEZONE_ID;

To get the full select statement, join with TIME_ZONE table using TIMEZONE_ID.

SELECT tz.TIMEZONE_ID,
       tz.TIMEZONE_OFFSET,
       v.TIME_ZONE_DESC,
       GREATEST(v.MODIFIED_DATE, tz.MODIFIED_DATE) AS MODIFIED_DATE
FROM   TIME_ZONE tz
       LEFT OUTER JOIN (SELECT x.TIMEZONE_ID,
                               MAX(x.MODIFIED_DATE) AS MODIFIED_DATE,
                               'EN:'
                               || MAX(DECODE(x.LANGUAGE_ID, 'EN', x.DESCRIPTION))
                               || ',FR:'
                               || MAX(DECODE(x.LANGUAGE_ID, 'FR', x.DESCRIPTION)) TIME_ZONE_DESC
                        FROM   (SELECT tzd.TIMEZONE_ID,
                                       tzd.LANGUAGE_ID,
                                       tzd.DESCRIPTION,
                                       tzd.MODIFIED_DATE
                                FROM   TIMEZONE_DESCRIPTIONS tzd) x
                        GROUP  BY x.TIMEZONE_ID) v
                    ON ( tz.TIMEZONE_ID = v.TIMEZONE_ID );

Merge statement syntax,

MERGE INTO TARET_TABLE USING
      (SELECT
    )ON (JOIN)
WHEN MATCHED THEN UPDATE
  SET
WHEN NOT MATCHED THEN
  INSERT
    ()
    VALUES
    ();

Finally, we can create the full merge statement to update TIME_ZONE_MASTER as follows.

MERGE INTO TIME_ZONE_MASTER t
USING (SELECT tz.TIMEZONE_ID,
              tz.TIMEZONE_OFFSET,
              v.TIME_ZONE_DESC,
              GREATEST(v.MODIFIED_DATE, tz.MODIFIED_DATE) AS MODIFIED_DATE
       FROM   TIME_ZONE tz
              LEFT OUTER JOIN (SELECT x.TIMEZONE_ID,
                                      MAX(x.MODIFIED_DATE)
                                      AS
                                                           MODIFIED_DATE,
                                      'EN:'
                                      || MAX(DECODE(x.LANGUAGE_ID, 'EN',
                                             x.DESCRIPTION))
                                      || ',FR:'
                                      || MAX(DECODE(x.LANGUAGE_ID, 'FR',
                                             x.DESCRIPTION))
                                                           TIME_ZONE_DESC
                               FROM   (SELECT tzd.TIMEZONE_ID,
                                              tzd.LANGUAGE_ID,
                                              tzd.DESCRIPTION,
                                              tzd.MODIFIED_DATE
                                       FROM   TIMEZONE_DESCRIPTIONS tzd) x
                               GROUP  BY x.TIMEZONE_ID) v
                           ON ( tz.TIMEZONE_ID = v.TIMEZONE_ID )) TD
ON (t.TIMEZONE_ID = TD.TIMEZONE_ID)
WHEN MATCHED THEN
  UPDATE SET t.DESCRIPTION = TD.TIME_ZONE_DESC,
             t.TIMEZONE_OFFSET = TD.TIMEZONE_OFFSET,
             t.MODIFIED_DATE = TD.MODIFIED_DATE
WHEN NOT MATCHED THEN
  INSERT ( TIMEZONE_ID,
           TIMEZONE_OFFSET,
           DESCRIPTION,
           MODIFIED_DATE )
  VALUES ( TD.TIMEZONE_ID,
           TD.TIMEZONE_OFFSET,
           TD.TIME_ZONE_DESC,
           TD.MODIFIED_DATE );

Final outcome would be like this.

Thoughts are welcome...

Http Session creation and Destroy events...

HttpSession is one of the commonly used way of identifying users across multiple requests between clients and servers. In order to provide various services such as authentication, authorization etc. for users and to store user information, logging and audit purposes, it is required to keep track of newly created sessions, active sessions and session destroy events. HttpSessionListener interface in servlet API provides methods to receive session events for its implementation classes published by the servlet container.

Lets look at a sample class 'WebSessionListener' which implements "HttpSessionListener" interface.

import javax.servlet.http.HttpSessionEvent;
import javax.servlet.http.HttpSessionListener;

public class WebSessionListener implements HttpSessionListener {
    
    //Notification that a session was created.
    @Override
    public void sessionCreated(HttpSessionEvent httpSessionCreatedEvent) {
    } 
    
    //Notification that a session is about to be invalidated.
    @Override
    public void sessionDestroyed(HttpSessionEvent httpSessionDestroyedEvent) {
    }

}

To receive notification events, the implementation class must be configured, commonly referred as registered  in the deployment descriptor (web.xml)  of the web application as follows.

        
            com.araTechBlog.sample.listeners.WebSessionListener
        

Any change in active sessions, i.e. Session creation, Session Timeout etc. can be monitored and necessary actions can be performed depending on the requirements.  HttpSession Object can be accessed via session event object which represents event notifications for changes to sessions within a web application.

HttpSession httpSession = httpSessionEvent.getSession();

Both Session invalidation and session timeout(expiry) are notified via same sessionDestroyed event and can't be distinguished using this method.

If you use Spring Security, application context can be accessed using 'context.getBean()' method as follows inside the listner.

 WebApplicationContext context = ContextLoader.getCurrentWebApplicationContext();
 CustomUserBean user= (CustomUserBean) context.getBean("customUser");

In a Spring environment. best practice is to use 'ApplicationListener' interface provided by the spring framework to receive session events.

In order to use ApplicationListner as a session event notifier,

You need to register 'HttpSessionEventPublisher' in the web.xml, which is the event publisher of the spring framework. If you look at the implementation of 'HttpSessionEventPublisher', you will see that it also implements the 'HttpSessionListener' and publish the session events to the Spring Root WebApplicationContext receieved from the servlet container.

  
       org.springframework.security.web.session.HttpSessionEventPublisher
  

Define you bean in the security.xml

Implement 'ApplicationListener' in your implementation.

import org.springframework.context.ApplicationEvent;
import org.springframework.context.ApplicationListener;
import org.springframework.security.web.session.HttpSessionCreatedEvent;
import org.springframework.security.web.session.HttpSessionDestroyedEvent;
import javax.servlet.http.HttpSession;
import org.apache.log4j.Logger

public class WebSessionListener implements ApplicationListener<ApplicationEvent> {
    
    private static final Logger LOG = Logger.getLogger(WebSessionListener.class);

    @Override
    public void onApplicationEvent(ApplicationEvent applicationEvent) {
       
        if(applicationEvent instanceof HttpSessionCreatedEvent){ //If event is a session created event

           HttpSession httpSession = httpSessionDestroyedEvent.getSession(); //get session object
           String sessionId = httpSession.getId(); //get session id
           ....
           persistSessionData(sessionId); //save session data to DB
           LOG.debug(" Session is invalidated |SESSION_ID :" + sessionId ); //log data
        
        }else if(applicationEvent instanceof HttpSessionDestroyedEvent){ //If event is a session destroy event
           ...
        }else{
           ...
        }
    }
}

Thoughts are welcome...