【kafka KSQL】游戏日志统计分析（2）

接上一篇文章【kafka KSQL】游戏日志统计分析（1），展示一下KSQL WINDOW 功能的使用。测试用日志数据：{“cost”:7, “epoch”:1512342568296,“gameId”:“2017-12-04_07:09:28_高手2区_500_015_185175”,“gameType”:“situan”,“gamers”: [{“balance”:4405682,“delta”:-60,“username”:“lza”}, {“balance”:69532,“delta”:-60,“username”:“lzb”}, {“balance”:972120,“delta”:-60,“username”:“lzc”}, {“balance”:23129,“delta”:180,“username”:“lze”}],“reason”:“game”}KSQL三种Window统计每2分钟内完成对局大于等于3局的玩家根据时间窗口（Tumbling window）建立table：CREATE TABLE users_per_minute AS \ SELECT username, COUNT() AS game_count, SUM(delta) AS delta_sum, SUM(tax) AS tax_sum , WINDOWSTART() AS win_start, WINDOWEND() AS win_end \ FROM USER_SCORE_EVENT \ WINDOW TUMBLING (SIZE 2 MINUTE) \ WHERE reason = ‘game’ \ GROUP BY username;过滤出game_count大于3局的玩家：SELECT username, game_count, win_start, win_end FROM users_per_minute WHERE game_count >= 3;输出：lze | 6 | 1546744320000 | 1546744440000lzc | 6 | 1546744320000 | 1546744440000lza | 6 | 1546744320000 | 1546744440000lzb | 6 | 1546744320000 | 1546744440000lzb | 3 | 1546744440000 | 1546744560000lzc | 3 | 1546744440000 | 1546744560000lza | 3 | 1546744440000 | 1546744560000lze | 3 | 1546744440000 | 1546744560000统计曾在10分钟之内完成过3局牌局的玩家不限定某个特定的10分钟，只要在某个10分钟之内完成了即可。创建HOPPING WINDOW时间窗口Table：CREATE TABLE users_hopping_10_minute AS \ SELECT username, COUNT() AS game_count, SUM(delta) AS delta_sum, SUM(tax) AS tax_sum , TIMESTAMPTOSTRING(WindowStart(), ‘yyyy-MM-dd HH:mm:ss’) AS win_start, TIMESTAMPTOSTRING(WindowEnd(), ‘yyyy-MM-dd HH:mm:ss’) AS win_end \ FROM USER_SCORE_EVENT \ WINDOW HOPPING (SIZE 10 MINUTE, ADVANCE BY 30 SECONDS) \ WHERE reason = ‘game’ \ GROUP BY username;过滤出game_count大于等于3的玩家SELECT username \FROM users_hopping_10_minute \WHERE game_count >= 3 \GROUP BY username;