sql - 如何计算此 sql 查询中为唯一 ID 返回的行数？

Question

此查询返回我将定义为“召回”的所有行。结果见截图。我已经尝试过，但迄今为止未能弄清楚如何对每个 id 的行数（每一个不同的召回）求和。在下面的结果截图中，只显示了一个 ID：61401。但实际结果中还有其他 ID 较低。我想计算每个唯一 ID 的行数（召回）。我不断遇到汇总错误。我尝试了子查询和“分组依据”，但就是想不通。任何想法我怎么能做到这一点？最终我想要两列：id 和recall_count。

SELECT 
prr_u.id, 
cprr_r.created_at AS recall_create_date, 
prr_m.created_at AS trial_start_date, 
prr_m.trial_days AS trial_period_length, 
prr_m.created_at + interval '1 day' * trial_period_length AS trial_end_date
FROM my_db.productionrr_users AS prr_u
INNER JOIN my_db.productionrr_memberships AS prr_m
ON prr_u.id = prr_m.id
INNER JOIN my_db.c4productionrr_recalls AS cprr_r
ON cprr_r.user_id = prr_m.id
WHERE recall_create_date BETWEEN trial_start_date AND trial_end_date;

这是结果的一个片段：

我希望看到看起来像这样的结果（recalls_count 只是每个 id 返回的行数）：

我还在这里尝试了 Tan 的建议，并获得了汇总的 DB Errorcode=500310。

SELECT
prr_u.id as id,
cprr_r.created_at AS recall_create_date, 
prr_m.created_at AS trial_start_date, 
prr_m.trial_days AS trial_period_length, 
prr_m.created_at + interval '1 day' * trial_period_length AS trial_end_date,
COUNT(DISTINCT recall_create_date) as recall_count 
FROM my_db.productionrr_users AS prr_u
INNER JOIN my_db.productionrr_memberships AS prr_m
ON prr_u.id = prr_m.id
INNER JOIN my_db.c4productionrr_recalls AS cprr_r
ON cprr_r.user_id = prr_m.id
WHERE recall_create_date BETWEEN trial_start_date AND trial_end_date
GROUP BY prr_u.id;

此外，我能够计算查询中的总行数，但我仍然无法为每个 id 执行此操作：

SELECT COUNT(*) FROM (
SELECT prr_u.id, prr_u.email, prr_m.status, cprr_r.created_at AS recall_create_date, prr_m.created_at AS trial_start_date, prr_m.trial_days AS trial_period_length, prr_m.created_at + interval '1 day' * trial_period_length AS trial_end_date, prr_m.expires_at
FROM my_db.productionrr_users AS prr_u
INNER JOIN my_db.productionrr_memberships AS prr_m
ON prr_u.id = prr_m.id
INNER JOIN my_db.c4productionrr_recalls AS cprr_r
ON cprr_r.user_id = prr_m.id
WHERE recall_create_date BETWEEN trial_start_date AND trial_end_date
);

输出是总计数：

@gmb 我无法克服这个聚合函数错误。我已经尝试过了，因为我无法按原样运行您的代码：

SELECT prr_u.id,
prr_m.created_at + interval '1 day' * prr_m.trial_days AS trial_end_date,
COUNT(*) AS recalls_count
FROM my_db.productionrr_users AS prr_u
INNER JOIN my_db.productionrr_memberships AS prr_m 
ON prr_u.id = prr_m.id
INNER JOIN my_db.c4productionrr_recalls AS cprr_r 
ON cprr_r.user_id = prr_m.id
WHERE cprr_r.created_at BETWEEN prr_m.created_at AND trial_end_date
GROUP BY prr_u.id;

这会产生以下聚合错误：

`

标签： sqlpostgresqljoingroup-bycount

Answer 1

看起来你只想要聚合：

SELECT prr_u.id, COUNT(*) recalls_count
FROM my_db.productionrr_users AS prr_u
INNER JOIN my_db.productionrr_memberships AS prr_m ON prr_u.id = prr_m.id
INNER JOIN my_db.c4productionrr_recalls AS cprr_r ON cprr_r.user_id = prr_m.id
WHERE recall_create_date BETWEEN trial_start_date AND trial_end_date
GROUP BY ppr_u.id;