【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 | 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;