Tag Archives: sql

日均线的SQL写法

近期得到一个需求,是计算某调度流程一段时间的5日平均运行时长,并与当前运行时长一起展示,形成类似于股票行情的K线图。

SCHEDULE.RUNTIMEFLOWLOG表结构如下:

名称 数据类型 长度 备注
LOGID DECIMAL 20 ID,主键
FLOWID DECIMAL 11 流程ID
STATUS DECIMAL 11 流程状态
STARTTIME TIMESTAMP 10 开始时间
ENDTIME TIMESTAMP 10 结束时间
DATATIME VARCHAR 32 数据时间
FLOWTYPE DECIMAL 11  

两个计算字段:
cursec:当前运行时长,单位:秒
avgsec:5日平均运行时长,单位:秒
SQL如下(以DB2为例):

SELECT logid,
status,
datatime,
starttime,
MIDNIGHT_SECONDS (ENDTIME) - MIDNIGHT_SECONDS (STARTTIME) cursec,
AVG(MIDNIGHT_SECONDS (ENDTIME) - MIDNIGHT_SECONDS (STARTTIME))
OVER (PARTITION BY flowid, status
ORDER BY logid DESC
ROWS BETWEEN 1 FOLLOWING AND 5 FOLLOWING) avgsec
FROM SCHEDULE.RUNTIMEFLOWLOG
WHERE FLOWID = 471
ORDER BY logid DESC;

如为Oracle,只需要将cursec和avgsec计算秒数的部分做相应调整:
(CAST(ENDTIME AS DATE)-CAST(STARTTIME AS DATE))*3600*24
而分析函数的用法此处Oracle和DB2均通用,不需要任何调整。

以下是5日均线的效果图:5日均线图


Data Warehouse For Ever原创文章,转载请注明出处

勿混用过滤条件(where)和连接条件(on)

SQL中,where子句是过滤条件,on子句是连接条件,两者不能混用。如果混用,可能会导致DB2产生错误的执行计划,从而引发严重的性能问题。很多新手比较容易犯此类错误,在没有发生严重的的性能问题时,往往因为没有引起注意而不会得到重视。下面是我在监控DB2性能问题时发现的一个问题SQL:

select t.* , case when u.user_id is not null then 1 else 0 end as flag
from shenll_gprs_flow_200908 t left join dw_user_m_200908 u
on t.user_id=u.user_id and u.is_bill=1 and u.user_type not in(2,3,4) and t.area_id='570'

该SQL语句将过滤条件写到关联条件中,导致DB2生成了错误的执行计划,使用了内连接NLJoin对两张大表进行了关联,引起了严重的性能问题。以下是上述语句的执行计划:

错误的执行计划

对该语句进行修改,使用正确的on语句和where语句:

select t.* , case when u.user_id is not null then 1 else 0 end as flag
from shenll_gprs_flow_200908 t left join dw_user_m_200908 u
on t.user_id=u.user_id
where u.is_bill=1 and u.user_type not in(2,3,4) and t.area_id='570'

上述SQL的执行计划如下:

正确的执行计划

从上面的执行计划中可以看到,DB2使用了哈希连接,整个SQL的执行成本从5亿降低到6万,执行效率得到了数据级的提升。


Data Warehouse For Ever原创文章,转载请注明出处