sql - 我如何在 SQL 中执行此操作？

Question

今天，我需要你的帮助。

我有一个统计网站，我从 Game Webservices 获取数据。我想实现一个新功能，但我不知道如何实现。

我想猜猜玩家的连接时间。

我有一个脚本，它每小时收集一次数据并将这些数据存储在一个表中。想象一下，我有一个表:player_id、score 和小时(整数，只是 H)，以及一个月中的第几天。

然后，例如，如果 17 小时和 18 小时之间的分数不同，则玩家已连接到他的帐户。

为简化起见，假设我有一张表，每天的日期从 1 到 31，小时从 0 到 23。

在月底，我需要执行一个查询来计算每小时，玩家在这一小时内连接的天数。

Example :

0 => 31     The player has been connected between 23 and 0  : every days
1 => 3      The player has been connected between 0 and 1  : 3 days a month
2 => 5      The player has been connected between 1 and 2  : 5 days a month
3 => 10     The player has been connected between 3 and 4  : 10 days a month
...
23 => 4

我想我可以从第 1 天的第 0 天到第 31 天的第 23 天按天数和小时数以及 player_id 进行排序并使用 CASE 进行第一个 SELECT:

SELECT 
    table.*,  
   (CASE WHEN ACTUAL_ROW.score!=PREVIOUS_ROW.score THEN 1 ELSE 0) AS active
FROM table

如果播放器已连接，则了解每一行。然后对每个小时执行 GROUP BY 和 SUM 很简单。但我不知道如何将前一行与实际进行比较

您有任何想法或提示如何做到这一点吗？ PL/SQL 更适合做这个吗？

注意:我使用的是 PostGreSQL

谢谢

Answer 1

您可以使用 LAG 窗口函数访问表格的前一行。

尝试使用类似的东西

SELECT player_id, count(CASE WHEN score > prev_score THEN 1 END)
FROM(
SELECT player_id, score, mm, hh, LAG(score) OVER (ORDER BY mm,hh) as prev_score
FROM your_table)
GROUP BY player_id

其他建议 - 存储完整的时间戳而不是日期和小时字段。您始终可以使用函数从时间戳中获取日期和时间。

窗口函数手册:one , two