计算第二天再次玩的百分比

本文介绍了计算第二天再次玩的百分比的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试确定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:

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