从外部查询中选择未分组列中的子查询

Question

我必须计算ARPU（收入/#用户），但我得到了这个错误：

子查询使用外部查询第7行的未分组列“usage_records.date”：WHERE created_at<=date_trunc('day'，usage_records.d...^)

null

SELECT 
    date_trunc('day', usage_records.date) AS day,
    SUM(usage_records.quantity_eur) as Revenue,
    ( SELECT 
        COUNT(users.id)
        FROM users 
        WHERE created_at <= date_trunc('day', usage_records.date)
    ) as users_count
    
FROM users 
INNER JOIN ownerships ON (ownerships.user_id = users.id) 
INNER JOIN profiles ON (profiles.id = ownerships.profile_id)
INNER JOIN usage_records ON (usage_records.profile_id = profiles.id)

GROUP BY DAY
ORDER BY DAY asc

Answer 1

您的子查询（对每一行执行）包含一个列，该列在group by中没有提到，但在聚合中没有涉及。这会产生错误，但您也可以对此值使用contional来重构查询

SELECT 
    date_trunc('day', usage_records.date) AS day,
    SUM(usage_records.quantity_eur) as Revenue,
    sum( case when  created_at <= date_trunc('day', usage_records.date) 
        AND users.id is not null
        then 1 else 0 end ) users_count
    
FROM users 
INNER JOIN ownerships ON (ownerships.user_id = users.id) 
INNER JOIN profiles ON (profiles.id = ownerships.profile_id)
INNER JOIN usage_records ON (usage_records.profile_id = profiles.id)

GROUP BY DAY
ORDER BY DAY asc