Cheesy Code

When running an OLTP system you are dealing with timestamped transactions or messages which you may like to report on. Let’s say you want to check whether the message rates broken down by hour have been consistent over the last week. So let’s start with some sample message times generated randomly:

EXEC dbms_random.initialize(123);

CREATE GLOBAL TEMPORARY TABLE rnd_message(dte DATE)
ON COMMIT DELETE ROWS;

INSERT INTO rnd_message
SELECT dte FROM
    (
    SELECT TRUNC(SYSDATE)-7 + dbms_random.value(0,8) dte
      FROM dual
    CONNECT BY LEVEL <= 100
    );

which yields:

Now we could run a simple group by and then scroll up and down to see what is going on

SELECT TO_CHAR(dte,'hh24') hh, TO_CHAR(dte,'dd') dd, COUNT(*) cnt
  FROM rnd_message
 WHERE TRUNC(dte) >= TRUNC(SYSDATE-7)
GROUP BY TO_CHAR(dte,'hh24'), TO_CHAR(dte,'dd')
ORDER BY dd, hh;

but it is inconvenient, difficult to see patterns and the groups are sparse. Also the gaps are hard to spot:

It may well be that the lack of messages is more interesting than their presence. So to show the gaps more clearly we'll need to do an outer join against a generated sequence like this:

SELECT '01' dd, hr, SUM(msg.cnt) "0"
  FROM
(
SELECT * FROM
    (
    SELECT LEVEL-1 hr
    FROM dual
    CONNECT BY LEVEL <= 24
    )
) hrs,
(
SELECT TO_CHAR(dte,'hh24') hh, TO_CHAR(dte,'dd') dd, COUNT(*) cnt
  FROM rnd_message
 WHERE TRUNC(dte) >= TRUNC(SYSDATE-7)
GROUP BY TO_CHAR(dte,'hh24'), TO_CHAR(dte,'dd')
) msg
WHERE hrs.hr = msg.hh (+)
  AND '01' = msg.dd(+)
GROUP BY hr
ORDER BY hr;

so doing that just for today gives:

That's nice and the gaps are clearly visible but the patterns across days are still difficult to see. What we'd really like is a side-by-side matrix with rows showing hours and columns being days. To do that we need to convert rows into columns or pivot the data. Pivot is available in 11g but for older versions we can use a series of case statements to achieve the desired effect:

SELECT hr,
       SUM(CASE WHEN dd=TO_CHAR(SYSDATE-7, 'dd') THEN cnt ELSE NULL END) "-7",
       SUM(CASE WHEN dd=TO_CHAR(SYSDATE-6, 'dd') THEN cnt ELSE NULL END) "-6",
       SUM(CASE WHEN dd=TO_CHAR(SYSDATE-5, 'dd') THEN cnt ELSE NULL END) "-5",
       SUM(CASE WHEN dd=TO_CHAR(SYSDATE-4, 'dd') THEN cnt ELSE NULL END) "-4",
       SUM(CASE WHEN dd=TO_CHAR(SYSDATE-3, 'dd') THEN cnt ELSE NULL END) "-3",
       SUM(CASE WHEN dd=TO_CHAR(SYSDATE-2, 'dd') THEN cnt ELSE NULL END) "-2",
       SUM(CASE WHEN dd=TO_CHAR(SYSDATE-1, 'dd') THEN cnt ELSE NULL END) "-1",
       SUM(CASE WHEN dd=TO_CHAR(SYSDATE-0, 'dd') THEN cnt ELSE NULL END) "0"
  FROM
(
SELECT * FROM
    (
    SELECT LEVEL-1 hr
    FROM dual
    CONNECT BY LEVEL <= 24
    )
) hrs,
(
SELECT TO_CHAR(dte,'hh24') hh, TO_CHAR(dte,'dd') dd, COUNT(*) cnt
  FROM rnd_message
 WHERE TRUNC(dte) >= TRUNC(SYSDATE-7)
GROUP BY TO_CHAR(dte,'hh24'), TO_CHAR(dte,'dd')
) msg
WHERE hrs.hr = msg.hh (+)
GROUP BY hr
ORDER BY hr;

which yields a nice matrix showing the days laid out side by side:

So we’ve used an outer join against a generated CONNECT BY sequence and then CASE to PIVOT the result yielding a nice, easy to read, report on transactions for the week broken down by hour.