blocks|key|1182028|text|您可以创建一个包含两列的临时表，即'old+count‘和'new+count’。使用从SELECT查询中获得的值填充该表。然后，从临时表中检索这些值以计算百分比差异并删除临时表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1182029|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

You could create a temporary table with two columns, say 'old count' and 'new count'. Populate the table with the values you get from your SELECT queries. 
Then, retrieve the values from the temp table to calculate the percentage difference and delete the temp table.

blocks|key|830576|text|你可以尝试用下面的方法计算最近7天的天数，然后计算7天前的所有天数，然后计算百分比|type|unstyled|depth|inlineStyleRanges|entityRanges|data|830577|++++select+max(last7days_count)+as+last7days_count,
max(before7days_count)+as+before7days_count,
++++((max(before7days_count)*1.00)/max(last7days_count))*100.00+as+percentage+from+
++++++++(
++++++++SELECT+COUNT(DISTINCT+account_type)+as+last7days_count,+0+as+before7days_count
++++++++FROM+account
++++++++WHERE+date_created+>+NOW()+-+INTERVAL+7+DAY

++++++++union+all

++++++++SELECT+0+as+last7days_count+COUNT(DISTINCT+account_type)+as+before7days_count
++++++++FROM+account
++++++++WHERE+date_created+<+NOW()+-+INTERVAL+7+DAY
++++++++)+as+T|code-block|syntax|javascript|830578|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

you can try below way calculate last 7days count and then calculate before 7days all then calculate percentage

<pre><code> select max(last7days_count) as last7days_count,
max(before7days_count) as before7days_count,
 ((max(before7days_count)*1.00)/max(last7days_count))*100.00 as percentage from 
 (
 SELECT COUNT(DISTINCT account_type) as last7days_count, 0 as before7days_count
 FROM account
 WHERE date_created &gt; NOW() - INTERVAL 7 DAY

 union all

 SELECT 0 as last7days_count COUNT(DISTINCT account_type) as before7days_count
 FROM account
 WHERE date_created &lt; NOW() - INTERVAL 7 DAY
 ) as T
</code></pre>

blocks|key|522790|text|为了在一个查询中运行所有功能，您可以考虑下一个查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|522791|SELECT
++/*+Count+for+previous+period.+*/
++beforeCount,
++/*+Count+for+current+period.+*/
++afterCount,
++/*+Simple+math,+just+calculating+percentage.+*/
++(beforeCount+*+100)+/+afterCount+AS+percent
FROM+(
++SELECT
++/*+Select+count+for+previous+period.+*/
++(
++++SELECT+COUNT(DISTINCT+account_type)
++++FROM+account
++++WHERE+date_created+BETWEEN+NOW()+-+INTERVAL+14+DAY+AND+NOW()+-+INTERVAL+7+DAY
++)+AS+beforeCount,
++/*+Select+count+for+current+period.+*/
++(
++++SELECT+COUNT(DISTINCT+account_type)
++++FROM+account
++++WHERE+date_created+>+NOW()+-+INTERVAL+7+DAY
++)+AS+afterCount
)+AS+tmp|code-block|syntax|javascript|522792|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

With purpose to run all in one query you may consider next query:

<pre><code>SELECT
 /* Count for previous period. */
 beforeCount,
 /* Count for current period. */
 afterCount,
 /* Simple math, just calculating percentage. */
 (beforeCount * 100) / afterCount AS percent
FROM (
 SELECT
 /* Select count for previous period. */
 (
 SELECT COUNT(DISTINCT account_type)
 FROM account
 WHERE date_created BETWEEN NOW() - INTERVAL 14 DAY AND NOW() - INTERVAL 7 DAY
 ) AS beforeCount,
 /* Select count for current period. */
 (
 SELECT COUNT(DISTINCT account_type)
 FROM account
 WHERE date_created &gt; NOW() - INTERVAL 7 DAY
 ) AS afterCount
) AS tmp
</code></pre>

