我有一个表hostapp_apps和一个具有列的视图host_app_usercount,如下所示
hostapp_apps
======================================================
host_app_id || app_type || app_id || platform || user_count
host_app_usercount
======================================================
host_id || TotalUsers
基本上我试图通过使用以下查询来获取app_id,平台,max(usercount),TotalUsers
SELECT hostapp_apps.app_type as app_type, host_app_usercount.TotalUsers as Total,
hostapp_apps.app_id as app_id, MAX(hostapp_apps.user_count) AS sum,
ROUND((MAX(hostapp_apps.user_count)/(host_app_usercount.TotalUsers) * 100 ),2) AS percentage
FROM pg_datascience.hostapp_apps LEFT OUTER JOIN
host_app_usercount
ON (host_app_usercount.host_id=hostapp_apps.host_app_id)
GROUP BY hostapp_apps.app_id, host_app_usercount.TotalUsers
HAVING sum >= 10000
ORDER BY percentage desc
limit 0, 50;
但是获取数据花费的时间太长。有没有办法做到这一点而没有左外部联接。我也尝试了另一种不使用join的方法
SELECT hostapp_apps.app_type as app_type,
(select TotalUsers
from host_app_usercount
where host_app_usercount.host_id=hostapp_apps.host_app_id
) as Total,
hostapp_apps.app_id as app_id, MAX(hostapp_apps.user_count) AS sum,
ROUND((MAX(hostapp_apps.user_count)/(Total) * 100 ),2) AS percentage
FROM pg_datascience.hostapp_apps
GROUP BY hostapp_apps.app_id, Total
HAVING sum >= 10000
ORDER BY percentage desc
limit 0, 50;
但是,当我尝试这样做时,我在字段列表中得到了未知列“总计”。非常感谢任何帮助,因为这会影响我的任务进度:(预先感谢
最佳答案
首先,您的第一个查询无效。您的列app_type
不是聚合的,也不在Group By
子句中。该查询将给您一个错误。
其次,这可能是索引问题。您需要查看索引并可能创建它们。我将在以下各项上创建一个:
host_app_usercount.host_id
hostapp_apps.host_app_id
那应该有帮助。