共计 1562 个字符，预计需要花费 4 分钟才能阅读完成。
接上一篇文章【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 | 1546744440000
lzc | 6 | 1546744320000 | 1546744440000
lza | 6 | 1546744320000 | 1546744440000
lzb | 6 | 1546744320000 | 1546744440000
lzb | 3 | 1546744440000 | 1546744560000
lzc | 3 | 1546744440000 | 1546744560000
lza | 3 | 1546744440000 | 1546744560000
lze | 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;
