如何计算百分比? [英] How to calculate percent?

问题描述

您能帮我计算付款的用户百分比吗? 我有两个桌子:

Could you help me to calculate percent of users, which made payments? I've got two tables:

activity
user_id  login_time
201      01.01.2017
202      01.01.2017
255      04.01.2017
255      05.01.2017
256      05.01.2017
260      15.03.2017

2

payments
user_id  payment_date
200      01.01.2017
202      01.01.2017
255      05.01.2017

我尝试使用此查询，但计算出错误的百分比:

I try to use this query, but it calculates wrong percent:

SELECT activity.login_time, (select COUNT(distinct payments.user_id) 
from payments where payments.payment_time between '2017-01-01' and 
'2017-01-05') / COUNT(distinct activity.user_id) * 100
AS percent
FROM payments INNER JOIN activity ON  
activity.user_id = payments.user_id and activity.login_time between 
'2017-01-01' and '2017-01-05'
GROUP BY activity.login_time;

我需要一个结果

01.01.2017  100 % 
02.01.2017  0% 
03.01.2017  0% 
04.01.2017  0% 
05.01.2017 - 50% 

推荐答案

如果您想要付款的用户与有活动的用户的比例，只需分别汇总每个表即可:

If you want the ratio of users who have made payments to those with activity, just summarize each table individually:

select p.cnt / a.cnt
from (select count(distinct user_id) as cnt from activity a) a cross join
     (select count(distinct user_id) as cnt from payment) p;

您需要一个表格，其中包含所有日期.那是最大的问题.

You need a table with all dates in the range. That is the biggest problem.

那么我会推荐:

SELECT d.dte,
       ( ( SELECT COUNT(DISTINCT p.user_id)
           FROM payments p
           WHERE p.payment_date >= d.dte and p.payment_date < d.dte + INTERVAL 1 DAY
          ) /
          NULLIF( (SELECT COUNT(DISTINCT a.user_id)
                   FROM activity a
                   WHERE a.login_time >= d.dte and p.login_time < d.dte + INTERVAL 1 DAY
                  ), 0
                 ) as ratio
FROM (SELECT date('2017-01-01') dte UNION ALL
      SELECT date('2017-01-02') dte UNION ALL
      SELECT date('2017-01-03') dte UNION ALL
      SELECT date('2017-01-04') dte UNION ALL
      SELECT date('2017-01-05') dte 
     ) d;

注意:

• 在没有活动的日子返回NULL.这对我来说比0更有意义.
• 这在适用于日期和日期/时间值的日期上使用逻辑.
• 日期逻辑可以利用索引，这对于这种类型的查询可能很重要.
• 我不建议使用LEFT JOIN.这将使数据成倍增加，从而使查询昂贵.

• This returns NULL on days where there is no activity. That makes more sense to me than 0.
• This uses logic on the dates that works for both dates and date/time values.
• The logic for dates can make use of an index, which can be important for this type of query.
• I don't recommend using LEFT JOINs. That will multiply the data which can make the query expensive.

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