每天执行1-2次的存储过程或SQL - Oracle
每天执行1-2次的存储过程或SQL - Oracle
提问于 2017-08-17 10:11:35
我使用一个巨大的oracle表呈现一个报告，该表有一个emoji列(可能的值为1、2、3、4、5)。我正在为每个报告查询做一个实时计数(*)和每个表情符号的百分比计数。我可以选择生成7天、30天、60天和90天的报告。
我的要求：-运行一个存储的proc/SQL，将每个表情符号的计数和百分比计数存储在一个单独的表中，每天1-2次。- Insert如果是新的，则更新现有的。每天将插入/更新4条记录，频率分别为7天、30天、60天和90天。-通过每天上午6点和/或下午6点的调度程序运行此程序。
我的模式
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)我目前从java程序中查询30天和60天的频率：
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;这将返回：
但是，为了便于阅读，我希望将行显示为列。像这样的东西每天都会插入。
我已经尝试了以下SQL，我可以获得计数，但无法获得工作的百分比。有什么建议吗？
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;编辑--新变体
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;回答 1
Stack Overflow用户
发布于 2017-08-17 17:39:22
您可以使用PIVOT查询(https://www.techonthenet.com/oracle/pivot.php)，也可以尝试如下所示
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))页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/45725581
复制相关文章
相似问题
腾讯云开发者
