我正在尝试确定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:
- 不要四舍五入插入整数列时无提示输入
更多推荐
计算第二天再次玩的百分比
发布评论