76 lines
2.8 KiB
MySQL
76 lines
2.8 KiB
MySQL
|
|
|||
|
|
DROP TABLE ods_history_today_fee_save;
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
CREATE TABLE ods_history_today_fee_save(
|
|||
|
|
dt VARCHAR(50) COMMENT '时间'
|
|||
|
|
,extract_date VARCHAR(50) COMMENT '抽数日期'
|
|||
|
|
,extract_time VARCHAR(50) COMMENT '抽数时间'
|
|||
|
|
,metric_value VARCHAR(50) COMMENT '指标值'
|
|||
|
|
)COMMENT = '今日收费历史数据保留';
|
|||
|
|
|
|||
|
|
SELECT * FROM ods_history_today_fee_save;
|
|||
|
|
/*
|
|||
|
|
总裁驾驶舱、财务看板PC端和移动端：
|
|||
|
|
1、今日收费：最新拉取数据=上次拉取数据时、08:00之前数据大于50w时；推送角色
|
|||
|
|
2、今日收费：最新拉取数据为空；
|
|||
|
|
*/
|
|||
|
|
|
|||
|
|
INSERT INTO ods_history_today_fee_save
|
|||
|
|
SELECT
|
|||
|
|
LEFT(CURRENT_DATE,10) AS dt -- 当天
|
|||
|
|
,CURRENT_TIMESTAMP() AS extract_date -- 抽数日期
|
|||
|
|
,CURRENT_TIME() AS extract_time -- 抽数时间
|
|||
|
|
,sum(ifnull(pr_paid_amt,0) + ifnull(cu_paid_amt,0) + ifnull(fu_paid_amt,0))/10000 metric_value
|
|||
|
|
from dws_finance_today_fees_d
|
|||
|
|
WHERE ParentCostCode not in ('0006','0009','0010','0011')
|
|||
|
|
and MiddleCostCode not in ('00080013','00080014','00080015','00080016','00080025','00080029','00080033','00080036','00080037')
|
|||
|
|
and commid not in(select comm_id from ods_remove_comm)
|
|||
|
|
;
|
|||
|
|
|
|||
|
|
-- 时间 上次拉取数据、当前拉取数据、8点前拉取数据
|
|||
|
|
|
|||
|
|
with base AS(
|
|||
|
|
SELECT
|
|||
|
|
t.dt AS dt
|
|||
|
|
,t.extract_date AS extract_date
|
|||
|
|
,t.extract_time AS extract_time
|
|||
|
|
,t.metric_value AS metric_value
|
|||
|
|
,row_number() OVER (PARTITION BY LEFT(dt,10) ORDER BY extract_date desc) as rk -- 根据抽数时间排序
|
|||
|
|
FROM
|
|||
|
|
(
|
|||
|
|
SELECT
|
|||
|
|
dt
|
|||
|
|
,extract_date
|
|||
|
|
,extract_time
|
|||
|
|
,metric_value
|
|||
|
|
-- 如果一个小时内多次抽数的情况，取最新的
|
|||
|
|
,row_number() OVER (PARTITION BY dt,hour(extract_date) ORDER BY extract_date desc) as rk
|
|||
|
|
FROM ods_history_today_fee_save
|
|||
|
|
WHERE LEFT(dt,10) = '${s_ym}'
|
|||
|
|
)t
|
|||
|
|
WHERE t.rk = 1
|
|||
|
|
AND hour(extract_date) >= 7
|
|||
|
|
AND hour(extract_date) <= 24
|
|||
|
|
)
|
|||
|
|
SELECT
|
|||
|
|
a.dt AS dt
|
|||
|
|
,a.extract_date AS extract_date
|
|||
|
|
,a.extract_time AS extract_time
|
|||
|
|
,hour(a.extract_date) AS extract_hour
|
|||
|
|
,a.metric_value AS current_value
|
|||
|
|
,coalesce(b.metric_value,0) AS last_metric_value
|
|||
|
|
,CASE WHEN hour(a.extract_date) < 8 THEN a.metric_value ELSE 0 END AS before_eight
|
|||
|
|
,CASE WHEN hour(a.extract_date) < 8 AND a.metric_value > 50 THEN '1'
|
|||
|
|
WHEN a.metric_value = b.metric_value THEN '1'
|
|||
|
|
WHEN hour(a.extract_date) > 8 AND coalesce(a.metric_value,0) = 0 THEN '1'
|
|||
|
|
ELSE '0' END AS is_exception -- 是否异常 1:是 0:否
|
|||
|
|
,CASE WHEN hour(a.extract_date) < 8 AND a.metric_value > 50 THEN '08:00之前数据大于50w'
|
|||
|
|
WHEN a.metric_value = b.metric_value THEN '最新数据等于上次拉取数据'
|
|||
|
|
WHEN hour(a.extract_date) > 8 AND coalesce(a.metric_value,0) = 0 THEN '最新拉取数据为空'
|
|||
|
|
ELSE '最新数据等于上次拉取数据' END AS exception_rule
|
|||
|
|
FROM base a
|
|||
|
|
LEFT JOIN base b
|
|||
|
|
on a.rk = b.rk - 1
|
|||
|
|
ORDER BY a.extract_date
|