blocks|key|1271766|text|条件聚合可能会起作用。使用CASE只计算新帐户，使用另一个只计算旧帐户。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1271767|SELECT+count(DISINCT+CASE
+++++++++++++++++++++++WHEN+date_created+>+NOW()+-+INTERVAL+7+DAY+THEN
+++++++++++++++++++++++++account_type
+++++++++++++++++++++END)
+++++++/
+++++++count(DISTINCT+CASE
++++++++++++++++++++++++WHEN+date_created+<=+NOW()+-+INTERVAL+7+DAY+THEN
++++++++++++++++++++++++++account_type
++++++++++++++++++++++END)
+++++++*+100+increase
+++++++FROM+account;|code-block|syntax|javascript|1271768|entityMap^0|D|4|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Conditional aggregation might work. Use a <code>CASE</code> to only count the new and another to only count the old accounts.

<pre><code>SELECT count(DISINCT CASE
 WHEN date_created &gt; NOW() - INTERVAL 7 DAY THEN
 account_type
 END)
 /
 count(DISTINCT CASE
 WHEN date_created &lt;= NOW() - INTERVAL 7 DAY THEN
 account_type
 END)
 * 100 increase
 FROM account;
</code></pre>

blocks|key|830583|text|有了临时表，你可以这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|830584|++++create+temporary+table+storeCount+IF+NOT+EXISTS+(
++++++++oldCount+INT(10)+not+null,
++++++++newCount+INT(10)+not+null
++++);

++++insert+into+percentage+(oldCount,newCount)+
++++values
++++(SELECT+COUNT(DISTINCT+acc1.account_type)FROM+account+acc1,+SELECT+COUNT(DISTINCT+acc2.account_type)
++++++++FROM+account+acc2+WHERE+acc2.date_created+>+NOW()+-+INTERVAL+7+DAY);

++++select+((newCount/oldCount)*100)+as+percentage+from+storeCount;

++++drop+temporary+table+IF+EXISTS+storeCount;|code-block|syntax|javascript|830585|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

With Temporary table you can do in like :

<pre><code> create temporary table storeCount IF NOT EXISTS (
 oldCount INT(10) not null,
 newCount INT(10) not null
 );

 insert into percentage (oldCount,newCount) 
 values
 (SELECT COUNT(DISTINCT acc1.account_type)FROM account acc1, SELECT COUNT(DISTINCT acc2.account_type)
 FROM account acc2 WHERE acc2.date_created &gt; NOW() - INTERVAL 7 DAY);

 select ((newCount/oldCount)*100) as percentage from storeCount;

 drop temporary table IF EXISTS storeCount;
</code></pre>

blocks|key|522802|text|假设每个帐户有一行，则不需要distinct。我猜你想要：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|522803|SELECT+(SUM(date_created+>=+CURDATE()+-+INTERVAL+7+DAY)+*+100/+
++++++++SUM(date_created+>+CURDATE()+-+INTERVAL+7+DAY)
+++++++)+as+percent_increase
FROM+account|code-block|syntax|javascript|522804|entityMap^0|E|8|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Assuming you have one row per account, you don't need <code>distinct</code>. I am guessing you want:

<pre><code>SELECT (SUM(date_created &gt;= CURDATE() - INTERVAL 7 DAY) * 100/ 
 SUM(date_created &gt; CURDATE() - INTERVAL 7 DAY)
 ) as percent_increase
FROM account
</code></pre>

I am trying to find the percentage increase in the last 7 days but I am a little stuck. Currently in the SQL query I have created, you can get the total of the new accounts in the last 7 days. But now, how can I improve to be able to return the result in percentage?

Here is the SQL query done so far.

Thanks

<pre><code>SELECT COUNT(DISTINCT account_type)
FROM account
WHERE date_created &gt; NOW() - INTERVAL 7 DAY
</code></pre>

How to convert SQL result into percentage

我试图找出过去7天的百分比增长，但我有点卡住了。目前，在我创建的SQL查询中，您可以获得最近7天的新帐户总数。但是现在，我如何改进才能以百分比返回结果呢？这是到目前为止完成的SQL查询。谢谢SELECT COUNT(DISTINCT account_type)FROM accountWHERE date_created...

问如何将SQL结果转换为百分比
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将SQL结果转换为百分比EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将SQL结果转换为百分比
EN