统计查询示例

本示例展示如何使用 Sleet ORM 进行复杂的数据统计和分析，包括玩家活跃度、经济数据和排行榜系统。

玩家活跃度统计

在线时长统计

lua

-- server/stats/player_activity.lua
local sl = Sleet
local s = require 'server.schema'
local db = sl.connect()

local PlayerActivityStats = {}

-- 获取玩家总在线时长排行
function PlayerActivityStats.GetPlaytimeLeaderboard(limit)
    limit = limit or 10
    
    return db.select({
        s.players.name,
        s.players.playtime,
        s.players.level,
        sl.sql('RANK() OVER (ORDER BY playtime DESC) as rank')
    })
        .from(s.players)
        .where(sl.gt(s.players.playtime, 0))
        .orderBy(s.players.playtime, 'desc')
        .limit(limit)
        .execute()
end

-- 获取最近活跃玩家
function PlayerActivityStats.GetRecentActivePlayers(days)
    days = days or 7
    local cutoffDate = os.date('%Y-%m-%d %H:%M:%S', os.time() - (days * 24 * 3600))
    
    return db.select({
        s.players.id,
        s.players.name,
        s.players.last_seen,
        sl.sql('TIMESTAMPDIFF(HOUR, last_seen, NOW()) as hours_ago')
    })
        .from(s.players)
        .where(sl.gte(s.players.last_seen, cutoffDate))
        .orderBy(s.players.last_seen, 'desc')
        .execute()
end

-- 按时间段统计玩家活跃度
function PlayerActivityStats.GetActivityByHour()
    return db.select({
        sl.sql('HOUR(last_seen) as hour'),
        sl.sql('COUNT(*) as player_count')
    })
        .from(s.players)
        .where(sl.gte(s.players.last_seen, sl.sql('CURDATE()')))
        .groupBy(sl.sql('HOUR(last_seen)'))
        .orderBy(sl.sql('hour'))
        .execute()
end

return PlayerActivityStats

新用户注册统计

lua

-- 每日新用户注册统计
function PlayerActivityStats.GetDailyRegistrations(days)
    days = days or 30
    
    return db.select({
        sl.sql('DATE(created_at) as date'),
        sl.sql('COUNT(*) as new_players')
    })
        .from(s.players)
        .where(sl.gte(s.players.created_at, sl.sql('DATE_SUB(NOW(), INTERVAL ? DAY)', { days })))
        .groupBy(sl.sql('DATE(created_at)'))
        .orderBy(sl.sql('date'))
        .execute()
end

-- 月度注册趋势
function PlayerActivityStats.GetMonthlyRegistrations()
    return db.select({
        sl.sql('YEAR(created_at) as year'),
        sl.sql('MONTH(created_at) as month'),
        sl.sql('COUNT(*) as new_players'),
        sl.sql('MONTHNAME(created_at) as month_name')
    })
        .from(s.players)
        .where(sl.gte(s.players.created_at, sl.sql('DATE_SUB(NOW(), INTERVAL 12 MONTH)')))
        .groupBy(sl.sql('YEAR(created_at), MONTH(created_at)'))
        .orderBy(sl.sql('year, month'))
        .execute()
end

经济数据分析

财富分布统计

lua

-- server/stats/economy.lua
local EconomyStats = {}

-- 玩家财富排行榜
function EconomyStats.GetWealthLeaderboard(limit)
    limit = limit or 20
    
    return db.select({
        s.players.name,
        s.players.money,
        s.players.bank,
        sl.sql('(money + bank) as total_wealth'),
        sl.sql('RANK() OVER (ORDER BY (money + bank) DESC) as rank')
    })
        .from(s.players)
        .orderBy(sl.sql('total_wealth'), 'desc')
        .limit(limit)
        .execute()
end

