CREATE OR REPLACE PACKAGE BODY PADINFODATA.INF_RESOURCE_STATS_COUNTER IS
/*********************************************************************
* 功能描述: 按照周 月范围统计资讯浏览， 点赞， 评论， 分享等所有业务线RANK数据
*
* 执行时间：每日 2:30执行
* 执行频次：每天执行一次
*********************************************************************/
PROCEDURE RESOURCE_RANK_STATS(O_MSG OUT VARCHAR2) IS
V_BIZ_SERIES VARCHAR2(30);
CURSOR C_RES_BIZ_SERIES IS
SELECT BIZ_SERIES FROM INF_BUSSINESS;
BEGIN
OPEN C_RES_BIZ_SERIES;
LOOP
FETCH C_RES_BIZ_SERIES INTO V_BIZ_SERIES;
EXIT WHEN C_RES_BIZ_SERIES%NOTFOUND;
RESOURCE_RANK_BIZ_SERIES_STATS(V_BIZ_SERIES);
END LOOP;
CLOSE C_RES_BIZ_SERIES;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
IF O_MSG IS NULL THEN
O_MSG := '资讯排行表统计异常' || SQLCODE || SUBSTR(SQLERRM, 1, 200);
END IF;
END RESOURCE_RANK_STATS;
/*********************************************************************
* 功能描述: 按照周 月范围统计资讯浏览， 点赞， 评论， 分享等某个业务线RANK数据
*
* 执行时间：每日 2:30执行
* 执行频次：每天执行一次
*********************************************************************/
PROCEDURE RESOURCE_RANK_BIZ_SERIES_STATS(I_BIZ_SERIES IN VARCHAR2) IS
BEGIN
-- 执行插入、修改前对排行表周(7天:VIEW_WEEK+VIEW_TODAY)、月统计(31天:VIEW_MONTH+VIEW_TODAY)先清零
UPDATE INF_RESOURCE_RANK
SET VIEW_TODAY = 0,
VIEW_WEEK = 0,
VIEW_MONTH = 0,
ALL_COMMENT_TODAY = 0,
ALL_COMMENT_WEEK = 0,
ALL_COMMENT_MONTH = 0,
ALL_THUMB_TODAY = 0,
ALL_THUMB_WEEK = 0,
ALL_THUMB_MONTH = 0,
CUST_THUMB_TODAY = 0,
CUST_THUMB_WEEK = 0,
CUST_THUMB_MONTH = 0
WHERE BIZ_SERIES=I_BIZ_SERIES;
COMMIT;
---VIEW MERGE
MERGE INTO INF_RESOURCE_RANK A
USING
(SELECT VIEW_MONTH_TMP.RESOURCE_ID,
VIEW_MONTH_TMP.BIZ_SERIES,
VIEW_MONTH_TMP.VIEW_MONTH,
VIEW_WEEK_TMP.VIEW_WEEK
FROM (SELECT RESV.RESOURCE_ID,
EMP.BIZ_SERIES AS BIZ_SERIES,
COUNT(1) AS VIEW_MONTH
FROM INF_RESOURCE_VIEW RESV
INNER JOIN INF_EMPLOYEE EMP
ON EMP.PAIC_UM_NUM = NVL(RESV.SENDER, RESV.VIEWER)
WHERE RESV.VIEW_DATE BETWEEN TRUNC(SYSDATE - 30) AND
TRUNC(SYSDATE)
AND EMP.BIZ_SERIES = I_BIZ_SERIES
GROUP BY RESV.RESOURCE_ID, EMP.BIZ_SERIES) VIEW_MONTH_TMP
LEFT JOIN (SELECT RESV.RESOURCE_ID AS RESOURCE_ID,
EMP.BIZ_SERIES AS BIZ_SERIES,
COUNT(1) AS VIEW_WEEK
FROM INF_RESOURCE_VIEW RESV
INNER JOIN INF_EMPLOYEE EMP
ON EMP.PAIC_UM_NUM = NVL(RESV.SENDER, RESV.VIEWER)
WHERE RESV.VIEW_DATE BETWEEN TRUNC(SYSDATE - 6) AND
TRUNC(SYSDATE)
AND EMP.BIZ_SERIES = I_BIZ_SERIES
GROUP BY RESV.RESOURCE_ID, EMP.BIZ_SERIES) VIEW_WEEK_TMP
ON (VIEW_MONTH_TMP.RESOURCE_ID = VIEW_WEEK_TMP.RESOURCE_ID
AND VIEW_MONTH_TMP.BIZ_SERIES = VIEW_WEEK_TMP.BIZ_SERIES)) B
ON (A.RESOURCE_ID = B.RESOURCE_ID AND A.BIZ_SERIES = B.BIZ_SERIES)
WHEN MATCHED THEN
UPDATE
SET A.VIEW_WEEK = B.VIEW_WEEK,
A.VIEW_MONTH = B.VIEW_MONTH
WHEN NOT MATCHED THEN
INSERT
(ID_RESOURCE_RANK,
RESOURCE_ID,
VIEW_TODAY,
VIEW_WEEK,
VIEW_MONTH,
SHARE_TODAY,
SHARE_WEEK,
SHARE_MONTH,
ALL_COMMENT_TODAY,
ALL_COMMENT_WEEK,
ALL_COMMENT_MONTH,
ALL_THUMB_TODAY,
ALL_THUMB_WEEK,
ALL_THUMB_MONTH,
CUST_THUMB_TODAY,
CUST_THUMB_WEEK,
CUST_THUMB_MONTH,
BIZ_SERIES)
VALUES
(SYS_GUID(),
B.RESOURCE_ID,
0,B.VIEW_WEEK,
B.VIEW_MONTH,
0,0,0,
0,0,0,
0,0,0,
0,0,0,
I_BIZ_SERIES);
COMMIT;
---COMMENT THUMB MERGE
MERGE INTO INF_RESOURCE_RANK A
USING
(SELECT
NVL(RESCOMMENT.RESOURCE_ID, RESTHUMB.RESOURCE_ID) AS RESOURCE_ID,
NVL(RESCOMMENT.BIZ_SERIES, RESTHUMB.BIZ_SERIES) AS BIZ_SERIES,
NVL(RESCOMMENT.ALL_COMMENT_MONTH, 0) AS ALL_COMMENT_MONTH,
NVL(RESCOMMENT.ALL_COMMENT_WEEK,0) AS ALL_COMMENT_WEEK,
NVL(RESTHUMB.ALL_THUMB_MONTH,0) AS ALL_THUMB_MONTH,
NVL(RESTHUMB.ALL_THUMB_WEEK,0) AS ALL_THUMB_WEEK,
NVL(RESTHUMB.CUST_THUMB_WEEK,0) AS CUST_THUMB_WEEK,
NVL(RESTHUMB.CUST_THUMB_MONTH,0) AS CUST_THUMB_MONTH
FROM
---COMMENT
(SELECT T_ALL_COMMENT_MONTH.RESOURCE_ID AS RESOURCE_ID,
T_ALL_COMMENT_MONTH.BIZ_SERIES AS BIZ_SERIES,
T_ALL_COMMENT_MONTH.ALL_COMMENT_MONTH AS ALL_COMMENT_MONTH,
T_ALL_COMMENT_WEEK.ALL_COMMENT_WEEK AS ALL_COMMENT_WEEK
FROM (SELECT RESC.RESOURCE_ID,
EMP.BIZ_SERIES AS BIZ_SERIES,
COUNT(1) AS ALL_COMMENT_MONTH
FROM INF_RESOURCE_COMMENT RESC
INNER JOIN INF_EMPLOYEE EMP
ON EMP.PAIC_UM_NUM = NVL(RESC.SENDER, RESC.COMMENTOR)
WHERE RESC.SUBMIT_DATE BETWEEN TRUNC(SYSDATE - 30) AND TRUNC(SYSDATE)
AND EMP.BIZ_SERIES = I_BIZ_SERIES
AND RESC.ID_PARENT_COMMENT IS NULL
GROUP BY RESC.RESOURCE_ID, EMP.BIZ_SERIES) T_ALL_COMMENT_MONTH
LEFT JOIN (SELECT RESC.RESOURCE_ID AS RESOURCE_ID,
EMP.BIZ_SERIES AS BIZ_SERIES,
COUNT(1) AS ALL_COMMENT_WEEK
FROM INF_RESOURCE_COMMENT RESC
INNER JOIN INF_EMPLOYEE EMP
ON EMP.PAIC_UM_NUM = NVL(RESC.SENDER, RESC.COMMENTOR)
WHERE RESC.SUBMIT_DATE BETWEEN TRUNC(SYSDATE - 6) AND TRUNC(SYSDATE)
AND EMP.BIZ_SERIES = I_BIZ_SERIES
AND RESC.ID_PARENT_COMMENT IS NULL
GROUP BY RESC.RESOURCE_ID, EMP.BIZ_SERIES) T_ALL_COMMENT_WEEK
ON (T_ALL_COMMENT_MONTH.RESOURCE_ID = T_ALL_COMMENT_WEEK.RESOURCE_ID AND T_ALL_COMMENT_MONTH.BIZ_SERIES = T_ALL_COMMENT_WEEK.BIZ_SERIES)) RESCOMMENT
FULL JOIN
---THUMB
--ALL THUMB
(SELECT T_ALL_THUMB_MONTH.RESOURCE_ID AS RESOURCE_ID,
T_ALL_THUMB_MONTH.BIZ_SERIES AS BIZ_SERIES,
T_ALL_THUMB_MONTH.ALL_THUMB_MONTH AS ALL_THUMB_MONTH,
T_ALL_THUMB_WEEK.ALL_THUMB_WEEK AS ALL_THUMB_WEEK,
RESCTW.CUST_THUMB_WEEK AS CUST_THUMB_WEEK,
RESCTM.CUST_THUMB_MONTH AS CUST_THUMB_MONTH
FROM
(SELECT REST.RESOURCE_ID AS RESOURCE_ID,
EMP.BIZ_SERIES AS BIZ_SERIES,
COUNT(1) AS ALL_THUMB_MONTH
FROM INF_RESOURCE_THUMB REST
INNER JOIN INF_EMPLOYEE EMP
ON EMP.PAIC_UM_NUM = NVL(REST.SENDER, REST.THUMBER)
WHERE REST.THUMB_DATE BETWEEN TRUNC(SYSDATE - 30) AND TRUNC(SYSDATE)
AND EMP.BIZ_SERIES = I_BIZ_SERIES
GROUP BY REST.RESOURCE_ID, EMP.BIZ_SERIES) T_ALL_THUMB_MONTH
LEFT JOIN
(SELECT REST.RESOURCE_ID AS RESOURCE_ID,
EMP.BIZ_SERIES AS BIZ_SERIES,
COUNT(1) AS ALL_THUMB_WEEK
FROM INF_RESOURCE_THUMB REST
INNER JOIN INF_EMPLOYEE EMP
ON EMP.PAIC_UM_NUM = NVL(REST.SENDER, REST.THUMBER)
WHERE REST.THUMB_DATE BETWEEN TRUNC(SYSDATE - 6) AND TRUNC(SYSDATE)
AND EMP.BIZ_SERIES = I_BIZ_SERIES
GROUP BY REST.RESOURCE_ID, EMP.BIZ_SERIES) T_ALL_THUMB_WEEK
ON (T_ALL_THUMB_MONTH.RESOURCE_ID = T_ALL_THUMB_WEEK.RESOURCE_ID AND T_ALL_THUMB_MONTH.BIZ_SERIES = T_ALL_THUMB_WEEK.BIZ_SERIES)
--CUS THUMB
LEFT JOIN
(SELECT REST.RESOURCE_ID AS RESOURCE_ID,
EMP.BIZ_SERIES AS BIZ_SERIES,
COUNT(1) AS CUST_THUMB_WEEK
FROM INF_RESOURCE_THUMB REST
INNER JOIN INF_EMPLOYEE EMP
ON EMP.PAIC_UM_NUM = REST.SENDER
WHERE REST.THUMB_DATE BETWEEN TRUNC(SYSDATE - 6) AND TRUNC(SYSDATE)
AND EMP.BIZ_SERIES = I_BIZ_SERIES
AND REST.THUMB_SOURCE = 'WEIXIN'
GROUP BY REST.RESOURCE_ID, EMP.BIZ_SERIES) RESCTW
ON (T_ALL_THUMB_MONTH.RESOURCE_ID = RESCTW.RESOURCE_ID AND T_ALL_THUMB_MONTH.BIZ_SERIES = RESCTW.BIZ_SERIES)
LEFT JOIN
(SELECT REST.RESOURCE_ID AS RESOURCE_ID,
EMP.BIZ_SERIES AS BIZ_SERIES,
COUNT(1) AS CUST_THUMB_MONTH
FROM INF_RESOURCE_THUMB REST
INNER JOIN INF_EMPLOYEE EMP
ON EMP.PAIC_UM_NUM = REST.SENDER
WHERE REST.THUMB_DATE BETWEEN TRUNC(SYSDATE - 30) AND TRUNC(SYSDATE)
AND EMP.BIZ_SERIES = I_BIZ_SERIES
AND REST.THUMB_SOURCE = 'WEIXIN'
GROUP BY REST.RESOURCE_ID, EMP.BIZ_SERIES) RESCTM
ON (T_ALL_THUMB_MONTH.RESOURCE_ID = RESCTM.RESOURCE_ID AND T_ALL_THUMB_MONTH.BIZ_SERIES = RESCTM.BIZ_SERIES)
) RESTHUMB
ON (RESCOMMENT.BIZ_SERIES = RESTHUMB.BIZ_SERIES AND RESCOMMENT.RESOURCE_ID = RESTHUMB.RESOURCE_ID)) B
ON (A.RESOURCE_ID = B.RESOURCE_ID AND B.BIZ_SERIES = A.BIZ_SERIES )
WHEN MATCHED THEN
UPDATE
SET A.ALL_COMMENT_WEEK = B.ALL_COMMENT_WEEK,
A.ALL_COMMENT_MONTH = B.ALL_COMMENT_MONTH,
A.ALL_THUMB_WEEK = B.ALL_THUMB_WEEK,
A.ALL_THUMB_MONTH = B.ALL_THUMB_MONTH,
A.CUST_THUMB_WEEK = B.CUST_THUMB_WEEK,
A.CUST_THUMB_MONTH = B.CUST_THUMB_MONTH
WHEN NOT MATCHED THEN
INSERT
(ID_RESOURCE_RANK, RESOURCE_ID,
VIEW_TODAY, VIEW_WEEK, VIEW_MONTH,
SHARE_TODAY, SHARE_WEEK, SHARE_MONTH,
ALL_COMMENT_TODAY, ALL_COMMENT_WEEK, ALL_COMMENT_MONTH,
ALL_THUMB_TODAY, ALL_THUMB_WEEK, ALL_THUMB_MONTH,
CUST_THUMB_TODAY, CUST_THUMB_WEEK, CUST_THUMB_MONTH,
BIZ_SERIES)
VALUES
(SYS_GUID(),B.RESOURCE_ID,
0,0,0,
0,0,0,
0,B.ALL_COMMENT_WEEK,B.ALL_COMMENT_MONTH,
0,B.ALL_THUMB_WEEK,B.ALL_THUMB_MONTH,
0,B.CUST_THUMB_WEEK,B.CUST_THUMB_MONTH,
I_BIZ_SERIES);
COMMIT;
END RESOURCE_RANK_BIZ_SERIES_STATS;
/*********************************************************************
* 功能描述: 维护某业务线当天的浏览RANK数据
*
* 执行时间：每日 2:30执行
* 执行频次：每天执行一次
*********************************************************************/
PROCEDURE INC_TODAY_RANK_STATS(I_RESOURCE_ID IN INF_RESOURCE_VIEW.RESOURCE_ID%TYPE,I_BIZ_SERIES IN VARCHAR2) IS
BEGIN
MERGE INTO INF_RESOURCE_RANK A
USING (SELECT I_RESOURCE_ID AS RESOURCE_ID,I_BIZ_SERIES AS BIZ_SERIES FROM DUAL) B
ON (A.RESOURCE_ID = B.RESOURCE_ID AND A.BIZ_SERIES = B.BIZ_SERIES)
WHEN MATCHED THEN
UPDATE SET A.VIEW_TODAY = A.VIEW_TODAY + 1
WHEN NOT MATCHED THEN
INSERT
(ID_RESOURCE_RANK, RESOURCE_ID,
VIEW_TODAY, VIEW_WEEK, VIEW_MONTH,
SHARE_TODAY, SHARE_WEEK, SHARE_MONTH,
ALL_COMMENT_TODAY, ALL_COMMENT_WEEK, ALL_COMMENT_MONTH,
ALL_THUMB_TODAY, ALL_THUMB_WEEK, ALL_THUMB_MONTH,
CUST_THUMB_TODAY, CUST_THUMB_WEEK, CUST_THUMB_MONTH,
BIZ_SERIES)
VALUES
(SYS_GUID(),B.RESOURCE_ID,
1,0,0,
0,0,0,
0,0,0,
0,0,0,
0,0,0,
B.BIZ_SERIES);
COMMIT;
END INC_TODAY_RANK_STATS;
END INF_RESOURCE_STATS_COUNTER;
/
