Hi all,
假设我们有很多设备,设备正常工作期间会定时发送心跳到服务器。如果某个设备在超过N分钟的时间内,没有发送任何心跳到服务器,服务器会认为设备已经离线。直到下一次心跳,才判定设备为在线。 需求:在判定设备离线时,产出一条设备离线消息;在设备经过一次离线后,第一次心跳时,产出一条设备上线的消息; 假设设备上报的消息包含当前时间(ts)和设备id(deviceid): 1575456144,dev1 1575456146,dev2 1575456147,dev1 …. 产出的离在线消息分别格式如下(第一列为设备离在线时间): 1575456158,dev1,offline 1575456169,dev2,online 能否用一条SQL来定义这个作业呢? 谢谢! 袁尤军 |
设定一个滑动窗口,窗口大小大于等于2n,滑动间隔大于等于n,若一次窗口结算,count 大于等于2,则在线,否则下线
---原始邮件--- 发件人: "Yuan,Youjun"<[hidden email]> 发送时间: 2019年12月4日(周三) 晚上6:49 收件人: "[hidden email]"<[hidden email]>; 主题: 如何用SQL表达对设备离在线监控 Hi all, 假设我们有很多设备,设备正常工作期间会定时发送心跳到服务器。如果某个设备在超过N分钟的时间内,没有发送任何心跳到服务器,服务器会认为设备已经离线。直到下一次心跳,才判定设备为在线。 需求:在判定设备离线时,产出一条设备离线消息;在设备经过一次离线后,第一次心跳时,产出一条设备上线的消息; 假设设备上报的消息包含当前时间(ts)和设备id(deviceid): 1575456144,dev1 1575456146,dev2 1575456147,dev1 …. 产出的离在线消息分别格式如下(第一列为设备离在线时间): 1575456158,dev1,offline 1575456169,dev2,online 能否用一条SQL来定义这个作业呢? 谢谢! 袁尤军 |
谢谢你的回复。
这种方案比较有意思,只是还不能区分设备第一次心跳产生的count=1的消息(上线),和设备最后一次心跳产生的count=1的消息(下线) -----邮件原件----- 发件人: 1193216154 <[hidden email]> 发送时间: Wednesday, December 4, 2019 9:39 PM 收件人: user-zh <[hidden email]> 主题: 回复:如何用SQL表达对设备离在线监控 设定一个滑动窗口,窗口大小大于等于2n,滑动间隔大于等于n,若一次窗口结算,count 大于等于2,则在线,否则下线 ---原始邮件--- 发件人: "Yuan,Youjun"<[hidden email]> 发送时间: 2019年12月4日(周三) 晚上6:49 收件人: "[hidden email]"<[hidden email]>; 主题: 如何用SQL表达对设备离在线监控 Hi all, 假设我们有很多设备,设备正常工作期间会定时发送心跳到服务器。如果某个设备在超过N分钟的时间内,没有发送任何心跳到服务器,服务器会认为设备已经离线。直到下一次心跳,才判定设备为在线。 需求:在判定设备离线时,产出一条设备离线消息;在设备经过一次离线后,第一次心跳时,产出一条设备上线的消息; 假设设备上报的消息包含当前时间(ts)和设备id(deviceid): 1575456144,dev1 1575456146,dev2 1575456147,dev1 …. 产出的离在线消息分别格式如下(第一列为设备离在线时间): 1575456158,dev1,offline 1575456169,dev2,online 能否用一条SQL来定义这个作业呢? 谢谢! 袁尤军 |
上线时间,前n窗口count == 0 , 后n窗口count > 1。说明是上线。由此得出上线时间.
离线时间,前n 窗口count>=1, 后n窗口count==0,说明下线,由此可得下线时间。 前n后n都>1 作为心跳维持。 在 2019/12/5 下午2:06,“Yuan,Youjun”<[hidden email]> 写入: 谢谢你的回复。 这种方案比较有意思,只是还不能区分设备第一次心跳产生的count=1的消息(上线),和设备最后一次心跳产生的count=1的消息(下线) -----邮件原件----- 发件人: 1193216154 <[hidden email]> 发送时间: Wednesday, December 4, 2019 9:39 PM 收件人: user-zh <[hidden email]> 主题: 回复:如何用SQL表达对设备离在线监控 设定一个滑动窗口,窗口大小大于等于2n,滑动间隔大于等于n,若一次窗口结算,count 大于等于2,则在线,否则下线 ---原始邮件--- 发件人: "Yuan,Youjun"<[hidden email]> 发送时间: 2019年12月4日(周三) 晚上6:49 收件人: "[hidden email]"<[hidden email]>; 主题: 如何用SQL表达对设备离在线监控 Hi all, 假设我们有很多设备,设备正常工作期间会定时发送心跳到服务器。如果某个设备在超过N分钟的时间内,没有发送任何心跳到服务器,服务器会认为设备已经离线。直到下一次心跳,才判定设备为在线。 需求:在判定设备离线时,产出一条设备离线消息;在设备经过一次离线后,第一次心跳时,产出一条设备上线的消息; 假设设备上报的消息包含当前时间(ts)和设备id(deviceid): 1575456144,dev1 1575456146,dev2 1575456147,dev1 …. 产出的离在线消息分别格式如下(第一列为设备离在线时间): 1575456158,dev1,offline 1575456169,dev2,online 能否用一条SQL来定义这个作业呢? 谢谢! 袁尤军 |
可以考虑用flink cep,应该可以解决你的问题。
------------------ 原始邮件 ------------------ 发件人: "Djeng Lee"<[hidden email]>; 发送时间: 2019年12月5日(星期四) 下午2:40 收件人: "[hidden email]"<[hidden email]>; 主题: Re: 回复: 回复:如何用SQL表达对设备离在线监控 上线时间,前n窗口count == 0 , 后n窗口count > 1。说明是上线。由此得出上线时间. 离线时间,前n 窗口count>=1, 后n窗口count==0,说明下线,由此可得下线时间。 前n后n都>1 作为心跳维持。 在 2019/12/5 下午2:06,“Yuan,Youjun”<[hidden email]> 写入: 谢谢你的回复。 这种方案比较有意思,只是还不能区分设备第一次心跳产生的count=1的消息(上线),和设备最后一次心跳产生的count=1的消息(下线) -----邮件原件----- 发件人: 1193216154 <[hidden email]> 发送时间: Wednesday, December 4, 2019 9:39 PM 收件人: user-zh <[hidden email]> 主题: 回复:如何用SQL表达对设备离在线监控 设定一个滑动窗口,窗口大小大于等于2n,滑动间隔大于等于n,若一次窗口结算,count 大于等于2,则在线,否则下线 ---原始邮件--- 发件人: "Yuan,Youjun"<[hidden email]&gt; 发送时间: 2019年12月4日(周三) 晚上6:49 收件人: "[hidden email]"<[hidden email]&gt;; 主题: 如何用SQL表达对设备离在线监控 Hi all, 假设我们有很多设备,设备正常工作期间会定时发送心跳到服务器。如果某个设备在超过N分钟的时间内,没有发送任何心跳到服务器,服务器会认为设备已经离线。直到下一次心跳,才判定设备为在线。 需求:在判定设备离线时,产出一条设备离线消息;在设备经过一次离线后,第一次心跳时,产出一条设备上线的消息; 假设设备上报的消息包含当前时间(ts)和设备id(deviceid): 1575456144,dev1 1575456146,dev2 1575456147,dev1 …. 产出的离在线消息分别格式如下(第一列为设备离在线时间): 1575456158,dev1,offline 1575456169,dev2,online 能否用一条SQL来定义这个作业呢? 谢谢! 袁尤军 |
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 <[hidden email]> 发送时间: Thursday, December 5, 2019 2:43 PM 收件人: user-zh <[hidden email]> 主题: 回复: 回复: 回复:如何用SQL表达对设备离在线监控 可以考虑用flink cep,应该可以解决你的问题。 ------------------ 原始邮件 ------------------ 发件人: "Djeng Lee"<[hidden email]>; 发送时间: 2019年12月5日(星期四) 下午2:40 收件人: "[hidden email]"<[hidden email]>; 主题: Re: 回复: 回复:如何用SQL表达对设备离在线监控 上线时间,前n窗口count == 0 , 后n窗口count > 1。说明是上线。由此得出上线时间. 离线时间,前n 窗口count>=1, 后n窗口count==0,说明下线,由此可得下线时间。 前n后n都>1 作为心跳维持。 在 2019/12/5 下午2:06,“Yuan,Youjun”<[hidden email]> 写入: 谢谢你的回复。 这种方案比较有意思,只是还不能区分设备第一次心跳产生的count=1的消息(上线),和设备最后一次心跳产生的count=1的消息(下线) -----邮件原件----- 发件人: 1193216154 <[hidden email]> 发送时间: Wednesday, December 4, 2019 9:39 PM 收件人: user-zh <[hidden email]> 主题: 回复:如何用SQL表达对设备离在线监控 设定一个滑动窗口,窗口大小大于等于2n,滑动间隔大于等于n,若一次窗口结算,count 大于等于2,则在线,否则下线 ---原始邮件--- 发件人: "Yuan,Youjun"<[hidden email]&gt; 发送时间: 2019年12月4日(周三) 晚上6:49 收件人: "[hidden email]"<[hidden email]&gt;; 主题: 如何用SQL表达对设备离在线监控 Hi all, 假设我们有很多设备,设备正常工作期间会定时发送心跳到服务器。如果某个设备在超过N分钟的时间内,没有发送任何心跳到服务器,服务器会认为设备已经离线。直到下一次心跳,才判定设备为在线。 需求:在判定设备离线时,产出一条设备离线消息;在设备经过一次离线后,第一次心跳时,产出一条设备上线的消息; 假设设备上报的消息包含当前时间(ts)和设备id(deviceid): 1575456144,dev1 1575456146,dev2 1575456147,dev1 …. 产出的离在线消息分别格式如下(第一列为设备离在线时间): 1575456158,dev1,offline 1575456169,dev2,online 能否用一条SQL来定义这个作业呢? 谢谢! 袁尤军 |
存在查询
在 2019/12/5 下午4:06,“Yuan,Youjun”<[hidden email]> 写入: 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 <[hidden email]> 发送时间: Thursday, December 5, 2019 2:43 PM 收件人: user-zh <[hidden email]> 主题: 回复: 回复: 回复:如何用SQL表达对设备离在线监控 可以考虑用flink cep,应该可以解决你的问题。 ------------------ 原始邮件 ------------------ 发件人: "Djeng Lee"<[hidden email]>; 发送时间: 2019年12月5日(星期四) 下午2:40 收件人: "[hidden email]"<[hidden email]>; 主题: Re: 回复: 回复:如何用SQL表达对设备离在线监控 上线时间,前n窗口count == 0 , 后n窗口count > 1。说明是上线。由此得出上线时间. 离线时间,前n 窗口count>=1, 后n窗口count==0,说明下线,由此可得下线时间。 前n后n都>1 作为心跳维持。 在 2019/12/5 下午2:06,“Yuan,Youjun”<[hidden email]> 写入: 谢谢你的回复。 这种方案比较有意思,只是还不能区分设备第一次心跳产生的count=1的消息(上线),和设备最后一次心跳产生的count=1的消息(下线) -----邮件原件----- 发件人: 1193216154 <[hidden email]> 发送时间: Wednesday, December 4, 2019 9:39 PM 收件人: user-zh <[hidden email]> 主题: 回复:如何用SQL表达对设备离在线监控 设定一个滑动窗口,窗口大小大于等于2n,滑动间隔大于等于n,若一次窗口结算,count 大于等于2,则在线,否则下线 ---原始邮件--- 发件人: "Yuan,Youjun"<[hidden email]&gt; 发送时间: 2019年12月4日(周三) 晚上6:49 收件人: "[hidden email]"<[hidden email]&gt;; 主题: 如何用SQL表达对设备离在线监控 Hi all, 假设我们有很多设备,设备正常工作期间会定时发送心跳到服务器。如果某个设备在超过N分钟的时间内,没有发送任何心跳到服务器,服务器会认为设备已经离线。直到下一次心跳,才判定设备为在线。 需求:在判定设备离线时,产出一条设备离线消息;在设备经过一次离线后,第一次心跳时,产出一条设备上线的消息; 假设设备上报的消息包含当前时间(ts)和设备id(deviceid): 1575456144,dev1 1575456146,dev2 1575456147,dev1 …. 产出的离在线消息分别格式如下(第一列为设备离在线时间): 1575456158,dev1,offline 1575456169,dev2,online 能否用一条SQL来定义这个作业呢? 谢谢! 袁尤军 |
用 udagg 应该能比较完美的解决你的问题 ^.^
On 12/6/19, Djeng Lee <[hidden email]> wrote: > 存在查询 > > 在 2019/12/5 下午4:06,“Yuan,Youjun”<[hidden email]> 写入: > > 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 <[hidden email]> > 发送时间: Thursday, December 5, 2019 2:43 PM > 收件人: user-zh <[hidden email]> > 主题: 回复: 回复: 回复:如何用SQL表达对设备离在线监控 > > 可以考虑用flink cep,应该可以解决你的问题。 > > > ------------------ 原始邮件 ------------------ > 发件人: "Djeng Lee"<[hidden email]>; > 发送时间: 2019年12月5日(星期四) 下午2:40 > 收件人: "[hidden email]"<[hidden email]>; > > 主题: Re: 回复: 回复:如何用SQL表达对设备离在线监控 > > > > 上线时间,前n窗口count == 0 , 后n窗口count > 1。说明是上线。由此得出上线时间. > 离线时间,前n 窗口count>=1, 后n窗口count==0,说明下线,由此可得下线时间。 > 前n后n都>1 作为心跳维持。 > > > > 在 2019/12/5 下午2:06,“Yuan,Youjun”<[hidden email]> 写入: > > 谢谢你的回复。 > > 这种方案比较有意思,只是还不能区分设备第一次心跳产生的count=1的消息(上线),和设备最后一次心跳产生的count=1的消息(下线) > > -----邮件原件----- > 发件人: 1193216154 <[hidden email]> > 发送时间: Wednesday, December 4, 2019 9:39 PM > 收件人: user-zh <[hidden email]> > 主题: 回复:如何用SQL表达对设备离在线监控 > > 设定一个滑动窗口,窗口大小大于等于2n,滑动间隔大于等于n,若一次窗口结算,count > 大于等于2,则在线,否则下线 > > ---原始邮件--- > 发件人: "Yuan,Youjun"<[hidden email]&gt; > 发送时间: 2019年12月4日(周三) 晚上6:49 > 收件人: > "[hidden email]"<[hidden email]&gt;; > 主题: 如何用SQL表达对设备离在线监控 > > > Hi all, > > > 假设我们有很多设备,设备正常工作期间会定时发送心跳到服务器。如果某个设备在超过N分钟的时间内,没有发送任何心跳到服务器,服务器会认为设备已经离线。直到下一次心跳,才判定设备为在线。 > > 需求:在判定设备离线时,产出一条设备离线消息;在设备经过一次离线后,第一次心跳时,产出一条设备上线的消息; > 假设设备上报的消息包含当前时间(ts)和设备id(deviceid): > 1575456144,dev1 > 1575456146,dev2 > 1575456147,dev1 > …. > > 产出的离在线消息分别格式如下(第一列为设备离在线时间): > 1575456158,dev1,offline > 1575456169,dev2,online > 能否用一条SQL来定义这个作业呢? > > 谢谢! > 袁尤军 > > > |
Free forum by Nabble | Edit this page |