As shown, thank the great god!

CodePudding user response:

Calculate the two data division by
Select (select sum (a) the from xx)/(select sum (b) the from xx) from dual;
Formatted as two decimal places a decimal=2
Select the convert (4545.1366, decimal);
For reference only, you write it will be a little bit more deep impression,
Does not provide a complete SQL

CodePudding user response:

This is a back number
The select a.f date as actfdate, b. date as backfdate, count (a.u id) as backCnt from
(SELECT fdate, uid from the table where fdate & gt;=startA and fdate & lt; EndA) as a,
(SELECT fdate, uid from the table where fdate & gt;=startB and fdate & lt; EndB) as b
Where a.u id=b.u id and a.f date & lt; B. fdate group by actfdate, backfdate

This is the active
The select fdate, count (uid) as actCnt from table where fdate & gt;=startA and fdate & lt; EndA group by fdate

A connection between the two tables and to lead the

As well as table structure, uid why with string? General int or bigint, isn't it? Then start time, one day before 8 w more seconds, why use bigint, too, start times and dates, why use string? Don't use int?

CodePudding user response:

Select *,
Concat (round (100 * the retained users/active users, 2), the '%') retention rate of the next day,
Concat (round (100 * 3 retained users/active users, 2), the '%') three days retention rates,
Concat (round (100 * 7 retained users/active users, 2), the '%') seven days retention rate
The from (
The select a. d. ayno date,
Count (distinct a.u id) active users,
Count (distinct b.u id) retained users, the next day,
Count (distinct c.u id) three days keep users,
Count (distinct d.u id) 7, retained users
The from act_user_info a
Left the join act_user_info b on a.u id=b.u id and b.d ayno=a. d. ayno + 1
Left the join act_user_info c on a.u id=c.u id and c.d ayno=a. d. ayno + 3
Left the join act_user_info d on a.u id=d.u id and d.d ayno=a. d. ayno + 7
Group by a. d. ayno
) p;