-- 财富分布区间统计
function EconomyStats.GetWealthDistribution()
    return db.select({
        sl.sql([[
            CASE 
                WHEN (money + bank) < 10000 THEN 'Poor (< $10K)'
                WHEN (money + bank) < 50000 THEN 'Lower Middle ($10K-$50K)'
                WHEN (money + bank) < 100000 THEN 'Middle ($50K-$100K)'
                WHEN (money + bank) < 500000 THEN 'Upper Middle ($100K-$500K)'
                WHEN (money + bank) < 1000000 THEN 'Rich ($500K-$1M)'
                ELSE 'Very Rich (> $1M)'
            END as wealth_tier
        ]]),
        sl.sql('COUNT(*) as player_count'),
        sl.sql('AVG(money + bank) as avg_wealth'),
        sl.sql('MIN(money + bank) as min_wealth'),
        sl.sql('MAX(money + bank) as max_wealth')
    })
        .from(s.players)
        .groupBy(sl.sql('wealth_tier'))
        .orderBy(sl.sql('min_wealth'))
        .execute()
end

-- 经济指标概览
function EconomyStats.GetEconomicOverview()
    return db.select({
        sl.sql('COUNT(*) as total_players'),
        sl.sql('SUM(money) as total_cash'),
        sl.sql('SUM(bank) as total_bank'),
        sl.sql('SUM(money + bank) as total_wealth'),
        sl.sql('AVG(money + bank) as avg_wealth'),
        sl.sql('MAX(money + bank) as max_wealth'),
        sl.sql('MIN(money + bank) as min_wealth')
    })
        .from(s.players)
        .execute()[1]
end

交易数据分析

lua

-- 商品销售统计
function EconomyStats.GetSalesStats(days)
    days = days or 7
    
    return db.select({
        s.items.label,
        s.categories.label,
        sl.sql('SUM(quantity) as total_sold'),
        sl.sql('SUM(total_price) as total_revenue'),
        sl.sql('AVG(unit_price) as avg_price'),
        sl.sql('COUNT(DISTINCT player_id) as unique_buyers')
    })
        .from(s.transactions)
        .leftJoin(s.items, sl.eq(s.transactions.item_id, s.items.id))
        .leftJoin(s.categories, sl.eq(s.items.category_id, s.categories.id))
        .where(sl.and_(
            sl.eq(s.transactions.transaction_type, 'buy'),
            sl.gte(s.transactions.created_at, sl.sql('DATE_SUB(NOW(), INTERVAL ? DAY)', { days }))
        ))
        .groupBy(s.items.id)
        .orderBy(sl.sql('total_revenue'), 'desc')
        .execute()
end

-- 每日交易量统计
function EconomyStats.GetDailyTransactionVolume(days)
    days = days or 30
    
    return db.select({
        sl.sql('DATE(created_at) as date'),
        sl.sql('COUNT(*) as transaction_count'),
        sl.sql('SUM(total_price) as daily_revenue'),
        sl.sql('COUNT(DISTINCT player_id) as active_buyers')
    })
        .from(s.transactions)
        .where(sl.and_(
            sl.eq(s.transactions.transaction_type, 'buy'),
            sl.gte(s.transactions.created_at, sl.sql('DATE_SUB(NOW(), INTERVAL ? DAY)', { days }))
        ))
        .groupBy(sl.sql('DATE(created_at)'))
        .orderBy(sl.sql('date'))
        .execute()
end

排行榜系统

多维度排行榜

lua

-- server/stats/leaderboards.lua
local LeaderboardStats = {}

-- 综合排行榜
function LeaderboardStats.GetOverallRanking(limit)
    limit = limit or 50
    
    return db.select({
        s.players.name,
        s.players.level,
        s.players.experience,
        s.players.playtime,
        sl.sql('(money + bank) as wealth'),
        -- 综合得分计算
        sl.sql([[
            (
                (level * 100) + 
                (experience / 1000) + 
                (playtime / 60) + 
                ((money + bank) / 10000)
            ) as overall_score
        ]]),
        sl.sql('RANK() OVER (ORDER BY overall_score DESC) as rank')
    })
        .from(s.players)
        .where(sl.gt(s.players.playtime, 60)) -- 至少1小时游戏时间
        .orderBy(sl.sql('overall_score'), 'desc')
        .limit(limit)
        .execute()
