需求背景
目前SQL形式的查询，无法满足指标监控的需求，需要写程序做成钉钉监控
需求描述
每天早上9点下午4点监控如下信息
-- 智能刊登基础产品数据同步
SELECT
'智能刊登基础产品数据同步' 监控指标,
case when TIMESTAMPDIFF(hour,NextEndTime,now=0 then TIMESTAMPDIFF(hour,NextEndTime,now()) else 0 end 延时误差,
NextEndTime 最近一次同步时间
FROM
sys_syncdata
WHERE
BusinessType LIKE '%SyncCheckProduct%';
-- 智能刊登ebay平衡库存
select
'智能刊登ebay平衡库存' 监控指标,
now() 查询时间,
TIMESTAMPDIFF(hour,t.`操作时间`,now()) 延误时长,
t.操作时间
from (
SELECT
a.Id,
d.OrderSourceName AS 来源渠道,
a.sku AS 系统SKU,
a.ProductId AS 平台ID,
a.SellerSku AS 渠道SKU,
a.StrategyId AS 执行策略ID,
b.NAME AS 执行策略名称,
a.`Status` AS 执行状态,
a.FailureReason AS 失败原因,
a.Time AS 操作时间,
a.Inventory AS 修改后库存,
a.OriginalInventory AS 原库存
FROM
pp_replenishment AS a
LEFT JOIN pp_replenishment_strategy AS b ON a.StrategyId = b.id
INNER JOIN sys_ordersource AS d ON a.OrderSourceId = d.OrderSourceId
WHERE
a.CustomerId = 1
AND a.time DATE_ADD(now(),interval -1 day)
AND a.time <now()
AND a.MarketId = 2
and a.`Status` ='normal'
and b.IsDeleted = 0
and b.IsEnabled = 1
and a.SiteCode is NOT NULL
order by id
limit 1
)t;
-- 促销调价下架未执行策略
select
'促销调价下架未执行策略' 监控指标,
now() 时间, count(0) 自动促销调价未正常执行策略数 from (
SELECT
MarketId,
Id AS StrategyId
FROM
pp_phoenixplan_strategy
WHERE
Id NOT IN ( SELECT StrategyId FROM sys_phoenixplandata_detail WHERE CustomerId = 1 AND Time curdate() and MarketId=2 GROUP BY StrategyId )
AND CustomerId = 1
AND IsEnabled = 1
AND IsDeleted = 0
AND Id NOT IN ( SELECT StrategyId FROM pp_phoenixplan_ebay_queue WHERE CustomerId = 1 AND Timecurdate() and MarketId=2 GROUP BY StrategyId )
and MarketId=2
)t;
-- 智能刊登系统ebaylisting同步明细
/*
select
t.延误时长,
count(t.OrderSourceId) 店铺数
from (
SELECT
a.OrderSourceId,a.timeinterval,a.nextbegintime,a.nextendtime,lasttime,TIMESTAMPDIFF(hour,a.nextendtime,now()) 延误时长
FROM
sys_requestdata a
WHERE
CustomerId = 1
AND MarketId = 2
AND BusinessType = 'GetEbayListings'
AND OrderSourceId NOT IN ( SELECT OrderSourceId FROM sys_ordersource WHERE ordersourcetype = 2 AND IsActive = 0 )
DATE_ADD(now(),interval -1 day)
-- and timeinterval=4
)t
group by t.延误时长
order by t.延误时长;
*/
SELECT
'智能刊登系统ebaylisting同步' 监控指标,
now() 查询时间,
count( DISTINCT CASE WHEN 2 THEN t.OrderSourceId ELSE NULL END ) 延时店铺数,
count( DISTINCT t.OrderSourceId ) 店铺数
FROM
(
SELECT
a.OrderSourceId,
a.timeinterval,
a.nextbegintime,
a.nextendtime,
lasttime,
TIMESTAMPDIFF(
HOUR,
a.nextendtime,
now()) 延误时长
FROM
sys_requestdata a
WHERE
CustomerId = 1
AND MarketId = 2
AND BusinessType = 'GetEbayListings'
AND OrderSourceId NOT IN ( SELECT OrderSourceId FROM sys_ordersource WHERE ordersourcetype = 2 AND IsActive = 0 )
AND DATE_ADD( now(), INTERVAL - 1 DAY ) -- and timeinterval=4
) t