Stored procedure or SQL to execute 1-2 times daily - Oracle

Question

I am rendering a report using a single huge oracle table having an emoji column (with possible values of 1, 2, 3, 4, 5). I am doing a live count(*) and percentage count per emoji for every report query currently. I have the option to generate the report for 7 days, 30 days, 60 days and 90 days.

My ask: - Run a stored proc/SQL to store the counts and percentage counts per emoji in a separate table 1-2 times daily. - Insert if new, update it existing. There will be 4 records inserted/updated per day, one each for frequency 7, 30, 60 and 90 days. - Run this via a scheduler at 6 am and/or 6 pm daily.

My schema

desc reports;

MESSAGE_ID NOT NULL NUMBER(10)    
USER_ID    NOT NULL VARCHAR2(10)  
EMOJI      NOT NULL NUMBER(2)     
MESSAGE             VARCHAR2(140) 
TS_CREATE  NOT NULL TIMESTAMP(0)  
TS_UPDATE  NOT NULL TIMESTAMP(0)

My current query from java program for 30 day and 60 day frequency:

select emoji, COUNT(1) Total_emoji_count, 
ROUND(RATIO_TO_REPORT(COUNT(1)) OVER() * 100,2) Total_emoji_percent
FROM reports where ts_create > sysdate - 30 GROUP BY emoji ORDER BY emoji;

select emoji, COUNT(1) Total_emoji_count, 
ROUND(RATIO_TO_REPORT(COUNT(1)) OVER() * 100,2) Total_emoji_percent
FROM reports where ts_create > sysdate - 60 GROUP BY emoji ORDER BY emoji;

This returns : enter image description here

However, I would like to have the rows displayed as columns for easy readability. Something like this inserted everyday.

enter image description here

I have tried the following SQL, I can get the counts but am unable to get the percentage(s) working. Any pointers?

SELECT SUM(DECODE(emoji, 1, count(1), 0)) "e1_count",
SUM(DECODE(emoji, 2, count(1), 0)) "e2_count",  
SUM(DECODE(emoji, 3, count(1), 0)) "e3_count",  
SUM(DECODE(emoji, 4, count(1), 0)) "e4_count",  
SUM(DECODE(emoji, 5, count(1), 0)) "e5_count"
FROM  reports where ts_create > trunc(sysdate) - 30
GROUP BY emoji ORDER BY emoji;

EDIT -- New variant

emoji_FREQ as (
select
trunc(sysdate),
f.FREQUENCY,
m.emoji,
COUNT(1) e_count,
RATIO_TO_REPORT(COUNT(1)) OVER() * 100 e_percent
FROM reports m, FREQUENCY_TBL f
where m.ts_create > trunc(sysdate) - f.FREQUENCY
GROUP BY FREQUENCY, emoji
) SELECT
trunc(sysdate),
FREQUENCY,
SUM(DECODE(emoji, 1, e_count, 0)) e1_count,
SUM(DECODE(emoji, 2, e_count, 0)) e2_count,
SUM(DECODE(emoji, 3, e_count, 0)) e3_count,
SUM(DECODE(emoji, 4, e_count, 0)) e4_count,
SUM(DECODE(emoji, 5, e_count, 0)) e5_count,
SUM(DECODE(emoji, 1, e_percent, 0)) e1_percent,
SUM(DECODE(emoji, 2, e_percent, 0)) e2_percent,
SUM(DECODE(emoji, 3, e_percent, 0)) e3_percent,
SUM(DECODE(emoji, 4, e_percent, 0)) e4_percent,
SUM(DECODE(emoji, 5, e_percent, 0)) e5_percent
FROM emoji_FREQ
GROUP BY FREQUENCY;

Are you looking for [PIVOT](http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#CHDFAFIE)? — Wernfried Domscheit, Aug 17 '17 at 05:59
Yes, PIVOT is fine, if simple DECODE is not possible. I will use this 'select' to insert 3 rows into another table later. — Shon, Aug 17 '17 at 11:38

MDP89 · Answer 1 · 2017-08-17T13:34:06.297

you ca either use a PIVOT query (https://www.techonthenet.com/oracle/pivot.php) or try something like this

SELECT log_message_date, 
       30 AS frequency, 
       e1_count, 
       Round(e1_count/total*100), 
       e2_count, 
       Round(e2_count/total*100), 
       e3_count, 
       Round(e3_count/total*100), 
       e4_count, 
       Round(e4_count/total*100), 
       e5_count, 
       Round(e5_count/total*100) 
FROM  ( 
                SELECT   SUM(Decode(emoji, 
                                    1, Count(1), 
                                    0)) "e1_count", 
                         SUM(Decode(emoji, 
                                    2, Count(1), 
                                    0)) "e2_count", 
                         SUM(Decode(emoji, 
                                    3, Count(1), 
                                    0)) "e3_count", 
                         SUM(Decode(emoji, 
                                    4, Count(1), 
                                    0)) "e4_count", 
                         SUM(Decode(emoji, 
                                    5, Count(1), 
                                    0)) "e5_count",
                         count(emoji) AS total, 
                         trunc(ts_create)                       AS log_message_date 
                FROM     reports 
                WHERE    ts_create > trunc(SYSDATE) - 30 
                GROUP BY trunc(ts_create))

I'm getting a ORA-00904: "E5_COUNT": invalid identifier 00904. 00000 - "%s: invalid identifier" for line "Round(e5_count/total*100)" — Shon, Aug 17 '17 at 11:38
I also tried a slightly different variant after creating a new table FREQUENCY_TBL with field named FREQUENCY records with values 7, 30, 60, 90....I was able to get the counts working but the percentages still isn't right....Editing original post with new query — Shon, Aug 17 '17 at 11:41
thanks but it is still failing : ORA-00904: "E5_COUNT": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: Error at Line: 12 Column: 14 for Round(e5_count/total*100) line — Shon, Aug 17 '17 at 14:53

Stored procedure or SQL to execute 1-2 times daily - Oracle

1 Answers1