flink-user-zh mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Djeng Lee <lee.ro...@gmail.com>
Subject Re: 回复: 回复: 回复: 回复:如何用SQL表达对设备离在线监控
Date Fri, 06 Dec 2019 07:25:18 GMT
存在查询

在 2019/12/5 下午4:06,“Yuan,Youjun”<yuanyoujun@baidu.com> 写入:

    Count=0的窗口如何能得到呢?没有数据就没有产出。
    然而可以同rows over窗口,将两个前后窗口的sum-当前的count,可以间接得到两个窗口的count是否相等。同时辅以前后窗口时间的差,来辅助判断。
    最终在自定义函数last_value_str/first_value_str的帮助下,勉强得以实现(尚不完美,可能出现连续的ONLINE的输出)
    下面是我的SQL,仅供参考:
    
    INSERT INTO mysink 
    SELECT userid, lastts, case when preCnt <= 0 OR tsdiff > 10 THEN 'ONLINE' ELSE 'offline'
END AS status 
    FROM (
    	SELECT curCnt, preCnt, lastts, firstts, userid, yeardiff * 31536000 + monthdiff * 2678400
+ daydiff * 86400 + hourdiff * 3600 + mindiff * 60 + seconddiff AS tsdiff 
    	FROM (
    		SELECT curCnt, preCnt, 
    			cast(substring(lastts, 1, 4) as bigint) - cast(substring(firstts, 1, 4) as bigint)
as yeardiff, 
    			cast(substring(lastts, 6, 2) as bigint) - cast(substring(firstts, 6, 2) as bigint)
as monthdiff, 
    			cast(substring(lastts, 9, 2) as bigint) - cast(substring(firstts, 9, 2) as bigint)
as daydiff, 
    			cast(substring(lastts, 12, 2) as bigint) - cast(substring(firstts, 12, 2) as bigint)
as hourdiff, 
    			cast(substring(lastts, 15, 2) as bigint) - cast(substring(firstts, 15, 2) as bigint)
as mindiff, 
    			cast(substring(lastts, 18, 2) as bigint) - cast(substring(firstts, 18, 2) as bigint)
as seconddiff, 
    			lastts, firstts, userid
    		FROM ( 
    			SELECT userid, cnt AS curCnt, sum(cnt) OVER w - cnt as preCnt, last_value_str(ts0)
OVER w as lastts, first_value_str(ts0) OVER w as firstts  
    			FROM (  
    				SELECT HOP_PROCTIME(rowtime, interval '5' second, interval '10' second) AS rowtime,
count(*) as cnt, userid, last_value_str(cast(rowtime AS varchar)) AS ts0  
    				FROM mysrc  
    				GROUP BY userid, hop(rowtime, interval '5' second, interval '10' second)
    			) 
    			WINDOW w as (PARTITION BY userid ORDER BY rowtime ROWS BETWEEN 1 PRECEDING AND CURRENT
ROW)
    		)
    	) 
    	WHERE (preCnt <= 0 OR yeardiff * 31536000 + monthdiff * 2678400 + daydiff * 86400
+ hourdiff * 3600 + mindiff * 60 + seconddiff > 10) OR (curCnt = preCnt AND lastts = lastts)
    )
    
    -----邮件原件-----
    发件人: 1193216154 <1193216154@qq.com> 
    发送时间: Thursday, December 5, 2019 2:43 PM
    收件人: user-zh <user-zh@flink.apache.org>
    主题: 回复: 回复: 回复:如何用SQL表达对设备离在线监控
    
    可以考虑用flink cep,应该可以解决你的问题。
    
    
    ------------------&nbsp;原始邮件&nbsp;------------------
    发件人:&nbsp;"Djeng Lee"<lee.roval@gmail.com&gt;;
    发送时间:&nbsp;2019年12月5日(星期四) 下午2:40
    收件人:&nbsp;"user-zh@flink.apache.org"<user-zh@flink.apache.org&gt;;
    
    主题:&nbsp;Re: 回复: 回复:如何用SQL表达对设备离在线监控
    
    
    
    上线时间,前n窗口count == 0 , 后n窗口count &gt; 1。说明是上线。由此得出上线时间.
    离线时间,前n 窗口count&gt;=1, 后n窗口count==0,说明下线,由此可得下线时间。
    前n后n都&gt;1 作为心跳维持。
    
    
    
    在 2019/12/5 下午2:06,“Yuan,Youjun”<yuanyoujun@baidu.com&gt; 写入:
    
    &nbsp;&nbsp;&nbsp; 谢谢你的回复。
    &nbsp;&nbsp;&nbsp; 这种方案比较有意思,只是还不能区分设备第一次心跳产生的count=1的消息(上线),和设备最后一次心跳产生的count=1的消息(下线)
    &nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp; -----邮件原件-----
    &nbsp;&nbsp;&nbsp; 发件人: 1193216154 <1193216154@qq.com&gt; &nbsp;&nbsp;&nbsp;
发送时间: Wednesday, December 4, 2019 9:39 PM &nbsp;&nbsp;&nbsp; 收件人:
user-zh <user-zh@flink.apache.org&gt; &nbsp;&nbsp;&nbsp; 主题: 回复:如何用SQL表达对设备离在线监控
    &nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp; 设定一个滑动窗口,窗口大小大于等于2n,滑动间隔大于等于n,若一次窗口结算,count
大于等于2,则在线,否则下线
    &nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp; ---原始邮件---
    &nbsp;&nbsp;&nbsp; 发件人: "Yuan,Youjun"<yuanyoujun@baidu.com&amp;gt;
    &nbsp;&nbsp;&nbsp; 发送时间: 2019年12月4日(周三) 晚上6:49
    &nbsp;&nbsp;&nbsp; 收件人: "user-zh@flink.apache.org"<user-zh@flink.apache.org&amp;gt;;
    &nbsp;&nbsp;&nbsp; 主题: 如何用SQL表达对设备离在线监控
    &nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp; Hi all,
    &nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp; 假设我们有很多设备,设备正常工作期间会定时发送心跳到服务器。如果某个设备在超过N分钟的时间内,没有发送任何心跳到服务器,服务器会认为设备已经离线。直到下一次心跳,才判定设备为在线。
    &nbsp;&nbsp;&nbsp; 需求:在判定设备离线时,产出一条设备离线消息;在设备经过一次离线后,第一次心跳时,产出一条设备上线的消息;
    &nbsp;&nbsp;&nbsp; 假设设备上报的消息包含当前时间(ts)和设备id(deviceid):
    &nbsp;&nbsp;&nbsp; 1575456144,dev1
    &nbsp;&nbsp;&nbsp; 1575456146,dev2
    &nbsp;&nbsp;&nbsp; 1575456147,dev1
    &nbsp;&nbsp;&nbsp; ….
    &nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp; 产出的离在线消息分别格式如下(第一列为设备离在线时间):
    &nbsp;&nbsp;&nbsp; 1575456158,dev1,offline &nbsp;&nbsp;&nbsp;
1575456169,dev2,online &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 能否用一条SQL来定义这个作业呢?
    &nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp; 谢谢!
    &nbsp;&nbsp;&nbsp; 袁尤军
    &nbsp;&nbsp;&nbsp;
    
Mime
View raw message