我有一个表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
  


那应该有帮助。

10-05 21:06
查看更多