end

-- 等级排行榜
function LeaderboardStats.GetLevelRanking(limit)
    limit = limit or 20
    
    return db.select({
        s.players.name,
        s.players.level,
        s.players.experience,
        sl.sql('RANK() OVER (ORDER BY level DESC, experience DESC) as rank')
    })
        .from(s.players)
        .orderBy(s.players.level, 'desc')
        .orderBy(s.players.experience, 'desc')
        .limit(limit)
        .execute()
end

-- 成就排行榜
function LeaderboardStats.GetAchievementRanking(limit)
    limit = limit or 15
    
    return db.select({
        s.players.name,
        s.players.level,
        sl.sql('COUNT(pa.id) as achievement_count'),
        sl.sql('RANK() OVER (ORDER BY achievement_count DESC) as rank')
    })
        .from(s.players)
        .leftJoin(s.player_achievements, sl.eq(s.players.id, s.player_achievements.player_id))
        .groupBy(s.players.id)
        .orderBy(sl.sql('achievement_count'), 'desc')
        .limit(limit)
        .execute()
end

return LeaderboardStats

分类排行榜

lua

-- 按职业/角色类型排行
function LeaderboardStats.GetRankingByRole()
    return db.select({
        sl.sql("JSON_EXTRACT(metadata, '$.role') as role"),
        s.players.name,
        s.players.level,
        sl.sql('RANK() OVER (PARTITION BY JSON_EXTRACT(metadata, "$.role") ORDER BY level DESC) as role_rank')
    })
        .from(s.players)
        .where(sl.and_(
            sl.isNotNull(s.players.metadata),
            sl.like(s.players.metadata, '%"role"%')
        ))
        .orderBy(sl.sql('role, role_rank'))
        .execute()
end

-- 新手排行榜（低等级玩家）
function LeaderboardStats.GetNewbieRanking()
    return db.select({
        s.players.name,
        s.players.level,
        s.players.experience,
        s.players.playtime,
        sl.sql('RANK() OVER (ORDER BY experience DESC) as rank')
    })
        .from(s.players)
        .where(sl.lte(s.players.level, 10))
        .orderBy(s.players.experience, 'desc')
        .limit(20)
        .execute()
end

高级统计分析

玩家留存率分析

lua

-- server/stats/retention.lua
local RetentionStats = {}

-- 计算玩家留存率
function RetentionStats.GetRetentionRate(cohortDays)
    cohortDays = cohortDays or 7
    
    -- 获取指定时间段注册的玩家
    local cohortStart = os.date('%Y-%m-%d', os.time() - (cohortDays * 24 * 3600))
    local cohortEnd = os.date('%Y-%m-%d', os.time() - ((cohortDays - 1) * 24 * 3600))
    
    return db.select({
        sl.sql('COUNT(*) as cohort_size'),
        sl.sql([[
            COUNT(CASE 
                WHEN last_seen >= DATE_SUB(NOW(), INTERVAL 1 DAY) 
                THEN 1 
            END) as day1_retained
        ]]),
        sl.sql([[
            COUNT(CASE 
                WHEN last_seen >= DATE_SUB(NOW(), INTERVAL 7 DAY) 
                THEN 1 
            END) as day7_retained
        ]]),
        sl.sql([[
            COUNT(CASE 
                WHEN last_seen >= DATE_SUB(NOW(), INTERVAL 30 DAY) 
                THEN 1 
            END) as day30_retained
        ]])
    })
        .from(s.players)
        .where(sl.between(
            sl.sql('DATE(created_at)'), 
            cohortStart, 
            cohortEnd
        ))
        .execute()[1]
end

return RetentionStats

