计算第二天再次玩的百分比 [英] Calculating percentage played again the next day

问题描述

我正在尝试确定2021年1月7日玩过国际象棋的球员，第二天(1月8日)又玩了几率?

I am trying to determine the players who played 'Chess' on Jan 7th 2021, what percentage played again the next day (Jan 8th)?

game_table
| column       | data_type|
|:----         |    -----:|
| user_id      |    BIGINT|
| date         |    STRING|
| game_name    |    STRING|
| session_count|    BIGINT|

这是我的代码，但我认为这是不正确的:

Here is my code but I don't think it is correct:

with t1 as
(select game_name, count(*) as count_jan7 
from instant_game_sessions 
where date = '2021-01-07' and game_name = 'Chess'
group by 1).

t2 as
(select games, count(*) as count_jan7_and_jan8
from instant_game_sessions 
where date = '2021-01-07' and game_name = 'Chess' and date= '2021-01-08'
group by 1)

select cast(count_jan7_and_jan8 as numeric)/count_jan7 *100
from t1 
join t2 on t1.game_name = t2.game_name 

推荐答案

假定表定义具有此核心:

Assuming the table definition has this solid core:

CREATE TABLE game_table (
  user_id   bigint NOT NULL
, date      date   NOT NULL  -- date, not text!
, game_name text   NOT NULL
, UNIQUE (date, game_name, user_id)  -- !
);

假设您的意思是同一位玩家第二天玩同一游戏:

And assuming you meant the same player playing the same game next day:

SELECT round(ct_day2 * 100.0 / ct_day1, 2) AS repeat_percentage
FROM  (
   SELECT count(*) AS ct_day1
        , count(d2.user_id) AS ct_day2
   FROM   instant_game_sessions d1
   LEFT   JOIN instant_game_sessions d2 ON (d2.user_id, d2.game_name, d2.date)
                                         = (d1.user_id, d1.game_name, d1.date + 1)
   WHERE  d1.date = '2021-01-07'
   AND    d1.game_name = 'Chess'
   ) sub;

UNIQUE 约束可确保第二天只有一场比赛.因此， count(*)是第一天的正确计数，而 count(d2.user_id)是第二天的正确计数.

The UNIQUE constraint makes sure there can only be a single match on the next day. So count(*) is the correct count for day 1, and count(d2.user_id) for day 2. The rest is obvious.

UNIQUE 约束(列名称按此顺序！)也为查询提供了理想的索引.参见:

The UNIQUE constraint (with column names in this order!) also provides the perfect index for the query. See:

• Postgres UNIQUE约束是否隐含索引?

请注意，数字常量 100.0 默认自动为数字，因此我们无需添加任何显式类型转换.相关:

Note that the numeric constant 100.0 defaults to numeric automatically, so we need not add any explicit type cast. Related:

• 不要四舍五入插入整数列时无提示输入

这篇关于计算第二天再次玩的百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！