服务器性能统计

lua

-- 服务器负载统计
function RetentionStats.GetServerLoadStats()
    return db.select({
        sl.sql('COUNT(*) as total_players'),
        sl.sql('COUNT(CASE WHEN last_seen >= DATE_SUB(NOW(), INTERVAL 1 HOUR) THEN 1 END) as players_last_hour'),
        sl.sql('COUNT(CASE WHEN last_seen >= DATE_SUB(NOW(), INTERVAL 1 DAY) THEN 1 END) as players_last_day'),
        sl.sql('COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 1 DAY) THEN 1 END) as new_players_today'),
        sl.sql('AVG(playtime) as avg_playtime')
    })
        .from(s.players)
        .execute()[1]
end

实用工具函数

统计数据缓存

lua

-- server/stats/cache.lua
local StatsCache = {}
local cache = {}
local CACHE_TTL = 300 -- 5分钟缓存

function StatsCache.Get(key, generator)
    local now = os.time()
    
    if cache[key] and cache[key].expires > now then
        return cache[key].data
    end
    
    local data = generator()
    cache[key] = {
        data = data,
        expires = now + CACHE_TTL
    }
    
    return data
end

-- 使用示例
function GetCachedLeaderboard()
    return StatsCache.Get('wealth_leaderboard', function()
        return EconomyStats.GetWealthLeaderboard(10)
    end)
end

报表生成器

lua

-- 生成服务器日报
function GenerateDailyReport()
    local report = {
        date = os.date('%Y-%m-%d'),
        economy = EconomyStats.GetEconomicOverview(),
        activity = PlayerActivityStats.GetDailyRegistrations(1)[1] or { new_players = 0 },
        top_players = LeaderboardStats.GetLevelRanking(5),
        retention = RetentionStats.GetServerLoadStats()
    }
    
    -- 保存报告到文件或发送到管理员
    print('=== 每日服务器报告 ===')
    print('日期: ' .. report.date)
    print('总玩家数: ' .. (report.economy.total_players or 0))
    print('新注册: ' .. (report.activity.new_players or 0))
    print('总财富: $' .. string.format('%.0f', report.economy.total_wealth or 0))
    
    return report
end

性能优化建议

索引建议

sql

-- 为统计查询添加索引
CREATE INDEX idx_players_last_seen ON players(last_seen);
CREATE INDEX idx_players_level ON players(level);
CREATE INDEX idx_players_wealth ON players((money + bank));
CREATE INDEX idx_transactions_created_at ON transactions(created_at);
CREATE INDEX idx_transactions_type_date ON transactions(transaction_type, created_at);

查询优化

lua

-- 使用分页避免大结果集
function GetPaginatedLeaderboard(page, pageSize)
    page = page or 1
    pageSize = pageSize or 20
    local offset = (page - 1) * pageSize
    
    return db.select()
        .from(s.players)
        .orderBy(s.players.level, 'desc')
        .limit(pageSize)
        .offset(offset)
        .execute()
end

这些统计功能为服务器管理员提供了全面的数据洞察，帮助优化游戏体验和服务器运营。

统计查询示例 ​

玩家活跃度统计 ​

在线时长统计 ​

新用户注册统计 ​

经济数据分析 ​

财富分布统计 ​

交易数据分析 ​

排行榜系统 ​

多维度排行榜 ​

分类排行榜 ​

高级统计分析 ​

玩家留存率分析 ​

服务器性能统计 ​

实用工具函数 ​

统计数据缓存 ​

报表生成器 ​

性能优化建议 ​

索引建议 ​

查询优化 ​

统计查询示例

玩家活跃度统计

在线时长统计

新用户注册统计

经济数据分析

财富分布统计

交易数据分析

排行榜系统

多维度排行榜

分类排行榜

高级统计分析

玩家留存率分析

服务器性能统计

实用工具函数

统计数据缓存

报表生成器

性能优化建议

索引建议

查询优化